Re: pg_dump slower than pg_restore

On 7/4/2014 7:19 AM, Tom Lane wrote:
> You haven't given us much info about the contents of this database.
> Are there a lot of tables? functions? large objects? How many is
> "a lot", if so?
>
> I'm suspicious that you're paying a penalty associated with pg_dump's
> rather inefficient handling of metadata for large objects, but there's
> not enough info in this thread to diagnose it. It'd be very interesting
> to see perf or oprofile stats on the pg_dump run, particularly during
> the parts where it doesn't seem to be writing anything.

There are only 32 table, no functions, but mostly large objects. Not
sure how to know about the LOs, but a quick check from the table sizes I
estimate at only 2GB, so 16GB could be LOs. There are 7,528,803 entries
in pg_catalog.pg_largeobject.

pg_database_size reports 18GB

biggest table sizes:
relation | size
-----------------------------------+--------
public.esf_formparty | 635 MB
public.esf_activity_log | 416 MB
public.esf_form | 181 MB
public.esf_encrypted_blob | 134 MB
public.esf_activity_log_ownertime | 73 MB
public.esf_tranfield | 72 MB
public.esf_formpartytranididx | 70 MB
public.esf_formparty_pkey | 65 MB
public.esf_encrypted_blob_pkey | 64 MB
public.esf_formpartyididx | 63 MB
public.esf_tranfield_pkey | 52 MB
public.esf_formpartypickupidx | 51 MB
public.esf_activity_log_typetime | 47 MB
public.esf_tran | 46 MB
public.esf_formorderidx | 46 MB
public.esf_form_pkey | 42 MB
public.esf_tranfieldvalueidx | 39 MB
public.esf_traninittimeidx | 19 MB
public.esf_tranupdatetimeidx | 19 MB
public.esf_tran_pkey | 13 MB

Basic top stats while running show:

top - 08:53:40 up 27 days, 17:38, 1 user, load average: 1.03, 1.12, 1.22
Tasks: 156 total, 1 running, 155 sleeping, 0 stopped, 0 zombie
Cpu(s): 1.3%us, 0.6%sy, 0.4%ni, 74.2%id, 23.5%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 3974112k total, 3954520k used, 19592k free, 46012k buffers
Swap: 4245496k total, 29996k used, 4215500k free, 1123844k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7549 esignfor 20 0 116m 1372 884 S 3.0 0.0 16:39.69 gpg --batch
--symmetric --cipher-algo AES256 --passphrase 3z4ig0Rq]w
7547 esignfor 30 10 1148m 1.0g 852 S 2.3 26.9 14:10.27 pg_dump
--format=c --oids ibc01
7548 esignfor 20 0 4296 748 372 S 2.3 0.0 13:05.44 gzip
7551 esignfor 20 0 555m 413m 410m D 1.7 10.6 9:32.03 postgres:
esignforms ibc01 [local] <FASTPATH>
1978 esignfor 20 0 15032 1372 1004 R 0.7 0.0 0:00.27 top -c
7550 esignfor 20 0 98.6m 592 472 S 0.3 0.0 0:49.80 split -b
512000000 - /home/esignforms/customers/archive/db/dump.20140704.gz.gpg