Re: Lots of stuck queries after upgrade to 9.4 - Mailing list pgsql-general
You might want to try pg replay: http://laurenz.github.io/pgreplay/
On Thu, Jul 30, 2015 at 7:23 AM, Spiros Ioannou <sivann@inaccess.com> wrote:
I'm very sorry but we don't have a synthetic load generator for our testing setup, only production and that is on SLA. I would be happy to test the next release though.On 29 July 2015 at 13:42, Heikki Linnakangas <hlinnaka@iki.fi> wrote: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
--
To understand recursion, one must first understand recursion.
pgsql-general by date: