From: Markus Schaber <schabi@logix-tt.com>
To: pgsql-performance@postgresql.org
Subject: Re: perf pb solved only after pg_dump and restore
Date: Mon, 28 Aug 2006 15:13:48 +0200
Message-ID: <44F2EC0C.7090407@logix-tt.com> (raw)
In-Reply-To: <874pvxyrbg.fsf@meuh.mnc.lan>
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>
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
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: schabi@logix-tt.com
Subject: Re: perf pb solved only after pg_dump and restore
In-Reply-To: <44F2EC0C.7090407@logix-tt.com>
* 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