Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 49A372E002E for ; Mon, 14 Apr 2008 22:08:39 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 83074-10 for ; Mon, 14 Apr 2008 22:08:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from exchange.DOCS.COM (unknown [75.13.188.5]) by developer.postgresql.org (Postfix) with ESMTP id C54EA2E0040 for ; Mon, 14 Apr 2008 22:08:35 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C89E95.39EFC9A8" Subject: Re: Slow pg_dump X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Mon, 14 Apr 2008 20:08:33 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Slow pg_dump Thread-Index: Aciek962TxhDoPa3TuKnLZ7fFxmCiQAAHTDv References: <004b01c89e86$ebe8fbe0$9b0014ac@wbaus090> <25879.1208221128@sss.pgh.pa.us> From: "Ryan Wells" To: "Tom Lane" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.663 tagged_above=0 required=5 tests=AWL=0.562, HTML_MESSAGE=0.001, RDNS_NONE=0.1 X-Spam-Level: X-Archive-Number: 200804/134 X-Sequence-Number: 28898 This is a multi-part message in MIME format. ------_=_NextPart_001_01C89E95.39EFC9A8 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks for the info on TOAST. We're still finding our legs with = Postgres after several years on MySQL. We do have the flexibility to adjust our data types and schema if we = need to. We try to keep it to a minimum, but it's doable. I'm = completely open to the possibility that we just have a very inefficient = DB design or that we're misusing the data types. We'll be running some more tests looking for the sweet spot between time = and size. I expect we'll find a good balance somewhere. Thanks! -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Mon 4/14/2008 7:58 PM To: Phillip Smith Cc: Ryan Wells; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Slow pg_dump=20 =20 "Phillip Smith" writes: >> Here's my interpretation of those results: the TOAST tables for >> our image files are compressed by Postgres. During the backup, >> pg_dump uncompresses them, and if compression is turned on, >> recompresses the backup. Please correct me if I'm wrong there. No, the TOAST tables aren't compressed, they're pretty much going to be the raw image data (plus a bit of overhead). What I think is happening is that COPY OUT is encoding the bytea data fairly inefficiently (one byte could go to \\nnn, five bytes) and the compression on the pg_dump side isn't doing very well at buying that back. I experimented a bit and noticed that pg_dump -Fc is a great deal smarter about storing large objects than big bytea fields --- it seems to be pretty nearly one-to-one with the original data size when storing a compressed file that was put into a large object. I dunno if it's practical for you to switch from bytea to large objects, but in the near term I think that's your only option if the dump file size is a showstopper problem for you. regards, tom lane ------_=_NextPart_001_01C89E95.39EFC9A8 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [ADMIN] Slow pg_dump

Thanks for the info on TOAST.  We're still = finding our legs with Postgres after several years on MySQL.

We do have the flexibility to adjust our data types and schema if we = need to.  We try to keep it to a minimum, but it's doable.  = I'm completely open to the possibility that we just have a very = inefficient DB design or that we're misusing the data types.

We'll be running some more tests looking for the sweet spot between time = and size.  I expect we'll find a good balance somewhere.

Thanks!

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Mon 4/14/2008 7:58 PM
To: Phillip Smith
Cc: Ryan Wells; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Slow pg_dump

"Phillip Smith" <phillip.smith@weatherbeeta.com.au> = writes:
>> Here's my interpretation of those results: the TOAST tables = for
>> our image files are compressed by Postgres.  During the = backup,
>> pg_dump uncompresses them, and if compression is turned on,
>> recompresses the backup.  Please correct me if I'm wrong = there.

No, the TOAST tables aren't compressed, they're pretty much going to = be
the raw image data (plus a bit of overhead).

What I think is happening is that COPY OUT is encoding the bytea
data fairly inefficiently (one byte could go to \\nnn, five bytes)
and the compression on the pg_dump side isn't doing very well at = buying
that back.

I experimented a bit and noticed that pg_dump -Fc is a great deal
smarter about storing large objects than big bytea fields --- it = seems
to be pretty nearly one-to-one with the original data size when = storing
a compressed file that was put into a large object.  I dunno if = it's
practical for you to switch from bytea to large objects, but in the = near
term I think that's your only option if the dump file size is a
showstopper problem for you.

        =         =         regards, tom lane

------_=_NextPart_001_01C89E95.39EFC9A8--