From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Vacuum goes worse
Date: Tue, 16 Oct 2007 10:08:58 +0100
Message-ID: <47147FAA.7010909@enterprisedb.com> (raw)
In-Reply-To: <47147AD8.9010704@postgresqlfr.org>
References: <47147AD8.9010704@postgresqlfr.org>
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
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: heikki@enterprisedb.com, stephane.schildknecht@postgresqlfr.org
Subject: Re: Vacuum goes worse
In-Reply-To: <47147FAA.7010909@enterprisedb.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