Re: pg_dump and server responsiveness

On Dec 6, 2007, at 9:58 AM, Bryan Murphy wrote:

> On Dec 5, 2007 9:49 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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(at)myemma(dot)com
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