pg_dump and write locks
We have an issue with running pg_dump while a database is under
reasonably heavy update load. This is 7.4.5 on Solaris 9/intel.
The observed behavior was that a pg_dump running with nothing else going
on takes a couple of minutes, but when we are running some system tests
that do heavy updates to a selection of application tables, it appears
that pg_dump blocks until the update run is done. This didn't make sense
because everything I can find about pg_dump indicates that it should
only be taking read locks, so I don't see why it should be blocked.
We looked at pg_locks, and saw that the pg_dump process was acquiring
locks like:
14764 | ExclusiveLock | 124576072 | COPY public.stats (id,
description, lastsavedate, lastsaveuser) TO stdout;
(we are using pg_dump with -Fc) If COPY is taking a ROW EXCLUSIVE lock,
then that would explain why we are seeing contention, but I don't
understand why COPY is locking at that level. Is there a better way to
run pg_dump against a database with a lot of update activity?
Thanks.
- DAP
======================================================
David Parker Tazz Networks
"David Parker" <dparker@tazznetworks.com> writes:
The observed behavior was that a pg_dump running with nothing else going
on takes a couple of minutes, but when we are running some system tests
that do heavy updates to a selection of application tables, it appears
that pg_dump blocks until the update run is done.
Are you sure the other processes aren't taking any exclusive locks?
Are you sure your system isn't saturated to the point where pg_dump
just can't make progress very fast?
We looked at pg_locks, and saw that the pg_dump process was acquiring
locks like:14764 | ExclusiveLock | 124576072 | COPY public.stats (id,
description, lastsavedate, lastsaveuser) TO stdout;
It's impossible to tell what you are actually looking at here --- that's
not the raw output of pg_locks, and you've conveniently omitted any
column headers --- but I wonder whether that isn't just the
transaction's standard lock on its own XID.
If pg_dump is actually getting blocked, that will show as a row with
granted = false and pg_dump's PID.
regards, tom lane
What happened was that I realized that here it was already noon on
Tuesday, and I hadn't made a fool of myself in public yet this week....
I blindly took the output from somebody else's query and didn't look at
the pg_locks table myself. Once I was able to look at it with a live
test running, everything is in order, just slow.
Thanks for your response, and apologies for the noise.
- DAP
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, July 19, 2005 10:45 AM
To: David Parker
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_dump and write locks
"David Parker" <dparker@tazznetworks.com> writes:
The observed behavior was that a pg_dump running with nothing else
going on takes a couple of minutes, but when we are running some
system tests that do heavy updates to a selection of application
tables, it appears that pg_dump blocks until the update run is done.
Are you sure the other processes aren't taking any exclusive locks?
Are you sure your system isn't saturated to the point where pg_dump just
can't make progress very fast?
We looked at pg_locks, and saw that the pg_dump process was acquiring
locks like:14764 | ExclusiveLock | 124576072 | COPY public.stats (id,
description, lastsavedate, lastsaveuser) TO stdout;
It's impossible to tell what you are actually looking at here --- that's
not the raw output of pg_locks, and you've conveniently omitted any
column headers --- but I wonder whether that isn't just the
transaction's standard lock on its own XID.
If pg_dump is actually getting blocked, that will show as a row with
granted = false and pg_dump's PID.
regards, tom lane
Import Notes
Resolved by subject fallback