Message-ID: <44F2EC0C.7090407@logix-tt.com>
Date: Mon, 28 Aug 2006 15:13:48 +0200
From: Markus Schaber <schabi@logix-tt.com>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Thunderbird 1.5.0.4 (X11/20060713)
MIME-Version: 1.0
To: 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>
	<874pvxyrbg.fsf@meuh.mnc.lan>
In-Reply-To: <874pvxyrbg.fsf@meuh.mnc.lan>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

Hi, Guillaume,

Guillaume Cottenceau wrote:

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

Yes, roughly speaking.

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

Yes, and it gets ugly as soon as the fsm setting is to low / VACUUM
frequency is to low, so it cannot keep up.

Big bunches of UPDATE/DELETE that hit more than, say 20% of the table
between VACUUM runs, justify a VACUUM FULL in most cases.

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

Which PostgreSQL version are you using? Maybe you should consider
autovacuum (which is a contrib module at least since 7.4, and included
in the server since 8.1). If you think that vacuum during working hours
puts too much load on your server, there are options to tweak that, at
least in 8.1.

Markus


-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org