Hacker News Reader

Comment by rosser | Hacker News Reader

As much as I love PostgreSQL (and I do; it's put food on my table for the last decade), I have to stress that this linear-ish scalability needs both pg >= 9.2 and a Linux kernel >= 3.2. It seems to be a combination of the lseek(2) changes in the kernel, and the lock contention/handling changes in the db.

That is: if you're running on an older kernel, you probably won't see quite as much gain.

View on HN

We upgraded from 2.6 to 3.2 recently and our Postgres has been flying ever since. The scheduler changes (and presumably the lseek changes) make a huge difference in load. We have not done any performance timings, though, so we don't know if the changes translate to better performance.

To be specific, the change reduced read I/O (http://i.imgur.com/L8NWO.png) and load average (http://i.imgur.com/7793A.png) both by an order of magnitude, and the variance is much tighter than before. (The system is a dual Xeon quad-core X5355/2.66GHz with 32GB RAM and RAID5.)

That improvement was fairly miraculous — a factor of 10x just by upgrading a kernel is not something that happens every day. Still, I would not be surprised if Postgres 9.2 pushes performance even higher.

There is another change of note in Linux 3.2 which could matter PostgreSQL, the major changes to how writebacks of dirty pages works.[1] Now in your case the changes where mostly to read perfromance so I do not think the writeback changes mattered here.

1. http://kernelnewbies.org/Linux_3.2#head-fbc26b4522e4e990a9ea...

This is going to sound pathetic and off topic, but do you have pointers toward any good materials on database performance benchmarking? Your screenshots display exactly the kinds of presentation I am after, yet I've never managed to progress beyond windows performance monitor and a pile of unorganized spreadsheets. I keep getting more and more DBA work dumped on me and it seems like somewhere I've missed the principles of the art of DBA (if there are any). Other than some Celko and Date, everything I come in contact with has market-speak and corporate buzzwords written all over it.

Greg Smith's PostgreSQL 9.0 High Performance [1] Cary Milsap's Optimizing Oracle Performance [2], and - a pale third, Davidson and Ford's Performance Tuning with SQL Server Dynamic Management Views [3]

The first 2 are bottom-up, structured approaches to benchmarking low-level system performance with an emphasis on *nix, and building up to database performance characterization and investigation. Despite their names, both have a lot of great general, non-product-specific use.

The third I include because it is the only MSSQL-specific book I have on the subject, and it sounds like you're in Windows Land. It has some real gems, but little coverage of the OS or methodology. I cannot over-emphasize how important that methodology is.

Make sure you learn how to use the Resource Monitor, and SQL Profiler.

If you want to chat about it more, I'm justinpitts at google's mail.

[1] http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm... [2] http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Mil... [3] http://www.amazon.com/Performance-Tuning-Server-Dynamic-Mana...

That graph looks like it was generated by rrdtool (http://oss.oetiker.ch/rrdtool/). If you are looking for monitoring of stats like system load, disk io, etc, check out cacti (http://www.cacti.net/). It has quite a few plugins to monitor other stuff as well. It also works for remote servers via snmp.

For motioning performance of Linux machines I recommend using munin. It has excellent PostgreSQL plugins, most of them written by a member of the PostgreSQL core team (Thanks Magnus!).

cacti is also supposed to be good but I have never used it myself.

The graphs are produced by a web frontend (I forget the name) that reads from Collectd data. Collectd runs on each box and collects performance data that can be graphed on a central box.

We used Munin previously, and I must admit that Collectd feels like a step backwards. Both are very primitive, so we might look for something better soon, perhaps Graphite.

I recommend migrating to Linux (or one of the BSDs) if you want to get serious about system administration. The wealth of mature tools is vastly superior.

The Postgres book by Greg Smith is indispensible if you want to tune a Postgres installation.

One thing to be careful of when producing benchmarks for databases is making sure you avoid artificial testing scenarios that don't match your actual workload.

If you don't already, understanding your concurrency and read/write patterns will go a long way to help you identify which benchmarks are meaningful for you.

That's a tremendous improvement. Obviously, I don't know the particulars of your situation but are you planning to update to more current hardware? The 5300 Cloverton was 65nm process and 2006 era. I'd be curious about the effect of using the 3.2+ kernel on the new e5-2600 Sandybridge process.

We have a new cluster that's intended to replace the current one, which as you point out is running on 2006 hardware. (It still performs extremely well, however.)

Those numbers would presumably translate pretty well to newer CPUs as the numbers reflect the change in read I/O. Newer CPUs would handle the load faster, but the read traffic would be the same.

what distros are offering a 3.2 kernel?

Ubuntu 12.04 will, when its released later this month.

Debian Backports has a 3.2 kernel for Squeeze.

Fedora 16 is on 3.3.0.

Arch, which should be moving to 3.3 soon. Obviously not really a server distro, but still an option if you really want/need bleeding edge.

opensuse tumbleweed (install 12.1 and then follow instructions at http://en.opensuse.org/Portal:Tumbleweed).

not as stable as a standard release though, in my experience (which was a while ago - stability is an important aim for the project, so they may have got better).

The soon-to-be released Ubuntu 12.04.

Gentoo.

Debian testing.

At my last company one of the biggest complaints against postgreSQL (when being compared to oracle) was that it "could only scale to 20 cores", which made it unsuitable for "enterprise use". Nice to see that non-issue removed.

Hey Rosser! Didn't know you hung out here.

Shane, I think most of the complaints about postgres at said company were made out of a pre-existing bias towards the expensive commercial solution, rather than against the (at the time, admittedly) somewhat less capable FOSS project. When I was asked about vertical scalability, I told them "16-20 cores right now, but at the rate they're improving things, it'll be 32, and then 64 in the next 3-5 years." Nice to see things tracking so closely with my predictions.

The lseek scalability issue was not very noticeable on PostgreSQL 9.1, but first became obvious after work had started on fixing the scalability problems in PostgreSQL itself.

http://rhaas.blogspot.se/2011/08/linux-and-glibc-scalability...

Does that mean that FreeBSD will become a bit of a second-class-citizen with the release of 9.2? Or are similar optimizations available and planned there?

The llseek scalability issue was caused by the Linux kernel taking a lock on a data structure when no such lock was necessary. While it is possible that FreeBSD has the same problem with llseek, I see no reason to assume that is the case.

FreeBSD probably works as well as Linux >= 3.2 when it comes to llseek, but there might have some other scalability issue which is not present in Linux.

EDIT: Here is a link to the Linux kernel patch, PostgreSQL uses SEEK_END to check the file sizes.

https://lkml.org/lkml/2011/9/15/401

Reply on news.ycombinator.com