Re: upping checkpoints on production server

On Wed, 2003-09-24 at 17:57, Tom Lane wrote:
> Robert Treat <xzilla(at)users(dot)sourceforge(dot)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