On Mon, 19 Jun 2017 17:33:23 +0000
Dmitry O Litvintsev <litvinse@fnal.gov> wrote:
>
> The test stand where I was to test schema upgrade is stuck cuz vacuum is blocking.
If you're in "panic mode" I would recommend cancelling the existing vacuum,
running your upgrades, then immeditely running VACUUM FREEZE ANALYZE on that table to
make up for cancelling the autovacuum. Note that the manual VACUUM may take quite a while,
so run it in a screen session or something. Additionally, autovacuum is going to try to
relaunch that vacuum pretty aggressively, so you might have to cancel it a few times (using
pg_terminate_backend()) before your other processes are able to sneak in ahead of it.
Once you're out of panic mode you can take some time to breathe and consider your options
for reducing the issue in the future.
I have to 2nd Alvaro's comments about the cost delay. Personally, I'd recommend
setting vacuum_cost_delay to zero, unless your hardware is crap. In my recent
experience, allowing vacuum to run full-bore is less intrustive on a busy database
with good hardware than forcing it to take it's time. Unfortunately, changing it
now isn't going to speed the current vacuum up any.
Another comment: schema changes almost always need exclusive locks on tables
that they're modifying. As a result, you really need to plan them out a bit. Anything
could block a schema update, even a simple SELECT statement; so it's important to
check the health of things before starting. While it's not _generally_ a good practice
to interrupt autovacuum, it _can_ be done if the schema upgrade is necessary. Keep in
mind that it's just going to start back up again, but hopefully your schema update will
be done by then and it can do it's work without interfering with things. Another thing
you can do is to monitor the transaction ID values (the Nagios check_postgres has a nice
mode for monitoring this) and manually launch a VACUUM FREEZE ahead of autovacuum, so that
_you_ can pick the time for it to run and not have it happen to crop up at the worst possible
time ;)
You might also find that things are easier to deal with if you tweak the autovacuum
settings on this table to cause it to be vacuumed more frequently. In my experience, more
frequent vacuums that do less work each time often lead to happier databases. See ALTER
TABLE and the available settings to tweak autovacuum behavior.
--
Bill Moran <wmoran@potentialtech.com>