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@[EMAIL PROTECTED]
Mon 4/14/2008 7:58 PM
To: Phillip Smith
Cc: Ryan Wells; pgsql-admin@[EMAIL PROTECTED]
Re: [ADMIN] Slow pg_dump=20
=20
"Phillip Smith" <phillip.smith@[EMAIL PROTECTED]
> 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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
6.5.7652.24">
<TITLE>RE: [ADMIN] Slow pg_dump </TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<BR>
<P><FONT SIZE=3D2>Thanks for the info on TOAST. We're still =
finding our legs with Postgres after several years on MySQL.<BR>
<BR>
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.<BR>
<BR>
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.<BR>
<BR>
Thanks!<BR>
<BR>
-----Original Message-----<BR>
From: Tom Lane [<A =
HREF=3D"mailto:tgl@[EMAIL PROTECTED]
">mailto:tgl@[EMAIL PROTECTED]
>]<BR>
Sent: Mon 4/14/2008 7:58 PM<BR>
To: Phillip Smith<BR>
Cc: Ryan Wells; pgsql-admin@[EMAIL PROTECTED]
>
Subject: Re: [ADMIN] Slow pg_dump<BR>
<BR>
"Phillip Smith" <phillip.smith@[EMAIL PROTECTED]
=
writes:<BR>
>> Here's my interpretation of those results: the TOAST tables =
for<BR>
>> our image files are compressed by Postgres. During the =
backup,<BR>
>> pg_dump uncompresses them, and if compression is turned on,<BR>
>> recompresses the backup. Please correct me if I'm wrong =
there.<BR>
<BR>
No, the TOAST tables aren't compressed, they're pretty much going to =
be<BR>
the raw image data (plus a bit of overhead).<BR>
<BR>
What I think is happening is that COPY OUT is encoding the bytea<BR>
data fairly inefficiently (one byte could go to \\nnn, five bytes)<BR>
and the compression on the pg_dump side isn't doing very well at =
buying<BR>
that back.<BR>
<BR>
I experimented a bit and noticed that pg_dump -Fc is a great deal<BR>
smarter about storing large objects than big bytea fields --- it =
seems<BR>
to be pretty nearly one-to-one with the original data size when =
storing<BR>
a compressed file that was put into a large object. I dunno if =
it's<BR>
practical for you to switch from bytea to large objects, but in the =
near<BR>
term I think that's your only option if the dump file size is a<BR>
showstopper problem for you.<BR>
<BR>
=
=
regards, tom lane<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C89E95.39EFC9A8--
|
