On Mon, 19 Jun 2017 17:33:23 +0000 Dmitry O Litvintsev <litvi...@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 <wmo...@potentialtech.com> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
