RE: v7.1b4 bad performance

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Wednesday, February 14, 2001 8:02 AM
> To: Schmidt, Peter
> Cc: 'Michael Ansley'; 'pgsql-admin(at)postgresql(dot)org'
> Subject: Re: [ADMIN] v7.1b4 bad performance
>
>
> "Schmidt, Peter" <peter(dot)schmidt(at)prismedia(dot)com> writes:
> > However, what I'm interested in is the comparison between
> 7.0x and 7.1x.
> > Shouldn't the 7.1b4 database be faster? Have you (or anyone
> else) done this
> > comparison?
>
> 7.1 is considerably faster if you compare performance without -F.
> If you are comparing performance with -F, I'm not sure exactly how it
> stacks up.
>
> regards, tom lane
>

Here are some of the results of my tests which are very consistent. I
dropped and recreated postgres db before each test so the only user tables
were the pgbench tables.

7.1b4(no -F) 49 tps
7.03 (no -F) 64 tps
7.03 (with -F) 121 tps

The test machine is a Dell Dimension 800mz w/128 RAM and it is dedicated to
this testing. Pgbench is run on the same machine. Any ideas why my results
differ? I will continue testing and appreciate all comments.
Peter

7.1b4 without -F option
---------------------------------

postgres(at)dev-postgres:~ > postmaster -i -D /usr/local/pgsql/data &
[1] 73010
postgres(at)dev-postgres:~ > DEBUG: starting up
DEBUG: database system was shut down at 2001-02-14 08:23:53
DEBUG: CheckPoint record at (0, 79220804)
DEBUG: Redo record at (0, 79220804); Undo record at (0, 0); Shutdown TRUE
DEBUG: NextTransactionId: 9947; NextOid: 126002
DEBUG: database system is in production state

postgres=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.1beta4 on i386-unknown-freebsd4.0, compiled by GCC 2.95.2
(1 row)

postgres(at)dev-postgres:~ > ./pgbench -t1000
starting vacuum...DEBUG: --Relation branches--
DEBUG: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 1: Vac 0, Keep/VTL
0/0, Crash 0, UnUsed 0, MinLen 44, MaxLen 44; Re-using: Free/Avail. Space
0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
DEBUG: Index branches_pkey: Pages 2; Tuples 1. CPU 0.00s/0.00u sec.
DEBUG: --Relation tellers--
DEBUG: Pages 1: Changed 0, reaped 0, Empty 0, New 0; Tup 10: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 48, MaxLen 48; Re-using: Free/Avail.
Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
DEBUG: Index tellers_pkey: Pages 2; Tuples 10. CPU 0.00s/0.00u sec.
DEBUG: --Relation history--
DEBUG: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL
0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 49.390375(including connections establishing)
tps = 49.426294(excluding connections establishing)

7.03 without -F option
-------------------------------

su-2.03$ postmaster -i -D/usr/local/pgsql/data &
[1] 73068
su-2.03$ DEBUG: Data Base System is starting up at Wed Feb 14 08:27:00 2001
DEBUG: Data Base System was shut down at Wed Feb 14 08:20:37 2001
DEBUG: Data Base System is in production state at Wed Feb 14 08:27:00 2001

postgres=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.0.3 on i386-unknown-freebsdelf4.0, compiled by gcc 2.95.2
(1 row)

su-2.03$ ./pgbench -t1000
starting vacuum...DEBUG: --Relation branches--
DEBUG: Pages 6: Changed 0, reaped 6, Empty 0, New 0; Tup 1: Vac 1000,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 44, MaxLen 44; Re-using: Free/Avail.
Space 45056/37520; EndEmpty/Avail. Pages 0/5. CPU 0.00s/0.00u sec.
DEBUG: Index branches_pkey: Pages 4; Tuples 1: Deleted 1000. CPU
0.00s/0.00u sec.
DEBUG: Rel branches: Pages: 6 --> 1; Tuple(s) moved: 1. CPU 0.00s/0.00u
sec.
DEBUG: Index branches_pkey: Pages 4; Tuples 1: Deleted 1. CPU 0.00s/0.00u
sec.
DEBUG: --Relation tellers--
DEBUG: Pages 7: Changed 1, reaped 7, Empty 0, New 0; Tup 10: Vac 1000,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 48, MaxLen 48; Re-using: Free/Avail.
Space 52768/45336; EndEmpty/Avail. Pages 0/6. CPU 0.00s/0.00u sec.
DEBUG: Index tellers_pkey: Pages 6; Tuples 10: Deleted 1000. CPU
0.00s/0.00u sec.
DEBUG: Rel tellers: Pages: 7 --> 1; Tuple(s) moved: 10. CPU 0.00s/0.00u
sec.
DEBUG: Index tellers_pkey: Pages 6; Tuples 10: Deleted 10. CPU 0.00s/0.00u
sec.
DEBUG: --Relation history--
DEBUG: Pages 8: Changed 0, reaped 8, Empty 0, New 0; Tup 0: Vac 1000,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail.
Space 61472/0; EndEmpty/Avail. Pages 8/0. CPU 0.00s/0.00u sec.
DEBUG: Rel history: Pages: 8 --> 0.
end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 64.836518(including connections establishing)
tps = 64.893213(excluding connections establishing)

7.03 with -F option
---------------------------

su-2.03$ postmaster -i -D/usr/local/pgsql/data -o -F &
[1] 73104
su-2.03$ DEBUG: Data Base System is starting up at Wed Feb 14 08:28:56 2001
DEBUG: Data Base System was shut down at Wed Feb 14 08:28:35 2001
DEBUG: Data Base System is in production state at Wed Feb 14 08:28:56 2001

postgres=# select version();
version
------------------------------------------------------------------------
PostgreSQL 7.0.3 on i386-unknown-freebsdelf4.0, compiled by gcc 2.95.2
(1 row)

su-2.03$ ./pgbench -t1000
starting vacuum...DEBUG: --Relation branches--
DEBUG: Pages 6: Changed 0, reaped 6, Empty 0, New 0; Tup 1: Vac 1000,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 44, MaxLen 44; Re-using: Free/Avail.
Space 45056/37520; EndEmpty/Avail. Pages 0/5. CPU 0.00s/0.00u sec.
DEBUG: Index branches_pkey: Pages 4; Tuples 1: Deleted 1000. CPU
0.00s/0.00u sec.
DEBUG: Rel branches: Pages: 6 --> 1; Tuple(s) moved: 1. CPU 0.00s/0.00u
sec.
DEBUG: Index branches_pkey: Pages 4; Tuples 1: Deleted 1. CPU 0.00s/0.00u
sec.
DEBUG: --Relation tellers--
DEBUG: Pages 7: Changed 1, reaped 7, Empty 0, New 0; Tup 10: Vac 1000,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 48, MaxLen 48; Re-using: Free/Avail.
Space 52768/45336; EndEmpty/Avail. Pages 0/6. CPU 0.00s/0.00u sec.
DEBUG: Index tellers_pkey: Pages 6; Tuples 10: Deleted 1000. CPU
0.00s/0.00u sec.
DEBUG: Rel tellers: Pages: 7 --> 1; Tuple(s) moved: 10. CPU 0.00s/0.00u
sec.
DEBUG: Index tellers_pkey: Pages 6; Tuples 10: Deleted 10. CPU 0.00s/0.00u
sec.
DEBUG: --Relation history--
DEBUG: Pages 8: Changed 0, reaped 8, Empty 0, New 0; Tup 0: Vac 1000,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail.
Space 61472/0; EndEmpty/Avail. Pages 8/0. CPU 0.00s/0.00u sec.
DEBUG: Rel history: Pages: 8 --> 0.
end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 121.475362(including connections establishing)
tps = 121.671287(excluding connections establishing)