Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
Dmitry Morozovsky wrote: On Sun, 18 Feb 2001, Dmitry Morozovsky wrote: DM I just done the experiment with increasing HZ to 1000 on my own machine DM (PII 374). Your test program reports 2 ms instead of 20. The other side DM of increasing HZ is surely more overhead to scheduler system. Anyway, it's DM a bit of data to dig into, I suppose ;-) DM DM Results for pgbench with 7.1b4: (BTW, machine is FreeBSD 4-stable on IBM DM DTLA IDE in ATA66 mode with tag queueing and soft updates turned on) 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 also, IIRC Tom suggested running with at least -B 1024 if you can. - Hannu
Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
On Fri, 23 Feb 2001, Hannu Krosing wrote: HK DM I just done the experiment with increasing HZ to 1000 on my own machine HK DM (PII 374). Your test program reports 2 ms instead of 20. The other side HK DM of increasing HZ is surely more overhead to scheduler system. Anyway, it's HK DM a bit of data to dig into, I suppose ;-) HK DM HK DM Results for pgbench with 7.1b4: (BTW, machine is FreeBSD 4-stable on IBM HK DM DTLA IDE in ATA66 mode with tag queueing and soft updates turned on) HK HK Is this unmodified pgbench or has it Hiroshi tweaked behaviour of HK connecting each client to its own database, so that locking and such HK does not shade the possible benefits (was it about 15% ?) of delay1 HK also, IIRC Tom suggested running with at least -B 1024 if you can. It was original pgbench. Maybe, duritng this weekend I'll make new kernel with big SHM table and try to test with larger -B (for now, -B 256 is the most I can set) Sincerely, D.Marck [DM5020, DM268-RIPE, DM3-RIPN] *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- [EMAIL PROTECTED] ***
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
Okay, plan B then: let's ask people to redo their benchmarks with -s bigger than one. Now, how much bigger? To the extent that you think this is a model of a real bank, it should be obvious that the number of concurrent transactions cannot exceed the number of tellers; there should never be any write contention on a teller's table row, because only that teller (client) should be issuing transactions against it. Contention on a branch's row is realistic, but not from more clients than there are tellers in the branch. As a rule of thumb, then, we could say that the benchmark's results are not to be believed for numbers of clients exceeding perhaps 5 times the scale factor, ie, half the number of teller rows (so that it's not too likely we will have contention on a teller row). At least -s 5 seems reasonable for me too. Maybe we should make it as the default setting for pgbench? -- Tatsuo Ishii
Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
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. What would make more sense to me is to increase the number of rows in the branches table. Right now, at the default "scale factor" of 1, pgbench makes tables of these sizes: accounts 10 branches 1 history 0 (filled during test) tellers 10 It seems to me that the branches table should have at least 10 to 100 entries, and tellers about 10 times whatever branches is. 10 accounts rows seems enough though. Those numbers are defined in the TPC-B spec. But pgbench is not an official test tool anyway, so you could modify it if you like. That is the benefit of the open source:-) -- Tatsuo Ishii
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);
Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
On Fri, Feb 23, 2001 at 01:09:37PM +0200, Hannu Krosing wrote: Dmitry Morozovsky wrote: DM I just done the experiment with increasing HZ to 1000 on my own machine DM (PII 374). Your test program reports 2 ms instead of 20. The other side DM of increasing HZ is surely more overhead to scheduler system. Anyway, it's DM a bit of data to dig into, I suppose ;-) DM DM Results for pgbench with 7.1b4: (BTW, machine is FreeBSD 4-stable on IBM DM DTLA IDE in ATA66 mode with tag queueing and soft updates turned on) 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 also, IIRC Tom suggested running with at least -B 1024 if you can. Just try this: explain select * from tablename where fieldname=any_value (Use for fieldname an indexed field). If postgres is using an sequential scan in stead of an index scan. You have to vacuum your database. This will REALLY remove deleted data from your indexes. Hope it will work, Dave Mertens System Administrator ISM, Netherlands
