From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Newall <postgresql@davidnewall.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_dump far too slow
Date: Sun, 14 Mar 2010 16:21:51 -0400
Message-ID: <24696.1268598111@sss.pgh.pa.us> (raw)
In-Reply-To: <4B9C97E1.40509@davidnewall.com>
References: <4B9C97E1.40509@davidnewall.com>
David Newall <postgresql@davidnewall.com> writes:
> [ very slow pg_dump of table with large bytea data ]
Did you look at "vmstat 1" output to see whether the system was under
any large I/O load?
Dumping large bytea data is known to be slow for a couple of reasons:
1. The traditional text output format for bytea is a bit poorly chosen.
It's not especially cheap to generate and it interacts very badly with
COPY processing, since it tends to contain lots of backslashes which
then have to be escaped by COPY.
2. Pulling the data from the out-of-line "toast" table can be expensive
if it ends up seeking all over the disk to do it. This will show up as
a lot of seeking and I/O wait, rather than CPU expense.
Since you mention having recently recopied the table into a new table,
I would guess that the toast table is reasonably well-ordered and so
effect #2 shouldn't be a big issue. But it's a good idea to check.
PG 9.0 is changing the default bytea output format to hex, in part
to solve problem #1. That doesn't help you in an 8.3 installation
of course. If you're desperate you could consider excluding this
table from your pg_dumps and backing it up separately via COPY BINARY.
The PITA factor of that might be more than you can stand though.
Offhand I can't think of any other way to ameliorate the problem
in 8.3.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, postgresql@davidnewall.com
Subject: Re: pg_dump far too slow
In-Reply-To: <24696.1268598111@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox