Re: 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."

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(at)gmail(dot)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