From: Mike Goldner <mgoldner@agmednet.com>
To: pgsql-admin@postgresql.org
Subject: Vacuum Problems (locking)
Date: Wed, 25 Oct 2006 15:54:17 -0400
Message-ID: <1161806057.2095.154.camel@localhost.localdomain> (raw)
I have a nightly vacuum scheduled as follows:
su - postgres -c "/usr/bin/vacuumdb --analyze --dbname=mydb"
Last night, it appears that the vacuum blocked db access from my
application server (JBoss). Here is the logfile snippet:
[3693-jbossdb-postgres-2006-10-25 06:52:29.488 EDT]NOTICE: number of
page slots needed (559472) exceeds max_fsm_pages (200000)
[3693-jbossdb-postgres-2006-10-25 06:52:29.488 EDT]HINT: Consider
increasing the configuration parameter "max_fsm_pages" to a value over
559472.
[3693-jbossdb-postgres-2006-10-25 06:52:29.488 EDT]LOG: number of page
slots needed (559472) exceeds max_fsm_pages (200000)
[3693-jbossdb-postgres-2006-10-25 06:52:29.488 EDT]HINT: Consider
increasing the configuration parameter "max_fsm_pages" to a value over
559472.
[3693-jbossdb-postgres-2006-10-25 06:52:29.530 EDT]LOG: transaction ID
wrap limit is 1073813592, limited by database "benchpg"
[3693-jbossdb-postgres-2006-10-25 06:52:29.530 EDT]LOG: transaction ID
wrap limit is 1073813592, limited by database "benchpg"
[3693-jbossdb-postgres-2006-10-25 06:52:29.530 EDT]LOG: duration:
10224738.050 ms statement: VACUUM ANALYZE;
First of all, my max_fsm_pages is obviously way off. However, every
time I increase my max_fsm_pages the next vacuum says that it requires
more. Will there every be a plateau in the requested pages? What is
the impact of exceeding the pages slots needed?
Secondly, in this case, it almost looks like there were two vacuum
process running based on the duplicated log messages. There is only one
cron job, so there shouldn't have been a second vacuum running. Should
I just ignore this?
Finally, and most important is the blocking. The vacuum duration
reported in the log converts to about 170 minutes. I can track
backwards in the log and the only messages prior to the 6:52am
completion of the vacuum end at 3:57am (almost exactly 170 minutes
prior).
So, all indications point to postgres blocking all access during the
entire vacuum.
The only semi-unusual characteristic of my database is that I have one
table in particular that grows and shrinks dramatically. My entire
database after a vacuum --full is about 8GB. However, after a day or
two, the db will grow as large as 75GB. Truncating that one table
(since it empties regularly) will shrink the db right back to the 8GB.
Is it possible that the extreme table size (maybe requiring the high
number of page slots) is causing postgres to block more than it should
in order to process that table?
Thanks,
Mike
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: mgoldner@agmednet.com
Subject: Re: Vacuum Problems (locking)
In-Reply-To: <1161806057.2095.154.camel@localhost.localdomain>
References: <1161806057.2095.154.camel@localhost.localdomain>
* 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