Re: Stalls on PGSemaphoreLock - Mailing list pgsql-performance

From Takashi Horikawa
Subject Re: Stalls on PGSemaphoreLock
Date
Msg-id 73FA3881462C614096F815F75628AFCD0192D27F@BPXM01GP.gisp.nec.co.jp
Whole thread Raw
In response to Stalls on PGSemaphoreLock  (Matthew Spilich <mspilich@tripadvisor.com>)
List pgsql-performance
On Mar 25, 2014, at 8:46 AM, Matthew Spilich wrote:
> Has any on the forum seen something similar?

I think I reported similar phenomenon in my SIGMOD 2013 paper (Latch-free
data structures for DBMS: design, implementation, and evaluation,
<http://dl.acm.org/citation.cfm?id=2463720>).

> ----- 47245 -----
> 0x00000037392eb197 in semop () from /lib64/libc.so.6
> #0  0x00000037392eb197 in semop () from /lib64/libc.so.6
> #1  0x00000000005e0c87 in PGSemaphoreLock ()
> #2  0x000000000061e3af in LWLockAcquire ()
> #3  0x000000000060aa0f in ReadBuffer_common ()
> #4  0x000000000060b2e4 in ReadBufferExtended ()
...

> ----- 47257 -----
> 0x00000037392eb197 in semop () from /lib64/libc.so.6
> #0  0x00000037392eb197 in semop () from /lib64/libc.so.6
> #1  0x00000000005e0c87 in PGSemaphoreLock ()
> #2  0x000000000061e3af in LWLockAcquire ()
> #3  0x000000000060aa0f in ReadBuffer_common ()
> #4  0x000000000060b2e4 in ReadBufferExtended ()
...

These stack trace results indicate that there was heavy contention of
LWLocks for buffers. What I observed is that, in a similar situation, there
was also heavy contention on spin locks that ensure mutual exclusion of
LWLock status data. Those contentions resulted in a sudden increase in CPU
utilization, which is consistent with the following description.
> At the time of the event, we see a spike in system CPU and load average,
but we do not see a corresponding spike in disk reads or writes which would
indicate IO load.

If the cause of the problem is the same as what I observed, a possible
instant countermeasure is increasing the value of 'NUM_BUFFER_PARTITIONS'
defined in src/include/storage/lwlock.h from 16 to, for example, 128 or 256,
and build the binary.
# Using latch-free buffer manager, proposed in my paper, would take long
time, since it is not unincorporated in the upstream.

--
Takashi Horikawa, Ph.D.,
Knowledge Discovery Research Laboratories,
NEC Corporation.


Attachment

pgsql-performance by date:

Previous
From: Emre Hasegeli
Date:
Subject: Re: Stalls on PGSemaphoreLock
Next
From: Alexey Vasiliev
Date:
Subject: Why shared_buffers max is 8GB?