Thread: Increasing checkpoint distance helps 7.2 noticeably

The attached curves are for pgbench in a scale-factor-500 database,
postmaster options -F -N 100 -B 3800 on a 4-way Linux machine,
current CVS sources.

I noticed that with the default WAL options, the system was spawning
a checkpoint process every fifteen seconds or so during the pgbench
run.  Bad news.  (Aside from the I/O implied by the checkpoint itself,
there's a big penalty in increased WAL traffic, since the first update
of any page after a checkpoint dumps that whole page to WAL.)
I bumped up the checkpoint_segments parameter to ensure checkpoints
wouldn't happen so frequently, and for good measure kicked wal_files
up too.  As you can see, it helped noticeably --- at the price of
several times as much WAL disk space, of course.

Curiously, I'm seeing no improvement whatever from increasing
checkpoint_segments and wal_files in 7.1.3 on the same hardware and same
test conditions.  7.2 has a better algorithm for managing WAL segments
(recycle rather than delete and recreate), but still it seems odd that
7.1.3 can't benefit at all.

BTW, has anyone experimented with the OSDB benchmark at
http://osdb.sourceforge.net/ ?  I'm wondering if it might give
more useful numbers than pgbench does.

            regards, tom lane

Tom Lane wrote:


> BTW, has anyone experimented with the OSDB benchmark at
> http://osdb.sourceforge.net/ ?  I'm wondering if it might give
> more useful numbers than pgbench does.


Graphs like this are worth saving and adding to the documentation as an 
aid to understanding various approaches to tuning.

Along with the scripts that generate the graphs as an example that folks 
can use if they have to tune a particular set of queries on a particular 
set of data.


-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org

Don Baccus wrote:
> 
> Tom Lane wrote:
> 
> > BTW, has anyone experimented with the OSDB benchmark at
> > http://osdb.sourceforge.net/ ?  I'm wondering if it might give
> > more useful numbers than pgbench does.

I heartily recommend OSDB.  It seems to give reliable numbers, and
simulates multiple clients quite well.

It's the continuation of the code which Compaq sponsored for internal
testing of their own servers.

It's based on the AS3AP (ANSI SQL, Standard Scalable and Portable)
benchmark, with the exception it generates a lot more information about
how the database is doing, unlike the official benchmark which pretty
much just generates one number at the end.

The AS3AP benchmark is described and documented at :

http://www.benchmarkresources.com/handbook/5.html

Version 0.11 of the OSDB software
(http://sourceforge.net/project/showfiles.php?group_id=18681&release_id=55146)
supports PostgreSQL 7.1+.

I remember having trouble with hash indices, and having to modify the
code to use btree indices instead, as PostgreSQL seems to have
deadlocking problems with hash indices when multiple people access them.

Also, you have to generate your own dataset using a specific program (I
have it if anyone needs it), or download the test datasets.  The test
datasets have a maximum of about 40MB, but if you use the program to
generate your own data, you can generate test data up to about 44GB or
so (or maybe more, I don't remember).

Hope that info is useful for someone.

:-)

Regards and best wishes,

Justin Clift


> 
> Graphs like this are worth saving and adding to the documentation as an
> aid to understanding various approaches to tuning.
> 
> Along with the scripts that generate the graphs as an example that folks
> can use if they have to tune a particular set of queries on a particular
> set of data.
> 
> --
> Don Baccus
> Portland, OR
> http://donb.photo.net, http://birdnotes.net, http://openacs.org
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."  - Indira Gandhi