Horton

Re: hung backends stuck in spinlock heavy endless loop

Andres Freund <andres@2ndquadrant.com>

View thread View on postgr.es
From: Andres Freund <andres@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Merlin Moncure <mmoncure@gmail.com>, Peter Geoghegan <pg@heroku.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2015-01-14T15:11:47Z
Lists: pgsql-hackers 
On 2015-01-14 10:05:01 -0500, Tom Lane wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
> > On Wed, Jan 14, 2015 at 8:41 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> What are the autovac processes doing (according to pg_stat_activity)?
> 
> > pid,running,waiting,query
> > 7105,00:28:40.789221,f,autovacuum: VACUUM ANALYZE pg_catalog.pg_class

It'd be interesting to know whether that vacuum gets very frequent
semaphore wakeups. Could you strace it for a second or three?

How did this perform < 9.4?  Can you guess how many times these dynamic
statements are planned? How many different relations are accessed in the
dynamically planned queries?

> Hah, I suspected as much.  Is that the one that's stuck in
> LockBufferForCleanup, or the other one that's got a similar backtrace
> to all the user processes?

Do you have a theory? Right now it primarily looks like contention on a
single buffer due to the high number of dynamic statements, possibly
made worse by the signalling between normal pinners and vacuum waiting
for cleanup.

Greetings,

Andres Freund

-- 
 Andres Freund	                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Commits

  1. Improve hash_create's API for selecting simple-binary-key hash functions.

  2. Fix WAL-logging of B-tree "unlink halfdead page" operation.

  3. Fix backup-block numbering in redo of b-tree split.

  4. Cleanup of new b-tree page deletion code.

  5. Fix bug in the new B-tree incomplete-split code.

  6. Fix bug in new B-tree page deletion code.

  7. Fix rm_desc routine of b-tree page delete records.

  8. Make the handling of interrupted B-tree page splits more robust.

  9. Fix race condition in B-tree page deletion.