From: Gregory Stark <stark@enterprisedb.com>
To: Jared Mauch <jared@puck.nether.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_dump performance
Date: Thu, 27 Dec 2007 13:14:25 +0000
Message-ID: <871w98xosu.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <20071226202230.GA76524@puck.nether.net>
References: <20071226202230.GA76524@puck.nether.net>
"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!
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: stark@enterprisedb.com, jared@puck.nether.net
Subject: Re: pg_dump performance
In-Reply-To: <871w98xosu.fsf@oxford.xeocode.com>
* 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