In Control-M/Enterprise with PostgreSQL Database, some tables can increase the size to several gigabyte

Version 1
    Share:|

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Control-M/Enterprise Manager


    COMPONENT:

    Control-M/Enterprise Manager


    APPLIES TO:

    Control-M/Enterprise Manager any version



    PROBLEM:

    Sometime, in Control-M/Enterprise Manager with Postgres Database, some tables can reach huge size.

    In particular, we observed the issue with the following tables:

                objectname             | objecttype |  #entries   |    size    
    ------------------------------------+---------------+-------------+------------
     avg_run_info                      | r                 |  1.4038e+06 | 19 GB
     runinfo_history                   | r                | 7.61634e+07 | 17 GB
     comm                                 | r                |          15         | 2591 MB
     runinfo_history_0                | i                | 7.61634e+07 | 2229 MB
     runinfo_history_1                 | i               | 7.61634e+07 | 2198 MB
     runinfo_history_2                 | i               | 7.61634e+07 | 2069 MB

    The size can be found running this query from your Control-M/Enterprise Manager server Database:

    SELECT relname AS objectname, relkind AS objecttype, reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size FROM pg_class order by relpages desc;

     


    CAUSE:

    Autovacuum is not working properly


    SOLUTION:

    To solve the issue, please add/edit the following lines in $HOME/ctm_em/pgsql/data/postgresql.conf file:

    log_autovacuum_min_duration = 0

      

    autovacuum_vacuum_cost_delay = 10

      

    autovacuum_vacuum_scale_factor = 0.0

      

    autovacuum_analyze_scale_factor = 0.0

      

    autovacuum_vacuum_cost_limit=2000

      

    autovacuum_max_workers=10

      

     

      

    For Control-M/Enterprise Manager version lower than 9.0.18.200, it is recommended to run the command "vacuum full comm" daily.
     

     


    Article Number:

    000153020


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles