Skip to Content
  2. Mailing Lists
  3. Contributors
  4. Regular postgres VACUUM command

Archives

Contributors

Calendar widget: restrict choice to certain dates

OCA Days 2023 - can you help?

Regular postgres VACUUM command

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?
by Tom Blauwendraat - 08:51 - 25 Jul 2023

Follow-Ups

  • Re: Regular postgres VACUUM command
    > can anyone recommend a visual Postgres monitoring tool

    I use pgbadger too but PGHero is my favorite tool to see real time stats


    El vie, 28 jul 2023 a las 3:22, Tom Blauwendraat (<notifications@odoo-community.org>) escribió:
    Thanks Moises and Graeme for the detailed information!

I'm going to look at tweaking autovacuum and statistics settings for the 
busy tables. Also, whenever replacing a big chunk of data, I'll run 
ANALYSE <table> afterwards.

As for most situations the default Postgres settings are fine, and I 
can't think of any "generic" solutions other than monitoring and doing 
individual tweaks, I won't create an OCA module for it.

Parting question: can anyone recommend a visual Postgres monitoring tool 
that could ideally also work on Amazon RDS? I'm familiar with log 
parsing tools such as pgBadger, but maybe there's a tool out there 
somewhere that can give information just by connecting to the instance 
and running smart queries?

-Tom

    _______________________________________________
    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
    hangout: moylop260@vauxoo.com
    http://www.vauxoo.com - Odoo Gold Partner
    Twitter: @vauxoo
    by Moisés López Calderón - 12:26 - 2 Aug 2023
  • Re: Regular postgres VACUUM command 
    Thanks Moises and Graeme for the detailed information!

I'm going to look at tweaking autovacuum and statistics settings for the 
busy tables. Also, whenever replacing a big chunk of data, I'll run 
ANALYSE <table> afterwards.

As for most situations the default Postgres settings are fine, and I 
can't think of any "generic" solutions other than monitoring and doing 
individual tweaks, I won't create an OCA module for it.

Parting question: can anyone recommend a visual Postgres monitoring tool 
that could ideally also work on Amazon RDS? I'm familiar with log 
parsing tools such as pgBadger, but maybe there's a tool out there 
somewhere that can give information just by connecting to the instance 
and running smart queries?

-Tom
    by Tom Blauwendraat - 11:20 - 28 Jul 2023
  • Re: Regular postgres VACUUM command
    It is a PostgreSQL tune

    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
    hangout: moylop260@vauxoo.com
    http://www.vauxoo.com - Odoo Gold Partner
    Twitter: @vauxoo
    by Moisés López Calderón - 12:46 - 26 Jul 2023
  • Re: Regular postgres VACUUM command
    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

    by Graeme Gellatly - 11:01 - 25 Jul 2023