Message-ID: <47147AD8.9010704@postgresqlfr.org>
Date: Tue, 16 Oct 2007 10:48:24 +0200
From: =?ISO-8859-1?Q?St=E9phane_Schildknecht?=
	<stephane.schildknecht@postgresqlfr.org>
User-Agent: Thunderbird 1.5.0.13 (X11/20070824)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Vacuum goes worse
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Hi,

For some times, we have a vacuuming process on a specific table that
goes slower and slower. In fact, it took some 3 minutes a month ago, and
now it take almost 20 minutes. But, if one day it take so many time, it
is possible that on the day after it will only take 4 minutes...

I know the table in concern had 450000 tuples two months ago and now has
more than 700000 tuples in it.

I wonder vacuum verbose would tell me if fsm parameters were not too
badly configured, but I can't get the 4 last lines of the output...

Is there another way to get these info ? Or is it a parameter badly
configured ?

For information, it's on AIX, PG8.1.9.

Some configuration parameters :
client_min_messages : notice
log_error_verbosity : default
log_min_error_statement : panic
log_min_messages : notice.

Whats's more, I wonder what we could monitor to get some explanation of
the recent time increase, and then have a quite-sure way of configuring
the server.

I have to say the database is hosted, accessed in production on a 24/7
basis and then every change in configuration has to be scheduled.

Some more information you may ask:
chackpoint_segments : 32
checkpoint_timeout    : 180
checkpoint_warning   : 30
wal_buffers                 : 64
maintenance_work_mem : 65536
max_fsm_pages                : 400000
max_fsm_relations           : 1000
shared_buffers                : 50000
temp_bufers                    : 1000

We also have 4Gb RAM.

Isn't checkpoint_segments too low as all files in pg_xlogs seem to be
recycled within a few minutes. (In fact among the 60 files, at least 30
have been modified during the few minutes of that particular vacuum).

Thanks for any advice you could give me.

Best regards,

-- 
Stéphane SCHILDKNECHT
Président de PostgreSQLFr
http://www.postgresqlfr.org