Thread: pgbench results interpretation?
Hi, I am trying to optimize my Debian Sarge AMD64 PostgreSQL 8.0 installation, based on the recommendations from "the Annotated POSTGRESQL.CONF Guide for PostgreSQL" (http://www.powerpostgresql.com/Downloads/annotated_conf_80.html). To see the result of the recommendations Iuse pgbench from postgresql-contrib. I have 3 questions about pgbench: 1. Is there a repository somewhere that shows results, using and documenting different kinds of hard- and software setups so that I can compare my results with someone elses? 2. Is there a reason for the difference in values from run-to-run of pgbench: The command I used (nothing else is done on the machine, not even mouse movement): jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 1000 test Results for 4 consecutive runs: tps = 272.932982 (including connections establishing) tps = 273.262622 (excluding connections establishing) tps = 199.501426 (including connections establishing) tps = 199.674937 (excluding connections establishing) tps = 400.462117 (including connections establishing) tps = 401.218291 (excluding connections establishing) tps = 223.695331 (including connections establishing) tps = 223.919031 (excluding connections establishing) 3. It appears that running more transactions with the same amount of clients leads to a drop in the transactions per second. I do not understand why this is (a drop from more clients I do understand). Is this because of the way pgbench works, the way PostgrSQL works or even Linux? jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 10 test tps = 379.218809 (including connections establishing) tps = 461.968448 (excluding connections establishing) jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 100 test tps = 533.878031 (including connections establishing) tps = 546.571141 (excluding connections establishing) jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 1000 test tps = 204.344440 (including connections establishing) tps = 204.533627 (excluding connections establishing) jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 10000 test tps = 121.486803 (including connections establishing) tps = 121.493681 (excluding connections establishing) TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
On Tue, 1 Nov 2005, Joost Kraaijeveld wrote: > Hi, > > I am trying to optimize my Debian Sarge AMD64 PostgreSQL 8.0 > installation, based on the recommendations from "the Annotated > POSTGRESQL.CONF Guide for > PostgreSQL" (http://www.powerpostgresql.com/Downloads/annotated_conf_80.html). To see the result of the recommendationsI use pgbench from postgresql-contrib. > > I have 3 questions about pgbench: > > 1. Is there a repository somewhere that shows results, using and > documenting different kinds of hard- and software setups so that I can > compare my results with someone elses? Other than the archives of this mailing list, no. > > 2. Is there a reason for the difference in values from run-to-run of > pgbench: > > The command I used (nothing else is done on the machine, not even mouse > movement): > jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 1000 test > > Results for 4 consecutive runs: > > tps = 272.932982 (including connections establishing) > tps = 273.262622 (excluding connections establishing) > > tps = 199.501426 (including connections establishing) > tps = 199.674937 (excluding connections establishing) > > tps = 400.462117 (including connections establishing) > tps = 401.218291 (excluding connections establishing) > > tps = 223.695331 (including connections establishing) > tps = 223.919031 (excluding connections establishing) Well, firstly: pgbench is not a good benchmarking tool. It is mostly used to generate load. Secondly, the numbers are suspicious: do you have fsync turned off? Do you have write caching enabled? If so, you'd want to make sure that cache is battery backed. Thirdly, the effects of caching will be seen on subsequent runs. > > 3. It appears that running more transactions with the same amount of > clients leads to a drop in the transactions per second. I do not > understand why this is (a drop from more clients I do understand). Is > this because of the way pgbench works, the way PostgrSQL works or even > Linux? > > jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 10 test > tps = 379.218809 (including connections establishing) > tps = 461.968448 (excluding connections establishing) > > jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 100 test > tps = 533.878031 (including connections establishing) > tps = 546.571141 (excluding connections establishing) Well, at this rate pgbench is only running for 2 seconds! > > jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 1000 test > tps = 204.344440 (including connections establishing) > tps = 204.533627 (excluding connections establishing) > > jkr@Panoramix:/usr/lib/postgresql/8.0/bin$ ./pgbench -c 10 -t 10000 test > tps = 121.486803 (including connections establishing) > tps = 121.493681 (excluding connections establishing) > This degradation seems to suggest effects caused by the disk cache filling up (assuming write caching is enabled) and checkpointing. Hope this helps. Gavin
Hi Gavin, Thanks for answering. On Tue, 2005-11-01 at 20:16 +1100, Gavin Sherry wrote: > On Tue, 1 Nov 2005, Joost Kraaijeveld wrote: > > 1. Is there a repository somewhere that shows results, using and > > documenting different kinds of hard- and software setups so that I can > > compare my results with someone elses? > > Other than the archives of this mailing list, no. OK. > > > > 2. Is there a reason for the difference in values from run-to-run of > > pgbench: > Well, firstly: pgbench is not a good benchmarking tool. Is there a reason why that is the case? I would like to understand why? Is it because the transaction is to small/large? Or that the queries are to small/large? Or just experience? > It is mostly used > to generate load. Secondly, the numbers are suspicious: do you have fsync > turned off? In the first trials I posted yes, in the second no. > Do you have write caching enabled? If so, you'd want to make > sure that cache is battery backed. I am aware of that, but for now, I am mostly interested in the effects of the configuration parameters. I won't do this at home ;-) > Thirdly, the effects of caching will be > seen on subsequent runs. In that case I would expect mostly rising values. I only copied and pasted 4 trials that were available in my xterm at the time of writing my email, but I could expand the list ad infinitum: the variance between the runs is very large. I also expect that if there is no shortage of memory wrt caching that the effect would be negligible, but I may be wrong. Part of using pgbench is learning about performance, not achieving it. > > 3. It appears that running more transactions with the same amount of > > clients leads to a drop in the transactions per second. I do not > > understand why this is (a drop from more clients I do understand). Is > > this because of the way pgbench works, the way PostgrSQL works or even > > Linux? > This degradation seems to suggest effects caused by the disk cache filling > up (assuming write caching is enabled) and checkpointing. Which diskcache are your referring to? The onboard harddisk or RAID5 controller caches or the OS cache? The first two I can unstand but if you refer to the OS cache I do not understand what I am seeing. I have enough memory giving the size of the database: during these duration (~) tests fsync was on, and the files could be loaded into memory easily (effective_cache_size = 32768 which is ~ 265 MB, the complete database directory 228 MB) -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl
On Tue, 1 Nov 2005, Joost Kraaijeveld wrote: > Hi Gavin, > > Thanks for answering. > > On Tue, 2005-11-01 at 20:16 +1100, Gavin Sherry wrote: > > On Tue, 1 Nov 2005, Joost Kraaijeveld wrote: > > > 1. Is there a repository somewhere that shows results, using and > > > documenting different kinds of hard- and software setups so that I can > > > compare my results with someone elses? > > > > Other than the archives of this mailing list, no. > OK. > > > > > > > 2. Is there a reason for the difference in values from run-to-run of > > > pgbench: > > Well, firstly: pgbench is not a good benchmarking tool. > Is there a reason why that is the case? I would like to understand why? > Is it because the transaction is to small/large? Or that the queries are > to small/large? Or just experience? > > > It is mostly used > > to generate load. Secondly, the numbers are suspicious: do you have fsync > > turned off? > In the first trials I posted yes, in the second no. > > > Do you have write caching enabled? If so, you'd want to make > > sure that cache is battery backed. > I am aware of that, but for now, I am mostly interested in the effects > of the configuration parameters. I won't do this at home ;-) Well, pgbench (tpc-b) suffers from inherent concurrency issues because all connections are updating the branches table heavily. As an aside, did you initialise with a scaling factor of 10 to match your level of concurrency? > > > > Thirdly, the effects of caching will be > > seen on subsequent runs. > In that case I would expect mostly rising values. I only copied and > pasted 4 trials that were available in my xterm at the time of writing > my email, but I could expand the list ad infinitum: the variance between > the runs is very large. I also expect that if there is no shortage of > memory wrt caching that the effect would be negligible, but I may be > wrong. Part of using pgbench is learning about performance, not > achieving it. Right. it is well known that performance with pgbench can vary wildly. I usually get a lot less variation than you are getting. My point is though, it's not a great indication of performance. I generally simulate the real application running in production and test configuration changes with that. The hackers list archive also contains links to the testing Mark Wong has been doing at OSDL with TPC-C and TPC-H. Taking a look at the configuration file he is using, along with the annotated postgresql.conf, would be useful, depending on the load you're antipating and your hardware. > > > > 3. It appears that running more transactions with the same amount of > > > clients leads to a drop in the transactions per second. I do not > > > understand why this is (a drop from more clients I do understand). Is > > > this because of the way pgbench works, the way PostgrSQL works or even > > > Linux? > > This degradation seems to suggest effects caused by the disk cache filling > > up (assuming write caching is enabled) and checkpointing. > Which diskcache are your referring to? The onboard harddisk or RAID5 > controller caches or the OS cache? The first two I can unstand but if > you refer to the OS cache I do not understand what I am seeing. I have > enough memory giving the size of the database: during these duration (~) > tests fsync was on, and the files could be loaded into memory easily > (effective_cache_size = 32768 which is ~ 265 MB, the complete database > directory 228 MB) Well, two things may be at play. 1) if you are using write caching on your controller/disks then at the point at which that cache fills up performance will degrade to roughly that you can expect if write through cache was being used. Secondly, we checkpoint the system periodically to ensure that recovery wont be too long a job. Running for pgbench for a few seconds, you will not see the effect of checkpointing, which usually runs once every 5 minutes. Hope this helps. Thanks, Gavin
On Wed, 2005-11-02 at 21:16 +1100, Gavin Sherry wrote: > connections are updating the branches table heavily. As an aside, did you > initialise with a scaling factor of 10 to match your level of concurrency? Yep, I did. > that. The hackers list archive also contains links to the testing Mark > Wong has been doing at OSDL with TPC-C and TPC-H. Taking a look at the > configuration file he is using, along with the annotated postgresql.conf, > would be useful, depending on the load you're antipating and your > hardware. I will look into that project. > Well, two things may be at play. 1) if you are using write caching on your > controller/disks then at the point at which that cache fills up > performance will degrade to roughly that you can expect if write through > cache was being used. Secondly, we checkpoint the system periodically to > ensure that recovery wont be too long a job. Running for pgbench for a few > seconds, you will not see the effect of checkpointing, which usually runs > once every 5 minutes. I still think it is strange. Simple tests with tar suggest that I could easily do 600-700 tps at 50.000 KB/second ( as measured by iostat), a test with bonnie++ measured throughputs > 40.000 KB/sec during very long times, with 1723 - 2121 operations per second. These numbers suggest that PostgreSQL is not using all it could from the hardware. Processor load however is negligible during the pgbench tests. As written before, I will look into the OSDL benchmarks. Maybe they are more suited for my needs: *understanding* performance determinators. > > Hope this helps. You certainly did, thanks. -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 e-mail: J.Kraaijeveld@Askesis.nl web: www.askesis.nl