From: Cédric Villemain <cedric.villemain@dalibo.com>
To: Guillaume Smet <guillaume.smet@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
Subject: Re: More shared buffers causes lower performances
Date: Wed, 26 Dec 2007 12:06:47 +0100
Message-ID: <477235C7.8010003@dalibo.com> (raw)
In-Reply-To: <1d4e0c10712251606q6f9503deye3da4b68c51bf5e@mail.gmail.com>
References: <1d4e0c10712251606q6f9503deye3da4b68c51bf5e@mail.gmail.com>
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.orgAttachments:cedric.villemain.vcf (text/x-vcard, 264B) download
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
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: cedric.villemain@dalibo.com, guillaume.smet@gmail.com, stefan@kaltenbrunner.cc
Subject: Re: More shared buffers causes lower performances
In-Reply-To: <477235C7.8010003@dalibo.com>
* 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