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 table


Here we come to the main section. The NTP …
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 uninstall the service 
Stop the VCACGuestAgentService  Run the below command through Command Prompt as Admin   WinService…
Read more

Unable to change Provider VDC virtual hardware support beyond version 10

-
Image
My environment:

vCloud Director 9.1.0.3 and ESXi 6.5.0.

 This could happen regardless of what vCloud director and ESXi version you are on.




Provider VDC config: 


ESXi Version : 



So ideally I should be able to see the hardware version 13 in the vCloud director but I see only till version 10


Why does this happen : 

It’s most likely a user error depending on how vCenter is set up and what vCenter has reported to VCD as far as available HW versions

How to fix ? 

 From vCenter FLEX UI, go to Datacenter, right click and select 'Edit Default VM Compatibility' and there select 'ESX 6.5 and later' (This config depends on your environment)
Then go to the cluster and right click and select 'Edit Default VM Compatibility' and there the value selected would be 'Use datacenter setting and host version'. This setting would take it from datacenter setting done above.

From the vCloud director , perform and refresh of the vCenter and try to create the Provider VDC and check the…
Read more