Date: Tue, 1 Nov 2005 20:16:58 +1100 (EST)
From: Gavin Sherry <swm@alcove.com.au>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
Subject: Re: pgbench results interpretation?
In-Reply-To: <1130832189.21036.19.camel@Panoramix>
Message-ID: <Pine.LNX.4.58.0511012011460.6141@linuxworld.com.au>
References: <1130832189.21036.19.camel@Panoramix>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 1 Nov 2005, Joost Kraaijeveld wrote:

> Hi,
>
> I am trying to optimize my Debian Sarge AMD64 PostgreSQL 8.0
> installation, based on the recommendations from "the Annotated
> POSTGRESQL.CONF Guide for
> PostgreSQL" (http://www.powerpostgresql.com/Downloads/annotated_conf_80.html). To see the result of the recommendations I use pgbench from  postgresql-contrib.
>
> I have 3 questions about pgbench:
>
> 1. Is there a repository somewhere that shows results, using and
> documenting different kinds of hard- and software setups so that I can
> compare my results with someone elses?

Other than the archives of this mailing list, no.

>
> 2. Is there a reason for the difference in values from run-to-run of
> pgbench:
>
> The command I used (nothing else is done on the machine, not even mouse
> movement):
> jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 1000 test
>
> Results for 4 consecutive runs:
>
> tps = 272.932982 (including connections establishing)
> tps = 273.262622 (excluding connections establishing)
>
> tps = 199.501426 (including connections establishing)
> tps = 199.674937 (excluding connections establishing)
>
> tps = 400.462117 (including connections establishing)
> tps = 401.218291 (excluding connections establishing)
>
> tps = 223.695331 (including connections establishing)
> tps = 223.919031 (excluding connections establishing)

Well, firstly: pgbench is not a good benchmarking tool. It is mostly used
to generate load. Secondly, the numbers are suspicious: do you have fsync
turned off? Do you have write caching enabled? If so, you'd want to make
sure that cache is battery backed. Thirdly, the effects of caching will be
seen on subsequent runs.

>
> 3. It appears that running more transactions with the same amount of
> clients leads to a drop in the transactions per second. I do not
> understand why this is (a drop from more clients I do understand). Is
> this because of the way pgbench works, the way PostgrSQL works or even
> Linux?
>
> jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 10 test
> tps = 379.218809 (including connections establishing)
> tps = 461.968448 (excluding connections establishing)
>
> jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 100 test
> tps = 533.878031 (including connections establishing)
> tps = 546.571141 (excluding connections establishing)

Well, at this rate pgbench is only running for 2 seconds!

>
> jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 1000 test
> tps = 204.344440 (including connections establishing)
> tps = 204.533627 (excluding connections establishing)
>
> jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 10000 test
> tps = 121.486803 (including connections establishing)
> tps = 121.493681 (excluding connections establishing)
>


This degradation seems to suggest effects caused by the disk cache filling
up (assuming write caching is enabled) and checkpointing.

Hope this helps.

Gavin