pgbench intriguing results: better tps figures for larger scale factor

From: Costin Oproiu <costin(dot)oproiu(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: pgbench intriguing results: better tps figures for larger scale factor
Date: 2013-02-26 21:45:57
Message-ID: CAEmewRLOADSHk4A5EmYrT8puZ7zFxw8H6bQEJ2_BVPkdFTikSA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I took some time to figure out a reasonable tuning for my fresh 9.2.3
installation when I've noticed the following:

[costin(at)fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U
postgres -i -s 1
...
100000 tuples done.
...
vacuum...done.
[costin(at)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(at)fsr costin]$ /home/pgsql/bin/pgbench -h 192.1.1.2 -p 5432 -U
postgres -i -s 100
...
10000000 tuples done.
...
vacuum...done.
[costin(at)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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2013-02-26 21:46:32 Re: High CPU usage / load average after upgrading to Ubuntu 12.04
Previous Message Jeff Janes 2013-02-26 21:30:42 Re: High CPU usage / load average after upgrading to Ubuntu 12.04