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

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/ind...benchmark.html
[2] http://www.kaltenbrunner.cc/blog/uploads/83b4shm.gif
[3] http://people.openwide.fr/~gsmet/pos...ed_buffers.png
(X=shared_buffers in MB/Y=results with pgbench)

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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/ind...benchmark.html
> [2] http://www.kaltenbrunner.cc/blog/uploads/83b4shm.gif
> [3] http://people.openwide.fr/~gsmet/pos...ed_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



---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Wed, 2007-12-26 at 01:06 +0100, Guillaume Smet wrote:

> 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

Can you try with

bgwriter_lru_maxpages = 0

So we can see if the bgwriter has any hand in this?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Dec 26, 2007 12:06 PM, Cédric Villemain <cedric.villemain@dalibo.com> wrote:
> Which kernel do you have ?

Kernel of the distro. So a RH flavoured 2.6.18.

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Dec 26, 2007 12:21 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> Can you try with
>
> bgwriter_lru_maxpages = 0
>
> So we can see if the bgwriter has any hand in this?

I will. I'm currently running tests with less concurrent clients (16)
with exactly the same results:
64M 4213.314902
256M 4012.782820
512M 3676.840722
768M 3377.791211
1024M 2863.133965
64M again 4274.531310

I'm rerunning the tests using Greg Smith's pgbench-tools [1] to obtain
a graph of each run.

[1] http://www.westnet.com/~gsmith/conte...ench-tools.htm

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Dec 26, 2007 12:21 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> bgwriter_lru_maxpages = 0
>
> So we can see if the bgwriter has any hand in this?

It doesn't change the behaviour I have.

It's not checkpointing either as using pgbench-tools, I can see that
tps and latency are quite stable during the entire run. Btw, thanks
Greg for these nice tools.

I thought it may be some sort of lock contention so I made a few tests
with -N but I have the same behaviour.

Then I decided to perform read-only tests using -S option (pgbench -S
-s 100 -c 16 -t 30000 -U postgres bench). And still the same
behaviour:
shared_buffers=64MB : 20k tps
shared_buffers=1024MB : 8k tps

Any other idea?

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Hello

I tested it and it is true. In my configuration 1GRam, Fedora 8, is
PostgreSQL most fast with 32M shared buffers . Diff is about 5% to
256M

Regards
Pavel Stehule

On 26/12/2007, Guillaume Smet <guillaume.smet@gmail.com> wrote:
> On Dec 26, 2007 12:21 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
> > bgwriter_lru_maxpages = 0
> >
> > So we can see if the bgwriter has any hand in this?
>
> It doesn't change the behaviour I have.
>
> It's not checkpointing either as using pgbench-tools, I can see that
> tps and latency are quite stable during the entire run. Btw, thanks
> Greg for these nice tools.
>
> I thought it may be some sort of lock contention so I made a few tests
> with -N but I have the same behaviour.
>
> Then I decided to perform read-only tests using -S option (pgbench -S
> -s 100 -c 16 -t 30000 -U postgres bench). And still the same
> behaviour:
> shared_buffers=64MB : 20k tps
> shared_buffers=1024MB : 8k tps
>
> Any other idea?
>
> --
> Guillaume
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Dec 26, 2007 4:41 PM, Guillaume Smet <guillaume.smet@gmail.com> wrote:
> Then I decided to perform read-only tests using -S option (pgbench -S
> -s 100 -c 16 -t 30000 -U postgres bench). And still the same
> behaviour:
> shared_buffers=64MB : 20k tps
> shared_buffers=1024MB : 8k tps

Some more information. If I strace the backends during the test, the
test is faster with shared_buffers=1024MB and I have less system calls
(less read and less lseek).

A quick cut | uniq | sort gives me:
With 64MB:
12548 semop
160039 sendto
160056 recvfrom
294289 read
613338 lseek

With 1024MB:
11396 semop
129947 read
160039 sendto
160056 recvfrom
449584 lseek

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

On Wed, 26 Dec 2007, Guillaume Smet wrote:

> It's not checkpointing either as using pgbench-tools, I can see that
> tps and latency are quite stable during the entire run. Btw, thanks
> Greg for these nice tools.

I stole the graph idea from Mark Wong's DBT2 code and one of these days
I'll credit him appropriately.

> Then I decided to perform read-only tests using -S option (pgbench -S
> -s 100 -c 16 -t 30000 -U postgres bench). And still the same
> behaviour:
> shared_buffers=64MB : 20k tps
> shared_buffers=1024MB : 8k tps

Ah, now this is really interesting, as it rules out all the write
components and should be easy to replicate even on a smaller server. As
you've already dumped a bunch of time into this the only other thing I
would suggest checking is whether the same behavior also happens on 8.2 on
your server.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Dec 26, 2007 7:23 PM, Greg Smith <gsmith@gregsmith.com> wrote:
> Ah, now this is really interesting, as it rules out all the write
> components and should be easy to replicate even on a smaller server. As
> you've already dumped a bunch of time into this the only other thing I
> would suggest checking is whether the same behavior also happens on 8.2 on
> your server.

Let's go with 8.2.5 on the same server (-s 100 / 16 clients / 50k
transactions per client / only read using -S option):
64MB: 33814 tps
512MB: 35833 tps
1024MB: 36986 tps
It's more consistent with what I expected.

I used PGDG RPMs compiled by Devrim for 8.2.5 and the ones I compiled
myself for 8.3b4 (based on the src.rpm of Devrim). I just asked Devrim
to build a set of x86_64 RPMs for 8.3b4 to see if it's not a
compilation problem (they were compiled on a brand new box freshly
installed so it would be a bit surprising but I want to be sure). He's
kindly uploading them right now so I'll work on new tests using his
RPMs.

I'll keep you informed of the results.

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate