6 ms·

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 a

by rosser 15y ago

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.

shanemhansen 15y ago
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.
- rosser 15y ago
  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.
jeltz 15y ago
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.html http://rhaas.blogspot.se/2011/08/linux-and-glibc-scalability...
ssmoot 15y ago
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?
- jeltz 15y ago
  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 https://lkml.org/lkml/2011/9/15/401
lobster_johnson 15y ago
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.
- 15y ago

by rosser 15y ago

shanemhansen 15y ago

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.

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

jeltz 15y ago

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.html http://rhaas.blogspot.se/2011/08/linux-and-glibc-scalability...

ssmoot 15y ago

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?

jeltz 15y ago
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 https://lkml.org/lkml/2011/9/15/401

lobster_johnson 15y ago

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