News for hackers. Stuff that matters.
Re:Postgres 9.2 will feature linear read scalability up to 6... by lobster_johnson on Tuesday April 03, 2012 @05:50PM (#3794967)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by jeltz on Tuesday April 03, 2012 @05:58PM (#3795008)
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...

Re:Postgres 9.2 will feature linear read scalability up to 6... by kingmanaz on Tuesday April 03, 2012 @07:13PM (#3795286)
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.
Re:Postgres 9.2 will feature linear read scalability up to 6... by jpitz on Wednesday April 04, 2012 @07:39AM (#3797082)
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...

Re:Postgres 9.2 will feature linear read scalability up to 6... by bitsaplenty on Tuesday April 03, 2012 @08:49PM (#3795610)
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.
Re:Postgres 9.2 will feature linear read scalability up to 6... by jeltz on Wednesday April 04, 2012 @03:03AM (#3796502)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by lobster_johnson on Friday April 06, 2012 @03:16PM (#3808474)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by btbuilder on Wednesday April 04, 2012 @02:34AM (#3796439)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by justauser on Tuesday April 03, 2012 @06:01PM (#3795012)
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.
Re:Postgres 9.2 will feature linear read scalability up to 6... by lobster_johnson on Friday April 06, 2012 @03:10PM (#3808456)
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.

Re:Postgres 9.2 will feature linear read scalability up to 6... by andyzweb on Tuesday April 03, 2012 @07:04PM (#3795253)
what distros are offering a 3.2 kernel?
Re:Postgres 9.2 will feature linear read scalability up to 6... by psadauskas on Tuesday April 03, 2012 @07:47PM (#3795398)
Ubuntu 12.04 will, when its released later this month.
Re:Postgres 9.2 will feature linear read scalability up to 6... by lmz on Tuesday April 03, 2012 @07:12PM (#3795281)
Debian Backports has a 3.2 kernel for Squeeze.
Re:Postgres 9.2 will feature linear read scalability up to 6... by exabytes18 on Tuesday April 03, 2012 @09:56PM (#3795825)
Fedora 16 is on 3.3.0.
Re:Postgres 9.2 will feature linear read scalability up to 6... by efuquen on Tuesday April 03, 2012 @07:54PM (#3795432)
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.
Re:Postgres 9.2 will feature linear read scalability up to 6... by andrewcooke on Wednesday April 04, 2012 @09:03AM (#3797326)
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).

Re:Postgres 9.2 will feature linear read scalability up to 6... by pbreit on Tuesday April 03, 2012 @07:47PM (#3795401)
The soon-to-be released Ubuntu 12.04.
Re:Postgres 9.2 will feature linear read scalability up to 6... by dandrews on Wednesday April 04, 2012 @12:04PM (#3798163)
Gentoo.
Re:Postgres 9.2 will feature linear read scalability up to 6... by sciurus on Wednesday April 04, 2012 @01:47AM (#3796350)
Debian testing.