Hardware problem solved, when you really need some cache.
Posted Friday May 22nd, 2009 09:38am
by Joshua Drake | Permalink
As reported in my last blog, Stefan was having much greater success with his pgbench results than I. In reviewing why, we found a problem with the hardware. What I like about this problem is that the results in the previous blog post become more interesting. As a reminder I was running 16 connections over 4 different users at 1M transactions. Below is the results from a single user from that batch:
Over 16 connections we were getting ~ 400 TPS. I verified that this was consistent by running a second test with a single user and 4 connections. The results:
So, what is it that causes a machine with plenty of resources to perform in such a consistently slow manner?. You can only write data as fast and the spindles turn. That is why they invented cache. The results should look very similar to Stefan's once we replace the battery cache.
Posted Friday May 22nd, 2009 09:38am
by Joshua Drake | Permalink
|
As reported in my last blog, Stefan was having much greater success with his pgbench results than I. In reviewing why, we found a problem with the hardware. What I like about this problem is that the results in the previous blog post become more interesting. As a reminder I was running 16 connections over 4 different users at 1M transactions. Below is the results from a single user from that batch:
pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 101.024360 (including connections establishing) tps = 101.024392 (excluding connections establishing)
Over 16 connections we were getting ~ 400 TPS. I verified that this was consistent by running a second test with a single user and 4 connections. The results:
pghost: localhost pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 404.021738 (including connections establishing) tps = 404.022316 (excluding connections establishing)
So, what is it that causes a machine with plenty of resources to perform in such a consistently slow manner?. You can only write data as fast and the spindles turn. That is why they invented cache. The results should look very similar to Stefan's once we replace the battery cache.
Categories: OpenSource, PostgreSQL
Thanks Stefan
Posted Thursday May 21st, 2009 02:13pm
by Joshua Drake | Permalink
So while doing the benchmarking of the various parameters, Stefan pointed out the my numbers were ridiculously low. I wasn't really paying attention because I was looking at differences between parameters but then he posted me an example of a single thread pgbench using my same parameters. His machine is a dual core connected to 10 spindles on a NetAPP. In theory my machine should be faster. It is not. His configuration, like mine was all defaults.
Stefan's numbers.
So now I am trying to figure out what is up with my hardware.
Posted Thursday May 21st, 2009 02:13pm
by Joshua Drake | Permalink
|
|
So while doing the benchmarking of the various parameters, Stefan pointed out the my numbers were ridiculously low. I wasn't really paying attention because I was looking at differences between parameters but then he posted me an example of a single thread pgbench using my same parameters. His machine is a dual core connected to 10 spindles on a NetAPP. In theory my machine should be faster. It is not. His configuration, like mine was all defaults.
Stefan's numbers.
scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 1681.615766 (including connections establishing) tps = 1681.622950 (excluding connections establishing)
So now I am trying to figure out what is up with my hardware.
8.3.7 TPS and checkpoint segments
Posted Thursday May 21st, 2009 09:09am
by Joshua Drake | Permalink
Continuing my postgresql.conf changes I ran a new test yesterday with checkpoint_segments set to 300. As a reminder the original results and specs of the machine being used in the test are here. The results of the new test below:
O.k. so same test, same hardware, 10x more checkpoint_segments gives us ~8 percent improvement.
Posted Thursday May 21st, 2009 09:09am
by Joshua Drake | Permalink
|
|
Continuing my postgresql.conf changes I ran a new test yesterday with checkpoint_segments set to 300. As a reminder the original results and specs of the machine being used in the test are here. The results of the new test below:
pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 101.024360 (including connections establishing) tps = 101.024392 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.796885 (including connections establishing) tps = 100.796924 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.801501 (including connections establishing) tps = 100.801534 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 100.852900 (including connections establishing) tps = 100.852944 (excluding connections establishing)
O.k. so same test, same hardware, 10x more checkpoint_segments gives us ~8 percent improvement.
Default TPS performance of 8.3.7
Posted Wednesday May 20th, 2009 10:58am
by Joshua Drake | Permalink
I recently purchased some used hardware for some performance testing of PostgreSQL. I didn't want to interrupt the great work that Mark Wong was doing with the PostgreSQL Performance Lab. The testing I am doing is a bit different than Mark's. Where Mark is testing various filesystem performance via PostgreSQL using DBT2 and FIO and wanted to go up a level.
I am testing using the PostgreSQL tool pgbench which is available in contrib. I am also testing in a basically default environment as a way to see how changing different parameters of the postgresql.conf changes overall performance.
Hardware Configuration
Operating System Configuration
Outside of the minor operating system changes the system remained in default. The only postgresql.conf parameter I changed was to set checkpoint_segments to 30.
pgbench configuration
I used four pgbench instances within a single database with four schemas. Each schema was assigned to its own pgbench user 01-04. The pgbench command used was:
Results
Each pgbench process executed 4M transactions with an average of ~ 24TPS per client. That is pretty miserable. However remember this is defaults and the defaults will make you checkpoint quite a bit (every 20 seconds or so) with the above stress test. Stay tuned for results as we change specific parameters to see the effect each one has.
Posted Wednesday May 20th, 2009 10:58am
by Joshua Drake | Permalink
|
|
I recently purchased some used hardware for some performance testing of PostgreSQL. I didn't want to interrupt the great work that Mark Wong was doing with the PostgreSQL Performance Lab. The testing I am doing is a bit different than Mark's. Where Mark is testing various filesystem performance via PostgreSQL using DBT2 and FIO and wanted to go up a level.
I am testing using the PostgreSQL tool pgbench which is available in contrib. I am also testing in a basically default environment as a way to see how changing different parameters of the postgresql.conf changes overall performance.
Hardware Configuration
Newisys Quad Opteron 846
32GB of memory
(2) MSA 30s (RAID 10, 14 drives each)
(2) HP 6402 Controllers (one for each MSA)
Operating System Configuration
Ubuntu Hardy LTS x86_64
/array1 , ext3, data=writeback
elevator=deadline
Outside of the minor operating system changes the system remained in default. The only postgresql.conf parameter I changed was to set checkpoint_segments to 30.
pgbench configuration
I used four pgbench instances within a single database with four schemas. Each schema was assigned to its own pgbench user 01-04. The pgbench command used was:
/array1/jd/pgsql/bin/pgbench -U bench01 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench01& /array1/jd/pgsql/bin/pgbench -U bench02 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench02& /array1/jd/pgsql/bin/pgbench -U bench03 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench03& /array1/jd/pgsql/bin/pgbench -U bench04 -s10 -t1000000 -c4 -p 6000 -d bench \ > bench04&
Results
pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 92.279931 (including connections establishing) tps = 92.279960 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.674708 (including connections establishing) tps = 91.674739 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.583754 (including connections establishing) tps = 91.583782 (excluding connections establishing) pghost: pgport: 6000 nclients: 4 nxacts: 1000000 dbName: bench transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 4 number of transactions per client: 1000000 number of transactions actually processed: 4000000/4000000 tps = 91.616330 (including connections establishing) tps = 91.616355 (excluding connections establishing)
Each pgbench process executed 4M transactions with an average of ~ 24TPS per client. That is pretty miserable. However remember this is defaults and the defaults will make you checkpoint quite a bit (every 20 seconds or so) with the above stress test. Stay tuned for results as we change specific parameters to see the effect each one has.
Surprising events from a top 20 list
Posted Monday May 11th, 2009 10:15am
by Joshua Drake | Permalink
In a completely unscientific review of community popularity I was reviewing the active members of various channels on #freenode. For those that don't know, Freenode is the defacto destination for the Open Source community on IRC. This is where you will find official community support channels for such open source luminaries as #gentoo, #ubuntu, #mysql and of course #postgresql.Read more...
Posted Monday May 11th, 2009 10:15am
by Joshua Drake | Permalink
|
|
In a completely unscientific review of community popularity I was reviewing the active members of various channels on #freenode. For those that don't know, Freenode is the defacto destination for the Open Source community on IRC. This is where you will find official community support channels for such open source luminaries as #gentoo, #ubuntu, #mysql and of course #postgresql.Read more...
Copyright © 2000-2010 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.