RE: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Hiroshi Inoue [EMAIL PROTECTED] writes: I changed pgbench so that different connection connects to the different database and got the following results. Hmm, you mean you set up a separate test database for each pgbench "client", but all under the same postmaster? Yes. Different database is to make the conflict as less as possible. The conflict among backends is a greatest enemy of CommitDelay. The results of pgbench -c 10 -t 100 [CommitDelay=0] 1st)tps = 18.484611(including connections establishing) tps = 19.827988(excluding connections establishing) 2nd)tps = 18.754826(including connections establishing) tps = 19.352268(excluditp connections establishing) 3rd)tps = 18.771225(including connections establishing) tps = 19.261843(excluding connections establishing) [CommitDelay=1] 1st)tps = 20.317649(including connections establishing) tps = 20.975151(excluding connections establishing) 2nd)tps = 24.208025(including connections establishing) tps = 24.663665(excluding connections establishing) 3rd)tps = 25.821156(including connections establishing) tps = 26.842741(excluding connections establishing) What platform is this on --- in particular, how long a delay is CommitDelay=1 in reality? What -B did you use? platform) i686-pc-linux-gnu, compiled by GCC egcs-2.91.60(turbolinux 4.2) min delay) 10msec according to your test program. -B) 64 (all other settings are default) Regards, Hiroshi Inoue
Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: In your test cases I always see "where bid = 1" at "update branches" i.e. update branches set bbalance = bbalance + ... where bid = 1 ISTM there's no multiple COMMIT in your senario-s due to their lock conflicts. Hmm. It looks like using a 'scaling factor' larger than 1 is necessary to spread out the updates of "branches". AFAIR, the people who reported runs with scaling factors 1 got pretty much the same results though. People seem to believe your results are decisive and would raise your results if the evidence is required. All clients of pgbench execute the same sequence of queries. There could be various conflicts e.g. oridinary lock, buffer lock, IO spinlock ... I've been suspicious if pgbench is an (unique) appropiriate test case for evaluaing commit_delay. Regards, Hiroshi Inoue
RE: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
-Original Message- From: Tom Lane Hannu Krosing [EMAIL PROTECTED] writes: Is this unmodified pgbench or has it Hiroshi tweaked behaviour of connecting each client to its own database, so that locking and such does not shade the possible benefits (was it about 15% ?) of delay1 I didn't much like that approach to altering the test, since it also means that all the clients are working with separate tables and hence not able to share read I/O; that doesn't seem like it's the same benchmark at all. I agree with you at this point. Generally speaking the benchmark has little meaning if it has no conflicts in the test case. I only borrowed pgbench's source code to implement my test cases. Note that there's only one database in my last test case. My modified "pgbench" isn't a pgbench any more and I didn't intend to change pgbench's spec like that. Probably it was my mistake that I had posted my test cases using the form of patch. My intension was to clarify the difference of my test cases. However heavy conflicts with scaling factor 1 doesn't seem preferable at least as the default of pgbench. Regards, Hiroshi Inoue
Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
Tom Lane wrote:
platform) i686-pc-linux-gnu, compiled by GCC egcs-2.91.60(turbolinux 4.2)
min delay) 10msec according to your test program.
-B) 64 (all other settings are default)
Thanks. Could I trouble you to run it again with a larger -B, say
1024 or 2048? What I've found is that at -B 64, the benchmark is
so constrained by limited buffer space that it doesn't reflect
performance at a more realistic production setting.
Hmm the result doesn't seem that obvious.
First I got the following result.
[CommitDelay=0]
1)tps = 23.024648(including connections establishing)
tps = 23.856420(excluding connections establishing)
2)tps = 30.276270(including connections establishing)
tps = 30.996459(excluding connections establishing)
[CommitDelay=1]
1)tps = 23.065921(including connections establishing)
tps = 23.866029(excluding connections establishing)
2)tps = 34.024632(including connections establishing)
tps = 35.671566(excluding connections establishing)
The result seems inconstant and after disabling
checkpoint process I got the following.
[CommitDelay=0]
1)tps = 24.060970(including connections establishing)
tps = 24.416851(excluding connections establishing)
2)tps = 21.361134(including connections establishing)
tps = 21.605583(excluding connections establishing)
3)tps = 20.377635(including connections establishing)
tps = 20.646523(excluding connections establishing)
[CommitDelay=1]
1)tps = 22.164379(including connections establishing)
tps = 22.790772(excluding connections establishing)
2)tps = 22.719068(including connections establishing)
tps = 23.040485(excluding connections establishing)
3)tps = 24.341675(including connections establishing)
tps = 25.869479(excluding connections establishing)
Unfortunately I have no more time to check today.
Please check the similar test case.
[My test case]
I created and initialized 10 datatabases as follows.
1) create databases.
createdb inoue1
craetedb inoue2
.
createdb inoue10
2) pgbench -i inoue1
pgbench -i inoue2
.
pgbench -i inoue10
3) invoke a modified pgbench
pgbench -c 10 -t 100 inoue
I've attached a patch to change pgbench so that
each connection connects to different database
whose name is '%d'( is the specified
database? name).
Regards,
Hiroshi Inoue
Index: pgbench.c
===
RCS file: /home/cvs/pgcurrent/contrib/pgbench/pgbench.c,v
retrieving revision 1.1
diff -c -r1.1 pgbench.c
*** pgbench.c 2001/02/20 07:55:21 1.1
--- pgbench.c 2001/02/20 09:31:13
***
*** 540,545
--- 540,546
PGconn *con;
PGresult *res;
+ char dbn[48];
while ((c = getopt(argc, argv, "ih:nvp:dc:t:s:S")) != EOF)
{
***
*** 639,645
}
/* opening connection... */
! con = PQsetdb(pghost, pgport, NULL, NULL, dbName);
if (PQstatus(con) == CONNECTION_BAD)
{
fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
--- 640,648
}
/* opening connection... */
! /*con = PQsetdb(pghost, pgport, NULL, NULL, dbName);*/
! sprintf(dbn, "%s1", dbName);
! con = PQsetdb(pghost, pgport, NULL, NULL, dbn);
if (PQstatus(con) == CONNECTION_BAD)
{
fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
***
*** 726,732
/* make connections to the database */
for (i = 0; i nclients; i++)
{
! state[i].con = PQsetdb(pghost, pgport, NULL, NULL, dbName);
if (PQstatus(state[i].con) == CONNECTION_BAD)
{
fprintf(stderr, "Connection to database '%s' failed.\n",
dbName);
--- 729,737
/* make connections to the database */
for (i = 0; i nclients; i++)
{
! /*state[i].con = PQsetdb(pghost, pgport, NULL, NULL, dbName);*/
! sprintf(dbn, "%s%d", dbName, i + 1);
! state[i].con = PQsetdb(pghost, pgport, NULL, NULL, dbn);
if (PQstatus(state[i].con) == CONNECTION_BAD)
{
fprintf(stderr, "Connection to database '%s' failed.\n",
dbName);
