On Thu, Feb 28, 2013 at 11:20 AM, Pavan Deolasee <pavan.deolasee@xxxxxxxxx> wrote: > On Wed, Feb 27, 2013 at 3:15 AM, Costin Oproiu <costin.oproiu@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
