Re: pg_dump far too slow

One more from me ....

If you think that the pipe to GZIP may be causing pg_dump to stall, try
putting something like buffer(1) in the pipeline ... it doesn't generally
come with Linux, but you can download source or create your own very easily
... all it needs to do is asynchronously poll stdin and write stdout. I
wrote one in Perl when I used to do a lot of digital video hacking, and it
helped with chaining together tools like mplayer and mpeg.

However, my money says that Tom's point about it being (disk) I/O bound is
correct :-)

Cheers
Dave

On Sun, Mar 21, 2010 at 8:17 AM, David Newall <postgresql(at)davidnewall(dot)com>wrote:

> Thanks for all of the suggestions, guys, which gave me some pointers on new
> directions to look, and I learned some interesting things.
>
> The first interesting thing was that piping (uncompressed) pg_dump into
> gzip, instead of using pg_dump's internal compressor, does bring a lot of
> extra parallelism into play. (Thank you, Matthew Wakeling.) I observed
> gzip using 100% CPU, as expected, and also two, count them, two postgres
> processes collecting data, each consuming a further 80% CPU. It seemed to
> me that Postgres was starting and stopping these to match the capacity of
> the consumer (i.e. pg_dump and gzip.) Very nice. Unfortunately one of
> these processes dropped eventually, and, according to top, the only non-idle
> process running was gzip (100%.) Obviously there were postgress and pg_dump
> processes, too, but they were throttled by gzip's rate of output and
> effectively idle (less than 1% CPU). That is also interesting. The final
> output from gzip was being produced at the rate of about 0.5MB/second, which
> seems almost unbelievably slow.
>
> I next tried Tom Lane's suggestion, COPY WITH BINARY, which produced the
> complete 34GB file in 30 minutes (a good result.) I then compressed that
> with gzip, which took an hour and reduced the file to 32GB (hardly worth the
> effort) for a total run time of 90 minutes. In that instance, gzip produced
> output at the rate of 10MB/second, so I tried pg_dump -Z0 to see how quickly
> that would dump the file. I had the idea that I'd go on to see how quickly
> gzip would compress it, but unfortunately it filled my disk before finishing
> (87GB at that point), so there's something worth knowing: pg_dump's output
> for binary data is very much less compact than COPY WITH BINARY; all those
> backslashes, as Tom pointed out. For the aforementioned reason, I didn't
> get to see how gzip would perform. For the record, pg_dump with no
> compression produced output at the rate of 26MB/second; a rather meaningless
> number given the 200%+ expansion of final output.
>
> I am now confident the performance problem is from gzip, not Postgres and
> wonder if I should read up on gzip to find why it would work so slowly on a
> pure text stream, albeit a representation of PDF which intrinsically is
> fairly compressed. Given the spectacular job that postgres did in adjusting
> it's rate of output to match the consumer process, I did wonder if there
> might have been a tragic interaction between postgres and gzip; perhaps
> postgres limits its rate of output to match gzip; and gzip tries to compress
> what's available, that being only a few bytes; and perhaps that might be so
> inefficient that it hogs the CPU; but it don't think that likely. I had a
> peek at gzip's source (surprisingly readable) and on first blush it does
> seem that unfortunate input could result in only a few bytes being written
> each time through the loop, meaning only a few more bytes could be read in.
>
> Just to complete the report, I created a child table to hold the PDF's,
> which are static, and took a dump of just that table, and adjusted my backup
> command to exclude it. Total size of compressed back sans PDFs circa 7MB
> taking around 30 seconds.
>