BUG #14223: stuck spinlock detected at bufmgr.c:1440

From: scott(at)deltaex(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14223: stuck spinlock detected at bufmgr.c:1440
Date: 2016-07-01 20:22:34
Message-ID: 20160701202234.1398.58348@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14223
Logged by: Scott Milliken
Email address: scott(at)deltaex(dot)com
PostgreSQL version: 9.5.3
Operating system: Linux 3.2.0-60-generic, Ubuntu 12.04.5 LTS
Description:

I experienced a PostgreSQL server crash, and thought I'd share the details
in case it's helpful.

Error message:
2016-07-01 10:45:27.155
PDT,"xxx","xxx",2057,"xxxx:50539",57765b4e.809,80,"INSERT",2016-07-01
05:00:14 PDT,8/739892,134888654,PANIC,XX000,"stuck spinlock (0x7f8e1d81e360)
detected at bufmgr.c:1440",,,,,,"INSERT INTO ...."

Build details
Version: 9.5.3
Configure flags: --with-openssl --with-python --with-segsize=16
--with-blocksize=32 --with-wal-blocksize=64 --with-ossp-uuid

Potentially relevant GUCs:
effective_cache_size = 400GB
effective_io_concurrency = 16
full_page_writes = off (ZFS guarantees consistency)
dynamic_shared_memory_type = posix
shared_buffers = 12GB
temp_buffers = 512MB
wal_buffers = 64MB
wal_keep_segments = 3000
wal_level = hot_standby
wal_sender_timeout = 0
work_mem = 16GB

The query that caused this crash was inside a nested transaction, and took
the form:
INSERT INTO foo SELECT * FROM (SELECT UNNEST(ARRAY[1, 2, 3, ....]) AS id))
sub WHERE NOT EXISTS (SELECT * FROM foo WHERE foo.id = sub.id);
(Effectively a batch upsert; now that PostgreSQL has upsert the query can be
refactored.)

This query has run hundreds of thousands of times on this particular
database successfully; this is the first instance of this error that has
occured.

This issue isn't urgent for me and I have no way to reproduce it, but figure
it wouldn't hurt to share. It may be relatively unique to me because it
relates to shared buffers, which I have set at the non-default 32k (saves
~30% size on disk, improves batch throughput).

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2016-07-01 21:22:15 Re: BUG #14223: stuck spinlock detected at bufmgr.c:1440
Previous Message Bruce Momjian 2016-07-01 14:50:55 Re: BUG #14221: using postgresql 9.1.22 to 9.3.13 failed using pg_upgrade