Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 520B5637270 for ; Sun, 14 Mar 2010 05:07:16 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 09082-05 for ; Sun, 14 Mar 2010 08:07:05 +0000 (UTC) X-Greylist: delayed 00:05:02.540542 by SQLgrey-1.7.6 Received: from hawking.rebel.net.au (hawking.rebel.net.au [203.20.69.83]) by mail.postgresql.org (Postfix) with ESMTP id 24634637200 for ; Sun, 14 Mar 2010 05:06:45 -0300 (ADT) Received: from [192.168.0.8] (ppp118-210-101-119.lns20.adl2.internode.on.net [::ffff:118.210.101.119]) (AUTH: LOGIN davidn, SSL: TLSv1/SSLv3,256bits,AES256-SHA) by hawking.rebel.net.au with esmtp; Sun, 14 Mar 2010 18:30:31 +1030 id 00110081.4B9C979F.00001F78 Message-ID: <4B9C97E1.40509@davidnewall.com> Date: Sun, 14 Mar 2010 18:31:37 +1030 From: David Newall User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: pg_dump far too slow Content-Type: multipart/alternative; boundary="------------080703080506020404060802" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/133 X-Sequence-Number: 37958 This is a multi-part message in MIME format. --------------080703080506020404060802 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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. CPU load is 100% on the core executing pg_dump, and negligible on all others cores. The system is read-mostly, and largely idle. The exact invocation was: nohup time pg_dump -f database.dmp -Z9 database I presumed pg_dump was CPU-bound because of gzip compression, but a test I ran makes that seem unlikely: Copying the database files to a USB hard drive (cp -r /var/lib/postgresql/8.3/main /mnt) took 25 minutes; and gzip-compressing the first first 500MB of the dumpfile (dd if=database.dmp bs=64k count=16000 | time gzip -9 > dd.gz) took one minute and 15 seconds; to gzip the complete 51GB set of files should take no more than 90 minutes. The database is unremarkable except for one table, the biggest, which contains a bytea column, and which pg_dump has been outputting for at least 39 hours. That table has 276,292 rows, in which the bytea for 140,695 contains PDFs totalling 32,791MB, and the bytea for the remaining 135,597 rows contains PostScript totalling 602MB. I think I've never done a full vacuum; only ever auto-vacuum; however I did copy the table to new, deleted the old, and renamed, which I expect is effectively equivalent for it; which is described by the following schema: Table "database.bigtable" Column | Type | Modifiers --------------+-------------------+-------------------- headerid | integer | not null member | numeric(10,0) | not null postcode | character varying | bsp | character varying | details | bytea | not null membertypeid | integer | not null default 0 Indexes: "bigtable_pkey" PRIMARY KEY, btree (headerid, member) "bigtable_member" btree (member) Foreign-key constraints: "bigtable_headerid_fkey" FOREIGN KEY (headerid) REFERENCES header(headerid) The following describes the application environment: * PostgreSQL 8.3.8 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3 * pg_dump (PostgreSQL) 8.3.8 * Ubuntu 9.04 * Linux server 2.6.28-13-server #45-Ubuntu SMP Tue Jun 30 20:51:10 UTC 2009 i686 GNU/Linux * Intel(R) Xeon(R) CPU E5430 @ 2.66GHz (4 core) * RAM 2GB * 2 SATA, 7200rpm disks with hardware RAID-1 (IBM ServeRAID) My question is, what's going on? Thanks, David --------------080703080506020404060802 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Evening all,

Maiden post to this list.=C2=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=C2=A0 and PG 8.3.=C2=A0 Forty hours ago I star= ted a plain-format dump, compressed with -Z9, and it is still running, having produced 32GB of an expected 40 - 45GB of compressed output.=C2=A0 CPU lo= ad is 100% on the core executing pg_dump, and negligible on all others cores.=C2=A0 The system is read-mostly, and largely idle.=C2=A0 The exact= invocation was:

=C2=A0=C2=A0=C2=A0 nohup time pg_dump -f database.dmp -Z9 database


I presumed pg_dump was CPU-bound because of gzip compression, but a
test I ran makes that seem unlikely: Copying the database files to a
USB hard drive (cp -r /var/lib/postgresql/8.3/main /mnt) took 25
minutes; and gzip-compressing the first first 500MB of the dumpfile (dd
if=3Ddatabase.dmp bs=3D64k count=3D16000 | time gzip -9 > dd.gz) took =
one
minute and 15 seconds; to gzip the complete 51GB set of files should
take no more than 90 minutes.



The database is unremarkable except for one table, the biggest, which
contains a bytea column, and which pg_dump has been outputting for at
least 39 hours.=C2=A0 That table has 276,292 rows, in which the bytea for=

140,695 contains PDFs totalling 32,791MB, and the bytea for the
remaining 135,597 rows contains PostScript totalling 602MB.=C2=A0 I think=

I've never done a full vacuum; only ever auto-vacuum; however I did
copy the table to new, deleted the old, and renamed, which I expect is
effectively equivalent for it; which is described by the following
schema:




             Table "database.bigtable"
    Column    |       Type        |     Modifiers   =20
--------------+-------------------+--------------------
 headerid     | integer           | not null
 member       | numeric(10,0)     | not null
 postcode     | character varying |
 bsp          | character varying |
 details      | bytea             | not null
 membertypeid | integer           | not null default 0
Indexes:
    "bigtable_pkey" PRIMARY KEY, btree (headerid, member)
    "bigtable_member" btree (member)
Foreign-key constraints:
    "bigtable_headerid_fkey" FOREIGN KEY (headerid) REFERENCES header(hea=
derid)




The following describes the application environment:


    
  
  • PostgreSQL 8.3.8 on i486-pc-linux-gnu, compiled by GCC
gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3
    • 
  
  • pg_dump (PostgreSQL) 8.3.8
    • 
  
  • Ubuntu 9.04
    • 
  
  • Linux server 2.6.28-13-server #45-Ubuntu SMP Tue Jun 30 20:51:10
UTC 2009 i686 GNU/Linux
    • 
  
  • Intel(R) Xeon(R) CPU E5430=C2=A0 @ 2.66GHz (4 core)
    • 
  
  • RAM 2GB
    • 
  
  • 2 SATA, 7200rpm disks with hardware=C2=A0 RAID-1 (IBM ServeRAID)




My question is, what's going on?



Thanks,



David




--------------080703080506020404060802--