Contributors mailing list archives
contributors@odoo-community.org
Browse archives
Re: Regular postgres VACUUM command
by
Vauxoo, Moisés López Calderón
It is a PostgreSQL tune
https://www.postgresql.org/docs/release/14.2/
- Allow vacuum to more eagerly add deleted btree pages to the free space map (Peter Geoghegan)
- Allow vacuum to reclaim space used by unused trailing heap line pointers (Matthias van de Meent, Peter Geoghegan)
- Allow vacuum to be more aggressive in removing dead rows during minimal-locking index operations (Álvaro Herrera)
- Speed up vacuuming of databases with many relations (Tatsuhito Kasahara)
- Reduce the default value of vacuum_cost_page_miss to better reflect current hardware capabilities (Peter Geoghegan)
- Add ability to skip vacuuming of TOAST tables (Nathan Bossart)
- Have COPY FREEZE appropriately update page visibility bits (Anastasia Lubennikova, Pavan Deolasee, Jeff Janes)
- Cause vacuum operations to be more aggressive if the table is near xid or multixact wraparound (Masahiko Sawada, Peter Geoghegan)
- Increase warning time and hard limit before transaction id and multi-transaction wraparound (Noah Misch)
- Add per-index information to autovacuum logging output (Masahiko Sawada)
However, PSQL <14 had issues related to vacuum process
Be sure to upgrade the PostgreSQL version >=14.6 where many issues related were already fixed
I have tested from odoo >=12.0 and it is working fine!
Check the different release notes for 14.x versions related to vacuum fixes:
- Avoid rare PANIC during updates occurring concurrently with VACUUM (Tom Lane, Jeff Davis)
- Avoid long-term memory leakage in the autovacuum launcher process (Reid Thompson)https://www.postgresql.org/docs/release/14.2/
- Fix corruption of HOT chains when a RECENTLY_DEAD tuple changes state to fully DEAD during page pruning (Andres Freund)
- Allow parallel vacuuming and concurrent index building to be ignored while computing oldest xmin (Masahiko Sawada)
- Ensure that parallel VACUUM doesn't miss any indexes (Peter Geoghegan, Masahiko Sawada)
- Allow the autovacuum launcher process to respond to pg_log_backend_memory_contexts() requests more quickly (Koyu Tanigawa)
- Numerous performance improvements have been made for parallel queries, heavily-concurrent workloads, partitioned tables, logical replication, and vacuuming.
- Allow vacuum to skip index vacuuming when the number of removable index entries is insignificant (Masahiko Sawada, Peter Geoghegan)- Allow vacuum to more eagerly add deleted btree pages to the free space map (Peter Geoghegan)
- Allow vacuum to reclaim space used by unused trailing heap line pointers (Matthias van de Meent, Peter Geoghegan)
- Allow vacuum to be more aggressive in removing dead rows during minimal-locking index operations (Álvaro Herrera)
- Speed up vacuuming of databases with many relations (Tatsuhito Kasahara)
- Reduce the default value of vacuum_cost_page_miss to better reflect current hardware capabilities (Peter Geoghegan)
- Add ability to skip vacuuming of TOAST tables (Nathan Bossart)
- Have COPY FREEZE appropriately update page visibility bits (Anastasia Lubennikova, Pavan Deolasee, Jeff Janes)
- Cause vacuum operations to be more aggressive if the table is near xid or multixact wraparound (Masahiko Sawada, Peter Geoghegan)
- Increase warning time and hard limit before transaction id and multi-transaction wraparound (Noah Misch)
- Add per-index information to autovacuum logging output (Masahiko Sawada)
Even if you are not able to upgrade postgresql version you can run a cron to run a "vacuum full"
WARNING: Consider it could get down the whole production instance during this process
El mar, 25 jul 2023 a las 15:02, Graeme Gellatly (<notifications@odoo-community.org>) escribió:
Hi,In general, auotvaccum is a postgres setting which does that. Usually, I thought it was enabled by default.In terms of index choice and counts, that is also a postgres setting, default_statistics_target which is probably set too low.Of course there are myriad other postgres settings which affect what the planner does.There are some OCA/FOSS modules I have seen over the years for logging slow queries, creating indexes etc, I've never used, but if you really want to run specific commands in Odoo, then it is simple enough with a server action as you can just do env.cr.execute.On Wed, Jul 26, 2023 at 6:52 AM tblauwendraat <notifications@odoo-community.org> wrote:Hello,
I'm faced with an Odoo database that is quite active and has some very big tables. I also notice that sometimes, indexes go unused and the query plans go haywire (30 secs where it could be 70ms if using a certain index)
I've learned that this could be because Postgres misestimates the size of the tables, and i should run ANALYZE on the table. VACUUM ANALYZE and other VACUUM commands can remedy this, when run regularly.
I would have expected some kind of OCA module that has scheduled actions for this, but I havent found any.
How would you / have you handle(d) this situation? Would a new OCA module be of use?
_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe
_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe
Moisés López Calderón
Mobile: (+521) 477-752-22-30
Twitter: @moylop260
Twitter: @vauxoo
