Category: postgres

Performance Statistics in vCenter using too many resources

Statistics are important to show how your VM’s are performing. However this may sometimes become a balancing act. Too many performance statistics can degrade the performance. These stats are like a data warehouse and can effect the critical operational side of virtual center.

The stats are /storage/seat.

Here is an example of a setup which has 130GB of hist stat.

du -sh /storage/seat/vpostgres/*
1.3M /storage/seat/vpostgres/alarmtblsp
54M /storage/seat/vpostgres/eventtblsp
26G /storage/seat/vpostgres/hs1tblsp
39G /storage/seat/vpostgres/hs2tblsp
22G /storage/seat/vpostgres/hs3tblsp
11G /storage/seat/vpostgres/hs4tblsp
2.3M /storage/seat/vpostgres/tasktblsp
hs1tblsp  --  past day stats
hs2tblsp  --  past week stats
hs3tblsp  --- past month stats
hs4tblsp  --  past year stats

Double check that your statistic levels are set to default. Anything higher than which on any of these columns will cause a big increase in the amount of data collected.

If you have vRealize Operations VROPS. You maybe be able to disable this stat collection and just rely on VROPS for your statistics.

You may want to clear out some of this data. This KB 2110031 covers how to trim out some data.

I’m a DBA however and I do love to truncate. This is fast and efficient. First step is to stop all the services except postgres. Then copy and paste in the script below. All your historical stats will be removed once this script is run. 

service-control --stop --all
service-control --start vmware-vpostgres
/opt/vmware/vpostgres/current/bin/psql -U postgres VCDB

Copy and paste in the code below and press return

CREATE OR REPLACE FUNCTION cleanup_hist_stats_truncate (SaveData INTEGER)
 returns void as $$
 DECLARE r record;
 DECLARE sql_stmt varchar(2000);
 DECLARE affected_rows integer;
 DECLARE affected_rows_step1 integer;
 BEGIN
 TRUNCATE TABLE VPX_SAMPLE_TIME1;
 TRUNCATE TABLE VPX_SAMPLE_TIME2;
 TRUNCATE TABLE VPX_SAMPLE_TIME3;
 TRUNCATE TABLE VPX_SAMPLE_TIME4;
 FOR r IN SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'vpx_hist_stat%' AND table_type='BASE TABLE'     LOOP         sql_stmt = 'TRUNCATE TABLE ' || r.table_name;         EXECUTE sql_stmt;                        END LOOP;
 END
 $$language plpgsql;
 select cleanup_hist_stats_truncate(1);