Message-ID: <03f801c56aa8$2867abe0$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>
	<010701c56817$fd543d20$f20214ac@bite.lt>
	<42A0889A.2050200@zeut.net>
Subject: Re: How to avoid database bloat
Date: Mon, 6 Jun 2005 17:58:10 +0300
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1257"
Content-Transfer-Encoding: 7bit


> 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