Message-ID: <477235C7.8010003@dalibo.com>
Date: Wed, 26 Dec 2007 12:06:47 +0100
From: =?ISO-8859-1?Q?C=E9dric_Villemain?= <cedric.villemain@dalibo.com>
Organization: Dalibo
User-Agent: Mozilla-Thunderbird 2.0.0.6 (X11/20071009)
MIME-Version: 1.0
To: Guillaume Smet <guillaume.smet@gmail.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>,
	Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
Subject: Re: More shared buffers causes lower performances
References: <1d4e0c10712251606q6f9503deye3da4b68c51bf5e@mail.gmail.com>
In-Reply-To: <1d4e0c10712251606q6f9503deye3da4b68c51bf5e@mail.gmail.com>
Content-Type: multipart/mixed; boundary="------------030602050005030802030908"

This is a multi-part message in MIME format.
--------------030602050005030802030908
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Guillaume Smet a écrit :
> Hi all,
>
> I'm currently benchmarking the new PostgreSQL server of one of our
> customers with PostgreSQL 8.3 beta4. I have more or less the same
> configuration Stefan tested in his blog [1]:
> - Dell 2900 with two brand new X5365 processors (quad core 3.0 GHz),
> 16 GB of memory
> - a RAID1 array for pg_xlog and a 6 disks RAID10 array for data (I
> moved pg_xlog to the RAID10 array for a few runs - same behaviour) -
> all 73 GB 15k drives
> - CentOS 5.1 - 64 bits
>
>   

Which kernel do you have ?


> I started working on pgbench tests. I made a "not so stupid"
> configuration to begin with and I was quite disappointed by my results
> compared to Stefan's. I decided to test with a more default
> shared_buffers configuration to be able to compare my results with
> Stefan's graph [2]. And the fact is that with a very low
> shared_buffers configuration, my results are quite similar to Stefan's
> results but, as soon as I put higher values of shared_buffers,
> performances begins degrading [3].
>
> I performed my tests with: pgbench -i -s 100 -U postgres bench and
> pgbench -s 100 -c 100 -t 30000 -U postgres bench. Of course, I
> initialize the database before each run. I made my tests in one
> direction then in the other with similar results so it's not a
> degradation due to consecutive runs.
>
> I lowered the number of concurrent clients to 50 because 100 is quite
> high and I obtain the same sort of results:
> shared_buffers=32MB: 1869 tps
> shared_buffers=64MB: 1844 tps
> shared_buffers=512MB: 1676 tps
> shared_buffers=1024MB: 1559 tps
>
> Non default parameters are:
> max_connections = 200
> work_mem = 32MB
> wal_buffers = 1024kB
> checkpoint_segments = 192
> effective_cache_size = 5GB
> (I use more or less the configuration used by Stefan - I had the same
> behaviour with default wal_buffers and checkpoint_segments)
>
> While monitoring the server with vmstat, I can't see any real reason
> why it's slower. When shared_buffers has a higher value, I/O are
> lower, context switches too and finally performances. The CPU usage is
> quite similar (~50-60%). I/O doesn't limit the performances AFAICS.
>
> I must admit I'm a bit puzzled. Does anyone have any pointer which
> could explain this behaviour or any way to track the issue? I'll be
> glad to perform any test needed to understand the problem.
>
> Thanks.
>
> [1] http://www.kaltenbrunner.cc/blog/index.php?/archives/21-8.3-vs.-8.2-a-simple-benchmark.html
> [2] http://www.kaltenbrunner.cc/blog/uploads/83b4shm.gif
> [3] http://people.openwide.fr/~gsmet/postgresql/tps_shared_buffers.png
> (X=shared_buffers in MB/Y=results with pgbench)
>
> --
> Guillaume
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>   


-- 
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org


--------------030602050005030802030908
Content-Type: text/x-vcard; charset=utf-8;
 name="cedric.villemain.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="cedric.villemain.vcf"

begin:vcard
fn;quoted-printable:C=C3=A9dric Villemain
n;quoted-printable:Villemain;C=C3=A9dric
org:Dalibo
email;internet:cedric.villemain@dalibo.com
title:Consultant PostgreSQL
tel;cell:+33 (0)6 74 15 56 53
x-mozilla-html:FALSE
url:dalibo.com
version:2.1
end:vcard


--------------030602050005030802030908--