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
>