From: Jared Mauch <jared@puck.nether.net>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Jared Mauch <jared@puck.nether.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_dump performance
Date: Wed, 26 Dec 2007 15:58:44 -0500
Message-ID: <20071226205844.GA86666@puck.nether.net> (raw)
In-Reply-To: <4772BEF8.2030509@enterprisedb.com>
References: <20071226202230.GA76524@puck.nether.net>
<4772BEF8.2030509@enterprisedb.com>
On Wed, Dec 26, 2007 at 10:52:08PM +0200, Heikki Linnakangas wrote:
> Jared Mauch wrote:
>> pg_dump is utilizing about 13% of the cpu and the
>> corresponding postgres backend is at 100% cpu time.
>> (multi-core, multi-cpu, lotsa ram, super-fast disk).
>> ...
>> Any tips on getting pg_dump (actually the backend) to perform much closer
>> to 500k/sec or more? This would also aide me when I upgrade pg versions
>> and need to dump/restore with minimal downtime (as the data never stops
>> coming.. whee).
>
> I would suggest running oprofile to see where the time is spent. There
> might be some simple optimizations that you could do at the source level
> that would help.
>
> Where the time is spent depends a lot on the schema and data. For example,
> I profiled a pg_dump run on a benchmark database a while ago, and found
> that most of the time was spent in sprintf, formatting timestamp columns.
> If you have a lot of timestamp columns that might be the bottleneck for you
> as well, or something else.
>
> Or if you can post the schema for the table you're dumping, maybe we can
> make a more educated guess.
here's the template table that they're all copies
of:
CREATE TABLE template_flowdatas (
routerip inet,
starttime integer,
srcip inet,
dstip inet,
srcifc smallint,
dstifc smallint,
srcasn integer,
dstasn integer,
proto smallint,
srcport integer,
dstport integer,
flowlen integer,
tcpflags smallint,
tosbit smallint
);
--
Jared Mauch | pgp key available via finger from jared@puck.nether.net
clue++; | http://puck.nether.net/~jared/ My statements are only mine.
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: jared@puck.nether.net, heikki@enterprisedb.com
Subject: Re: pg_dump performance
In-Reply-To: <20071226205844.GA86666@puck.nether.net>
* 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