Обсуждение: Postgresql on multi-core CPU's: is this old news?

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.htmlshows most of
theobvious comments to be made about it.  There is more detail explaining why the hand-waving done in the paper about
increasingNUM_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