Re: hung backends stuck in spinlock heavy endless loop

On Wed, Jan 14, 2015 at 9:11 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> On 2015-01-14 10:05:01 -0500, Tom Lane wrote:
>> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> > On Wed, Jan 14, 2015 at 8:41 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)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