Thread: PostgreSQL optimisations on Linux machines with more than 24 cores
Hello all ... again !
SHORT INTRODUCTION
===================
Remember me? A couple of years ago (maybe 10) I created the PgAccess package for managing PostgreSQL!
Since than I have done many things, all of them related to PostgreSQL, of course!
Our company is managing the National Animal Tracking and Registering Database in Romania, a pretty big database on a 24 core Intel Xeon machine with 32 Gb RAM
THE PROBLEM
============
After many months of optimisation and fine tuning, getting the most out of our machine, I just found an article regarding Linux scalability on many cores, including an analysis of other different programs, including PostgreSQL: http://pdos.csail.mit.edu/papers/linux:osdi10.pdf
"This paper analyzes the scalability of seven system applications (Exim, memcached, Apache, PostgreSQL, gmake, Psearchy, and MapReduce) running on Linux on a 48-core computer. Except for gmake, all applications trigger scalability bottlenecks inside a recent Linux kernel. Using mostly standard parallel programming techniques— this paper introduces one new technique, sloppy counters—these bottlenecks can be removed from the kernel or avoided by changing the applications slightly. Modifying the kernel required in total 3002 lines of code changes. A speculative conclusion from this analysis is that there is no scalability reason to give up on traditional operating system organizations just yet."
"This paper analyzes the scalability of seven system applications (Exim, memcached, Apache, PostgreSQL, gmake, Psearchy, and MapReduce) running on Linux on a 48-core computer. Except for gmake, all applications trigger scalability bottlenecks inside a recent Linux kernel. Using mostly standard parallel programming techniques— this paper introduces one new technique, sloppy counters—these bottlenecks can be removed from the kernel or avoided by changing the applications slightly. Modifying the kernel required in total 3002 lines of code changes. A speculative conclusion from this analysis is that there is no scalability reason to give up on traditional operating system organizations just yet."
There are a couple of recommendations there for PostgreSQL, small changes in PostgreSQL source (ex. increasing the number of mutexes from 16 to 1024, row and table-level locking) other in Linux kernel (modified lseek) that seems to be effective for scaling beyond 24 cores.
THE QUESTION
=============
The article says that the tests were done using a PostgreSQL 8.3.9 version.
Is the new 9.1.3 PostgreSQL using some of the patches recommended in that article that would enhance multi-core scalability?
Thank you very much for your attention,
Constantin Teodorescu
On Sun, Mar 25, 2012 at 7:14 AM, Constantin Teodorescu <brailateo@gmail.com> wrote: > "This paper analyzes the scalability of seven system applications (Exim, > memcached, Apache, PostgreSQL, gmake, Psearchy, and MapReduce) running on > Linux on a 48-core computer. Except for gmake, all applications > trigger scalability bottlenecks inside a recent Linux kernel. Using mostly > standard parallel programming techniques— this paper introduces one new > technique, sloppy counters—these bottlenecks can be removed from the > kernel or avoided by changing the applications slightly. Modifying the > kernel required in total 3002 lines of code changes. A speculative > conclusion from this analysis is that there is no scalability reason to give > up on traditional operating system organizations just yet." > > There are a couple of recommendations there for PostgreSQL, small changes > in PostgreSQL source (ex. increasing the number of mutexes from 16 to 1024, > row and table-level locking) other in Linux kernel (modified lseek) that > seems to be effective for scaling beyond 24 cores. Linux 3.2 includes lseek modifications to improve scalability. PostgreSQL 9.2 will include significant lock manager changes; we did not adopt their proposal of just bumping up the number of lock manager locks, as that doesn't help in common cases, such as when lots of concurrent backends are accessing the same table, and it has other downsides, such as slowing down operations that must touch multiple lock manager partitions. Rather, we introduced a "fast path" for common cases that bypasses the main lock manager altogether; thus, traffic on the lock manager locks will be dramatically reduced in PostgreSQL 9.2. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company