From: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Vacuum goes worse
Date: Tue, 16 Oct 2007 10:48:24 +0200
Message-ID: <47147AD8.9010704@postgresqlfr.org> (raw)
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
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: stephane.schildknecht@postgresqlfr.org
Subject: Re: Vacuum goes worse
In-Reply-To: <47147AD8.9010704@postgresqlfr.org>
* 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