From: Mindaugas Riauba <mind@bi.lt>
To: Matthew T. O'Connor <matthew@zeut.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to avoid database bloat
Date: Fri, 3 Jun 2005 11:41:08 +0300
Message-ID: <010701c56817$fd543d20$f20214ac@bite.lt> (raw)
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>
> >>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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mind@bi.lt, matthew@zeut.net, tgl@sss.pgh.pa.us
Subject: Re: How to avoid database bloat
In-Reply-To: <010701c56817$fd543d20$f20214ac@bite.lt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox