DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=E3tRf2/ub1RG1QMNOpkNfzcHy6/l3aUsjJtzkvCGICjPQtzwaUeUohzO5Q8SSGohcKzMQZvG8zgOFIll56029jqqbTHhrrqbKmZEWxsrkwAj4L80OQAkJSGT1lfM9R4eohn/jfv2FQ9B5BeJ/Qz0MUVIjHOGh3i218C/Pe4Ac50=
Message-ID: <47147FAA.7010909@enterprisedb.com>
Date: Tue, 16 Oct 2007 10:08:58 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: =?ISO-8859-1?Q?St=E9phane_Schildknecht?=
	<stephane.schildknecht@postgresqlfr.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Vacuum goes worse
References: <47147AD8.9010704@postgresqlfr.org>
In-Reply-To: <47147AD8.9010704@postgresqlfr.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Stéphane Schildknecht wrote:
> 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...

Why not?

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

sar or iostat output would be a good start, to determine if it's waiting
for I/O or what.

> 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).

Increasing checkpoint_segments seems like a good idea then. You should
increase checkpoint_timeout as well, 180 is just 3 minutes. How much
concurrent activity is there in the database? 30 pg_xlog files equals
512 MB of WAL; that's quite a lot.

Have you changed the vacuum cost delay settings from the defaults?

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com