Date: Wed, 26 Dec 2007 15:58:44 -0500
From: Jared Mauch <jared@puck.nether.net>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Jared Mauch <jared@puck.nether.net>, pgsql-performance@postgresql.org
Subject: Re: pg_dump performance
Message-ID: <20071226205844.GA86666@puck.nether.net>
References: <20071226202230.GA76524@puck.nether.net>
	<4772BEF8.2030509@enterprisedb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4772BEF8.2030509@enterprisedb.com>
User-Agent: Mutt/1.5.17 (2007-11-01)

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.