From: Robert Treat <xzilla@users.sourceforge.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: upping checkpoints on production server
Date: 25 Sep 2003 11:23:12 -0400
Message-ID: <1064503392.30308.2160.camel@camel> (raw)
In-Reply-To: <25883.1064440665@sss.pgh.pa.us>
References: <1064438654.25702.2076.camel@camel>
<25883.1064440665@sss.pgh.pa.us>
On Wed, 2003-09-24 at 17:57, Tom Lane wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> > In .conf file I have default checkpoints set to 3, but I noticed that in
> > my pg_xlog directory I always seem to have at least 8 log files. Since
> > this is more than the suggested 7, I'm wondering if this means I ought
> > to bump my checkpoint segments up to 4?
>
> Hm. What is the typical delta in the mod times of the log files? It
> sounds like you are in a regime where checkpoints are always triggered
> by checkpoint_segments and never by checkpoint_timeout, in which case
> increasing the former might be a good idea. Or decrease the latter,
> but that could put a drag on performance.
>
# ls -lht /var/lib/pgsql/data/pg_xlog/
total 129M
-rw------- 1 postgres postgres 16M Sep 25 11:12 0000006E00000059
-rw------- 1 postgres postgres 16M Sep 25 11:12 0000006E0000005A
-rw------- 1 postgres postgres 16M Sep 25 11:08 0000006E00000058
-rw------- 1 postgres postgres 16M Sep 25 11:05 0000006E0000005F
-rw------- 1 postgres postgres 16M Sep 25 11:02 0000006E0000005E
-rw------- 1 postgres postgres 16M Sep 25 10:59 0000006E0000005D
-rw------- 1 postgres postgres 16M Sep 25 10:55 0000006E0000005B
-rw------- 1 postgres postgres 16M Sep 25 10:51 0000006E0000005C
#ls -lht /var/lib/pgsql/data/pg_xlog/
total 129M
-rw------- 1 postgres postgres 16M Sep 25 10:52 0000006E00000054
-rw------- 1 postgres postgres 16M Sep 25 10:51 0000006E00000053
-rw------- 1 postgres postgres 16M Sep 25 10:49 0000006E00000052
-rw------- 1 postgres postgres 16M Sep 25 10:45 0000006E00000059
-rw------- 1 postgres postgres 16M Sep 25 10:40 0000006E00000057
-rw------- 1 postgres postgres 16M Sep 25 10:37 0000006E00000058
-rw------- 1 postgres postgres 16M Sep 25 10:33 0000006E00000056
-rw------- 1 postgres postgres 16M Sep 25 10:29 0000006E00000055
from the 7.4 docs:
"Checkpoints are fairly expensive because they force all dirty kernel
buffers to disk using the operating system sync() call. Busy servers may
fill checkpoint segment files too quickly, causing excessive
checkpointing."
it goes on to mention checkpoint_warning, which I don't have in 7.3, but
I think this is a case where I'd likely see those warnings. The server
in question has a fairly high write/read ratio and is fairly busy (over
100 tps iirc).
since more often than not I don't make it to 5 minutes, seems like
upping checkpoint segments is the way to go, right?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
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: xzilla@users.sourceforge.net, tgl@sss.pgh.pa.us
Subject: Re: upping checkpoints on production server
In-Reply-To: <1064503392.30308.2160.camel@camel>
* 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