Обсуждение: pg_dump slower than pg_restore

I'm running PG 9.3.4 on CentOS 6.4 and noted that backing up my database
takes much longer than restoring it.

That seems counter-intuitive to me because it seems like reading from a
database should generally be faster than writing to it.

I have a database that pg_database_size reports as 18GB, and resulting
dump is about 13GB in 27 files (split creates them as 512MB).

A pg_dump backup -- with most of the data stored as large objects --
takes about 5 hours.

But restoring that dump takes about 2 hours.  So it's taking 2.5 times
longer to back it up than to restore it.

My backup script runs vacuumlo, then vacuum, then analyze, then pg_dump
--format=c --oids $DB

I actually push pg_dump output through gzip, gpg and split on 512MB
files, but they shouldn't matter too much I figure as I have to run cat,
gpg and gunzip before pg_restore.  In fact, my restore should have been
at a disadvantage because I used '-v' and showed the results to my ssh
term over the Internet which includes a line for each LOID, and the
postgresql.conf had 'ddl' logging on (which I suspect I can turn off in
future restores to speed things up a bit).

Is there something that might be wrong about my configuration that the
backup is slower than the restore?

Thanks,
David

On 07/03/14 10:04, David Wall wrote:
>
> A pg_dump backup -- with most of the data stored as large objects --
> takes about 5 hours.

If those large objects are 'files' that are already compressed (e.g.
most image files and pdf's) you are spending a lot of time trying to
compress the compressed data ... and failing.

Try setting the compression factor to an intermediate value, or even
zero (i.e. no dump compression).  For example, to get the 'low hanging
fruit' compressed:
    $ pg_dump -Z1 -Fc ...

IIRC, the default value of '-Z' is 6.

As usual your choice will be a run-time vs file-size trade-off so try
several values for '-Z' and see what works best for you.

HTH,
Bosco.

On 03/07/14 18:36, Bosco Rama wrote:
> On 07/03/14 10:04, David Wall wrote:
>> A pg_dump backup -- with most of the data stored as large objects --
>> takes about 5 hours.
> If those large objects are 'files' that are already compressed (e.g.
> most image files and pdf's) you are spending a lot of time trying to
> compress the compressed data ... and failing.
>
> Try setting the compression factor to an intermediate value, or even
> zero (i.e. no dump compression).  For example, to get the 'low hanging
> fruit' compressed:
>     $ pg_dump -Z1 -Fc ...
>
> IIRC, the default value of '-Z' is 6.
>
> As usual your choice will be a run-time vs file-size trade-off so try
> several values for '-Z' and see what works best for you.
>
> HTH,
> Bosco.

I'd also check the effect of those other run components; the vacuum's
and other things that are only running with the backup and not during
the restore.

--
Tim Clarke

On Thu, 03 Jul 2014 10:04:12 -0700
David Wall <d.wall@computer.org> wrote:

> I'm running PG 9.3.4 on CentOS 6.4 and noted that backing up my
> database takes much longer than restoring it.
>
> That seems counter-intuitive to me because it seems like reading from
> a database should generally be faster than writing to it.
>
> I have a database that pg_database_size reports as 18GB, and
> resulting dump is about 13GB in 27 files (split creates them as
> 512MB).
>
> A pg_dump backup -- with most of the data stored as large objects --
> takes about 5 hours.
>
> But restoring that dump takes about 2 hours.  So it's taking 2.5
> times longer to back it up than to restore it.
>
> My backup script runs vacuumlo, then vacuum, then analyze, then
> pg_dump
> --format=c --oids $DB
>
> I actually push pg_dump output through gzip, gpg and split on 512MB
> files, but they shouldn't matter too much I figure as I have to run
> cat, gpg and gunzip before pg_restore.  In fact, my restore should
> have been at a disadvantage because I used '-v' and showed the
> results to my ssh term over the Internet which includes a line for
> each LOID, and the postgresql.conf had 'ddl' logging on (which I
> suspect I can turn off in future restores to speed things up a bit).
>
> Is there something that might be wrong about my configuration that
> the backup is slower than the restore?

No, there's nothing wrong. All transparent compressed objects stored in database, toast, lo, etc.. is transparently
decompressedwhile pg_dump access them and then you gzip it again. I don't know why it doesn't dump the compressed data
directly.

>
> Thanks,
> David


---   ---
Eduardo Morras <emorrasg@yahoo.es>

On 7/3/2014 10:36 AM, Bosco Rama wrote:
> If those large objects are 'files' that are already compressed (e.g.
> most image files and pdf's) you are spending a lot of time trying to
> compress the compressed data ... and failing.
>
> Try setting the compression factor to an intermediate value, or even
> zero (i.e. no dump compression).  For example, to get the 'low hanging
> fruit' compressed:
>      $ pg_dump -Z1 -Fc ...
>
> IIRC, the default value of '-Z' is 6.
>
> As usual your choice will be a run-time vs file-size trade-off so try
> several values for '-Z' and see what works best for you.

That's interesting.  Since I gzip the resulting output, I'll give -Z0 a
try.  I didn't realize that any compression was on by default.

Thanks for the tip...

On 7/3/2014 10:38 AM, Tim Clarke wrote:
> I'd also check the effect of those other run components; the vacuum's
> and other things that are only running with the backup and not during
> the restore.

The vacuumlo, vacuum and analyze run before the pg_dump.  I am not
talking about any of the time they spend doing anything.  What I am
measuring is the last modified timestamps of the 512MB split files only,
so if anything, I'm under-measuring by the time it takes to do the first
512MB segment.

On 7/3/2014 4:51 PM, David Wall wrote:
> That's interesting.  Since I gzip the resulting output, I'll give -Z0
> a try.  I didn't realize that any compression was on by default.

default compression only happens in with pg_dump -Fc



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 07/03/14 16:51, David Wall wrote:
>
> On 7/3/2014 10:36 AM, Bosco Rama wrote:
>> If those large objects are 'files' that are already compressed (e.g.
>> most image files and pdf's) you are spending a lot of time trying to
>> compress the compressed data ... and failing.
>>
>> Try setting the compression factor to an intermediate value, or even
>> zero (i.e. no dump compression).  For example, to get the 'low hanging
>> fruit' compressed:
>>      $ pg_dump -Z1 -Fc ...
>>
>> IIRC, the default value of '-Z' is 6.
>>
>> As usual your choice will be a run-time vs file-size trade-off so try
>> several values for '-Z' and see what works best for you.
>
> That's interesting.  Since I gzip the resulting output, I'll give -Z0 a
> try.  I didn't realize that any compression was on by default.

If you use gzip you will be doing the same 'possibly unnecessary'
compression step.  Use a similar approach to the gzip command as you
would for the pg_dump command.  That is, use one if the -[0-9] options,
like this:
  $ pg_dump -Z0 -Fc ... | gzip -[0-9] ...

> Thanks for the tip...

NP.

HTH,
Bosco.

On 07/03/14 17:00, John R Pierce wrote:
> On 7/3/2014 4:51 PM, David Wall wrote:
>> That's interesting.  Since I gzip the resulting output, I'll give -Z0
>> a try.  I didn't realize that any compression was on by default.
>
> default compression only happens in with pg_dump -Fc

Yeah.  OP says he is using --format=c

On 7/3/2014 5:13 PM, Bosco Rama wrote:
> If you use gzip you will be doing the same 'possibly unnecessary'
> compression step. Use a similar approach to the gzip command as you
> would for the pg_dump command. That is, use one if the -[0-9] options,
> like this: $ pg_dump -Z0 -Fc ... | gzip -[0-9] ...

Bosco, maybe you can recommend a different approach.  I pretty much run
daily backups that I only have for disaster recovery.  I generally don't
do partials recoveries, so I doubt I'd ever modify the dump output.  I
just re-read the docs about formats, and it's not clear what I'd be best
off with, and "plain" is the default, but it doesn't say it can be used
with pg_restore.

Maybe the --format=c isn't the fastest option for me, and I'm less sure
about the compression.  I do want to be able to restore using pg_restore
(unless plain is the best route, in which case, how do I restore that
type of backup?), and I need to include large objects (--oids), but
otherwise, I'm mostly interested in it being as quick as possible.

Many of the large objects are gzip compressed when stored.  Would I be
better off letting PG do its compression and remove gzip, or turn off
all PG compression and use gzip?  Or perhaps use neither if my large
objects, which take up the bulk of the database, are already compressed?

On 07/03/14 17:30, David Wall wrote:
>
> Bosco, maybe you can recommend a different approach.  I pretty much run
> daily backups that I only have for disaster recovery.  I generally don't
> do partials recoveries, so I doubt I'd ever modify the dump output.  I
> just re-read the docs about formats, and it's not clear what I'd be best
> off with, and "plain" is the default, but it doesn't say it can be used
> with pg_restore.

Correct.  Plain is essentially one big SQL command file that you feed to
psql as the restore process.

> Maybe the --format=c isn't the fastest option for me,

I think you are on the right track with -Fc since plain would end up
converting the LO parts to escaped byte format for storage in the SQL
command file.

> and I'm less sure  about the compression.

You seem to be gaining a considerable amount of compression and that is
probably worthwhile given that it is mostly going to be the 'easy' kind.

> I do want to be able to restore using pg_restore (unless plain is the
> best route, in which case, how do I restore that type of backup?), and
> I need to include large objects (--oids), but otherwise, I'm mostly
> interested in it being as quick as possible.

Hmmm.  You are using '--oids' to *include* large objects?  IIRC, that's
not the intent of that option.  Large objects are dumped as part of a
DB-wide dump unless you request that they not be.

However, if you restrict your dumps to specific schemata and/or tables
then the large objects are NOT dumped unless you request that they are.

Something to keep in mind.

> Many of the large objects are gzip compressed when stored.  Would I be
> better off letting PG do its compression and remove gzip, or turn off
> all PG compression and use gzip?  Or perhaps use neither if my large
> objects, which take up the bulk of the database, are already compressed?

OK.  Given all the above (and that gpg will ALSO do compression unless
told not to), I'd go with the following (note lowercase 'z' in gpg
command).  Note also that there may be a CPU vs I/O trade-off here that
may make things muddier but the following are 'conceptually' true.

Fast but big
============
   $ pg_dump -Z0 -Fc ... $DB | gpg -z0 ... | split

Less fast but smaller
=====================
   $ pg_dump -Z1 -Fc ... $DB | gpg -z0 ... | split

(the '...' would be any access and/or key selection options)

Do some timing/size comparisons and see which is best for you.

BTW, is there any particular reason to do the 'split'?


For later consideration
=======================
Once you have this under control you may want to investigate the -Fd
format.  It allows you to use the '-j' option that may speed things
up even further.  However, I have no experience with it so I am unable
to make any concrete recommendations about it.  It *would* require a
post dump tar/zip/cpio to get to a single file backup, though.  If you
don't need a single file solution (as possibly exemplified by your use
of 'split') it may be just what you want.


Let us know how it goes. :-)

HTH,
Bosco.

On 7/3/2014 6:26 PM, Bosco Rama wrote:
> Hmmm. You are using '--oids' to *include* large objects? IIRC, that's
> not the intent of that option. Large objects are dumped as part of a
> DB-wide dump unless you request that they not be. However, if you
> restrict your dumps to specific schemata and/or tables then the large
> objects are NOT dumped unless you request that they are. Something to
> keep in mind.

I can get rid of --oids and see what happens.  I used to have
cross-table references to OID fields before, so this is no doubt a
holdover, but I think I am now using UUIDs for all such links/references
and the OID fields are just like any other data field.  It may not be
needed and I'll see if it speeds up the backup and restores correctly.

>> Many of the large objects are gzip compressed when stored.  Would I be
>> better off letting PG do its compression and remove gzip, or turn off
>> all PG compression and use gzip?  Or perhaps use neither if my large
>> objects, which take up the bulk of the database, are already compressed?
> OK.  Given all the above (and that gpg will ALSO do compression unless
> told not to), I'd go with the following (note lowercase 'z' in gpg
> command).  Note also that there may be a CPU vs I/O trade-off here that
> may make things muddier but the following are 'conceptually' true.
>
> Fast but big
> ============
>     $ pg_dump -Z0 -Fc ... $DB | gpg -z0 ... | split
>
> Less fast but smaller
> =====================
>     $ pg_dump -Z1 -Fc ... $DB | gpg -z0 ... | split

I'll give that a try now.  I didn't notice any real time savings when I
changed pg_dump without any -Z param to -Z 0, and oddly, not much of a
difference removing gzip entirely.

> BTW, is there any particular reason to do the 'split'?
Yes, I transfer the files to Amazon S3 and there were too many troubles
with one really big file.

Thanks again...

On 07/03/14 21:26, David Wall wrote:
> On 7/3/2014 6:26 PM, Bosco Rama wrote:
>> BTW, is there any particular reason to do the 'split'?
> Yes, I transfer the files to Amazon S3 and there were too many troubles
> with one really big file.

Is the issue with S3 or just transfer time?  I would expect that 'rsync'
with the '--partial' option (or -P if you want progress info too) may
help there.

HTH,
Bosco.

On 7/3/2014 10:13 PM, Bosco Rama wrote:
> Is the issue with S3 or just transfer time? I would expect that
> 'rsync' with the '--partial' option (or -P if you want progress info
> too) may help there.

Don't know if rsync and S3 work together or what that would mean, but
it's not an issue I'm suffering now.  I do think they may now have a
multipart upload with s3cmd (which I use), though that also wasn't
available when we first built our scripts.

I suspect nothing is really helping here and I'm mostly limited by disk
I/O, but not sure why the pg_dump is so much slower than pg_restore as
they are all on the same disks.  I say this because even with pg_dump
-Z0 | gpg -z 0 and gzip removed entirely and no --oids on pg_dump,
there's no effective difference in overall speed.  While I can see all
of those processes vying for resources via 'top -c', the throughput
remains much the same.

On 7/3/2014 11:47 AM, Eduardo Morras wrote:
> No, there's nothing wrong. All transparent compressed objects stored
> in database, toast, lo, etc.. is transparently decompressed while
> pg_dump access them and then you gzip it again. I don't know why it
> doesn't dump the compressed data directly.

That sounds odd, but if pg_dump decompresses the large objects and then
I gzip them on backup, doesn't the same more or less happen in reverse
when I pg_restore them?  I mean, I gunzip the backup and then pg_restore
must compress the large objects when it writes them back.

It just seems odd that pg_dump is slower than pg_restore to me. Most
grumblings I read about suggest that pg_restore is too slow.

I have noted that the last split file segment will often appear to be
done -- no file modifications -- while pg_dump is still running, often
for another 20 minutes or so, and then some last bit is finally
written.  It's as if pg_dump is calculating something at the end that is
quite slow.  At startup, there's a delay before data is written, too,
but it's generally 1-2 minutes at most.

David Wall <d.wall@computer.org> writes:

> A pg_dump backup -- with most of the data stored as large objects --
> takes about 5 hours.
>
> But restoring that dump takes about 2 hours.  So it's taking 2.5 times
> longer to back it up than to restore it.

Does top(1) reveal any bottlenecks?

Is the backup constrained by CPU (top will show your backup at close to
100% CPU usage) or I/O (top will often show your process in state 'D').

I also like dstat for looking at how things play out in such situations.

Best regards

Jacob

David Wall <d.wall@computer.org> writes:
> It just seems odd that pg_dump is slower than pg_restore to me. Most
> grumblings I read about suggest that pg_restore is too slow.

> I have noted that the last split file segment will often appear to be
> done -- no file modifications -- while pg_dump is still running, often
> for another 20 minutes or so, and then some last bit is finally
> written.  It's as if pg_dump is calculating something at the end that is
> quite slow.  At startup, there's a delay before data is written, too,
> but it's generally 1-2 minutes at most.

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.

            regards, tom lane

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

On 07/03/14 22:51, David Wall wrote:
>
> It just seems odd that pg_dump is slower than pg_restore to me. Most
> grumblings I read about suggest that pg_restore is too slow.
>
> I have noted that the last split file segment will often appear to be
> done -- no file modifications -- while pg_dump is still running, often
> for another 20 minutes or so, and then some last bit is finally
> written.  It's as if pg_dump is calculating something at the end that is
> quite slow.  At startup, there's a delay before data is written, too,
> but it's generally 1-2 minutes at most.

Random thought:  What OS & kernel are you running?  Kernels between
3.2.x and 3.9.x were known to have IO scheduling issues.  This was
highlighted most by the kernel in Ubuntu 12.04 (precise) as shown
here:

<http://www.postgresql.org/message-id/50BF9247.2010800@optionshouse.com>

Bosco.

On 07/03/14 22:51, David Wall wrote:
>
> On 7/3/2014 11:47 AM, Eduardo Morras wrote:
>> No, there's nothing wrong. All transparent compressed objects stored
>> in database, toast, lo, etc.. is transparently decompressed while
>> pg_dump access them and then you gzip it again. I don't know why it
>> doesn't dump the compressed data directly.
>
> That sounds odd, but if pg_dump decompresses the large objects and then
> I gzip them on backup, doesn't the same more or less happen in reverse
> when I pg_restore them?  I mean, I gunzip the backup and then pg_restore
> must compress the large objects when it writes them back.

I believe there is a little confusion here.  (If not, I apologize in
advance :-) ).

There are at least 3 forms of (de)compression going on here:
   1) Application level:  The data itself is presented to the
      application in a compressed format (image file, etc.) or
      it is compressed by the application prior to presentation to PG.
      The fact that this data is compressed is not relevant to the
      following;

   2) PG server-side: Automated TOAST (de)compression which may or may
      not be enabled for a table/column.

   3) Dump/restore/tools:  the pg dump/restore and any tools you
      use to (de)compress the DB backup.  Without due care this can
      end up in multiple attempts to (de)compress the same file, as
      we've seen. :-)

Regardless of what happens in 2) & 3) the application-level compression
state in 1) will not be affected.

Thus a blob that represents a gzip'd file presented to PG in 1) will
remain compressed through all 3 stages.  It is *not* auto-magically
uncompressed at any stage.

I believe Eduardo was referring to 2) which, because it is an
automated, transparent, server-side operation needs to be performed
even as part of a dump/restore.

HTH,
Bosco.

David Wall <d.wall@computer.org> writes:
> 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?

> 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.

Hmm ... how many rows in pg_largeobject_metadata?

> Basic top stats while running show:

>   7547 esignfor  30  10 1148m 1.0g  852 S  2.3 26.9 14:10.27 pg_dump
> --format=c --oids ibc01

That's a pretty large resident size for pg_dump :-( ... you evidently
have a lot of objects of some sort, and I'm betting it's LOs, but
let's make sure.

            regards, tom lane

On 7/4/2014 11:30 AM, Bosco Rama wrote:
> Random thought:  What OS & kernel are you running?  Kernels between
> 3.2.x and 3.9.x were known to have IO scheduling issues.  This was
> highlighted most by the kernel in Ubuntu 12.04 (precise) as shown
> here:
>
> <http://www.postgresql.org/message-id/50BF9247.2010800@optionshouse.com>

I'm on CentOS 6.4 which seems to be Linux version 2.6.32-431.20.3.el6.x86_64

But it is a VM, so disk I/O can be rather random as there are other
tenants.  While improving performance is nice, I was most interested in
wy a pg_dump takes longer than a pg_restore (nearly 50% longer as it
takes about 2.75 hours to dump, but 2 hours to restore).  It's
counter-intuitive as reading from a DB is usually faster than writing
into a DB.  I think those LOs are getting me as our DB is LO-intensive
(most data is encrypted blobs: encrypted uploaded user files and
encrypted app-generated XML/HTML).

On 7/4/2014 9:18 PM, Tom Lane wrote:
>> 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.
> Hmm ... how many rows in pg_largeobject_metadata?

pg_largeobject_metadata reports 1,656,417 rows.

By the way, what is pg_largeobject_metadata vs. pg_largeobject since the
counts are so different?


>>    7547 esignfor  30  10 1148m 1.0g  852 S  2.3 26.9 14:10.27 pg_dump
>> --format=c --oids ibc01

I haven't tested it for any side issues, but the --oids can probably be
removed as we don't cross reference against OID columns anymore (all
OIDs are just a field in a table that uses a UUID now for
cross-referencing).  But removing it seemed to make no difference if
overall time for the pg_dump to complete.

> That's a pretty large resident size for pg_dump :-( ... you evidently
> have a lot of objects of some sort, and I'm betting it's LOs, but
> let's make sure.

Is there postgresql.conf setting that might help?  It's a small 1GB RAM
Linux VM with Tomcat web server (we give it 500-700MB) with PG DB on
it.  We don't do much but change max-connections to 70, shared_buffers
to 128MB, maintenance_work_mem to 120MB, checkpoint_segments to 6.

But in the end, I guess the main question is why the backup takes longer
than the restore, which just seems counter-intuitive to me.

Thanks for all your help and thinking about it!

David Wall <d.wall@computer.org> writes:
>>> 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.

>> Hmm ... how many rows in pg_largeobject_metadata?

> pg_largeobject_metadata reports 1,656,417 rows.

> By the way, what is pg_largeobject_metadata vs. pg_largeobject since the
> counts are so different?

There's one row in pg_largeobject_metadata per large object.  The rows in
pg_largeobject represent 2KB "pages" of large objects (so it looks like
your large objects are averaging only 8KB-10KB apiece).  The "metadata"
table was added in 9.0 to carry ownership and access permission data for
each large object.

I think this report confirms something we'd worried about during 9.0
development, which was whether pg_dump wouldn't have issues with
sufficiently many large objects.  At the time we'd taught it to handle LOs
as if they were full-fledged database objects, since that was the easiest
way to piggyback on its existing machinery for handling ownership and
permissions; but that's rather expensive for objects that don't really
need all the trappings of, eg, dependency tracking.  We'd done some
measurements that seemed to indicate that the overhead wasn't awful for
medium-size numbers of large objects, but I'm not sure we tried it for
millions of 'em.

I guess the good news is that it's only being a bit slow for you and not
falling over completely.  Still, it seems like some more work is indicated
in this area.

            regards, tom lane

On 7/6/2014 9:06 AM, Tom Lane wrote:
> David Wall <d.wall@computer.org> writes:
>
> There's one row in pg_largeobject_metadata per large object.  The rows in
> pg_largeobject represent 2KB "pages" of large objects (so it looks like
> your large objects are averaging only 8KB-10KB apiece).  The "metadata"
> table was added in 9.0 to carry ownership and access permission data for
> each large object.

Thanks for that insight.

That metadata table is what first got me when I upgraded from 8.3 to 9.2
when there were all sorts of LO permission errors.  I have found that I
get the same sort of issue when I migrate from one system to another,
presumably because the id of the owner has changed, though I use the
same name each time.  I've taken to doing the large object permission
assignment after every restore "just to be safe," but it has the
drawback that I often have to set max_locks_per_transaction to a very
high number (40000) for the restore, and then I comment it back out once
it's done and restart.  That number is less than the number of LOs by a
long shot, so I'm not sure an optimal number is, but I think at 20000 I
ran out during the re-permissioning of LOs.

It could be that when I restore, the objects take on the permission of
the DB admin user (i.e. postgres) since it has full permissions for
creating everything.  But I'd prefer that the objects all take on the
ownership of the DB app user, which of course has more limited
permissions, but otherwise is the user that does all of the
inserts/updates/deletes/selects.  I'm not sure if I can create users in
new databases with the same id when I'm using the same name or not.

> I think this report confirms something we'd worried about during 9.0
> development, which was whether pg_dump wouldn't have issues with
> sufficiently many large objects.  At the time we'd taught it to handle LOs
> as if they were full-fledged database objects, since that was the easiest
> way to piggyback on its existing machinery for handling ownership and
> permissions; but that's rather expensive for objects that don't really
> need all the trappings of, eg, dependency tracking.  We'd done some
> measurements that seemed to indicate that the overhead wasn't awful for
> medium-size numbers of large objects, but I'm not sure we tried it for
> millions of 'em.
>
> I guess the good news is that it's only being a bit slow for you and not
> falling over completely.  Still, it seems like some more work is indicated
> in this area.
Yes, it takes 3 hours to do the backup, which is generally okay.  It was
just surprising that I could restore in 2 hours <smile>.

David