Message-ID: <010701c56817$fd543d20$f20214ac@bite.lt>
From: "Mindaugas Riauba" <mind@bi.lt>
To: "Matthew T. O'Connor" <matthew@zeut.net>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
	<pgsql-performance@postgresql.org>
References: <00d501c5667d$8cc12320$f20214ac@bite.lt>
	<13878.1117636799@sss.pgh.pa.us>
	<018601c56744$9d5aae50$f20214ac@bite.lt>
	<13178.1117719939@sss.pgh.pa.us>
	<02db01c5677e$d43d3b10$f20214ac@bite.lt>
	<429F2F73.8050406@zeut.net>
Subject: Re: How to avoid database bloat
Date: Fri, 3 Jun 2005 11:41:08 +0300
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1257"
Content-Transfer-Encoding: 7bit

> >>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