PostgreSQL bloat (full) on vCloud director 9.7.x




Since the last few weeks, I have noticed couple of vCloud director 9.7 facing disk usage full issue on the appliance. It is mostly due to the PostgreSQL using up most of the space.

Running the below command on the appliance console gives you the output of disk usage per partition

df -h

Output : 

root@vcd [ ~ ]# df -h
Filesystem                                                 Size  Used Avail Use% Mounted on
devtmpfs                                                    5.9G     0  5.9G   0% /dev
tmpfs                                                          5.9G   28K  5.9G   1% /dev/shm
tmpfs                                                          5.9G  748K  5.9G   1% /run
tmpfs                                                          5.9G     0  5.9G   0% /sys/fs/cgroup
/dev/sda3                                                    28G  2.6G   24G  10% /
tmpfs                                                          5.9G   36K  5.9G   1% /tmp
/dev/sda2                                                    380M   22M  338M   7% /boot
/dev/mapper/database_vg-vpostgres          79G   77G   2G  99% /var/vmware/vpostgres
sql.wecloud.lab:/VCD_transfer                  10G   43M   10G   1% /opt/vmware/vcloud-director/data/transfer
tmpfs                                                           1.2G     0  1.2G   0% /run/user/0


There is already a blog for a similar issue by Tomas Fojta here where the table 'activity_parameters' is huge in size.

If you are VCD 9.7.0.2 or later , I have noticed we no longer have 'activity_parameters' tables as mentioned in the blog by Tomas Fojta. So what's eating up space now?

Follow the below procedure to find out :


  • Connect to vCloud director postgreSQL host (embedded or external)
  • If embedded, 
    1.  su - postgres 
    2. /opt/vmware/vpostgres/current/bin/psql
    3. /c vcloud 
  • Now run the below query to list the first 30 tables ordered by the space used
vcloud=# SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog','information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 30;

In my case, it was the 'activity_partition_x' tables were using a lot of space


           relation           | total_size
------------------------------+------------
 public.activity_partition_6  | 6182 MB
 public.activity_partition_9  | 5859 MB
 public.activity_partition_4  | 4315 MB
 public.activity_partition_0  | 4231 MB
 public.activity_partition_22 | 4075 MB
 public.activity_partition_26 | 4049 MB
 public.activity_partition_25 | 2368 MB
 public.activity_partition_12 | 2235 MB
 public.activity_partition_31 | 2102 MB
 public.activity_partition_10 | 2099 MB
 public.activity_partition_16 | 2094 MB
 public.activity_partition_5  | 2077 MB
 public.activity_partition_27 | 2073 MB
 public.activity_partition_30 | 2072 MB
 public.activity_partition_28 | 2061 MB
 public.activity_partition_3  | 2055 MB
 public.activity_partition_23 | 2048 MB
 public.activity_partition_14 | 1965 MB
 public.activity_partition_24 | 1901 MB


Fix : 

vacuum full activity; 

Note : Kindly take a snapshot of the host before vacuuming the tables. 


***UPDATE***

Run the below statement to check the auto-vacuum settings. If the settings returns 'Off' it means auto-vacuum will never be able to execute. 

vcloud=# SELECT setting FROM pg_settings WHERE name='track_counts'; 

setting
---------
off


- It is a known issue with the product where the track_counts is getting disabled upon firstboot . So every time the appliance is rebooted or started the track_counts is set to Off which turns off the Auto-vacuum. 

- Restarting the vPostgres should fix the problem. 

       systemctl restart vpostgres







Comments

Popular posts from this blog

vCloud director 9.7 appliance deployment step by step guide

How to Install and Uninstall Guest agent for vRA Windows machines

Unable to change Provider VDC virtual hardware support beyond version 10