Search Postgresql Archives

Re: Autovacuum not functioning for large tables but it is working for few other small tables.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Absolutely check the logs, or do a manual vacuum verbose with setting cost delay and cost limit (and maintenance work mem) the same as the values for auto vacuum runs. It should work out the same and you could time it for a period when the system is more lightly used it applicable.

If you have many very large indexes on the tables with a high number of tuples and bloat, that may be slowing the execution particularly if your allowed work memory for the operation doesn't allow a single pass of the index.

If you are on PG12+, you can reindex concurrently and then run vacuum and see how it goes.

Freezing will automatically happen according to settings, but if it is near the threshold then it could be that autovacuum is doing more work scanning old data. A manual vacuum freeze would mitigate that. That may not be significant though.

For your larger tables, or system in general, turning down your scale factor settings will qualify tables for autovacuum sooner. If it hurts, you aren't doing it often enough.

Also, reducing cost delays may be needed to pause for less time in the middle of autovacuum executions. The default changed from 20ms to 2ms with PG12 but if your I/O system can handle it, lower may be prudent to get the work done more quickly.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux