Обсуждение: pg_dump and server responsiveness
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? 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... sdb: tables sdc: logs sdd: backup share ----total-cpu-usage---- -dsk/total----dsk/sdc-----dsk/sdb-----dsk/sdd-- ------memory-usage----- -net/total- ----swap--- usr sys idl wai hiq siq|_read _writ:_read _writ:_read _writ:_read _writ|_used _buff _cach _free|_recv _send|_used _free 34 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1276M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 0 83k: 0 0 : 0 67k: 0 0 | 525M 96k 14G 1272M|1176B 48k| 132k 7632M 34 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1269M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1265M| 564B 9824B| 132k 7632M 35 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 | 525M 96k 14G 1261M| 140B 556B| 132k 7632M 36 1 63 0 0 0| 0 2048B: 0 0 : 0 0 : 0 0 | 525M 96k 14G 1258M| 140B 556B| 132k 7632M 36 4 49 10 0 1| 0 60M: 0 2048B: 0 184k: 0 60M| 535M 96k 14G 1245M|6827B 8682B| 132k 7632M 48 4 39 8 0 1| 0 46M: 0 168k: 0 0 : 0 46M| 542M 96k 14G 1234M| 652k 119M| 132k 7632M 38 2 52 7 0 0| 0 15M: 0 608k: 0 0 : 0 14M| 543M 96k 14G 1230M| 56k 24k| 132k 7632M Thanks, Bryan
Sorry about the formatting, here's the dump as a text file. Thanks, Bryan On Dec 5, 2007 10:05 AM, Bryan Murphy <bryan.murphy@gmail.com> 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? 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
Вложения
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 > > sdb: tables > sdc: logs > sdd: backup share > > ----total-cpu-usage---- > -dsk/total----dsk/sdc-----dsk/sdb-----dsk/sdd-- > ------memory-usage----- -net/total- ----swap--- > usr sys idl wai hiq siq|_read _writ:_read _writ:_read _writ:_read > _writ|_used _buff _cach _free|_recv _send|_used _free > 34 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 > | 525M 96k 14G 1276M| 140B 556B| 132k 7632M > 36 1 63 0 0 0| 0 83k: 0 0 : 0 67k: 0 0 > | 525M 96k 14G 1272M|1176B 48k| 132k 7632M > 34 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 > | 525M 96k 14G 1269M| 140B 556B| 132k 7632M > 36 1 63 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 > | 525M 96k 14G 1265M| 564B 9824B| 132k 7632M > 35 1 64 0 0 0| 0 0 : 0 0 : 0 0 : 0 0 > | 525M 96k 14G 1261M| 140B 556B| 132k 7632M > 36 1 63 0 0 0| 0 2048B: 0 0 : 0 0 : 0 0 > | 525M 96k 14G 1258M| 140B 556B| 132k 7632M > 36 4 49 10 0 1| 0 60M: 0 2048B: 0 184k: 0 > 60M| 535M 96k 14G 1245M|6827B 8682B| 132k 7632M > 48 4 39 8 0 1| 0 46M: 0 168k: 0 0 : 0 > 46M| 542M 96k 14G 1234M| 652k 119M| 132k 7632M > 38 2 52 7 0 0| 0 15M: 0 608k: 0 0 : 0 > 14M| 543M 96k 14G 1230M| 56k 24k| 132k 7632M > > Thanks, > Bryan > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
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