On Thu, May 24, 2012 at 4:26 PM, Sergey Koposov <koposov@ast.cam.ac.uk> wrote:
> On Thu, 24 May 2012, Jeff Janes wrote:
>>
>> Add
>> #define LWLOCK_STATS
>> near the top of:
>> src/backend/storage/lmgr/lwlock.c
>>
>> and recompile and run a reduced-size workload. When the processes
>> exits, they will dump a lot of data about LWLock usage to the logfile.
>> Generally the LWLock with the most blocks on it will be the main
>> culprit.
>
>
> Here is the output from a multi-threaded run (8thtreads, 22 seconds) sorted
> by blk. Not sure whether that's of much use or not:
>
> PID 7112 lwlock 48: shacq 1124394 exacq 1350 blk 1373
> PID 7110 lwlock 48: shacq 1124460 exacq 1128 blk 1110
> PID 7114 lwlock 48: shacq 1124502 exacq 1041 blk 976
> PID 7111 lwlock 48: shacq 1124523 exacq 1009 blk 955
> PID 7113 lwlock 48: shacq 1124383 exacq 868 blk 871
> PID 7112 lwlock 44: shacq 1127148 exacq 1323 blk 838
> PID 7110 lwlock 44: shacq 1127256 exacq 1132 blk 774
> PID 7114 lwlock 44: shacq 1127418 exacq 1024 blk 702
> PID 7113 lwlock 44: shacq 1127179 exacq 920 blk 665
> PID 7111 lwlock 44: shacq 1127324 exacq 957 blk 651
> PID 7109 lwlock 48: shacq 1124402 exacq 384 blk 602
> PID 7108 lwlock 48: shacq 1125039 exacq 1592 blk 546
> PID 7108 lwlock 44: shacq 1127902 exacq 1548 blk 511
> PID 7109 lwlock 44: shacq 1127261 exacq 388 blk 466
That is not an informative as I thought it would be (except to show
WAL was not the issue).
I'm guessing that 44 and 48 are the buffer mapping partitions which
cover the root block of some highly used index.
But just because those things are at the top of the list doesn't mean
they are a problem. Something has to be at the top, and they don't
dominate the total number of blocking they way I would expect them to
if they were truly a substantial bottleneck.
Cheers,
Jeff