Re: Lots of stuck queries after upgrade to 9.4 - Mailing list pgsql-general

On 07/28/2015 11:36 PM, Heikki Linnakangas wrote:
> A-ha, I succeeded to reproduce this now on my laptop, with pgbench! It
> seems to be important to have a very large number of connections:
>
> pgbench -n -c400 -j4 -T600 -P5
>
> That got stuck after a few minutes. I'm using commit_delay=100.
>
> Now that I have something to work with, I'll investigate this more tomorrow.

Ok, it seems that this is caused by the same issue that I found with my
synthetic test case, after all. It is possible to get a lockup because
of it.

For the archives, here's a hopefully easier-to-understand explanation of
how the lockup happens. It involves three backends. A and C are
insertion WAL records, while B is flushing the WAL with commit_delay.
The byte positions 2000, 2100, 2200, and 2300 are offsets within a WAL
page. 2000 points to the beginning of the page, while the others are
later positions on the same page. WaitToFinish() is an abbreviation for
WaitXLogInsertionsToFinish(). "Update pos X" means a call to
WALInsertLockUpdateInsertingAt(X). "Reserve A-B" means a call to
ReserveXLogInsertLocation, which returned StartPos A and EndPos B.

Backend A        Backend B        Backend C
---------        ---------        ---------
Acquire InsertLock 2
Reserve 2100-2200
            Calls WaitToFinish()
              reservedUpto is 2200
              sees that Lock 1 is
              free
                        Acquire InsertLock 1
                        Reserve 2200-2300
                        GetXLogBuffer(2200)
                         page not in cache
                         Update pos 2000
                         AdvanceXLInsertBuffer()
                          run until about to
                          acquire WALWriteLock
GetXLogBuffer(2100)
  page not in cache
  Update pos 2000
  AdvanceXLInsertBuffer()
   Acquire WALWriteLock
   write out old page
   initialize new page
   Release WALWriteLock
finishes insertion
release InsertLock 2
            WaitToFinish() continues
              sees that lock 2 is
              free. Returns 2200.

            Acquire WALWriteLock
            Call WaitToFinish(2200)
              blocks on Lock 1,
              whose initializedUpto
              is 2000.

At this point, there is a deadlock between B and C. B is waiting for C
to release the lock or update its insertingAt value past 2200, while C
is waiting for WALInsertLock, held by B.

To fix that, let's fix GetXLogBuffer() to always advertise the exact
position, not the beginning of the page (except when inserting the first
record on the page, just after the page header, see comments).

This fixes the problem for me. I've been running pgbench for about 30
minutes without lockups now, while without the patch it locked up within
a couple of minutes. Spiros, can you easily test this patch in your
environment? Would be nice to get a confirmation that this fixes the
problem for you too.

- Heikki