Re: [GENERAL] pg_dump slower than pg_restore

On 7/6/2014 9:06 AM, Tom Lane wrote: David Wall 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

Re: [GENERAL] pg_dump slower than pg_restore

David Wall 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 ma

Re: [GENERAL] pg_dump slower than pg_restore

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 m

Re: [GENERAL] pg_dump slower than pg_restore

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:

Re: [GENERAL] pg_dump slower than pg_restore

David Wall 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 h

Re: [GENERAL] pg_dump slower than pg_restore

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

Re: [GENERAL] pg_dump slower than pg_restore

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

Re: [GENERAL] 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 metadat

Re: [GENERAL] pg_dump slower than pg_restore

David Wall 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, oft

Re: [GENERAL] pg_dump slower than pg_restore

David Wall 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 b

Re: [GENERAL] pg_dump slower than pg_restore

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

Re: [GENERAL] pg_dump slower than pg_restore

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 suffer

Re: [GENERAL] pg_dump slower than pg_restore

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

Re: [GENERAL] pg_dump slower than pg_restore

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 ta

Re: [GENERAL] pg_dump slower than pg_restore

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 form

Re: [GENERAL] pg_dump slower than pg_restore

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

Re: [GENERAL] pg_dump slower than pg_restore

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 h

Re: [GENERAL] pg_dump slower than pg_restore

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 co

Re: [GENERAL] pg_dump slower than pg_restore

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 s

Re: [GENERAL] pg_dump slower than pg_restore

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 t

Re: [GENERAL] pg_dump slower than pg_restore

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 ze

Re: [GENERAL] pg_dump slower than pg_restore

On Thu, 03 Jul 2014 10:04:12 -0700 David Wall 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

Re: [GENERAL] pg_dump slower than pg_restore

On Thu, Jul 3, 2014 at 10:04 AM, David Wall 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. > Are you dumping to a slower disk/storage than the database is using? What does top -c look like during the dump vs. the restore?

Re: [GENERAL] pg_dump slower than pg_restore

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

Re: [GENERAL] pg_dump slower than pg_restore

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 d

[GENERAL] 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