RE: v7.1b4 bad performance

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, February 14, 2001 8:02 AM
> To: Schmidt, Peter
> Cc: 'Michael Ansley'; 'pgsql-admin@postgresql.org'
> Subject: Re: [ADMIN] v7.1b4 bad performance
>
>
> "Schmidt, Peter" <peter.schmidt@prismedia.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@dev-postgres:~ > postmaster -i -D /usr/local/pgsql/data &
[1] 73010
postgres@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@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)