Hello,
For one of our customer, we are running a PostgreSQL database on a
dynamic PHP-driven site. This site has a minimum of 40 visitors at a
time and must be responsive 24h a day.
One of the table has 500.000 rows and is very frequently accessed
(it is the table registering basic users infos). We have no performance
problem dispite the large amount of updates done on this table.
The problem is with VACUUMing this table. It takes 2 long minutes
everyday. Two minutes during wich no request can be done because of the
lock on the table... (nearly every request is involving this large
table). Our customer really dislike this fact and is planning to
replace PostgreSQL with Oracle.
2 minutes is seen by our customer as sufficent for his customer to
get away from his site.
Questions :
- Is 2 minutes a standard time for vacuuming a 500.000 rows table ?
- Can it be reduced ?
- In a far future, what are the problems we can run into not vacuuming
that table ? We have already seen that after a month, some transactions
involving where id >= some_value take forever, so we supressed them.
Below are details on the table :
erp-# \d visiteurs
Table "visiteurs"
Attribute | Type | Modifier
---------------+--------------+----------------------
id | integer | not null
login | varchar(127) | not null
password | varchar(10) | not null
name | varchar(10) | not null
datecrea | timestamp | not null
payszoneid | varchar(127) | not null
ptzoneid | varchar(127) | not null
dialertitle | varchar(15) |
referer | varchar(255) |
exported | varchar(2) | not null default 'N'
earncentmin | float8 |
opearncentmin | float8 |
ret | float8 |
paymentid | integer |
entiteid | varchar(127) | not null
etat | varchar(2) | default 'E'
devise | smallint |
entitelogin | varchar(20) |
Indices: visiterus_etat,
visiteurs_exported,
visiteurs_id_btree,
visiteurs_login
erp=# select relname,relpages,reltuples from pg_class where
relname='visiteurs';
relname | relpages | reltuples
-----------+----------+-----------
visiteurs | 14549 | 584489
(1 row)
Thank you.
Thomas FAVIER
thomas.favier@accelance.fr
______________________________________________________
ACCELANCE - www.accelance.fr
97, rue Racine - 69100 Villeurbanne
Tel: +33 (0)4 37 43 12 22 / Fax: +33 (0)4 37 43 12 20
______________________________________________________
