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,
- su - postgres
- /opt/vmware/vpostgres/current/bin/psql
- /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
***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
Post a Comment