If I see something like:

db=> SELECT datname,pid,state, (now() - query_start)::interval, query FROM pg_stat_activity where state != 'idle' and query like 'autovacuum%';
 datname |  pid  | state  |    interval     |                        query                        
 theseus | 21936 | active | 00:00:00.940774 | autovacuum: VACUUM ANALYZE pg_catalog.pg_constraint
 theseus | 16775 | active | 00:33:57.896069 | autovacuum: VACUUM pg_toast.pg_toast_1234

How should I interpret xact_start vis a vis the vacuum_cost_limit and autovacuum_vacuum_cost_delay? In other words is xact_start the time at which the thread woke up again to do another chunk of vacuum_cost_limit work, or does it represent many chunks?

And a follow-up: my vacuum_cost_limit setting is the default of 200 (very conservative); if we're seeing a very long "transaction" interval and that interval should be measuring a very small amount of work, what could explain the long runtime? It's my understanding that autovacuum doesn't block on other activity.

  • you're mixing query_start in the example ans xact_start in the text, but for autovacuum I would expect the same value for both. – Jasen May 19 '18 at 3:04

The xact_start field should be before or equal to query_start, covering the entire duration of the vacuuming of the table.

Your vacuum is slow because your setting of vacuum_cost_limit is very conservative. It is throttling itself, not blocking on other things.

  • I don't doubt it (it seems most likely to me too), but do you have a source or can you explain how you came to that conclusion? – jberryman May 17 '18 at 14:37
  • There are cases where autovac will block trying to get a clean-up pin on a buffer, but it is very rare for this to be frequent enough or long enough to explain problems. The default conservatism of vacuum throttling settings is not rare, so it is a good bet. I don't know of a good way to get definitive evidence, though, other than running strace on the vacuum worker and seeing what it is doing. – jjanes May 20 '18 at 21:27

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.