On Dec 6, 2007, at 9:58 AM, Bryan Murphy wrote:
On Dec 5, 2007 9:49 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Only access-share locks, but that could still be an issue if
anything in
your system likes to take exclusive locks. Have you looked into
pg_locks to see if anything's getting blocked?
pg_dump is entirely capable of causing an unpleasant amount of I/O
load, but that shouldn't result in "complete unresponsiveness",
and anyway your iostat output doesn't look like you're saturated...
It does appear to be lock contention. I took a closer look this
morning, and I noticed our web site was consistently locking up on a
particular table, and there were a number of exclusive locks. I
started eliminating various jobs, and found the one that essentially
rewrites that particular table every 5 minutes to be the culprit
(create new table, drop old table, rename new table).
Is there a better way we can do this so that we won't causes lock
contention during a dump? I can disable the process, but if the
backup takes an hour that's an hour where all the data in this table
is stale. I believe we chose to do it this way, because we wanted to
minimize the amount of time the table wasn't available, which is why
we didn't use a truncate based strategy.
If this table has such transient data in it, does it even need to be
included in the dump? If not, either move it into another database,
another schema, or just use the -T flag in your dump command.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
