Horton

Re: hung backends stuck in spinlock heavy endless loop

Merlin Moncure <mmoncure@gmail.com>

View thread View on postgr.es
From: Merlin Moncure <mmoncure@gmail.com>
To: Andres Freund <andres@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Peter Geoghegan <pg@heroku.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2015-01-14T15:22:45Z
Lists: pgsql-hackers 
On Wed, Jan 14, 2015 at 9:11 AM, Andres Freund <andres@2ndquadrant.com> wrote:
> 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?

for 30 seconds+ it just looks like this:
mmoncure@mernix2 ~ $ sudo strace -p 7105
Process 7105 attached
semop(5701638, {{4, -1, 0}}, 1

all of other processes are yielding out of the spinlock, for example:
select(0, NULL, NULL, NULL, {0, 1408})  = 0 (Timeout)

> How did this perform < 9.4?
this is a new project. However, I can run it vs earlier version.

Can you guess how many times these dynamic
> statements are planned? How many different relations are accessed in the
> dynamically planned queries?

only once or twice, and only a couple of tables.     This is an
operation that should only take few seconds (inserting a few 10s of
thousands of rows), that has blocked for many hours now.  Usually it
runs through taking a few seconds.  This is either a deadlock or a
deadlock emulating sequence of operations.

I'll try to pull commits that Peter suggested and see if that helps
(I'm getting ready to bring the database down).  I can send the code
off-list if you guys think it'd help.

merlin

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.