Postgresql on multi-core CPU's: is this old news?
Came across the following in a paper from Oct 2010. Was wondering is this is old news I missed in this group.
http://pdos.csail.mit.edu/papers/linux:osdi10.pdf
about Linux optimization on multi-core CPU's.
The group at MIT were exploring how some Linux apps were scaling up --- sometimes badly, mostly due to hidden contention over cache-line consistency across the cores' caches.
In a nutshell: if an app, or the system calls it uses, tries to modify anything in a cache line (32-64 byte slice of memory) that another core is using, there's a lot of fumbling in the dark to make sure there is no conflict. When I saw PostgreSQL named in the abstract, I thought, "Aha! Contention over shm". Not so. Skip to page 11 (section 5.5) for most of the PG specifics.
On Tue, Apr 5, 2011 at 2:21 PM, Mischa Sandberg
<mischa.sandberg@sophos.com> wrote:
Came across the following in a paper from Oct 2010. Was wondering is this is
old news I missed in this group.http://pdos.csail.mit.edu/papers/linux:osdi10.pdf
about Linux optimization on multi-core CPU’s.
The group at MIT were exploring how some Linux apps were scaling up ---
sometimes badly, mostly due to hidden contention over cache-line consistency
across the cores’ caches.In a nutshell: if an app, or the system calls it uses, tries to modify
anything in a cache line (32-64 byte slice of memory) that another core is
using, there’s a lot of fumbling in the dark to make sure there is no
conflict. When I saw PostgreSQL named in the abstract, I thought, “Aha!
Contention over shm”. Not so. Skip to page 11 (section 5.5) for most of the
PG specifics.
Someone posted this before, but unfortunately making this really work
in PG is more of a research project than something we can just go do.
I made a stab at writing a spinlock-free version of the LWLock code a
few months ago (which is one of the things they did in the paper) and
I wasn't able to show a lick of benefit. Part of that may be because
I didn't have access to anything bigger than an 8-core box, but it's
also because these things are fairly workload-dependent. In the test
cases I tried I kept bottlenecking on WALInsertLock or, on read-only
workloads, the lock manager partition lock for whichever table I was
hitting, and the changes they made don't address those bottlenecks.
As they write - regarding their benchmark - "This workload is
intended to minimize application-level contention within PostgreSQL in
order to maximize the stress PostgreSQL places on the kernel." -- i.e.
PostgreSQL wasn't really the thing they were trying to stress. It's
interesting stuff - I'm just not sure how much near-term practical
benefit we can get out of it.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 04/05/2011 02:21 PM, Mischa Sandberg wrote:
Came across the following in a paper from Oct 2010. Was wondering is
this is old news I missed in this group.http://pdos.csail.mit.edu/papers/linux:osdi10.pdf
about Linux optimization on multi-core CPU's.
Only a little old;
http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html
shows most of the obvious comments to be made about it. There is more
detail explaining why the hand-waving done in the paper about increasing
NUM_LOCK_PARTITIONS is not a simple improvement at
http://postgresql.1045698.n5.nabble.com/Lock-partitions-td1952557.html
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
On Apr 7, 2011, at 1:13 AM, Greg Smith wrote:
On 04/05/2011 02:21 PM, Mischa Sandberg wrote:
Came across the following in a paper from Oct 2010. Was wondering is this is old news I missed in this group.
http://pdos.csail.mit.edu/papers/linux:osdi10.pdf
about Linux optimization on multi-core CPU’s.Only a little old; http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html shows most of the obvious comments to be made about it. There is more detail explaining why the hand-waving done in the paper about increasing NUM_LOCK_PARTITIONS is not a simple improvement at http://postgresql.1045698.n5.nabble.com/Lock-partitions-td1952557.html
Given that when those tests were done 16 cores was a massive machine, it would probably be a good idea to run them again. If anyone is interested in doing that let me know; we have a 40 core machine that I could probably arrange access to.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net