Обсуждение: pg_dump performance
I've been looking at the performance of pg_dump in
the past week off and on trying to see if I can get it to
work a bit faster and was looking for tips on this.
doing a pg_dump on my 34311239 row table (1h of data btw)
results in a wallclock time of 187.9 seconds or ~182k rows/sec.
I've got my insert (COPY) performance around 100k/sec and
was hoping to get the reads to be much faster. The analysis I'm
doing is much faster doing a pg_dump than utilizing a few
queries for numerous reasons. (If you care, I can enumerate them
to you privately but the result is pg_dump is the best way to handle
the multiple bits of analysis that are needed, please trust me).
What i'm seeing:
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).
I'm not seeing myself being I/O bound so was interested
if there was a way I could tweak the backend performance or
offload some of the activity to another process.
pg8.3(beta) with the following variances from default
checkpoint_segments = 300 # in logfile segments, min 1, 16MB each
effective_cache_size = 512MB # typically 8KB each
wal_buffers = 128MB # min 4, 8KB each
shared_buffers = 128MB # min 16, at least max_connections*2, 8KB each
work_mem = 512MB # min 64, size in KB
unrelated but associated data, the table has one index on it.
not relevant for pg_dump but i'm interested in getting better concurent index
creation (utilize those cpus better but not slow down my row/sec perf)
but that's another topic entirely..
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).
Thanks!
- Jared
--
Jared Mauch | pgp key available via finger from jared@puck.nether.net
clue++; | http://puck.nether.net/~jared/ My statements are only mine.
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. -- Heikki Linnakangas EnterpriseDB http://www.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.
Jared Mauch wrote: > 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 > ); I run a quick oprofile run on my laptop, with a table like that, filled with dummy data. It looks like indeed ~30% of the CPU time is spent in sprintf, to convert the integers and inets to string format. I think you could speed that up by replacing the sprintf calls in int2, int4 and inet output functions with faster, customized functions. We don't need all the bells and whistles of sprintf, which gives the opportunity to optimize. A binary mode dump should go a lot faster, because it doesn't need to do those conversions, but binary dumps are not guaranteed to work across versions. BTW, the profiling I did earlier led me to think this should be optimized in the compiler. I started a thread about that on the gcc mailing list but got busy with other stuff and didn't follow through that idea: http://gcc.gnu.org/ml/gcc/2007-10/msg00073.html -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Wed, Dec 26, 2007 at 11:35:59PM +0200, Heikki Linnakangas wrote:
> I run a quick oprofile run on my laptop, with a table like that, filled
> with dummy data. It looks like indeed ~30% of the CPU time is spent in
> sprintf, to convert the integers and inets to string format. I think you
> could speed that up by replacing the sprintf calls in int2, int4 and inet
> output functions with faster, customized functions. We don't need all the
> bells and whistles of sprintf, which gives the opportunity to optimize.
Hmm. Given the above+below perhaps there's something that can
be tackled in the source here.. will look at poking around in there ...
our sysadmin folks don't like the idea of running patched stuff (aside from
conf changes) as they're concerned about losing patches btw upgrades.
I'm waiting on one of my hosts in Japan to come back online
so perhaps I can hack the source and attempt some optimization
after that point. It's not the beefy host that I have this on
though and not even multi-{core,cpu} so my luck may be poor.
> A binary mode dump should go a lot faster, because it doesn't need to do
> those conversions, but binary dumps are not guaranteed to work across
> versions.
I'll look at this. Since this stuff is going into something else
perhaps I can get it to be slightly faster to not convert from binary ->
string -> binary(memory) again. A number of the columns are unused in my
processing and some are used only when certain criteria are met (some
are always used).
> BTW, the profiling I did earlier led me to think this should be optimized
> in the compiler. I started a thread about that on the gcc mailing list but
> got busy with other stuff and didn't follow through that idea:
> http://gcc.gnu.org/ml/gcc/2007-10/msg00073.html
(* drift=off mode=drifting-fast *)
I'd have to say after a quick review of this, it does look
like they're right and it should go somewhat in the C lib. I'm on
Solaris 10 with my host. There may be some optimizations that the compiler
could do when linking the C library but I currently think they're on
sound footing.
(* drift=off mode=end *)
- Jared
--
Jared Mauch | pgp key available via finger from jared@puck.nether.net
clue++; | http://puck.nether.net/~jared/ My statements are only mine.
"Jared Mauch" <jared@puck.nether.net> writes: > 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). >... > pg8.3(beta) with the following variances from default > > checkpoint_segments = 300 # in logfile segments, min 1, 16MB each > effective_cache_size = 512MB # typically 8KB each > wal_buffers = 128MB # min 4, 8KB each > shared_buffers = 128MB # min 16, at least max_connections*2, 8KB each > work_mem = 512MB # min 64, size in KB Fwiw those are pretty unusual numbers. Normally work_mem is much smaller than shared_buffers since you only need one block of memory for shared buffers and work_mem is for every query (and every sort within those queries). If you have ten queries running two sorts each this setting of work_mem could consume 5GB. Raising shared buffers could improve your pg_dump speed. If all the data is in cache it would reduce the time spend moving data between filesystem cache and postgres shared buffers. What made you raise wal_buffers so high? I don't think it hurts but that's a few orders of magnitude higher than what I would expect to help. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
On Thu, Dec 27, 2007 at 01:14:25PM +0000, Gregory Stark wrote:
> "Jared Mauch" <jared@puck.nether.net> writes:
>
> > 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).
> >...
> > pg8.3(beta) with the following variances from default
> >
> > checkpoint_segments = 300 # in logfile segments, min 1, 16MB each
> > effective_cache_size = 512MB # typically 8KB each
> > wal_buffers = 128MB # min 4, 8KB each
> > shared_buffers = 128MB # min 16, at least max_connections*2, 8KB each
> > work_mem = 512MB # min 64, size in KB
>
> Fwiw those are pretty unusual numbers. Normally work_mem is much smaller than
> shared_buffers since you only need one block of memory for shared buffers and
> work_mem is for every query (and every sort within those queries). If you have
> ten queries running two sorts each this setting of work_mem could consume 5GB.
I'd still have lots of ram left :)
I'm dealing with normal query results that end up matching 5-10 million
rows based on the index (starttime) not counting the filter afterwards. Each
backend rarely makes it over 256m.
> Raising shared buffers could improve your pg_dump speed. If all the data is in
> cache it would reduce the time spend moving data between filesystem cache and
> postgres shared buffers.
I doubt it's all in cache, but I can look at this. I did not do a
lot of fine tuning of numbers, just enough to get past the defaults and have
an acceptable amount of performance.
> What made you raise wal_buffers so high? I don't think it hurts but that's a
> few orders of magnitude higher than what I would expect to help.
I'm adding chunks of ~1.2m rows every other minute. Once I increase
my data collection pool, this will go up to around [1]2-3m rows or so. I
found having higher wal and checkpoint helped. I didn't spend a lot of time
tweaking these options. Is there some way you know to determine high
watermark numbers for what is being used?
- Jared
[1] - I am concerned that with my 'insert' speed being around 100k/sec
and raw pg_dump speed being around 182k/sec i will start getting data
faster than can be stored and postprocessed.
--
Jared Mauch | pgp key available via finger from jared@puck.nether.net
clue++; | http://puck.nether.net/~jared/ My statements are only mine.