2

I'm running Postgres 14.6, and have a number of sessions with a wait event described as Timeout: VacuumDelay. The SQL being run by the session is....

autovacuum: VACUUM public.mytable_202110 (to prevent wraparound)

(this information is gleaned from the dashboard on pgAdmin4)

I have a number of questions

  1. These sessions are highlighted in red, does that mean there is problem?
  2. The query started many hours ago and the state hasn't changed recently is this significant?
  3. Is this session actually telling me? is it actively VACUUMing right now? or is it waiting for the opportunity to start VACUUMing? Or is it an unrelated session saying it can't do anything until VACUUMing has finished?
  4. The table(s) being VACUUMed are very old, very large and very rarely change. Do they need to be VACUUMed?

There are no blocking PIDS listed, and all locks have been granted. The database is up and running (albeit slowly)

UPDATE - this may be relevant, this database has just been transferred from an old, slow server to a newer and better machine. I'd hoped to see significantly better performance, but I'm guessing that statistics and analysis data on the tables does NOT get transferred over with the original data, so the new database is spending a lot of resources analysing old, archived data.

1
  • You kind of tacked 'albeit slowly' on there as an after thought. Do you think the vacuums are the cause of the database running slowly? Is that why you care in the first place?
    – jjanes
    Feb 13 at 16:03

2 Answers 2

4

The table(s) being VACUUMed are very old, very large and very rarely change. Do they need to be VACUUMed?

Yes. This is not a regular vacuum, but a vacuum to prevent wraparound. From time to time such vacuum processes each table, well, to prevent wraparound.

Is this session actually telling me? is it actively VACUUMing right now? or is it waiting for the opportunity to start VACUUMing?

Both. This is an actively running vacuum that deliberately slows itself down (by the amount of autovacuum_vacuum_cost_delay time) to reduce the load on the system.

Or is it an unrelated session saying it can't do anything until VACUUMing has finished?

You can continue to work, this is a regular background process. However, "to prevent wraparound vacuum" will prevent you from changing the structure of the table due the conflicting lock.

The query started many hours ago and the state hasn't changed recently is this significant?

The system view pg_stat_progress_vacuum can give you more detailed information about vacuum progress. Large tables, especially with a large autovacuum_vacuum_cost_delay, can take several days to process.

PS: No idea why pgAdmin4 might highlight the process in red.

2

Autovacuum throttles itself, to avoid using all (or more than you want) of the IO capacity on the system. That is what you are seeing. It makes sense that if it is using throttling, it will spend most of its time on the throttling. Afterall, it would be kind of pointless to implement throttling only to have it slow itself down by 2%. Peeking at pg_stat_activity might show it waiting on the throttle most of the time, but it is a different sleep call each time, not one long call.

Maybe your throttle settings are not optimal for your set up. You could make this run faster by lowering autovacuum_vacuum_cost_delay or increasing autovacuum_vacuum_cost_limit. Unfortunately, changing those settings will not change the values used by the currently running vacuums, so you will need to cancel them so they can pick up the new settings. And you say your database is already running slowly, so making the vacuums use even more resource might be the wrong direction to go.

A "(to prevent wraparound)" vacuum does need to happen once every 2 billion transactions, even if the table is large, old, and rarely changes. If almost all pages are marked as frozen, then doing that vacuum should be very fast--but that doesn't help you the very first time these table are vacuumed in order to freeze tuples, though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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