Re: How to avoid database bloat

> >>AFAICT the vacuum is doing what it is supposed to, and the problem has
> >>to be just that it's not being done often enough. Which suggests either
> >>an autovacuum bug or your autovacuum settings aren't aggressive enough.
> >
> > -D -d 1 -v 1000 -V 0.5 -a 1000 -A 0.1 -s 10
> >
> > That is autovacuum settings. Should be aggressive enough I think?
>
> Might e aggressive enough, but might not. I have seen some people run
> -V 0.1. Also you probably don't need -A that low. This could an issue
> where analyze results in an inaccurate reltuples value which is
> preventing autovacuum from doing it's job. Could you please run it with
> -d 2 and show us the relevant log output.

Relevant parts are below. And we had to set so aggressive analyze because
otherwise planer statistics were getting old too fast. As I said table has
very
high turnover most of the records live here only for a few seconds.

And one more question - anyway why table keeps growing? It is shown that
it occupies
<10000 pages and max_fsm_pages = 200000 so vacuum should keep up with the
changes?
Or is it too low according to pg_class system table? What should be the
reasonable value?

select sum(relpages) from pg_class;
sum
-------
77994
(1 row)

Thanks,

Mindaugas

[2005-06-03 09:30:31 EEST] DEBUG: Performing: ANALYZE "queue"
[2005-06-03 09:30:31 EEST] INFO: table name: database."queue"
[2005-06-03 09:30:31 EEST] INFO: relid: 465440; relisshared: 0
[2005-06-03 09:30:31 EEST] INFO: reltuples: 98615.000000; relpages:
6447
[2005-06-03 09:30:31 EEST] INFO: curr_analyze_count: 39475111;
curr_vacuum_count: 30
953987
[2005-06-03 09:30:31 EEST] INFO: last_analyze_count: 39475111;
last_vacuum_count: 30
913733
[2005-06-03 09:30:31 EEST] INFO: analyze_threshold: 10861;
vacuum_threshold: 43700

[2005-06-03 09:31:11 EEST] DEBUG: Performing: VACUUM ANALYZE "queue"
[2005-06-03 09:31:12 EEST] INFO: table name: database."queue"
[2005-06-03 09:31:12 EEST] INFO: relid: 465440; relisshared: 0
[2005-06-03 09:31:12 EEST] INFO: reltuples: 99355.000000; relpages:
6447
[2005-06-03 09:31:12 EEST] INFO: curr_analyze_count: 39480332;
curr_vacuum_count: 30
957872
[2005-06-03 09:31:12 EEST] INFO: last_analyze_count: 39480332;
last_vacuum_count: 30
957872
[2005-06-03 09:31:12 EEST] INFO: analyze_threshold: 10935;
vacuum_threshold: 50677