Re: v7.1b4 bad performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Schmidt, Peter" <peter(dot)schmidt(at)prismedia(dot)com>
Cc: pgsql-hackers(at)postgreSQL(dot)org, pgsql-admin(at)postgreSQL(dot)org
Subject: Re: v7.1b4 bad performance
Date: 2001-02-17 06:10:38
Message-ID: 3038.982390238@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-hackers

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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tatsuo Ishii 2001-02-17 06:46:35 Re: [HACKERS] Re: v7.1b4 bad performance
Previous Message Schmidt, Peter 2001-02-17 05:28:35 RE: v7.1b4 bad performance

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2001-02-17 06:46:35 Re: [HACKERS] Re: v7.1b4 bad performance
Previous Message Bruce Momjian 2001-02-17 05:37:58 Re: Re: beta5 ...