Обсуждение: Postgresql on multi-core CPU's: is this old news?
<div class="WordSection1"><p class="MsoNormal">Came across the following in a paper from Oct 2010. Was wondering is thisis old news I missed in this group.<p class="MsoNormal"><a href="http://pdos.csail.mit.edu/papers/linux:osdi10.pdf">http://pdos.csail.mit.edu/papers/linux:osdi10.pdf</a><p class="MsoNormal">aboutLinux optimization on multi-core CPU’s.<p class="MsoNormal"> <p class="MsoNormal">The group at MITwere exploring how some Linux apps were scaling up --- sometimes badly, mostly due to hidden contention over cache-lineconsistency across the cores’ caches.<p class="MsoNormal">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 fumblingin the dark to make sure there is no conflict. When I saw PostgreSQL named in the abstract, I thought, “Aha! Contentionover shm”. Not so. Skip to page 11 (section 5.5) for most of the PG specifics.</div>
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: <blockquote
cite="mid:CB0F73E165CFFA4496D12161D835562C030AE939BA@US-COL-EXCHMBX1.green.sophos"type="cite"><style>
<!--/* Font Definitions */@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}/* Style Definitions
*/p.MsoNormal,li.MsoNormal,
div.MsoNormal{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink{mso-style-priority:99;color:blue;text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed{mso-style-priority:99;color:purple;text-decoration:underline;}
span.EmailStyle17{mso-style-type:personal-compose;font-family:"Calibri","sans-serif";color:windowtext;}
.MsoChpDefault{mso-style-type:export-only;}
@page WordSection1{size:8.5in 11.0in;margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1{page:WordSection1;}
--> </style><div class="WordSection1"><p class="MsoNormal">Came across the following in a paper from Oct 2010. Was
wonderingis this is old news I missed in this group.<p class="MsoNormal"><a
href="http://pdos.csail.mit.edu/papers/linux:osdi10.pdf"
moz-do-not-send="true">http://pdos.csail.mit.edu/papers/linux:osdi10.pdf</a><pclass="MsoNormal">about Linux
optimizationon multi-core CPU’s.</div></blockquote><br /> Only a little old; <a class="moz-txt-link-freetext"
href="http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html">http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html</a>
showsmost of the obvious comments to be made about it. There is more detail explaining why the hand-waving done in the
paperabout increasing NUM_LOCK_PARTITIONS is not a simple improvement at <a class="moz-txt-link-freetext"
href="http://postgresql.1045698.n5.nabble.com/Lock-partitions-td1952557.html">http://postgresql.1045698.n5.nabble.com/Lock-partitions-td1952557.html</a><br
/><br/><pre class="moz-signature" cols="72">--
Greg Smith 2ndQuadrant US <a class="moz-txt-link-abbreviated"
href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a> Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support <a class="moz-txt-link-abbreviated"
href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
"PostgreSQL 9.0 High Performance": <a class="moz-txt-link-freetext"
href="http://www.2ndQuadrant.com/books">http://www.2ndQuadrant.com/books</a>
</pre>
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