On Wed, Feb 6, 2019 at 9:42 AM Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> wrote:
Well, basically I'm trying to tune it because the table still keep growing. I thought that by setting the scale and the threshold it will be enough but its seems that it wasnt. I attached some of the logs output to hear what you guys think about it ..
Are all four log entries from well after you made the change? My first inclination is to think that the first 2 are from either before the change, or just after the change when it is still settling into the new regime. Also, is the table still continuing to grow, or is at a new steady-state of bloat which isn't growing but also isn't shrinking back to where you want it to be? More aggressive vacuuming alone should stop the bloat, but is not likely to reverse it.
I habitually set vacuum_cost_page_hit and vacuum_cost_page_miss to zero. Page reads are self-limiting (vacuum is single threaded, so you can't have more than one read (times autovacuum_max_workers) going on at a time) so I don't see a need to throttle them intentionally as well--unless your entire db is sitting on one spindle. Based on the high ratio of read rates to write rates in the last two log entries, this change alone should be enough greatly speed up the run time of the vacuum.
If you need to speed it up beyond that, I don't think it matters much whether you decrease cost_delay or increase cost_limit, it is the ratio that mostly matters.
And if these latter measures do work, you should consider undoing changes to autovacuum_vacuum_scale_factor. Reading the entire index just to remove 10,000 rows from the table is a lot of extra work that might be unnecessary. Although that extra work might not be on anyone's critical path.
