Re: Sun Donated a Sun Fire T2000 to the PostgreSQL

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Tom Lane wrote:

Tatsuo Ishii <ishii@xxxxxxxxxxxx> writes:
18% in s_lock is definitely bad :-(.  Were you able to determine which
LWLock(s) are accounting for the contention?

Sorry for the delay. Finally I got the oprofile data. It's
huge(34MB). If you are interested, I can put somewhere. Please let me
know.

I finally got a chance to look at this, and it seems clear that all the
traffic is on the BufMappingLock.  This is essentially the same problem
we were discussing with respect to Gavin Hamill's report of poor
performance on an 8-way IBM PPC64 box (see hackers archives around
2006-04-21).  If your database is fully cached in shared buffers, then
you can do a whole lot of buffer accesses per unit time, and even though
all the BufMappingLock acquisitions are in shared-LWLock mode, the
LWLock's spinlock ends up being heavily contended on an SMP box.

It's likely that CVS HEAD would show somewhat better performance because
of the btree change to cache local copies of index metapages (which
eliminates a fair fraction of buffer accesses, at least in Gavin's test
case).   Getting much further than that seems to require partitioning
the buffer mapping table.  The last discussion stalled on my concerns
about unpredictable shared memory usage, but I have some ideas on that
which I'll post separately.  In the meantime, thanks for sending along
the oprofile data!

			regards, tom lane
I ran pgbench and fired up a DTrace script using the lwlock probes we've added, and it looks like BufMappingLock is the most contended lock, but CheckpointStartLocks are held for longer duration!

            Lock Id            Mode           Count
    ControlFileLock       Exclusive               1
SubtransControlLock       Exclusive               1
   BgWriterCommLock       Exclusive               6
      FreeSpaceLock       Exclusive               6
   FirstLockMgrLock       Exclusive              48
    BufFreelistLock       Exclusive              74
     BufMappingLock       Exclusive              74
    CLogControlLock       Exclusive             184
         XidGenLock       Exclusive             184
CheckpointStartLock          Shared             185
       WALWriteLock       Exclusive             185
      ProcArrayLock       Exclusive             368
    CLogControlLock          Shared             552
SubtransControlLock          Shared            1273
      WALInsertLock       Exclusive            1476
         XidGenLock          Shared            1842
      ProcArrayLock          Shared            3160
         SInvalLock          Shared            3684
     BufMappingLock          Shared           14578

            Lock Id   Combined Time (ns)
    ControlFileLock                 7915
   BgWriterCommLock                43438
      FreeSpaceLock               111139
    BufFreelistLock               448530
   FirstLockMgrLock              2879957
    CLogControlLock              4237750
SubtransControlLock              6378042
         XidGenLock              9500422
      WALInsertLock             16372040
         SInvalLock             23284554
      ProcArrayLock             32188638
     BufMappingLock            113128512
       WALWriteLock            142391501
CheckpointStartLock           4171106665


Regards,
-Robert


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux