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
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;
Thank! it worked for me and helped debloating the vRA postgres DB.