Re: Oracle vs PostgreSQL in real life

----- Original Message -----
From: "Jean-Paul ARGUDO" <jean-paul.argudo@idealx.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@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@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