BUG #14223: stuck spinlock detected at bufmgr.c:1440
The following bug has been logged on the website:
Bug reference: 14223
Logged by: Scott Milliken
Email address: scott@deltaex.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).
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi,
On 2016-07-01 20:22:34 +0000, scott@deltaex.com wrote:
The following bug has been logged on the website:
Bug reference: 14223
Logged by: Scott Milliken
Email address: scott@deltaex.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 ...."
Hm. Interesting.
full_page_writes = off (ZFS guarantees consistency)
I don't believe that's actually true, but it's surely unrelated to this
issue.
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.
Hm. Do you happen to have system statistics (load, user cpu, sys cpu)
available by any chance?
This issue isn't urgent for me and I have no way to reproduce it, but figure
it wouldn't hurt to share.
That's helpful!
Andres
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
scott@deltaex.com wrote:
I experienced a PostgreSQL server crash, and thought I'd share the details
in case it's helpful.
FWIW
/messages/by-id/DF53FBB6-81A2-4423-A0CD-822536F97E26@thebuild.com
(this one appears fixed)
/messages/by-id/AANLkTin6WgLj5yMHMnUBCCvxUM-NLaPAj1A5zp6_v1Cz@mail.gmail.com
/messages/by-id/20150810204319.2695.22673@wrigleys.postgresql.org
/messages/by-id/20160127203119.2537.78623@wrigleys.postgresql.org
--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi,
On 2016-07-01 20:22:34 +0000, scott@deltaex.com wrote:
The following bug has been logged on the website:
Bug reference: 14223
Logged by: Scott Milliken
Email address: scott@deltaex.com
PostgreSQL version: 9.5.3
Operating system: Linux 3.2.0-60-generic, Ubuntu 12.04.5 LTS
Given that the kernel is so old, two likely causes are transparent
hugepages, and zone_reclaim_mode. Both have to be disabled on older
kernels (the latter is disabled by default these days).
Greetings,
Andres Freund
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs