Re: pg_dump and server responsiveness

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
>