Horton
Menu

Thread

  1. RE: v7.1b4 bad performance

    Schmidt, Peter <peter.schmidt@prismedia.com> — 2001-02-17T03:54:45Z

    
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Friday, February 16, 2001 7:13 PM
> To: Schmidt, Peter
> Cc: 'Bruce Momjian'; 'Michael Ansley'; 'pgsql-admin@postgresql.org'
> Subject: Re: [ADMIN] v7.1b4 bad performance 
> 
> 
> "Schmidt, Peter" <peter.schmidt@prismedia.com> writes:
> > I tried -B 1024 and got roughly the same results (~50 tps).
> 
> What were you using before?
> 
> > However, when I change WAL option commit_delay from the default of 5
> > to 0, I get ~200 tps (which is double what I get with 7.03). I'm not
> > sure I want to do this, do I?
> 
> Hmm.  There have been several discussions about whether CommitDelay is
> a good idea or not.  What happens if you vary it --- try 1 
> microsecond,
> and then various multiples of 1000.  I suspect you may find that there
> is no difference in the range 1..10000, then a step, then no change up
> to 20000.  In other words, your kernel may be rounding the delay up to
> the next multiple of a clock tick, which might be 10 milliseconds.
> That would explain a 50-tps limit real well...
> 
> BTW, have you tried pgbench with multiple clients (-c) rather 
> than just
> one?
> 
> 			regards, tom lane
> 


I get ~50 tps for any commit_delay value > 0. I've tried many values in the
range 0 - 999, and always get ~50 tps. commit_delay=0 always gets me ~200+
tps.

Yes, I have tried multiple clients but got stuck on the glaring difference
between versions with a single client. The tests that I ran showed the same
kind of results you got earlier today i.e. 1 client/1000 transactions = 10
clients/100 transactions.

So, is it OK to use commit_delay=0?

Peter

  2. Re: v7.1b4 bad performance

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-02-17T04:11:37Z

    > I get ~50 tps for any commit_delay value > 0. I've tried many values in the
> range 0 - 999, and always get ~50 tps. commit_delay=0 always gets me ~200+
> tps.
> 
> Yes, I have tried multiple clients but got stuck on the glaring difference
> between versions with a single client. The tests that I ran showed the same
> kind of results you got earlier today i.e. 1 client/1000 transactions = 10
> clients/100 transactions.
> 
> So, is it OK to use commit_delay=0?

commit_delay was designed to provide better performance in multi-user
workloads.  If you are going to use it with only a single backend, you
certainly should set it to zero.  If you will have multiple backends
committing at the same time, we are not sure if 5 or 0 is the right
value.  If multi-user benchmark shows 0 is faster, we may change the
default.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  3. Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-17T04:43:29Z

    "Schmidt, Peter" <peter.schmidt@prismedia.com> writes:
> So, is it OK to use commit_delay=0?

Certainly.  In fact, I think that's about to become the default ;-)

I have now experimented with several different platforms --- HPUX,
FreeBSD, and two considerably different strains of Linux --- and I find
that the minimum delay supported by select(2) is 10 or more milliseconds
on all of them, as much as 20 msec on some popular platforms.  Try it
yourself (my test program is attached).

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.

			regards, tom lane


/* To use: gcc test.c, then

	time ./a.out N

N=0 should return almost instantly, if your select(2) does not block as
per spec.  N=1 shows the minimum achievable delay, * 1000 --- for
example, if time reports the elapsed time as 10 seconds, then select
has rounded your 1-microsecond delay request up to 10 milliseconds.

Some Unixen seem to throw in an extra ten millisec of delay just for
good measure, eg, on FreeBSD 4.2 N=1 takes 20 sec, N=20000 takes 30.
*/

#include <stdio.h>
#include <stdlib.h>
#include <sys/stat.h>
#include <sys/time.h>
#include <sys/types.h>

int main(int argc, char** argv)
{
	struct timeval	delay;
	int i, del;

	del = atoi(argv[1]);

	for (i = 0; i < 1000; i++) {
		delay.tv_sec = 0;
		delay.tv_usec = del;
		(void) select(0, NULL, NULL, NULL, &delay);
	}
	return 0;
}

  4. Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-17T06:10:38Z

    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...

Platform: HPUX 10.20 on HPPA C180, fast wide SCSI discs, 7200rpm (I think).
Minimum select(2) delay is 10 msec on this platform.

POSTMASTER OPTIONS: -i -B 1024 -N 100

$ PGOPTIONS='-c commit_delay=1' pgbench -c 1 -t 1000 bench
tps = 13.304624(including connections establishing)
tps = 13.323967(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=0' pgbench -c 1 -t 1000 bench
tps = 16.614691(including connections establishing)
tps = 16.645832(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=1' pgbench -c 10 -t 100 bench
tps = 13.612502(including connections establishing)
tps = 13.712996(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=0' pgbench -c 10 -t 100 bench
tps = 14.674477(including connections establishing)
tps = 14.787715(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=1' pgbench -c 30 -t 100 bench
tps = 10.875912(including connections establishing)
tps = 10.932836(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=0' pgbench -c 30 -t 100 bench
tps = 12.853009(including connections establishing)
tps = 12.934365(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=1' pgbench -c 50 -t 100 bench
tps = 9.476856(including connections establishing)
tps = 9.520800(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=0' pgbench -c 50 -t 100 bench
tps = 9.807925(including connections establishing)
tps = 9.854161(excluding connections establishing)

With -F (no fsync), it's the same story:

POSTMASTER OPTIONS: -i -o -F -B 1024 -N 100

$ PGOPTIONS='-c commit_delay=1' pgbench -c 1 -t 1000 bench
tps = 40.584300(including connections establishing)
tps = 40.708855(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=0' pgbench -c 1 -t 1000 bench
tps = 51.585629(including connections establishing)
tps = 51.797280(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=1' pgbench -c 10 -t 100 bench
tps = 35.811729(including connections establishing)
tps = 36.448439(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=0' pgbench -c 10 -t 100 bench
tps = 43.878827(including connections establishing)
tps = 44.856029(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=1' pgbench -c 30 -t 100 bench
tps = 23.490464(including connections establishing)
tps = 23.749558(excluding connections establishing)

$ PGOPTIONS='-c commit_delay=0' pgbench -c 30 -t 100 bench
tps = 23.452935(including connections establishing)
tps = 23.716181(excluding connections establishing)


I vote for commit_delay = 0, unless someone can show cases where
positive delay is significantly better than zero delay.

			regards, tom lane

  5. Re: [HACKERS] Re: v7.1b4 bad performance

    Tatsuo Ishii <t-ishii@sra.co.jp> — 2001-02-17T06:46:35Z

    > "Schmidt, Peter" <peter.schmidt@prismedia.com> 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

  6. Re: [HACKERS] Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-17T06:59:59Z

    Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> 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.

Interesting curves.  One thing you might like to know is that while
poking around with a profiler this afternoon, I found that the vast
majority of the work done for this benchmark is in the uniqueness
checks driven by the unique indexes.  Declare those as plain (non
unique) and the TPS figures would probably go up noticeably.  That
doesn't make the test invalid, but it does suggest that pgbench is
emphasizing one aspect of system performance to the exclusion of
others ...

			regards, tom lane

  7. Re: v7.1b4 bad performance

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 2001-02-17T07:20:48Z

    > ... See included png file.

What kind of machine was this run on?

                     - Thomas

  8. Re: v7.1b4 bad performance

    Tatsuo Ishii <t-ishii@sra.co.jp> — 2001-02-17T08:13:50Z

    lockhart> > ... See included png file.
lockhart> 
lockhart> What kind of machine was this run on?
lockhart> 
lockhart>                      - Thomas

Sorry to forget to mention about that.

SONY VAIO Z505CR/K (note PC)
Pentium III 750MHz/256MB memory/20GB IDE HDD
Linux (kernel 2.2.17)
configure --enable-multibyte=EUC_JP
postgresql.conf:
		fsync = on
		max_connections = 128
		shared_buffers = 1024
		silent_mode = on
		commit_delay = 0
postmaster opts for 7.0.3:
		-B 1024 -N 128 -S
pgbench settings:
		scaling factor = 1
		data excludes connetion establishing time
		number of total transactions are always 640
			   (see included scripts I ran for the testing)
------------------------------------------------------
#! /bin/sh
pgbench -i test
for i in 1 2 4 8 16 32 64 128
do
	t=`expr 640 / $i`
	pgbench -t $t -c $i test
	echo "===== sync ======"
	sync;sync;sync;sleep 10
	echo "===== sync done ======"
done
------------------------------------------------------
--
Tatsuo Ishii

  9. Re: [HACKERS] Re: v7.1b4 bad performance

    Larry Rosenman <ler@lerctr.org> — 2001-02-17T15:52:33Z

    * Tom Lane <tgl@sss.pgh.pa.us> [010216 22:49]:
> "Schmidt, Peter" <peter.schmidt@prismedia.com> writes:
> > So, is it OK to use commit_delay=0?
> 
> Certainly.  In fact, I think that's about to become the default ;-)
> 
> I have now experimented with several different platforms --- HPUX,
> FreeBSD, and two considerably different strains of Linux --- and I find
> that the minimum delay supported by select(2) is 10 or more milliseconds
> on all of them, as much as 20 msec on some popular platforms.  Try it
> yourself (my test program is attached).
> 
> 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.
> 
> 			regards, tom lane
Here is another one.  UnixWare 7.1.1 on a P-III 500 256 Meg Ram:

$ cc -o tgl.test -O tgl.test.c
$ time ./tgl.test 0

real    0m0.01s
user    0m0.01s
sys     0m0.00s
$ time ./tgl.test 1

real    0m10.01s
user    0m0.00s
sys     0m0.01s
$ time ./tgl.test 2

real    0m10.01s
user    0m0.00s
sys     0m0.00s
$ time ./tgl.test 3

real    0m10.11s
user    0m0.00s
sys     0m0.01s
$ uname -a
UnixWare lerami 5 7.1.1 i386 x86at SCO UNIX_SVR5
$ 

-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

  10. Re: v7.1b4 bad performance

    Dmitry Morozovsky <marck@rinet.ru> — 2001-02-18T19:36:03Z

    On Sat, 17 Feb 2001, Tom Lane wrote:

[skip]

TL> Platform: HPUX 10.20 on HPPA C180, fast wide SCSI discs, 7200rpm (I think).
TL> Minimum select(2) delay is 10 msec on this platform.

[skip]

TL> I vote for commit_delay = 0, unless someone can show cases where
TL> positive delay is significantly better than zero delay.

BTW, for modern versions of FreeBSD kernels, there is HZ kernel option
which describes maximum timeslice granularity (actually, HZ value is
number of timeslice periods per second, with default of 100 = 10 ms). On
modern CPUs HZ may be increased to at least 1000, and sometimes even to
5000 (unfortunately, I haven't test platform by hand).

So, maybe you can test select granularity at ./configure phase and then
define default commit_delay accordingly.

Your thoughts?

Sincerely,
D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------

  11. Re: v7.1b4 bad performance

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-02-18T19:59:40Z

    > TL> I vote for commit_delay = 0, unless someone can show cases where
> TL> positive delay is significantly better than zero delay.
> 
> BTW, for modern versions of FreeBSD kernels, there is HZ kernel option
> which describes maximum timeslice granularity (actually, HZ value is
> number of timeslice periods per second, with default of 100 = 10 ms). On
> modern CPUs HZ may be increased to at least 1000, and sometimes even to
> 5000 (unfortunately, I haven't test platform by hand).
> 
> So, maybe you can test select granularity at ./configure phase and then
> define default commit_delay accordingly.

According to the BSD4.4 book by Karels/McKusick, even though computers
are faster now, increasing the Hz doesn't seem to improve performance. 
This is probably because of cache misses from context switches.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  12. Re: v7.1b4 bad performance

    Dmitry Morozovsky <marck@rinet.ru> — 2001-02-18T20:32:11Z

    On Sun, 18 Feb 2001, Dmitry Morozovsky wrote:

I just done the experiment with increasing HZ to 1000 on my own machine
(PII 374). Your test program reports 2 ms instead of 20. The other side
of increasing HZ is surely more overhead to scheduler system. Anyway, it's
a bit of data to dig into, I suppose ;-)

Results for pgbench with 7.1b4: (BTW, machine is FreeBSD 4-stable on IBM
DTLA IDE in ATA66 mode with tag queueing and soft updates turned on)

>> default delay (5 us)

number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 96.678008(including connections establishing)
tps = 96.982619(excluding connections establishing)

number of clients: 10
number of transactions per client: 100
number of transactions actually processed: 1000/1000
tps = 77.538398(including connections establishing)
tps = 79.126914(excluding connections establishing)

number of clients: 20
number of transactions per client: 50
number of transactions actually processed: 1000/1000
tps = 68.448429(including connections establishing)
tps = 70.957500(excluding connections establishing)

>> delay of 0

number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 111.939751(including connections establishing)
tps = 112.335089(excluding connections establishing)

number of clients: 10
number of transactions per client: 100
number of transactions actually processed: 1000/1000
tps = 84.262936(including connections establishing)
tps = 86.152702(excluding connections establishing)

number of clients: 20
number of transactions per client: 50
number of transactions actually processed: 1000/1000
tps = 79.678831(including connections establishing)
tps = 83.106418(excluding connections establishing)


Results are very close... Another thing to dig into.

BTW, postgres parameters were: -B 256 -F -i -S




DM> BTW, for modern versions of FreeBSD kernels, there is HZ kernel option
DM> which describes maximum timeslice granularity (actually, HZ value is
DM> number of timeslice periods per second, with default of 100 = 10 ms). On
DM> modern CPUs HZ may be increased to at least 1000, and sometimes even to
DM> 5000 (unfortunately, I haven't test platform by hand).
DM> 
DM> So, maybe you can test select granularity at ./configure phase and then
DM> define default commit_delay accordingly.
DM> 
DM> Your thoughts?
DM> 
DM> Sincerely,
DM> D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
DM> ------------------------------------------------------------------------
DM> *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
DM> ------------------------------------------------------------------------
DM> 

Sincerely,
D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------

  13. Re: v7.1b4 bad performance

    Dmitry Morozovsky <marck@rinet.ru> — 2001-02-18T20:54:33Z

    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)

Oh, I forgot to paste the results from original system (with HZ=100). Here
they are:

>> delay = 5

number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 47.422866(including connections establishing)
tps = 47.493439(excluding connections establishing)

number of clients: 10
number of transactions per client: 100
number of transactions actually processed: 1000/1000
tps = 37.930605(including connections establishing)
tps = 38.308613(excluding connections establishing)

number of clients: 20
number of transactions per client: 50
number of transactions actually processed: 1000/1000
tps = 35.757531(including connections establishing)
tps = 36.420532(excluding connections establishing)

>> delay = 0

number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 111.521859(including connections establishing)
tps = 111.904026(excluding connections establishing)

number of clients: 10
number of transactions per client: 100
number of transactions actually processed: 1000/1000
tps = 62.808216(including connections establishing)
tps = 63.819590(excluding connections establishing)

number of clients: 20
number of transactions per client: 50
number of transactions actually processed: 1000/1000
tps = 64.250431(including connections establishing)
tps = 66.438067(excluding connections establishing)


So, I suppose (very preliminary, of course ;):

1 -  at least for dedicated PostgreSQL servers it _may_ be
reasonable to increase HZ
2 - there is still no advantages of using delay != 0.

Your ideas?



DM> 
DM> >> default delay (5 us)
DM> 
DM> number of clients: 1
DM> number of transactions per client: 1000
DM> number of transactions actually processed: 1000/1000
DM> tps = 96.678008(including connections establishing)
DM> tps = 96.982619(excluding connections establishing)
DM> 
DM> number of clients: 10
DM> number of transactions per client: 100
DM> number of transactions actually processed: 1000/1000
DM> tps = 77.538398(including connections establishing)
DM> tps = 79.126914(excluding connections establishing)
DM> 
DM> number of clients: 20
DM> number of transactions per client: 50
DM> number of transactions actually processed: 1000/1000
DM> tps = 68.448429(including connections establishing)
DM> tps = 70.957500(excluding connections establishing)
DM> 
DM> >> delay of 0
DM> 
DM> number of clients: 1
DM> number of transactions per client: 1000
DM> number of transactions actually processed: 1000/1000
DM> tps = 111.939751(including connections establishing)
DM> tps = 112.335089(excluding connections establishing)
DM> 
DM> number of clients: 10
DM> number of transactions per client: 100
DM> number of transactions actually processed: 1000/1000
DM> tps = 84.262936(including connections establishing)
DM> tps = 86.152702(excluding connections establishing)
DM> 
DM> number of clients: 20
DM> number of transactions per client: 50
DM> number of transactions actually processed: 1000/1000
DM> tps = 79.678831(including connections establishing)
DM> tps = 83.106418(excluding connections establishing)
DM> 
DM> 
DM> Results are very close... Another thing to dig into.
DM> 
DM> BTW, postgres parameters were: -B 256 -F -i -S
DM> 
DM> 
DM> 
DM> 
DM> DM> BTW, for modern versions of FreeBSD kernels, there is HZ kernel option
DM> DM> which describes maximum timeslice granularity (actually, HZ value is
DM> DM> number of timeslice periods per second, with default of 100 = 10 ms). On
DM> DM> modern CPUs HZ may be increased to at least 1000, and sometimes even to
DM> DM> 5000 (unfortunately, I haven't test platform by hand).
DM> DM> 
DM> DM> So, maybe you can test select granularity at ./configure phase and then
DM> DM> define default commit_delay accordingly.
DM> DM> 
DM> DM> Your thoughts?
DM> DM> 
DM> DM> Sincerely,
DM> DM> D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
DM> DM> ------------------------------------------------------------------------
DM> DM> *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
DM> DM> ------------------------------------------------------------------------
DM> DM> 
DM> 
DM> Sincerely,
DM> D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
DM> ------------------------------------------------------------------------
DM> *** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
DM> ------------------------------------------------------------------------
DM> 
DM> 

Sincerely,
D.Marck                                   [DM5020, DM268-RIPE, DM3-RIPN]
------------------------------------------------------------------------
*** Dmitry Morozovsky --- D.Marck --- Wild Woozle --- marck@rinet.ru ***
------------------------------------------------------------------------

  14. Re: [HACKERS] Re: v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-19T08:15:03Z

    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

  15. Re: [HACKERS] Re: v7.1b4 bad performance

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-02-19T16:50:02Z

    I did not realize how much WAL improved performance when using fsync.

> > "Schmidt, Peter" <peter.schmidt@prismedia.com> 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

[ Attachment, skipping... ]


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

  16. Re: [HACKERS] Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-19T17:15:03Z

    Hiroshi Inoue <Inoue@tpf.co.jp> 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.

			regards, tom lane

  17. Re: [HACKERS] Re: v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-19T23:28:47Z

    Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> 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

  18. Re: [HACKERS] Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-19T23:40:45Z

    Hiroshi Inoue <Inoue@tpf.co.jp> 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?

			regards, tom lane

  19. Re: [HACKERS] Re: v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-20T10:45:25Z

    Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> 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

  20. Re: [HACKERS] Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-20T16:19:04Z

    Hiroshi Inoue <Inoue@tpf.co.jp> 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?

> 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?

			regards, tom lane

  21. RE: [HACKERS] Re: v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-20T21:48:19Z

    > -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> 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

  22. Re: [HACKERS] Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-20T21:52:43Z

    "Hiroshi Inoue" <Inoue@tpf.co.jp> 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.

>> 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.

			regards, tom lane

  23. Re: [HACKERS] Re: v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-20T23:30:41Z

    Tom Lane wrote:
> 
> "Hiroshi Inoue" <Inoue@tpf.co.jp> 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

  24. Re: [HACKERS] Re: v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-21T01:53:45Z

    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 'xxxx%d'(xxxx is the specified
database? name).

Regards,
Hiroshi Inoue

  25. Re: Re: [ADMIN] v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-21T03:35:53Z

    I Inoue wrote:
> 
> 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.

Sorry I forgot to mention the -B setting of my previous
posting. All results are with -B 1024.

Regards,
Hiroshi Inoue

  26. RE: Re: [ADMIN] v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-21T15:21:46Z

    > 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.
>

I tried with -B 1024 10 times for commit_delay=0 and 1 respectively.
The average result of 'pgbench -c 10 -t 100' is as follows.

[commit_delay=0]
 26.462817(including connections establishing)
 26.788047(excluding connections establishing)
[commit_delay=1]
 27.630405(including connections establishing)
 28.042666(excluding connections establishing)

Hiroshi Inoue

  27. RE: Re: [ADMIN] v7.1b4 bad performance

    Lincoln Yeoh <lyeoh@pop.jaring.my> — 2001-02-22T07:13:14Z

    Just another data point.

I downloaded a snapshot yesterday - Changelogs dated Feb 20 17:02

It's significantly slower than "7.0.3 with fsync off" for one of my webapps.

7.0.3 with fsync off gets me about 55 hits per sec max (however it's
interesting that the speed keeps dropping with continued tests).
( PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66)

For 7.1b4 snapshot I get about 23 hits per second (drops gradually too).
I'm using Pg::DBD compiled using the 7.1 libraries for both tests.
(PostgreSQL 7.1beta4 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66)

For a simple "select only" webapp I'm getting 112 hits per sec for 7.0.3.
and 109 hits a sec for the 7.1 beta4 snapshot. These results remain quite
stable over many repeated tests.

The first webapp does a rollback, begin, select, update, commit, begin, a
bunch of selects in sequence and rollback. 

So my guess is that the 7.1 updates (with default fsync) are significantly
slower than 7.0.3 fsync=off now. 

But it's interesting that the updates slow things down significantly. Going
from 50 to 30 hits per second after a few thousand hits for 7.0.3, and 23
to 17 after about a thousand hits for 7.1beta4.


For postgresql 7.0.3 to speed things back up from 30 to 60 hits per sec I
had to do:

lylyeoh=# delete from session;
DELETE 1
lylyeoh=# vacuum; vacuum analyze;
VACUUM
NOTICE:  RegisterSharedInvalid: SI buffer overflow
NOTICE:  InvalidateSharedInvalid: cache state reset
VACUUM
(Not sure why the above happened, but I repeated the vacuum again for good
measure)

lylyeoh=# vacuum; vacuum analyze;
VACUUM
VACUUM

Then I ran the apachebench again (after visiting the webpage once to create
the session).

Note that even with only one row in the session table it kept getting
slower and slower as it kept getting updated, even when I kept trying to
vacuum and vacuum analyze it. I had to delete the row and vacuum only then
was there a difference.

I didn't try this on 7.1beta4.

Cheerio,
Link.

  28. Re: Re: [ADMIN] v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-22T10:51:43Z

    I wrote:
> 
> I tried with -B 1024 10 times for commit_delay=0 and 1 respectively.
> The average result of 'pgbench -c 10 -t 100' is as follows.
> 
> [commit_delay=0]
>  26.462817(including connections establishing)
>  26.788047(excluding connections establishing)
> [commit_delay=1]
>  27.630405(including connections establishing)
>  28.042666(excluding connections establishing)
> 

I got another clear result by simplifying pgbench.

[commit_delay = 0]
1)tps = 52.682295(including connections establishing)
  tps = 53.574140(excluding connections establishing)
2)tps = 54.580892(including connections establishing)
  tps = 55.672988(excluding connections establishing)
3)tps = 60.409452(including connections establishing)
  tps = 61.740995(excluding connections establishing)
4)tps = 60.787502(including connections establishing)
  tps = 62.131317(excluding connections establishing)
5)tps = 60.968409(including connections establishing)
  tps = 62.328142(excluding connections establishing)
6)tps = 62.396566(including connections establishing)
  tps = 63.614357(excluding connections establishing)
7)tps = 52.720152(including connections establishing)
  tps = 54.811739(excluding connections establishing)
8)tps = 53.417274(including connections establishing)
  tps = 54.454355(excluding connections establishing)
9)tps = 54.862412(including connections establishing)
  tps = 55.953512(excluding connections establishing)
10)tps = 60.616255(including connections establishing)
   tps = 63.423590(excluding connections establishing)

[commit_delay = 1]
1)tps = 68.458715(including connections establishing)
  tps = 71.147012(excluding connections establishing)
2)tps = 71.059064(including connections establishing)
  tps = 72.685829(excluding connections establishing)
3)tps = 67.625556(including connections establishing)
  tps = 69.288699(excluding connections establishing)
4)tps = 84.749505(including connections establishing)
  tps = 87.430563(excluding connections establishing)
5)tps = 83.001418(including connections establishing)
  tps = 85.525377(excluding connections establishing)
6)tps = 66.235768(including connections establishing)
  tps = 67.830999(excluding connections establishing)
7)tps = 80.993308(including connections establishing)
  tps = 87.333491(excluding connections establishing)
8)tps = 69.844893(including connections establishing)
  tps = 71.640972(excluding connections establishing)
9)tps = 71.135311(including connections establishing)
  tps = 72.979021(excluding connections establishing)
10)tps = 68.091439(including connections establishing)
   tps = 69.539728(excluding connections establishing)

The patch to let pgbench execute 1 query/trans is the following.

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/22 10:03:52
***************
*** 217,222 ****
--- 217,224 ----
  			st->state = 0;
  	}
  
+ if (st->state > 1)
+ st->state=6;
  	switch (st->state)
  	{
  		case 0:			/* about to start */

Regards,
Hiroshi Inoue

  29. Re: RE: Re: [ADMIN] v7.1b4 bad performance

    Hannu Krosing <hannu@tm.ee> — 2001-02-22T11:49:04Z

    Lincoln Yeoh wrote:
> 
> Just another data point.
> 
> I downloaded a snapshot yesterday - Changelogs dated Feb 20 17:02
> 
> It's significantly slower than "7.0.3 with fsync off" for one of my webapps.
> 
> 7.0.3 with fsync off gets me about 55 hits per sec max (however it's
> interesting that the speed keeps dropping with continued tests).
> ( PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66)
> 
> For 7.1b4 snapshot I get about 23 hits per second (drops gradually too).
> I'm using Pg::DBD compiled using the 7.1 libraries for both tests.
> (PostgreSQL 7.1beta4 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66)
> 
> For a simple "select only" webapp I'm getting 112 hits per sec for 7.0.3.
> and 109 hits a sec for the 7.1 beta4 snapshot. These results remain quite
> stable over many repeated tests.
> 
> The first webapp does a rollback, begin, select, update, commit, begin, a
> bunch of selects in sequence and rollback.

It may be that WAL has changed the rollback time-characteristics to
worse 
than pre-wal ?

If that is the case tha routeinely rollbacking transactions is no longer 
a good programming practice.

It may have used to be as I think that before wal both rollback and
commit 
had more or less the same cost.

> So my guess is that the 7.1 updates (with default fsync) are significantly
> slower than 7.0.3 fsync=off now.

the consensus seems to be that they are only "a little" slower.

> But it's interesting that the updates slow things down significantly. Going
> from 50 to 30 hits per second after a few thousand hits for 7.0.3, and 23
> to 17 after about a thousand hits for 7.1beta4.
> 
> For postgresql 7.0.3 to speed things back up from 30 to 60 hits per sec I
> had to do:

-------------
Hannu

  30. Re: [HACKERS] Re: v7.1b4 bad performance

    Hannu Krosing <hannu@tm.ee> — 2001-02-23T11:09:37Z

    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

  31. Re: [HACKERS] Re: v7.1b4 bad performance

    Dmitry Morozovsky <marck@rinet.ru> — 2001-02-23T11:56:41Z

    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 --- marck@rinet.ru ***
------------------------------------------------------------------------

  32. Re: [HACKERS] Re: v7.1b4 bad performance

    Dave Mertens <dave@redbull.zyprexia.com> — 2001-02-23T12:21:32Z

    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 <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

  33. Re: [HACKERS] Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-23T15:53:11Z

    Hannu Krosing <hannu@tm.ee> 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.  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	100000
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.  100000
accounts rows seems enough though.

Making such a change would render results not comparable with the prior
pgbench, but that would be true with Hiroshi's change too.

Alternatively we could just say that we won't believe any numbers taken
at scale factors less than, say, 10, but I doubt we really need
million-row accounts tables in order to learn anything...

			regards, tom lane

  34. Re: [HACKERS] Re: v7.1b4 bad performance

    Tatsuo Ishii <t-ishii@sra.co.jp> — 2001-02-23T16:13:32Z

    > 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	100000
> 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.  100000
> 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

  35. Re: [HACKERS] Re: v7.1b4 bad performance

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-23T16:42:22Z

    Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> It seems to me that the branches table should have at least 10 to 100
>> entries, and tellers about 10 times whatever branches is.  100000
>> accounts rows seems enough though.

> Those numbers are defined in the TPC-B spec.

Ah.  And of course, the TPC bunch never thought anyone would be
interested in the results with scale factors so tiny as one ;-),
so they didn't see any problem with it.

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).

			regards, tom lane

  36. RE: [HACKERS] Re: v7.1b4 bad performance

    Inoue, Hiroshi <inoue@tpf.co.jp> — 2001-02-23T21:38:27Z

    > -----Original Message-----
> From: Tom Lane
> 
> Hannu Krosing <hannu@tm.ee> 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

  37. Re: [HACKERS] Re: v7.1b4 bad performance

    Tatsuo Ishii <t-ishii@sra.co.jp> — 2001-02-24T02:38:13Z

    > 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