Re: how to investigate GIN fast updates and cleanup cycles? - Mailing list pgsql-general

From Jeff Janes
Subject Re: how to investigate GIN fast updates and cleanup cycles?
Date
Msg-id CAMkU=1xOt7=ANcEpo1E7BPWa=55Uj31=Zs0ajqEanbfN_KU+hw@mail.gmail.com
Whole thread Raw
In response to Re: how to investigate GIN fast updates and cleanup cycles?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Fri, Aug 28, 2015 at 10:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Steve Kehlet <steve.kehlet@gmail.com> writes:
> This is Postgres 9.4.4. I am troubleshooting some occasional (every 3-4
> hours) slowness with UPDATEs on a table that has a GIN index on a JSONB
> column. During these episodes, UPDATEs that normally take < 1sec take
> upwards of 2-4 minutes, and all finish simultaneously, like they were all
> blocked on something and finally got released.

Hm ... have you tried checking pg_locks to see if they're blocked on
something identifiable?

You might be right that this is caused by flushing the GIN pending list,
but I thought that that was not supposed to block concurrent insertions.
What I'd expect to see is *one* insert taking significantly longer than
normal, but no effect on concurrent operations. 

If they all have the same work_mem setting, then they all hit the pending list limit at the same time and all of them try to do the clean up.  None of them realize that other clean-ups are in progress until one process gets far enough along to actually start truncating away the pending list pages. At that point the others will soon detect the concurrent clean up and bail out, but the performance damage has already been done.

This isn't just a performance problem, it could also lead to very rare correctness bugs that are being discussed on "hackers" (http://www.postgresql.org/message-id/55D183FF.60307@iki.fi).

 
Also, 2-4 minutes sounds
much longer than should be needed to flush a 10MB pending list, anyway.

I've seen it take longer than that for 4MB pending lists, once the gin index doesn't fit in RAM.  10MB is 1280 pages. If that holds 1280 tuples and each tuple has 50 keys, that would be 64,000 keys.  If the keys don't have a lot of internal overlap inserting them would take 6.4 minutes with 10,000 rpm drives, assuming all the non-leaf pages are already in memory.  There is no pre-fetching (not even with multiple backends trying to do the same work at the same time), so a RAID won't speed things up.

Cheers,

Jeff

pgsql-general by date:

Previous
From: Steve Kehlet
Date:
Subject: Re: how to investigate GIN fast updates and cleanup cycles?
Next
From: Tom Lane
Date:
Subject: Re: how to investigate GIN fast updates and cleanup cycles?