Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 7F52065A74B for ; Tue, 16 Mar 2010 00:05:39 -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 87098-10 for ; Tue, 16 Mar 2010 03:05:27 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 8E53B635422 for ; Tue, 16 Mar 2010 00:04:46 -0300 (ADT) Received: by bwz8 with SMTP id 8so3565509bwz.3 for ; Mon, 15 Mar 2010 20:04:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=NO2Lus/ndriL6BvSRW3DyQ0YqWYL4IBylKU0McUDYKw=; b=QVylappDmeebe1LSCHXX5zr6RAI4wD/nEjrVI/dbuuyQxsDYsJPl894od4Rqid0C9X xZAw18c/AsyqwD08QhYMCeXgl1458Xvm4Z5lR4//i2t4Z3nIPtl9dcepDYfdzBXbEyEQ SPjSnDRWzEmPsU1Hbxe0s/Q83KLQ4IN5UnH2U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=T3LaGn2DFrr8w136o8HA+zas2keoOp4vySGD9/69Tzqh22hy9aLWZd0LbR8f2MlMFX Yic1Br7kK3azQUi3W9E2ic5FHinns+M7HU57z9zc/x1a8dOnQo73Opb09+0NB+7eB3x7 eljle2EHzFUUjx+ZbcprNeJ24qgabtDD3bn1I= MIME-Version: 1.0 Received: by 10.204.36.70 with SMTP id s6mr3507806bkd.22.1268708685380; Mon, 15 Mar 2010 20:04:45 -0700 (PDT) In-Reply-To: <4B9C97E1.40509@davidnewall.com> References: <4B9C97E1.40509@davidnewall.com> Date: Mon, 15 Mar 2010 22:04:45 -0500 Message-ID: Subject: Re: pg_dump far too slow From: Dave Crooke To: David Newall Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00032555413683fcc40481e242bc X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.364 tagged_above=-10 required=5 tests=AWL=0.234, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/160 X-Sequence-Number: 37985 --00032555413683fcc40481e242bc Content-Type: text/plain; charset=ISO-8859-1 As a fellow PG newbie, some thoughts / ideas .... 1. What is the prupose of the dump (backup, migration, ETL, etc.)? Why plain? Unless you have a need to load this into a different brand of database at short notice, I'd use native format. 2. If you goal is indeed to get the data into another DB, use an app which can do a binary-to-binary transfer, e.g. a little homegrown tool in Java that connects to both with JDBC, or a data migration ETL tool. 3. If pg_dump is still CPU bound, then don't get pg_dump to compress the archive, instead do *pg_dump -F c -Z 0 ... | gzip >foo.dmp.gz* ... this way the compression runs on a different core from the formatting 4. Don't use *-Z9*, the return on investment isn't worth it (esp. if you are CPU bound), use the default GZIP compression instead, or if you need to minimize storage, experiment with higher levels until the CPU running GZIP is close to, but not totally, maxed out. 5. I see no other drives mentioned ... is your dump being written to a partition on the same RAID-1 pair that PG is running on? Spring for another drive to avoid the seek contention ... even if you were to stream the dump to a temporary filesystem on a single commodity consumer drive ($99 for a 1.5TB SATA-300 spindle) with no RAID, you could then copy it back to the RAID set after pg_dump completes, and I'd give you good odds it'd be a quicker end to end process. Cheers Dave On Sun, Mar 14, 2010 at 3:01 AM, David Newall wrote: > Evening all, > > Maiden post to this list. I've a performance problem for which I'm > uncharacteristically in need of good advice. > > I have a read-mostly database using 51GB on an ext3 filesystem on a server > running Ubuntu 9.04 and PG 8.3. Forty hours ago I started a plain-format > dump, compressed with -Z9, and it is still running, having produced 32GB of > an expected 40 - 45GB of compressed output. > --00032555413683fcc40481e242bc Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable As a fellow PG newbie, some thoughts / ideas ....

1. What is the pru= pose of the dump (backup, migration, ETL, etc.)? Why plain? Unless you have= a need to load this into a different brand of database at short notice, I&= #39;d use native format.

2. If you goal is indeed to get the data into another DB, use an app wh= ich can do a binary-to-binary transfer, e.g. a little homegrown tool in Jav= a that connects to both with JDBC, or a data migration ETL tool.

3. If pg_dump is still CPU bound, then don't get pg_dump to compress th= e archive, instead do=A0 pg_dump -F c -Z 0=A0 ... | gzip >foo.dmp.gz<= /b> ... this way the compression runs on a different core from the formatti= ng

4. Don't use -Z9, the return on investment isn't worth i= t (esp. if you are CPU bound), use the default GZIP compression instead, or= if you need to minimize storage, experiment with higher levels until the C= PU running GZIP is close to, but not totally, maxed out.

5. I see no other drives mentioned ... is your dump being written to a = partition on the same RAID-1 pair that PG is running on? Spring for another= drive to avoid the seek contention ... even if you were to stream the dump= to a temporary filesystem on a single commodity consumer drive ($99 for a = 1.5TB SATA-300 spindle) with no RAID, you could then copy it back to the RA= ID set after pg_dump completes, and I'd give you good odds it'd be = a quicker end to end process.

Cheers
Dave

On Sun, Mar 14, 2010 a= t 3:01 AM, David Newall <postgresql@davidnewall.com> wrote:
Evening all,

Maiden post to this list.=A0 I've a performance problem for which I'= ;m uncharacteristically in need of good advice.

I have a read-mostly database using 51GB on an ext3 filesystem on a server running Ubuntu 9.04=A0 and PG 8.3.=A0 Forty hours ago I started a plain-format dump, compressed with -Z9, and it is still running, having produced 32GB of an expected 40 - 45GB of compressed output.=A0
<snip>
--00032555413683fcc40481e242bc--