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

Post a Comment

Popular posts from this blog

vCloud director 9.7 appliance deployment step by step guide

-
Image
With the new vCloud director releasing and making it full appliance with embedded vpostgres, I though I will put up a post with the complete installation guide. There have been some challenges in the getting the deployment successful. Prerequisite : Reverse DNS proxy must be configured for the eth0 ipaddress. This is to get the correct hostname for the appliance.  The NFS share is setup for the VCD transfer directory. It is recommended to have the eth0 and eth1 in different subnets. eth0 is for the vcd cell and eth1 is for the vPostgres .The reason for the having 2 subnets is to avoid duplicate entries in the route table.  Installing vCloud director step by step guide :  I should ideally be using different submets as per the VMware documentation but since I do not have a different portgroup setup, I am going with the same subnet.  As mentioned in the prereq section, the reason for the having 2 subnets is to avoid duplicate entries in the route tab
Read more

How to Install and Uninstall Guest agent for vRA Windows machines

-
Image
Connect to the vRA appliance using the IP or hostname  Choose the appropriate setup for your Windows server  Extract the files to C:\VRMGuestAgent\ . In my case, I have already extracted it.  Before you install the guest agent download and install the IAAS manager server certificate into the windows machine  Save the file as Cert.cer  Install the Cert into the Local machine and Trusted root store Rename the cert.cer to cert.pem and move it to folder C:\VRMGuestAgent\ Now, copy the certificate file into the vRMGuestAgent Extracted folder  Open the command prompt as admin and navigate to C:\VRMGuestAgent. In this case 192.168.2.15 is my Manager server . The default port is 443.  You will see the Guest agent service in the services list. Manually start the service.  Your windows guest agent is installed :)  How to Uninstall Guest agent on windows machine  To uninsta
Read more

VM console fails to connect - vCloud director 9.7

-
Image
I deployed a new vCloud director 9.7 and the VM console fails to connect even though the certificates and URLs are updated correctly in Public Addresses. The chrome browser console (ctrl+shift+j) fails like below :  failed: Error during WebSocket handshake: Unexpected response code: 404 After reading the VMware documentation which says " For an installation with a single IP address, you can customize the console proxy address from the  vCloud Director  Web Console. For example, for the  vCloud Director  appliance, you must customize the console proxy address to  vcloud.example.com :8443 " From vCloud director 9.7, the second network (eth1) is dedicated to vpostgres database and console proxy uses (eth0) with a custom port 8443 Fix :  Update the console proxy address with custom port 8443 under vCD portal --> Administrator --> Public addresses Tadaa!  Thanks for reading. Hope it helps :) 
Read more