Re: pg_dump slower than pg_restore - Mailing list pgsql-general

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