Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
> "Schmidt, Peter" <[EMAIL PROTECTED]> writes: > > So, is it OK to use commit_delay=0? > > Certainly. In fact, I think that's about to become the default ;-) I agree with Tom. I did some benchmarking tests using pgbench for a computer magazine in Japan. I got a almost equal or better result for 7.1 than 7.0.3 if commit_delay=0. See included png file. -- Tatsuo Ishii performance.png
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 delay>1 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 delay>1 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
Tom Lane wrote: > > Hiroshi Inoue <[EMAIL PROTECTED]> writes: > > I've been suspicious if pgbench is an (unique) > > appropiriate test case for evaluaing commit_delay. > > Of course it isn't. Never trust only one benchmark. > > I've asked the Great Bridge folks to run their TPC-C benchmark with both > zero and small nonzero commit_delay. It will be a couple of days before > we have the results, however. Can anyone else offer any comparisons > based on other multiuser benchmarks? > I changed pgbench so that different connection connects to the different database and got the following results. 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) Regards, Hiroshi Inoue
Re: [HACKERS] Re: [ADMIN] v7.1b4 bad performance
Tom Lane wrote: > > I wrote: > > Thus, our past arguments about whether a few microseconds of delay > > before commit are a good idea seem moot; we do not have any portable way > > of implementing that, and a ten millisecond delay for commit is clearly > > Not Good. > > I've now finished running a spectrum of pgbench scenarios, and I find > no case in which commit_delay = 0 is worse than commit_delay > 0. > Now this is just one benchmark on just one platform, but it's pretty > damning... > 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. Regards, Hiroshi Inoue
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
Tom Lane wrote: > > "Hiroshi Inoue" <[EMAIL PROTECTED]> writes: > >> 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. > > Okay, so this errs in the opposite direction from the original form of > the benchmark: there will be *no* cross-backend locking delays, except > for accesses to the common WAL log. That's good as a comparison point, > but we shouldn't trust it absolutely either. > Of cource it's only one of the test cases. Because I've ever seen one-sided test cases, I had to provide this test case unwillingly. There are some obvious cases that CommitDelay is harmful and I've seen no test case other than such cases i.e 1) There's only one session. 2) The backends always conflict(e.g pgbench with scaling factor 1). > >> 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) > > 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. > OK I would try it later though I'm not sure I could increase -B that large in my current environment. 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 delay>1 > > 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 delay>1 > > also, IIRC Tom suggested running with at least -B 1024 if you can. Just try this: explain select * from where = (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
