----- 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
