Vacuum Problems (locking)

From: Mike Goldner <mgoldner(at)agmednet(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Vacuum Problems (locking)
Date: 2006-10-25 19:54:17
Message-ID: 1161806057.2095.154.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Joshua D. Drake 2006-10-25 19:58:07 Re: Vacuum Problems (locking)
Previous Message Luís Sousa 2006-10-25 17:13:22 pg_restore and drop table problems