Обсуждение: autovacuum?

Hi.

I have a mostly insert/select database, so vacumming is generally not
much needed.. but among the tables I have one which is used as a
message-queue so a lot of insert/updates/deletes on small simple records
on that one.

How do I check that autovacumm is running at all? (I think i have
enabled it in /etc/postgresql/8.2/*/postgresql.conf ).

select relname,last_autovacuum,last_autoanalyze from pg_stat_user_tables
where relname = 'job';
  relname |        last_autovacuum        |       last_autoanalyze
---------+-------------------------------+-------------------------------
  job     | 2008-04-16 07:19:24.832413+02 | 2008-04-17 01:40:05.242914+02


So it seems to be running. But shouldn't it hit the table a lot more?
How do I check that it is somehow near optimal?


--
Jesper

On Fri, Apr 18, 2008 at 1:18 AM, Jesper Krogh <jesper@krogh.cc> wrote:
>
>
>  So it seems to be running.

Yes.

> But shouldn't it hit the table a lot more?

That depends on your autovac settings, especially
autovacuum_vacuum_scale_factor. If it is set to 0.2 (which is
default), then autovacuum would hit the table whenever the percentage
of dead tuples is at least 20%. Again, the percentage of dead tuples
depends on your table size and rate of insert/update/delete.

>  How do I check that it is somehow near optimal?
>

Again that depends on how much table bloat you can sustain. If you
don't want the table to bloat too much, you can set aggressive
settings for autovac. But that would trigger autovac more often and
may have performance implication on your other concurrent queries.


Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com