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: Mon, 6 Jun 2005 17:58:10 +0300
Message-ID: <03f801c56aa8$2867abe0$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>
<010701c56817$fd543d20$f20214ac@bite.lt>
<42A0889A.2050200@zeut.net>
> Looked like pg_autovacuum is operating as expected. One of the annoying
> limitations of pg_autovacuum in current releases is that you can't set
> thresholds on a per table basis. It looks like this table might require
> an even more aggressive vacuum threshold. Couple of thoughts, are you
> sure it's the table that is growing and not the indexes? (assuming this
> table has indexes on it).
Yes I am sure (oid2name :) ).
> > 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?
> >
> >
>
> Does the table keep growing? Or does it grow to a point an then stop
> growing? It's normal for a table to operate at a steady state size that
> is bigger that it's fresly "vacuum full"'d size. And with -V set at 0.5
> it should be at a minimum 50% larger than it's minimum size. Your email
> before said that this table went from 20M to 70M but does it keep
> going? Perhaps it would start leveling off at this point, or some point
> shortly there-after.
Yes it keeps growing. And the main problem is that performance starts to
suffer from that. Do not forget that we are talking about 100+ insert/
update/select/delete cycles per second.
> Anyway, I'm not sure if there is something else going on here, but from
> the log it looks as though pg_autovacuum is working as advertised.
Something is out there :). But how to fix that bloat? More aggressive
autovacuum settings? Even larger FSM?
Do not know if that matters but database has very many connections to
it (400-600) and clients are doing mostly asynchronous operations.
How to find out where this extra space gone?
Thanks,
Mindaugas
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: <03f801c56aa8$2867abe0$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