1

I have a 14.7 TB Postgres(9.1) database that has to endure heavy writes for prolonged periods of time. I know that I need to closely manage my transaction ids to prevent db lockup. Recently I noticed the queries slowing, and saw that there were multiple autovacuums running on our massive read-only tables, with the "(to prevent wraparound)" subscript. I stopped the running software and executed a vacuumdb -F -a command. However, when I run select current_query from pg_stat_activity I see that the autovacuum processes are still running, even during a manual vacuum. I tried killing the autovacuums with select pg_cancel_backend(pid) and they died but then immediately restart again. My questions:

  1. Is autovacuum supposed to continue to run during a manual db vacuum?
  2. How do I effectively get these autovacuum processes to stop?
  3. Why would these autovacuums continue to run on a read-only table? What is there to vacuum?
  • 2
    Unrelated to your problem, but: Postgres 9.1 is no longer supported you should plan an upgrade as soon as possible. – a_horse_with_no_name May 26 at 21:45
  • 1
    Yeah absolutely. Every day I tell our DBA this, but... – lolynns May 26 at 23:32
1

Autovacuum is triggered by the table statistics on the table, and as long as your manual VACUUM (FREEZE) is not done, these are not updated. That is why anti-wraparound autovacuum processes will still start.

But that's not a big problem: Only one VACUUM can run on a table at any given time. Now anti-wraparound autovacuum workers won't give up when they block another process, in that case your manual VACUUM. But if you kill the anti-wraparound autovacuum workers, your manual VACUUM gets the lock, and now it is the restarted anti-wraparound autovacuum worker that is blocked. Look at pg_locks to verify that they are waiting for a lock (granted = FALSE).

Now your vacuumdb processes one table after the other, so you must be ready to kill autovacuum workers as soon as it starts processing the next table and is blocked by a new autovacuum worker there.

It might be easier to manually launch VACUUM on those big read-only tables rather than using vacuumdb, because then you can control when which table gets vacuumed.

Make sure you set maintenance_work_mem high to speed up VACUUM. You should also set autovacuum_vacuum_cost_delay to 2ms or lower at least on these big tables, so that future autovacuum runs get done faster.

To reduce the pain for the future, lower autovacuum_freeze_max_age on the affected tables substantially. Then the next anti-wraparound vacuum will kick in sooner and will be done faster.

Best of all, upgrade to v13 as soon as you can, because from that version on insert-only tables will receive regular vacuum runs as well, which should do away with the problem.

| improve this answer | |
  • thanks so much for the very thorough response! It worked to do my manual vacuum command, then kill the autovacuum process and give the lock to the manual command. For preventing this issue moving forward: once I successfully execute the vacuum freeze on these giant tables, would there ever be a need to vacuum them again? Since they are static? Would there be harm, in say, setting their autovacuum_enabled = false? – lolynns May 27 at 14:56
  • (and yes, I would dearly love to upgrade to v13. Unfortunately we'll have to completely rebuild our dbs to do it and this is only one of many 15TB+ dbs in our cluster, and we're a small shop barely getting data updates out as fast as our customers want them. It's a problem. Hopefully after we get this next release out I'll be able to talk my boss into taking the update plunge.) – lolynns May 27 at 14:58
  • You don't need to rebuild for an upgrade. pg_upgrade can be finished in seconds. With an old release like 9.1,, the next anti-wraparound vacuum might cause the same problem. From 9.6 on, such autovacuums would be finished in a jiffy without actually doing anything. Upgrading pays. – Laurenz Albe May 27 at 15:27
  • from the link that @a_horse_with_no_name posted above : "Major versions usually change the internal format of system tables and data files. These changes are often complex, so we do not maintain backward compatibility of all stored data. A dump/reload of the database or use of the pg_upgrade module is required for major upgrades." When we explored upgrading earlier this year, we were unable to upgrade the dbs in place. Even pg_upgrade would cause a rebuild of the db from what we understood. – lolynns May 27 at 16:33
  • No, it wouldn't. Check again. – Laurenz Albe May 27 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.