4 ms·

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 differenc

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 http://i.imgur.com/L8NWO.png) and load average (http://i.imgur.com/7793A.png 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.

jeltz 15y ago
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-fbc26b4522e4e990a9ea1aebd4a73a68e8ee5e07 http://kernelnewbies.org/Linux_3.2#head-fbc26b4522e4e990a9ea...
justauser 15y ago
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.
- lobster_johnson 15y ago
  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.
andyzweb 15y ago
what distros are offering a 3.2 kernel?
- lmz 15y ago
  Debian Backports has a 3.2 kernel for Squeeze.
- psadauskas 15y ago
  Ubuntu 12.04 will, when its released later this month.
- pbreit 15y ago
  The soon-to-be released Ubuntu 12.04.
- efuquen 15y ago
  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.
- exabytes18 15y ago
  Fedora 16 is on 3.3.0.
- sciurus 15y ago
  Debian testing.
- andrewcooke 15y ago
  opensuse tumbleweed (install 12.1 and then follow instructions at http://en.opensuse.org/Portal:Tumbleweed 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).
- dandrews 15y ago
  Gentoo.
kingmanaz 15y ago
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.
- bitsaplenty 15y ago
  That graph looks like it was generated by rrdtool (http://oss.oetiker.ch/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/ http://www.cacti.net/). It has quite a few plugins to monitor other stuff as well. It also works for remote servers via snmp.
- btbuilder 15y ago
  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.
- jeltz 15y ago
  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.
- jpitz 15y ago
  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-Smith/dp/184951030X http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm... [2] http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/059600527X http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Mil... [3] http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476 http://www.amazon.com/Performance-Tuning-Server-Dynamic-Mana...

jeltz 15y ago

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-fbc26b4522e4e990a9ea1aebd4a73a68e8ee5e07 http://kernelnewbies.org/Linux_3.2#head-fbc26b4522e4e990a9ea...

justauser 15y ago

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.

lobster_johnson 15y ago
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.

andyzweb 15y ago

what distros are offering a 3.2 kernel?

lmz 15y ago
Debian Backports has a 3.2 kernel for Squeeze.
psadauskas 15y ago
Ubuntu 12.04 will, when its released later this month.
pbreit 15y ago
The soon-to-be released Ubuntu 12.04.
efuquen 15y ago
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.
exabytes18 15y ago
Fedora 16 is on 3.3.0.
sciurus 15y ago
Debian testing.
andrewcooke 15y ago
opensuse tumbleweed (install 12.1 and then follow instructions at http://en.opensuse.org/Portal:Tumbleweed 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).
dandrews 15y ago
Gentoo.

kingmanaz 15y ago

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.

bitsaplenty 15y ago
That graph looks like it was generated by rrdtool (http://oss.oetiker.ch/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/ http://www.cacti.net/). It has quite a few plugins to monitor other stuff as well. It also works for remote servers via snmp.
btbuilder 15y ago
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.
jeltz 15y ago
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.
jpitz 15y ago
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-Smith/dp/184951030X http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm... [2] http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/059600527X http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Mil... [3] http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476 http://www.amazon.com/Performance-Tuning-Server-Dynamic-Mana...