VACUUM and 24/7 database operation
От | Thomas.Favier@accelance.fr |
---|---|
Тема | VACUUM and 24/7 database operation |
Дата | |
Msg-id | H0000071001296af.0980269314.mercure.accelance.net@MHS обсуждение исходный текст |
Ответы |
Re: VACUUM and 24/7 database operation
|
Список | pgsql-general |
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 ______________________________________________________
В списке pgsql-general по дате отправления: