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
