Thread: Problem with corrupt index
Hi everybody, I have a table that requires periodical updates (every 5 seconds) in more than one register. The table is very small (about 7 registers) and it wont grow. Sometimes the access to that table becomes very slow, and VACUUM dont fix it, I have to REINDEX the table. Anyone can tell me what should be the cause of this problem? Thanks in advance, -- Diogo de Oliveira Biazus diogo@ikono.com.br Ikono Sistemas e Automação http://www.ikono.com.br
Diogo Biazus <diogo@ikono.com.br> writes: > Hi everybody, > I have a table that requires periodical updates (every 5 seconds) in > more than one register. > The table is very small (about 7 registers) and it wont grow. > Sometimes the access to that table becomes very slow, and VACUUM dont > fix it, I have to REINDEX the table. > Anyone can tell me what should be the cause of this problem? Indexes (currently) aren't affected by VACUUM. You have to REINDEX periodically, depending on table activity. -Doug
On Mon, Sep 30, 2002 at 02:41:16PM -0300, Diogo Biazus wrote: > Hi everybody, > I have a table that requires periodical updates (every 5 seconds) in > more than one register. > The table is very small (about 7 registers) and it wont grow. > Sometimes the access to that table becomes very slow, and VACUUM dont > fix it, I have to REINDEX the table. Do you have an incrementing field in the table, and index it? If so, your problem is that the index doesn't drop empty pages or mark them as free, and updating it will make the index bigger; the only way to reclaim space is by means of recreating the index. If you only have 7 registers in the table, you don't need the index anyway: a sequential scan is much faster. My advice would be to drop the index. If you have a primary key you can't do that, and you'll have to continue doing REINDEX and VACUUM periodically. -- Alvaro Herrera (<alvherre[a]atentus.com>) "La virtud es el justo medio entre dos defectos" (Aristoteles)
Alvaro Herrera <alvherre@atentus.com> writes:
> If you only have 7 registers in the table, you don't need the index
> anyway: a sequential scan is much faster. My advice would be to drop
> the index.
Mine too.
> If you have a primary key you can't do that, and you'll have
> to continue doing REINDEX and VACUUM periodically.
In any case, the fact you are having this problem suggests that you
aren't doing VACUUMs often enough on this table. If you've got seven
live rows and you update one of them every 5 seconds, then after an hour
you have seven live rows and 720 dead ones. Things are going to be a
good bit slower than before. If you wait days between vacuums then it
gets worse.
I'd recommend putting in a cron task to vacuum this particular table
hourly, or maybe even oftener.
regards, tom lane