Dear Postgres Gurus;
I've just diagnosed a PostgreSQL 8.3.4 server which, about once a month, would
deadlock shortly after 11pm. It had been doing this for years,
and the prior response was simply to reboot everything. The culprit boils down
to:
# create table cache_table_20090921 (
site_key int NOT NULL REFERENCES contexts(context_key),
blah_blah TEXT NULL,
PRIMARY KEY (site_key)
);
Without the REFERENCES this is a very fast operation and always works.
Adding "DEFERRABLE INITIALLY DEFERRED" or "ON DELETE CASCADE" (as the original
did) makes no difference. With any REFERENCES, the create table may block a
long time (up to 45 minutes and counting), all while attempting to get an
AccessExclusiveLock. The server quickly backs up,
simple select statements block, the server maxes out the number of incoming
apache connections, things start swapping, and generally it all gets into a
huge tangle:
# SELECT l.locktype,c.relname,l.pid,l.mode,granted from pg_locks l,pg_class c
where l.relation=c.oid order by relname,granted;
locktype | relname | pid | mode |
granted
relation | article_key_idx | 18891 | AccessShareLock |
t
relation | articles | 18891 | AccessShareLock |
t
relation | articles_editorid_idx | 18891 | AccessShareLock |
t
relation | articles_pkey | 18891 | AccessShareLock |
t
relation | articles_response_to_key_idx | 18891 | AccessShareLock |
t
relation | articles_state_idx | 18891 | AccessShareLock |
t
relation | article_words | 18891 | AccessShareLock |
t
relation | article_words_cw | 18891 | AccessShareLock |
t
relation | article_words_wc | 18891 | AccessShareLock |
t
relation | collection_context_key_idx | 18891 | AccessShareLock |
t
relation | collection_owner_key_idx | 18891 | AccessShareLock |
t
relation | collections | 18891 | AccessShareLock |
t
relation | context_publication_key_idx | 18891 | AccessShareLock |
t
relation | contexts | 18891 | AccessShareLock |
t
relation | contexts | 3879 | AccessExclusiveLock |
f
relation | contexts | 5477 | AccessShareLock |
f
relation | contexts | 5484 | AccessShareLock |
f
relation | contexts | 5485 | AccessShareLock |
f
relation | contexts | 5486 | AccessShareLock |
f
relation | contexts | 5487 | AccessShareLock |
f
relation | contexts | 5489 | AccessShareLock |
f
relation | contexts | 5493 | AccessShareLock |
f
relation | contexts | 5494 | AccessShareLock |
f
relation | contexts | 5496 | AccessShareLock |
f
relation | contexts | 5497 | AccessShareLock |
f
relation | contexts | 5498 | AccessShareLock |
f
relation | contexts | 5499 | AccessShareLock |
f
relation | contexts | 5500 | AccessShareLock |
f
relation | contexts | 5502 | AccessShareLock |
f
relation | contexts | 5503 | AccessShareLock |
f
relation | contexts | 5504 | AccessShareLock |
f
relation | contexts | 5505 | AccessShareLock |
f
relation | contexts | 5506 | AccessShareLock |
f
relation | contexts | 5507 | AccessShareLock |
f
relation | contexts | 5508 | AccessShareLock |
f
relation | contexts | 5509 | AccessShareLock |
f
relation | contexts | 5510 | AccessShareLock |
f
relation | contexts | 5511 | AccessShareLock |
f
relation | contexts | 5512 | AccessShareLock |
f
relation | contexts | 5515 | AccessShareLock |
f
relation | contexts | 5516 | AccessShareLock |
f
relation | contexts | 5517 | AccessShareLock |
f
relation | contexts | 5518 | AccessShareLock |
f
relation | contexts | 5519 | AccessShareLock |
f
relation | contexts | 5520 | AccessShareLock |
f
relation | contexts | 5521 | AccessShareLock |
f
relation | contexts | 5523 | AccessShareLock |
f
relation | contexts | 5524 | AccessShareLock |
f
relation | contexts_id_key | 18891 | AccessShareLock |
t
relation | contexts_pkey | 18891 | AccessShareLock |
t
relation | contexts_publication_date_idx | 18891 | AccessShareLock |
t
relation | contexts_site_key_ct_id_journal_key | 18891 | AccessShareLock |
t
relation | contexts_site_key_ct_type_idx | 18891 | AccessShareLock |
t
relation | group_key_idx | 18891 | AccessShareLock |
t
relation | parent_key_idx | 18891 | AccessShareLock |
t
relation | pg_class | 3911 | AccessShareLock |
t
relation | pg_class_oid_index | 3911 | AccessShareLock |
t
relation | pg_class_relname_nsp_index | 3911 | AccessShareLock |
t
relation | pg_locks | 3911 | AccessShareLock |
t
relation | site_key_idx | 18891 | AccessShareLock |
t
relation | virtual_ancestor_key_idx | 18891 | AccessShareLock |
t
relation | virtual_ancestors | 18891 | AccessShareLock |
t
relation | virtual_ancestors_pkey | 18891 | AccessShareLock |
t
relation | virtual_context_key_idx | 18891 | AccessShareLock |
t
relation | words | 18891 | AccessShareLock |
t
relation | words_pkey | 18891 | AccessShareLock |
t
relation | words_word | 18891 | AccessShareLock |
t
# select pg_size_pretty(pg_total_relation_size('contexts_pkey'));
35MB
# select pg_size_pretty(pg_total_relation_size('contexts'));
787MB
# select pg_cancel_backend(3879)
t
And all is well again.
-----------------------------------------------------------------------------------------------------------------
So my questions are various
1) Why the AccessExclusiveLock on create table?
2) Why is the foreign key check a heavy operation, since a new table
will have zero foreign keys, it can't possibly violate the constraint yet.
3) Other than eliminating dynamic table creation, how can this operation
be altered?
4) Is there more data I should collect about the lock (e.g. do I have a
good lock summary select statement)?
Note that process 18891 in the example is doing a select:
--------------
SELECT contexts.context_key FROM contexts
JOIN articles ON
(articles.context_key=contexts.context_key)
WHERE contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word =
'four')
AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word =
'step')
AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word =
'control')
AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word =
'process')
AND contexts.context_key IN
(SELECT context_key FROM article_words JOIN words using (word_key) WHERE word =
'business')
AND contexts.context_key IN (SELECT DISTINCT
a1.context_key FROM virtual_ancestors a1, collections, virtual_ancestors a2
WHERE a1.ancestor_key = collections.context_key AND collections.owner_key =
a2.context_key AND a2.ancestor_key = '82034')
AND articles.indexed
ORDER BY contexts.publication_date DESC;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
