Re: write scalability

On Mon, Jul 25, 2011 at 4:07 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> As to what that something might be, I reran this last test with
> LWLOCK_STATS enabled and here are the top things that are blocking:
>
> lwlock 310: shacq 96846 exacq 108433 blk 16275
> lwlock 3: shacq 64 exacq 2628381 blk 36027
> lwlock 7: shacq 0 exacq 2628615 blk 85220
> lwlock 11: shacq 84913908 exacq 4539551 blk 2119423
> lwlock 4: shacq 28667307 exacq 2628524 blk 3356651
>
> During this 5-minute test run, an LWLock acquisition blocked 6180335
> times.  As you can see from the above results, ProcArrayLock accounts
> for 54% of that blocking, and CLogControlLock accounts for another
> 34%.  lwlock 7 is WALInsertLock, which manages to account for more
> than 1% of the blocking despite the fact that WAL has been largely
> eliminated in this test...

I reran this test on master with permanent (not unlogged) tables, and
got the following stats for blocking:

lwlock 11: shacq 58376164 exacq 2122076 blk 1130974
lwlock 4: shacq 15168924 exacq 1367303 blk 1555799
lwlock 7: shacq 0 exacq 8440615 blk 1726896
grand total: shacq 144563929 exacq 41618873 blk 4886107

So that's 35% WALInsertLock, 32% ProcArrayLock, and 23%
CLogControlLock. No other single lock accounted for more than 1% of
the blocking. It's a good guess we're going to have to fix more than
one thing to really make this zippy.

Also note that CLogControlLock accounts for better than 40% of the
shared-lock acquisitions across all LWLocks. The next-most-frequently
share-locked LWLock is ProcArrayLock, with 10% of the total
shared-lock acquisitions, followed by SInvalReadLock, at 9%.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company