Michael, Your complete explanation is very helpful! I appreciate it Thank you so much!
Regards! El jue., 30 abr. 2020 a las 10:52, Michael Lewis (<mle...@entrata.com>) escribió: > In staging environment we have disabled autovacuum since that it is a >> testing environment and the database are restored very often. >> But in production environment it is enabled autovacuum=on >> >> The weird case is that production was slow and staging environment was >> faster. >> > > You haven't specified how you are doing backup and restore, but unless it > is a byte-for-byte file copy method, then there would be no bloat on the > restored staging environment so no need to vacuum. You would want to ensure > you take a new statistics sample with analyze database after restore if you > aren't. > > In your production system, if your configs for autovacuum settings have > not been changed from the default parameters, it probably is not keeping up > at all if the system is moderately high in terms of update/delete > transactions. You can check pg_stat_activity for active vacuums, change the > parameter to log autovacuums longer than X to 0 and review the logs, or > check pg_stat_user_tables to see how many autovacuums/analyze have been > done since you last reset those stats. > > If you have tables that are in the millions or hundreds or millions of > rows, then I would recommend decreasing autovacuum_vacuum_scale_factor from > 20% down to 1% or perhaps less and similar > for autovacuum_analyze_scale_factor. You can do this on individual tables > if you have mostly small tables and just a few large ones. Else, increase > the threshold settings as well. The default value > for autovacuum_vacuum_cost_delay changed from 20ms to 2ms in PG12 so that > may also be prudent to do likewise if you upgraded to PG12 and kept your > old settings, assuming your I/O system can handle it. > > Otherwise, if you have a period of time when the activity is low for your > database(s), then a last resort can be a daily scheduled vacuum analyze on > all tables. Note- do not do vacuum FULL which requires an exclusive lock on > the table to re-write it entirely. You are just looking to mark space > re-usable for future transactions, not recover the disk space back to the > OS to be consumed again if autovacuum still can't keep up. pg_repack > extension would be an option if you need to recover disk space while online. > -- Moisés López Calderón Mobile: (+521) 477-752-22-30 Twitter: @moylop260 hangout: moylop...@vauxoo.com http://www.vauxoo.com - Odoo Gold Partner Twitter: @vauxoo
