Debloat DB (PostgreSQL) for VMware appliances

Posted by

As vSphere admin you might be managing a few VMware appliances running an instance of PostgreSQL like SRM, vCenter etc

In most of these appliances PostgreSQL runs with the default setting for Auto-vacuum.  In some setups the default setting might not be aggressive  enough to keep the DB lean. If you have a fat DB,  you might want to check the bloating levels

The procedure requires the application services to be down.

Step1: Connect to the application DB

  • Connect to the appliance using SSH as root
  • Connect to the PostgreSQL instance
/opt/vmware/vpostgres/current/bin/psql -U postgres
  • The above will connect you to the default DB instance which in a few cases might not be the application DB
  • List the database present on the system using the meta command \l
VCDB=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 VCDB      | vc       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/vc               +
           |          |          |             |             | vc=CTc/vc
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
  • Connect to the application DB using meta command \c
 postgres=# \c VCDB

Step2: Analyze for bloating

  • Save following SQL script to a file
/* WARNING: executed with a non-superuser role, the query inspect only tables you are granted to read.
* This query is compatible with PostgreSQL 9.0 and more
*/
create temp table bloat as
SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
  (tblpages-est_tblpages)*bs AS extra_size,
  CASE WHEN tblpages - est_tblpages > 0
    THEN 100 * (tblpages - est_tblpages)/tblpages::float
    ELSE 0
  END AS extra_ratio, fillfactor,
  CASE WHEN tblpages - est_tblpages_ff > 0
    THEN (tblpages-est_tblpages_ff)*bs
    ELSE 0
  END AS bloat_size,
  CASE WHEN tblpages - est_tblpages_ff > 0
    THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
    ELSE 0
  END AS bloat_ratio, is_na
  -- , tpl_hdr_size, tpl_data_size, (pst).free_percent + (pst).dead_tuple_percent AS real_frag -- (DEBUG INFO)
FROM (
  SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
    ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
    tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
    -- , tpl_hdr_size, tpl_data_size, pgstattuple(tblid) AS pst -- (DEBUG INFO)
  FROM (
    SELECT
      ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
        - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
        - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
      ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
      toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
      -- , tpl_hdr_size, tpl_data_size
    FROM (
      SELECT
        tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
        tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
        coalesce(toast.reltuples, 0) AS toasttuples,
        coalesce(substring(
          array_to_string(tbl.reloptions, ' ')
          FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
        current_setting('block_size')::numeric AS bs,
        CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
        24 AS page_hdr,
        23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
           + CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
        sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
        bool_or(att.atttypid = 'pg_catalog.name'::regtype)
          OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
      FROM pg_attribute AS att
        JOIN pg_class AS tbl ON att.attrelid = tbl.oid
        JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
        LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
          AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
        LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
      WHERE NOT att.attisdropped
        AND tbl.relkind = 'r'
      GROUP BY 1,2,3,4,5,6,7,8,9,10
      ORDER BY 2,3
    ) AS s
  ) AS s2
) AS s3
-- WHERE NOT is_na
--   AND tblpages*((pst).free_percent + (pst).dead_tuple_percent)::float4/100 >= 1
ORDER BY schemaname, tblname;

  • Run the above script to prepare a temporary table named “bloat” with the bloating information
  • You will use the meta command \i
VCDB=# \i /tmp/bloat_analysis.sql
SELECT 1091
  • Run following statements to select all the vCenter server database tables with more than 20% bloating
VCDB=# select * from bloat where schemaname ='vc' and bloat_ratio >='20';
 current_database | schemaname |          tblname           | real_size | extra_size |   extra_ratio    | fillfactor | bloat_size |   bloat_ratio    | is_na
------------------+------------+----------------------------+-----------+------------+------------------+------------+------------+------------------+-------
 VCDB             | vc         | vpx_alarm                  |    122880 |      24576 |               20 |        100 |      24576 |               20 | f
 VCDB             | vc         | vpx_alarm_action           |     40960 |       8192 |               20 |        100 |       8192 |               20 | f
 VCDB             | vc         | vpx_alarm_runtime          |     49152 |      16384 | 33.3333333333333 |        100 |      16384 | 33.3333333333333 | f
 VCDB             | vc         | vpx_event_38               |     81920 |      16384 |               20 |        100 |      16384 |               20 | f
 VCDB             | vc         | vpx_event_49               |     81920 |      16384 |               20 |        100 |      16384 |               20 | f
 VCDB             | vc         | vpx_event_53               |     81920 |      16384 |               20 |        100 |      16384 |               20 | f
 VCDB             | vc         | vpx_event_59               |     81920 |      16384 |               20 |        100 |      16384 |               20 | f
 VCDB             | vc         | vpx_event_63               |    147456 |      49152 | 33.3333333333333 |        100 |      49152 | 33.3333333333333 | f
 VCDB             | vc         | vpx_event_arg_38           |    286720 |      57344 |               20 |        100 |      57344 |               20 | f
 VCDB             | vc         | vpx_event_arg_45           |    286720 |      57344 |               20 |        100 |      57344 |               20 | f
 VCDB             | vc         | vpx_event_arg_50           |    286720 |      57344 |               20 |        100 |      57344 |               20 | f
 VCDB             | vc         | vpx_event_arg_64           |     98304 |      24576 |               25 |        100 |      24576 |               25 | f
 VCDB             | vc         | vpx_host                   |     32768 |      24576 |               75 |        100 |      24576 |               75 | f
 VCDB             | vc         | vpx_journal_entry          |      8192 |       8192 |              100 |        100 |       8192 |              100 | f
 VCDB             | vc         | vpx_nic                    |     40960 |      16384 |               40 |        100 |      16384 |               40 | f
 VCDB             | vc         | vpx_sample_time1           |     32768 |       8192 |               25 |        100 |       8192 |               25 | f
 VCDB             | vc         | vpx_sample_time2           |     32768 |       8192 |               25 |        100 |       8192 |               25 | f
 VCDB             | vc         | vpx_sdrs_stats_vm          |    237568 |      90112 | 37.9310344827586 |        100 |      90112 | 37.9310344827586 | f
 VCDB             | vc         | vpx_topn_past_day          |   1122304 |     303104 |  27.007299270073 |        100 |     303104 |  27.007299270073 | f
 VCDB             | vc         | vpx_vdevice_backing_rel    |     40960 |      16384 |               40 |        100 |      16384 |               40 | f
 VCDB             | vc         | vpx_vdevice_file_backing   |     40960 |       8192 |               20 |        100 |       8192 |               20 | f
 VCDB             | vc         | vpx_vdevice_file_backing_x |     16384 |       8192 |               50 |        100 |       8192 |               50 | f
 VCDB             | vc         | vpx_vm_config_info         |     49152 |      16384 | 33.3333333333333 |        100 |      16384 | 33.3333333333333 | f
 VCDB             | vc         | vpx_vm_virtual_device      |    475136 |     131072 | 27.5862068965517 |        100 |     131072 | 27.5862068965517 | f

Step3: Debloating

  • Run following statements and meta commands to prepare a debloating script
  • The debloating script will be at /tmp/fix_bloat.sql
VCDB=# \t
Tuples only is on.
VCDB=#  \o /tmp/fix_bloat.sql
VCDB=# select 'vacuum full analyze verbose '|| tblname ||';'from bloat where schemaname = 'vc' and bloat_ratio >='20' ;
VCDB=# \o
VCDB=# \t
Tuples only is off.

  • Review the debloating script using meta command \!
VCDB=# \! cat /tmp/fix_bloat.sql
 vacuum full analyze verbose vpx_alarm;
 vacuum full analyze verbose vpx_alarm_action;
 vacuum full analyze verbose vpx_alarm_runtime;
 vacuum full analyze verbose vpx_event_38;
 vacuum full analyze verbose vpx_event_49;
 vacuum full analyze verbose vpx_event_53;
 vacuum full analyze verbose vpx_event_59;
 vacuum full analyze verbose vpx_event_63;
 vacuum full analyze verbose vpx_event_arg_38;
 vacuum full analyze verbose vpx_event_arg_45;
 vacuum full analyze verbose vpx_event_arg_50;
 vacuum full analyze verbose vpx_event_arg_64;
 vacuum full analyze verbose vpx_host;
 vacuum full analyze verbose vpx_journal_entry;
 vacuum full analyze verbose vpx_nic;
 vacuum full analyze verbose vpx_sample_time1;
 vacuum full analyze verbose vpx_sample_time2;
 vacuum full analyze verbose vpx_sdrs_stats_vm;
 vacuum full analyze verbose vpx_topn_past_day;
 vacuum full analyze verbose vpx_vdevice_backing_rel;
 vacuum full analyze verbose vpx_vdevice_file_backing;
 vacuum full analyze verbose vpx_vdevice_file_backing_x;
 vacuum full analyze verbose vpx_vm_config_info;
 vacuum full analyze verbose vpx_vm_virtual_device;

  • The moment of truth execute the debloat script using meta command \i
VCDB=# \i /tmp/fix_bloat.sql
psql.bin:/tmp/fix_bloat.sql:1: INFO:  vacuuming "vc.vpx_alarm"
psql.bin:/tmp/fix_bloat.sql:1: INFO:  "vpx_alarm": found 3 removable, 214 nonremovable row versions in 15 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
psql.bin:/tmp/fix_bloat.sql:1: INFO:  analyzing "vc.vpx_alarm"
psql.bin:/tmp/fix_bloat.sql:1: INFO:  "vpx_alarm": scanned 13 of 13 pages, containing 214 live rows and 0 dead rows; 214 rows in sample, 214 estimated total rows
VACUUM
psql.bin:/tmp/fix_bloat.sql:2: INFO:  vacuuming "vc.vpx_alarm_action"
psql.bin:/tmp/fix_bloat.sql:2: INFO:  "vpx_alarm_action": found 2 removable, 142 nonremovable row versions in 5 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
psql.bin:/tmp/fix_bloat.sql:2: INFO:  analyzing "vc.vpx_alarm_action"
psql.bin:/tmp/fix_bloat.sql:2: INFO:  "vpx_alarm_action": scanned 4 of 4 pages, containing 142 live rows and 0 dead rows; 142 rows in sample, 142 estimated total rows
VACUUM
psql.bin:/tmp/fix_bloat.sql:3: INFO:  vacuuming "vc.vpx_alarm_runtime"
psql.bin:/tmp/fix_bloat.sql:3: INFO:  "vpx_alarm_runtime": found 24 removable, 143 nonremovable row versions in 6 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
psql.bin:/tmp/fix_bloat.sql:3: INFO:  analyzing "vc.vpx_alarm_runtime"
psql.bin:/tmp/fix_bloat.sql:3: INFO:  "vpx_alarm_runtime": scanned 4 of 4 pages, containing 143 live rows and 0 dead rows; 143 rows in sample, 143 estimated total rows
VACUUM

2 comments

  1. This blog is application for vRA postgress database as well with below changes in the query
    1.
    /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB
    replace with
    /opt/vmware/vpostgres/current/bin/psql -U postgres -d vcac
    2.
    SELECT schema_name, relname, pg_size_pretty(table_size) AS size, table_size FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname, pg_relation_size(pg_catalog.pg_class.oid) AS table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid) t WHERE schema_name NOT LIKE ‘pg_%’ and schema_name like ‘vc’ ORDER BY table_size DESC limit 10;
    Replace with,
    SELECT schema_name, relname, pg_size_pretty(table_size) AS size, table_size FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname, pg_relation_size(pg_catalog.pg_class.oid) AS table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid) t WHERE schema_name NOT LIKE ‘pg_%’ ORDER BY table_size DESC limit 10;

Comments are closed.