To: "Guillaume Smet" <guillaume.smet@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: perf pb solved only after pg_dump and restore
References: <8764gd1dkz.fsf@meuh.mnc.lan> <44F2B737.7070301@logix-tt.com>
	<87sljhyz4b.fsf@meuh.mnc.lan>
	<1d4e0c10608280317i4eb9d7d0r2b397845ffb8c19d@mail.gmail.com>
From: Guillaume Cottenceau <gc@mnc.ch>
Date: 28 Aug 2006 14:31:47 +0200
In-Reply-To: <1d4e0c10608280317i4eb9d7d0r2b397845ffb8c19d@mail.gmail.com>
Message-ID: <874pvxyrbg.fsf@meuh.mnc.lan>
Lines: 87
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Guillaume,

Thanks for your help.

> On 28 Aug 2006 11:43:16 +0200, Guillaume Cottenceau <gc@mnc.ch> wrote:
> >  max_fsm_pages is 20000
> >  max_fsm_relations is 1000
> > Do they look low?
> 
> Yes they are probably too low if you don't run VACUUM on a regular
> basis and you have a lot of UPDATE/DELETE activity. FSM doesn't take a
> lot of memory so it's usually recommended to have a confortable value
> for it.

Normally, we run VACUUM ANALYZE overnight. I'd say we have low
DELETE activity, kinda high SELECT/INSERT activity, and UPDATE
would be in the middle of that.
 
> I usually recommend to read:
> http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10116&cNode=5K1C3W
> http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10087&cNode=5K1C3W
> to understand better what VACUUM and FSM mean.

Thanks for the pointer, will read that.
 
> > Can you elaborate? I have created a couple of indexes (according
> > to multiple models of use in our application) and they do take up
> > quite some disk space (table dump is 600M but after restore it
> > takes up 1.5G on disk) but I thought they could only do good or
> > never be used, not impair performance..
> 
> Index slow downs write activity (you have to maintain them). It's not
> always a good idea to create them.

Of course. How newbie did I look :/. The thing is that I once did
a few measurements and noticed no (measurable) impact in INSERT
with a supplementary index, so I (wrongly) forgot about this.
 
> > > Maybe a VACUUM FULL fullowed by a REINDEX will have solved your problem.
> >
> > So these would have reordered the data for faster sequential
> > access which is not the case of VACUUM ANALYZE?
> 
> VACUUM ANALYZE won't help you if your database is completely bloated.

What do you mean exactly by bloated? If you mean that there is a
lot of (unused) data, the thing is that our trim removed most of
it. I was kinda hoping that after analyzing the database, the old
data would exit the whole picture, which obviously wasn't the
case.

About REINDEX: is it ok to consider that REINDEX is to indexes
what VACUUM FULL is to table data, because it cleans up unused
index pages?

> And AFAICS you're not running it on a regular basis so your database
> was probably completely bloated which means:
> - bloated indexes,
> - bloated tables (ie a lot of fragmentation in the pages which means
> that you need far more pages to store the same data).

I suppose that table fragmentation occurs when DELETE are
interleaved with INSERT?
 
> The only ways to solve this situation is either to dump/restore or run
> a VACUUM FULL ANALYZE (VERBOSE is better to keep a log), and
> eventually reindex any bloated index (depends on your situation).

Ok.
 
> > > When the free_space_map is to low, VACUUM ANALYZE should have told you
> > > via a warning (at least, if your logging is set appropriately).
> >
> > Unfortunately, we didn't keep the logs of VACUUM ANALYZE, so I
> > can't be sure :/
> 
> You should really run VACUUM ANALYZE VERBOSE on a regular basis and
> analyze the logs to be sure your VACUUM strategy and FSM settings are
> OK.

VACUUM ANALYZE is normally run overnight (each night). Is it not
regular enough? There can be hundreds of thousands of statements
a day.

-- 
Guillaume Cottenceau
Create your personal SMS or WAP Service - visit http://mobilefriends.ch/