Re: lots of updates on small table

Alison Winters wrote:
> Hi,
>
>
>>>Our application requires a number of processes to select and update rows
>>>from a very small (<10 rows) Postgres table on a regular and frequent
>>>basis. These processes often run for weeks at a time, but over the
>>>space of a few days we find that updates start getting painfully slow.
>>>We are running a full vacuum/analyze and reindex on the table every day,
>>
>>Full vacuum, eh? I wonder if what you really need is very frequent
>>non-full vacuum. Say, once in 15 minutes (exact rate depending on dead
>>tuple rate.)
>>
>
> Is there a difference between vacuum and vacuum full? Currently we have
> a cron job going every hour that does:
>
> VACUUM FULL VERBOSE ANALYZE plc_fldio
> REINDEX TABLE plc_fldio

VACUUM FULL exclusively locks the table (so that nothing else can
happen) and the compacts it as much as it can.
You almost definitely want to only VACUUM every 15min, maybe VACUUM FULL
1/day.

VACUUM FULL is more for when you haven't been VACUUMing often enough. Or
have major changes to your table.
Basically VACUUM marks rows as empty and available for reuse, VACUUM
FULL removes empty space (but requires a full lock, because it is moving
rows around).

If anything, I would estimate that VACUUM FULL would be hurting your
performance. But it may happen fast enough not to matter.

>
> The most recent output was this:
>
> INFO: --Relation public.plc_fldio--
> INFO: Pages 1221: Changed 3, reaped 256, Empty 0, New 0; Tup 108137: Vac 4176, Keep/VTL 108133/108133, UnUsed 19, MinLen 84, MaxLen 84; Re-using: Free/Avail. Space 445176/371836; EndEmpty/Avail. Pages 0/256.
> CPU 0.04s/0.14u sec elapsed 0.18 sec.
> INFO: Index plcpage_idx: Pages 315; Tuples 108137: Deleted 4176.
> CPU 0.03s/0.04u sec elapsed 0.14 sec.
> INFO: Rel plc_fldio: Pages: 1221 --> 1221; Tuple(s) moved: 0.
> CPU 0.03s/0.04u sec elapsed 0.36 sec.
> INFO: Analyzing public.plc_fldio
> VACUUM
> REINDEX
>
> We'll up it to every 15 minutes, but i don't know if that'll help
> because even with the current vacuuming the updates are still getting
> slower and slower over the course of several days. What really puzzles
> me is why restarting the processes fixes it. Does PostgreSQL keep some
> kind of backlog of transactions all for one database connection? Isn't
> it normal to have processes that keep a single database connection open
> for days at a time?

I believe that certain locks are grabbed per session. Or at least there
is some command that you can run, which you don't want to run in a
maintained connection. (It might be VACUUM FULL, I don't remember which
one it is).

But the fact that your application works at all seems to be that it
isn't acquiring any locks.

I know VACUUM cannot clean up any rows that are visible in one of the
transactions, I don't know if this includes active connections or not.

>
> Regarding the question another poster asked: all the transactions are
> very short. The table is essentially a database replacement for a
> shared memory segment - it contains a few rows of byte values that are
> constantly updated byte-at-a-time to communicate data between different
> industrial control processes.
>
> Thanks for the thoughts everyone,
>
> Alison
>

Is it possible to have some sort of timer that would recognize it has
been connected for too long, drop the database connection, and
reconnect? I don't know that it would solve anything, but it would be
something you could try.

John
=:->