Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3756A635070 for ; Sun, 21 Mar 2010 15:04:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 07860-04-5 for ; Sun, 21 Mar 2010 18:03:48 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web39705.mail.mud.yahoo.com (web39705.mail.mud.yahoo.com [209.191.106.51]) by mail.postgresql.org (Postfix) with SMTP id D02F6634A3C for ; Sun, 21 Mar 2010 15:03:31 -0300 (ADT) Received: (qmail 47748 invoked by uid 60001); 21 Mar 2010 18:03:30 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1269194610; bh=6O/Vc5augnDgZltN+Xzt3nWguaF2zi5M2SONjX9DvqA=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=HZNAEnjDOXNj8cXr4Pe5jNfgzebXCJ86gt07/iPikxhu9+BrKPrbfppc99kH+OSabkoQly4JWSChX39vpPi4b6h/1pJeCTzvM36VFyEXUdDrFgqRqru+hf3avW9cyOQzgtt69+1qetvzIivBBevqGlnxkTufhU+dZD+cA5zsr+Q= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=OHjaVh4sQaZMxWpZaM87YWd1HWusMlCOE9Rd9BiqQF1G3HAiK9RyfEtZR30DIMSsXzY9w/Co2HjWQ4IgzzG/t2y3cvqW/iC+KysmpK2bUbwimRipUfTj0Z0i9XC2qzV4bpgPnIJd7TEhxEr8TnbF6EgVVOvVxzSKE6/xQ2XP8Ik=; Message-ID: <639478.7290.qm@web39705.mail.mud.yahoo.com> X-YMail-OSG: _EOWJPEVM1nMYBKogEMeNgy3wZJwwKgXGN.CCMlMw89Cp.7 K7fFLJy07v3hwuV48iA_YJ6_pEgaqL5kkyFB3zoylKPDlm7c6gVlT.Bg9MTL goamfm7C6rF12Jd04gosO_uxze5RcP2qVQGKFNchLyIINa.dfKqSfgROhrQb o.o_c8.ua5vzsZ7TtPWaK7AK0l8yZBuTgALXkpHLltsn2Tyl7fVupr.Nyupn mQWZUyQrnZ7UdoZRuQUcCV7bgngwZ9nax8Hr1Ql74rI6QS0WpZhUT6BuvARK iUZ30O6y.IyY0PqooZ.DJAFzxVktpGcdYOxHnFdSXMNCllY1sLLr_bSqB86_ HjX9tzkD7o7mpmCP0AOPtA5suBX3ng2S7ew-- Received: from [69.76.130.83] by web39705.mail.mud.yahoo.com via HTTP; Sun, 21 Mar 2010 11:03:30 PDT X-Mailer: YahooMailClassic/10.0.8 YahooMailWebService/0.8.100.260964 Date: Sun, 21 Mar 2010 11:03:30 -0700 (PDT) From: Bob Lunney Subject: Re: pg_dump far too slow To: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-2117330868-1269194610=:7290" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=AWL=-0.000, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/261 X-Sequence-Number: 38086 --0-2117330868-1269194610=:7290 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable If you have a multi-processor machine (more than 2) you could look into pig= z, which is a parallelized implementation of gzip. I gotten dramatic reduct= ions in wall time using it to zip dump files.=A0 The compressed file is rea= dable by ungzip. Bob Lunney From: Dave Crooke Subject: Re: [PERFORM] pg_dump far too slow To: "David Newall" Cc: "Tom Lane" , pgsql-performance@postgresql.org, rober= tmhaas@gmail.com Date: Sunday, March 21, 2010, 10:33 AM One more from me .... If you think that the pipe to GZIP may be causing pg_dump to stall, try put= ting 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. =0A 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 = wrote: =0AThanks for all of the suggestions, guys, which gave me some pointers on = new directions to look, and I learned some interesting things. =0A=0A =0AThe first interesting thing was that piping (uncompressed) pg_dump into = gzip, instead of using pg_dump's internal compressor, does bring a lot of e= xtra parallelism into play. =A0(Thank you, Matthew Wakeling.) =A0I observed= gzip using 100% CPU, as expected, and also two, count them, two postgres p= rocesses collecting data, each consuming a further 80% CPU. =A0It seemed to= me that Postgres was starting and stopping these to match the capacity of = the consumer (i.e. pg_dump and gzip.) =A0Very nice. =A0Unfortunately one of= these processes dropped eventually, and, according to top, the only non-id= le process running was gzip (100%.) =A0Obviously there were postgress and p= g_dump processes, too, but they were throttled by gzip's rate of output and= effectively idle (less than 1% CPU). =A0That is also interesting. =A0The f= inal output from gzip was being produced at the rate of about 0.5MB/second,= which seems almost unbelievably slow. =0A=0A =0AI next tried Tom Lane's suggestion, COPY WITH BINARY, which produced the= complete 34GB file in 30 minutes (a good result.) =A0I then compressed tha= t with gzip, which took an hour and reduced the file to 32GB (hardly worth = the effort) for a total run time of 90 minutes. =A0In that instance, gzip p= roduced output at the rate of 10MB/second, so I tried pg_dump -Z0 to see ho= w quickly that would dump the file. =A0I had the idea that I'd go on to see= how quickly gzip would compress it, but unfortunately it filled my disk be= fore 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 BIN= ARY; all those backslashes, as Tom pointed out. =A0For the aforementioned r= eason, I didn't get to see how gzip would perform. =A0For the record, pg_du= mp with no compression produced output at the rate of 26MB/second; a rather= meaningless number given the 200%+ expansion of final output. =0A=0A =0AI am now confident the performance problem is from gzip, not Postgres an= d 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. =A0Given the spectacular job that postgres did in adjust= ing it's rate of output to match the consumer process, I did wonder if ther= e might have been a tragic interaction between postgres and gzip; perhaps p= ostgres 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 s= o inefficient that it hogs the CPU; but it don't think that likely. =A0I ha= d a peek at gzip's source (surprisingly readable) and on first blush it doe= s seem that unfortunate input could result in only a few bytes being writte= n each time through the loop, meaning only a few more bytes could be read i= n. =0A=0A =0AJust 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 backu= p command to exclude it. =A0Total size of compressed back sans PDFs circa 7= MB taking around 30 seconds. =0A=0A =0A=0A=0A=0A --0-2117330868-1269194610=:7290 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
If you have a multi-processor machine (more t= han 2) you could look into pigz, which is a parallelized implementation of = gzip. I gotten dramatic reductions in wall time using it to zip dump files.=   The compressed file is readable by ungzip.

Bob Lunney

From: Dave Crooke <dcrooke@gmail.com>
Sub= ject: Re: [PERFORM] pg_dump far too slow
To: "David Newall" <postgres= ql@davidnewall.com>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>, pgsql-p= erformance@postgresql.org, robertmhaas@gmail.com
Date: Sunday, March 21,= 2010, 10:33 AM

One more from me ....
If you think that the pipe to GZIP may be causing pg_dump to stall, try p= utting something like buffer(1) in the pipeline ... it doesn't generally co= me 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 i= n Perl when I used to do a lot of digital video hacking, and it helped with= chaining together tools like mplayer and mpeg.
=0A
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@davidnewall.com> wr= ote:
=0A
Thanks= for all of the suggestions, guys, which gave me some pointers on new direc= tions to look, and I learned some interesting things.
=0A=0A
=0AThe f= irst interesting thing was that piping (uncompressed) pg_dump into gzip, in= stead of using pg_dump's internal compressor, does bring a lot of extra par= allelism into play.  (Thank you, Matthew Wakeling.)  I observed g= zip using 100% CPU, as expected, and also two, count them, two postgres pro= cesses collecting data, each consuming a further 80% CPU.  It seemed t= o 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 postg= ress 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 interest= ing.  The final output from gzip was being produced at the rate of abo= ut 0.5MB/second, which seems almost unbelievably slow.
=0A=0A
=0AI ne= xt tried Tom Lane's suggestion, COPY WITH BINARY, which produced the comple= te 34GB file in 30 minutes (a good result.)  I then compressed that wi= th 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 pr= oduced 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 s= ee 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 B= INARY; all those backslashes, as Tom pointed out.  For the aforementio= ned reason, I didn't get to see how gzip would perform.  For the recor= d, pg_dump with no compression produced output at the rate of 26MB/second; = a rather meaningless number given the 200%+ expansion of final output.
=0A=0A
=0AI am now confident the performance problem is from= gzip, not Postgres and wonder if I should read up on gzip to find why it w= ould work so slowly on a pure text stream, albeit a representation of PDF w= hich intrinsically is fairly compressed.  Given the spectacular job th= at postgres did in adjusting it's rate of output to match the consumer proc= ess, I did wonder if there might have been a tragic interaction between pos= tgres and gzip; perhaps postgres limits its rate of output to match gzip; a= nd gzip tries to compress what's available, that being only a few bytes; an= d perhaps that might be so inefficient that it hogs the CPU; but it don't t= hink that likely.  I had a peek at gzip's source (surprisingly readabl= e) and on first blush it does seem that unfortunate input could result in o= nly a few bytes being written each time through the loop, meaning only a fe= w more bytes could be read in.
=0A=0A
=0AJust to complete the report,= I created a child table to hold the PDF's, which are static, and took a du= mp 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.=
=0A=0A

=0A
=0A=0A=0A=0A --0-2117330868-1269194610=:7290--