Re: More shared buffers causes lower performances

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