pg_dump and server responsiveness

Sorry about the formatting, here's the dump as a text file.

Thanks,
Bryan

Bryan Murphy wrote:

When we run pg_dump on our database, our web site becomes completely
unresponsive. I thought pg_dump was runnable while the database was
still being actively used?

It is but it assumes you have resources available.

Anyway, I'm not entirely sure why, but
here's what I'm seeing.

pg_dump -v database_name | gzip > output_file
25% to 50% CPU usage (4 proc machine)
Entire database is cached in memory, so the only I/O is what's being
dumped to the output file
Web site becomes unresponsive almost immediately

Does pg_dump create table locks? It doesn't look like an I/O problem
as far as I can tell...

Pg_dump uses Access Share if I recall. You can operate normally while
running pg_dump. I am having a hard time parsing that. Could you instead
go over to pgsql.privatepaste.com and send back a paste link?

Joshua D. Drake

On Dec 5, 2007 10:14 AM, Joshua D. Drake <jd@commandprompt.com> wrote:

Pg_dump uses Access Share if I recall. You can operate normally while
running pg_dump. I am having a hard time parsing that. Could you instead
go over to pgsql.privatepaste.com and send back a paste link?

http://pgsql.privatepaste.com/5ako244Xe5

Sorry about that. Google tricked me into thinking it would format properly. :)

Bryan

"Bryan Murphy" <bryan.murphy@gmail.com> writes:

When we run pg_dump on our database, our web site becomes completely
unresponsive. ...
Does pg_dump create table locks? It doesn't look like an I/O problem
as far as I can tell...

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...

regards, tom lane

On Dec 5, 2007 9:49 PM, Tom Lane <tgl@sss.pgh.pa.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.

Thanks,
Bryan

"Bryan Murphy" <bryan.murphy@gmail.com> writes:

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?

Why dump such a table at all? It evidently doesn't contain any
data you need to preserve ...

I forget which version you are running, but 8.2 pg_dump has an
--exclude-table switch which'd work peachy for this.

regards, tom lane

On Dec 6, 2007 10:09 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Why dump such a table at all? It evidently doesn't contain any
data you need to preserve ...

I forget which version you are running, but 8.2 pg_dump has an
--exclude-table switch which'd work peachy for this.

I did not know about that option but it sounds like it will get the
job done. This is our last database running 8.1.9, so even if it
doesn't support that, I plan on migrating it to 8.2 soon anyway.

Thanks,
Bryan

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

On Dec 5, 2007 9:49 PM, Tom Lane <tgl@sss.pgh.pa.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@myemma.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