Thread: More shared buffers causes lower performances
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/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
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
Attachment
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
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
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/content/postgresql/pgbench-tools.htm -- Guillaume
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
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 >
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
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
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
On Dec 26, 2007 10:52 PM, Guillaume Smet <guillaume.smet@gmail.com> wrote: > 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 had the same numbers with 8.3b4.x86_64 RPMs compiled by Devrim than with the ones I compiled myself. While discussing with Devrim, I checked the .spec with a little more attention and... I noticed that beta RPMs are by default compiled with --enable-debug and --enable-cassert which doesn't help them to fly fast... I did all my previous benchmarks with binaries compiled directly from CVS so I didn't notice it before and this new server was far faster than the box I tested 8.3devel before so I wasn't surprised by the other results.. So, the conclusion is: if you really want to test/benchmark 8.3beta4 using the RPM packages, you'd better compile your own set of RPMs using --define "beta 0". Really sorry for the noise but anyway quite happy to have discovered the pgbench-tools of Greg. I hope it will be useful to other people. I'll post new results yesterday with a clean beta4 install. -- Guillaume
On Wed, 26 Dec 2007, Guillaume Smet wrote: > beta RPMs are by default compiled with --enable-debug and > --enable-cassert which doesn't help them to fly fast... Got that right. Last time I was going crazy after running pgbench with those options and not having realized what I changed, I was getting a 50% slowdown on results that way compared to without the debugging stuff. Didn't realize it scaled with shared_buffers though. > Really sorry for the noise Nothing to be sorry for, I know I wasn't aware the beta RPMs were compiled that way. Probably need to put a disclaimer about that fact *somewhere*. It's unfortunate for you, but I know I'm glad you run into it rather than someone who wouldn't have followed through to figure out the cause. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Hi, On Wed, 2007-12-26 at 18:35 -0500, Greg Smith wrote: > Probably need to put a disclaimer about that fact *somewhere*. We mention about that in README.rpm-dist file, but I think we should mention about that at a more visible place. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/
Attachment
Greg Smith <gsmith@gregsmith.com> writes:
> On Wed, 26 Dec 2007, Guillaume Smet wrote:
>> beta RPMs are by default compiled with --enable-debug and
>> --enable-cassert which doesn't help them to fly fast...
> Got that right. Last time I was going crazy after running pgbench with
> those options and not having realized what I changed, I was getting a 50%
> slowdown on results that way compared to without the debugging stuff.
> Didn't realize it scaled with shared_buffers though.
See AtEOXact_Buffers(). There are probably any number of other
interesting scaling behaviors --- in my tests, AllocSetCheck() is
normally a major cycle-eater if --enable-cassert is set, and that costs
time proportional to the number of memory chunks allocated by the query.
Currently the docs say that --enable-cassert
Enables <firstterm>assertion</> checks in the server, which test for
many <quote>cannot happen</> conditions. This is invaluable for
code development purposes, but the tests slow things down a little.
Maybe we ought to put that more strongly --- s/a little/significantly/,
perhaps?
regards, tom lane
On Dec 27, 2007 7:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Enables <firstterm>assertion</> checks in the server, which test for > many <quote>cannot happen</> conditions. This is invaluable for > code development purposes, but the tests slow things down a little. > > Maybe we ought to put that more strongly --- s/a little/significantly/, > perhaps? +1. It seems closer to the reality. -- Guillaume
On Thu, Dec 27, 2007 at 01:10:29AM -0500, Tom Lane wrote: > Greg Smith <gsmith@gregsmith.com> writes: > > On Wed, 26 Dec 2007, Guillaume Smet wrote: > >> beta RPMs are by default compiled with --enable-debug and > >> --enable-cassert which doesn't help them to fly fast... > > > Got that right. Last time I was going crazy after running pgbench with > > those options and not having realized what I changed, I was getting a 50% > > slowdown on results that way compared to without the debugging stuff. > > Didn't realize it scaled with shared_buffers though. > > See AtEOXact_Buffers(). There are probably any number of other > interesting scaling behaviors --- in my tests, AllocSetCheck() is > normally a major cycle-eater if --enable-cassert is set, and that costs > time proportional to the number of memory chunks allocated by the query. > > Currently the docs say that --enable-cassert > > Enables <firstterm>assertion</> checks in the server, which test for > many <quote>cannot happen</> conditions. This is invaluable for > code development purposes, but the tests slow things down a little. > > Maybe we ought to put that more strongly --- s/a little/significantly/, > perhaps? Sounds like a good idea. We got bit by the same thing when doing some benchmarks on the MSVC port (and with we I mean Dave did the work, and several people couldn't understand why the numbers sucked) //Magnus
Tom Lane escribió: > Currently the docs say that --enable-cassert > > Enables <firstterm>assertion</> checks in the server, which test for > many <quote>cannot happen</> conditions. This is invaluable for > code development purposes, but the tests slow things down a little. > > Maybe we ought to put that more strongly --- s/a little/significantly/, > perhaps? I don't think it will make any difference, because people don't read configure documentation. They read configure --help. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
"Alvaro Herrera" <alvherre@commandprompt.com> writes: > Tom Lane escribió: > >> Currently the docs say that --enable-cassert >> >> Enables <firstterm>assertion</> checks in the server, which test for >> many <quote>cannot happen</> conditions. This is invaluable for >> code development purposes, but the tests slow things down a little. >> >> Maybe we ought to put that more strongly --- s/a little/significantly/, >> perhaps? > > I don't think it will make any difference, because people don't read > configure documentation. They read configure --help. Fwiw I think you're all getting a bit caught up in this one context. While the slowdown is significant when you take out the stopwatch, under normal interactive use you're not going to notice your queries being especially slow. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
On Thu, 27 Dec 2007, Gregory Stark wrote: > Fwiw I think you're all getting a bit caught up in this one context. I lost a day once over this problem. Guillaume lost at least that much. Sounds like Magnus and Dave got a good sized dose as well. Seems like something worth warning people about to me. The worst time people can run into a performance regression is when they're running a popular benchmarking tool. I didn't think this was a big problem because I thought it was limited to developers who shot their own foot, but if there are packagers turning this on to improve beta feedback it deserves some wider mention. As for the suggestion that people don't read the documentation, take a look at the above list of developers and tell me whether that group is aware of what's in the docs or not. I had never seen anyone bring this up before I ran into it, and I dumped a strong warning into http://developer.postgresql.org/index.php/Working_with_CVS#Initial_setup so at least it was written down somewhere. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith <gsmith@gregsmith.com> writes:
> ... I didn't think this was a big problem because I
> thought it was limited to developers who shot their own foot, but if there
> are packagers turning this on to improve beta feedback it deserves some
> wider mention.
Yeah, binary packages that are built with --enable-cassert perhaps need
to be labeled as "not intended for benchmarking" or some such.
regards, tom lane
"Greg Smith" <gsmith@gregsmith.com> writes: > The worst time people can run into a performance > regression is when they're running a popular benchmarking tool. Hm, perhaps pg_bench should do a "show debug_assertions" and print a warning if the answer isn't "off". We could encourage other benchmark software to do something similar. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!
Tom Lane escribió: > Greg Smith <gsmith@gregsmith.com> writes: > > ... I didn't think this was a big problem because I > > thought it was limited to developers who shot their own foot, but if there > > are packagers turning this on to improve beta feedback it deserves some > > wider mention. > > Yeah, binary packages that are built with --enable-cassert perhaps need > to be labeled as "not intended for benchmarking" or some such. Perhaps make them emit a WARNING at server start or something. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Perhaps make them emit a WARNING at server start or something.
I concur with Greg Stark's earlier comment that this is all
overreaction. Let's just fix the misleading comment in the
documentation and leave it at that.
regards, tom lane
On Dec 27, 2007 7:54 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I concur with Greg Stark's earlier comment that this is all > overreaction. Let's just fix the misleading comment in the > documentation and leave it at that. IMHO, we should also have a special tag for all the binaries distributed with these options on the official website (RPM or not). If the RPM packages' version has been tagged .debug or something like that, it would have been the first thing I checked. I like Gregory's idea to add a warning in pgbench. I usually run a few pgbench tests to check there is no obvious problem even if I use another more complicated benchmark afterwards. I don't know if that's what other people do, though. -- Guillaume
On Dec 25, 2007 7:06 PM, Guillaume Smet <guillaume.smet@gmail.com> wrote: > 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. Can you confirm that i/o is lower according to iostat? One possibility is that you are on the cusp of where your server's memory covers the database and the higher buffers results in lower memory efficiency. If raising shared buffers is getting you more page faults to disk, this would explain the lower figures regardless of the # of syscalls. If your iowait is zero though the test is cpu bound and this distinction is moot. merlin
Tom Lane wrote: > Greg Smith <gsmith@gregsmith.com> writes: > > On Wed, 26 Dec 2007, Guillaume Smet wrote: > >> beta RPMs are by default compiled with --enable-debug and > >> --enable-cassert which doesn't help them to fly fast... > > > Got that right. Last time I was going crazy after running pgbench with > > those options and not having realized what I changed, I was getting a 50% > > slowdown on results that way compared to without the debugging stuff. > > Didn't realize it scaled with shared_buffers though. > > See AtEOXact_Buffers(). There are probably any number of other > interesting scaling behaviors --- in my tests, AllocSetCheck() is > normally a major cycle-eater if --enable-cassert is set, and that costs > time proportional to the number of memory chunks allocated by the query. > > Currently the docs say that --enable-cassert > > Enables <firstterm>assertion</> checks in the server, which test for > many <quote>cannot happen</> conditions. This is invaluable for > code development purposes, but the tests slow things down a little. > > Maybe we ought to put that more strongly --- s/a little/significantly/, > perhaps? Docs updated with attached patch, backpatched to 8.3.X. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/installation.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/installation.sgml,v retrieving revision 1.302 diff -c -c -r1.302 installation.sgml *** doc/src/sgml/installation.sgml 17 Feb 2008 16:36:43 -0000 1.302 --- doc/src/sgml/installation.sgml 6 Mar 2008 21:36:39 -0000 *************** *** 1144,1157 **** <para> Enables <firstterm>assertion</> checks in the server, which test for many <quote>cannot happen</> conditions. This is invaluable for ! code development purposes, but the tests slow things down a little. Also, having the tests turned on won't necessarily enhance the stability of your server! The assertion checks are not categorized for severity, and so what might be a relatively harmless bug will still lead to server restarts if it triggers an assertion ! failure. Currently, this option is not recommended for ! production use, but you should have it on for development work ! or when running a beta version. </para> </listitem> </varlistentry> --- 1144,1158 ---- <para> Enables <firstterm>assertion</> checks in the server, which test for many <quote>cannot happen</> conditions. This is invaluable for ! code development purposes, but the tests can slow down the ! server significantly. Also, having the tests turned on won't necessarily enhance the stability of your server! The assertion checks are not categorized for severity, and so what might be a relatively harmless bug will still lead to server restarts if it triggers an assertion ! failure. This option is not recommended for production use, but ! you should have it on for development work or when running a beta ! version. </para> </listitem> </varlistentry>