Re: Oracle vs PostgreSQL in real life

----- Original Message -----
From: "Jean-Paul ARGUDO" <jean-paul(dot)argudo(at)idealx(dot)com>
>
>
> PS: I am currently testing vacuums between the script to pause the data
> manipulation, make a vacuum analyze and continue the treatments.
>

I ran a small test (Pg 7.2, RH 7.1, Athlon 850, 512Mb) that created a small
table of 2 fields with primary key on first,
filled it with 768 values and then run the following script:

--------------------------------
#!/usr/bin/python

import pg, random
con = pg.connect()
q = "update t01 set val='bum' where i = %s"
for trx in range(5000):
con.query('begin')
for cmd in range(20):
rn = random.randint(1,768)
con.query(q % rn)
con.query('commit')
--------------------------------

when run as is it made average of 152 updates/sec
[hannu(at)taru hannu]$ time ./abench.py

real 10m55.034s
user 0m27.270s
sys 0m4.700s

after doing a vacuum full i run it together with a parallel process
that was a simple loop sleeping 5 sec and then doing vacuum

--------------------------------
#!/usr/bin/python

import time, pg

con = pg.connect()

for trx in range(5000):
for cmd in range(20):
time.sleep(5)
print 'vacuum'
con.query('vacuum')
print 'done!'
--------------------------------

The same script runs now at average 917
[hannu(at)taru hannu]$ time ./abench.py

real 1m48.416s
user 0m16.840s
sys 0m3.300s

So here we have a case where the new vacuum can really save a day !

I also tried other vacuuming intervals and it seems that ~4 sec is the best
for this case

here are test results
interval - time - updates per sec

2 sec - 1.53.5 - 881
3 sec - 1.49.6 - 912
4 sec - 1.48.0 - 925
5 sec - 1.48.4 - 922
6 sec - 1.49.7 - 911
10 sec - 1.56.8 - 856
no vac - 10.55.0 - 152
--------------
Hannu