Thread: pgbench intriguing results: better tps figures for larger scale factor
I took some time to figure out a reasonable tuning for my fresh 9.2.3 installation when I've noticed the following: [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U postgres -i -s 1 ... 100000 tuples done. ... vacuum...done. [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U postgres -c 32 -t 5000 ... tps = 245.628075 (including connections establishing) tps = 245.697421 (excluding connections establishing) ... [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U postgres -i -s 100 ... 10000000 tuples done. ... vacuum...done. [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U postgres -c 32 -t 5000 ... tps = 1125.035567 (including connections establishing) tps = 1126.490634 (excluding connections establishing) 32 connections makes a comfortable load for the 8 core 4GB production server, a rather old machine. I kept testing for almost two days with various configuration parameters. In the beginning I was warned to increase the checkpoint_segments, which is now 32. The results were consistent and always showing small scale test (-s 1) at about 245-248 tps while big scale test (-s 100) at least 4 and up to 7 times better. According to top, at small scale tests, server processes are doing a lot of UPDATE waiting. A "select relation::regclass, * from pg_locks where not granted" showed frequent contention on tellers rows. First, I've got no good explanation for this and it would be nice to have one. As far as I can understand this issue, the heaviest update traffic should be on the branches table and should affect all tests. Second, I'd really like to bring the small scale test close to a four figure tps if it proves to be a matter of server configuration. Thank you, Costin Oproiu
Re: pgbench intriguing results: better tps figures for larger scale factor
From
Pavan Deolasee
Date:
On Wed, Feb 27, 2013 at 3:15 AM, Costin Oproiu <costin.oproiu@gmail.com> wrote: > I took some time to figure out a reasonable tuning for my fresh 9.2.3 > installation when I've noticed the following: > > [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U > postgres -i -s 1 > ... > 100000 tuples done. > ... > vacuum...done. > [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U > postgres -c 32 -t 5000 > ... > tps = 245.628075 (including connections establishing) > tps = 245.697421 (excluding connections establishing) > ... > [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U > postgres -i -s 100 > ... > 10000000 tuples done. > ... > vacuum...done. > [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U > postgres -c 32 -t 5000 > ... > tps = 1125.035567 (including connections establishing) > tps = 1126.490634 (excluding connections establishing) > > 32 connections makes a comfortable load for the 8 core 4GB production > server, a rather old machine. I kept testing for almost two days with > various configuration parameters. In the beginning I was warned to > increase the checkpoint_segments, which is now 32. The results were > consistent and always showing small scale test (-s 1) at about 245-248 > tps while big scale test (-s 100) at least 4 and up to 7 times > better. > > According to top, at small scale tests, server processes are doing a > lot of UPDATE waiting. A "select relation::regclass, * from pg_locks > where not granted" showed frequent contention on tellers rows. > > First, I've got no good explanation for this and it would be nice to > have one. As far as I can understand this issue, the heaviest update > traffic should be on the branches table and should affect all tests. > Its not very surprising. The smallest table in the test i.e. pgbench_branches has the number of rows equal to the scale factor. When you test with scale factor 1 and 32 clients, all those clients are contending to update that single row in the table. Since a transaction must wait for the other updating transaction before it can update the same row, you would get a almost linear behaviour in this test. You may actually want to test with just 1 or 5 or 10 clients and my gut feel is you will still get the same or similar tps. As the scale factor is increased, the contention on the smaller tables reduces and you will start seeing an increase in the tps as you increase the number of clients. Of course, beyond a point either it will flatten out or even go down. While testing with pgbench, its recommended that the scale factor should be set larger than the number of clients. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee
Re: pgbench intriguing results: better tps figures for larger scale factor
From
Merlin Moncure
Date:
On Thu, Feb 28, 2013 at 11:20 AM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote: > On Wed, Feb 27, 2013 at 3:15 AM, Costin Oproiu <costin.oproiu@gmail.com> wrote: >> I took some time to figure out a reasonable tuning for my fresh 9.2.3 >> installation when I've noticed the following: >> >> [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U >> postgres -i -s 1 >> ... >> 100000 tuples done. >> ... >> vacuum...done. >> [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U >> postgres -c 32 -t 5000 >> ... >> tps = 245.628075 (including connections establishing) >> tps = 245.697421 (excluding connections establishing) >> ... >> [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U >> postgres -i -s 100 >> ... >> 10000000 tuples done. >> ... >> vacuum...done. >> [costin@fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U >> postgres -c 32 -t 5000 >> ... >> tps = 1125.035567 (including connections establishing) >> tps = 1126.490634 (excluding connections establishing) >> >> 32 connections makes a comfortable load for the 8 core 4GB production >> server, a rather old machine. I kept testing for almost two days with >> various configuration parameters. In the beginning I was warned to >> increase the checkpoint_segments, which is now 32. The results were >> consistent and always showing small scale test (-s 1) at about 245-248 >> tps while big scale test (-s 100) at least 4 and up to 7 times >> better. >> >> According to top, at small scale tests, server processes are doing a >> lot of UPDATE waiting. A "select relation::regclass, * from pg_locks >> where not granted" showed frequent contention on tellers rows. >> >> First, I've got no good explanation for this and it would be nice to >> have one. As far as I can understand this issue, the heaviest update >> traffic should be on the branches table and should affect all tests. >> > > Its not very surprising. The smallest table in the test i.e. > pgbench_branches has the number of rows equal to the scale factor. > When you test with scale factor 1 and 32 clients, all those clients > are contending to update that single row in the table. Since a > transaction must wait for the other updating transaction before it can > update the same row, you would get a almost linear behaviour in this > test. You may actually want to test with just 1 or 5 or 10 clients and > my gut feel is you will still get the same or similar tps. > > As the scale factor is increased, the contention on the smaller tables > reduces and you will start seeing an increase in the tps as you > increase the number of clients. Of course, beyond a point either it > will flatten out or even go down. > > While testing with pgbench, its recommended that the scale factor > should be set larger than the number of clients. or, you can suppress the high contention updates with the -N switch (if you do this make sure to disclose it when giving tps results). merlin
On 2/26/13 4:45 PM, Costin Oproiu wrote: > First, I've got no good explanation for this and it would be nice to > have one. As far as I can understand this issue, the heaviest update > traffic should be on the branches table and should affect all tests. From http://www.postgresql.org/docs/current/static/pgbench.html : "For the default TPC-B-like test scenario, the initialization scale factor (-s) should be at least as large as the largest number of clients you intend to test (-c); else you'll mostly be measuring update contention. There are only -s rows in the pgbench_branches table, and every transaction wants to update one of them, so -c values in excess of -s will undoubtedly result in lots of transactions blocked waiting for other transactions." I normally see peak TPS at a scale of around 100 on current generation hardware, stuff in the 4 to 24 core range. Nowadays there really is no reason to consider running pgbench on a system with a smaller scale than that. I normally get a rough idea of things by running with scales 100, 250, 500, 1000, 2000. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com