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
