Error: "Total available resources could not be determined for reservation" due to database query timeout

Products

• VMware Cloud Director

Issue/Introduction

• When performing VM disk reconfigures, VM deployments, or Storage Policy changes in VMware Cloud Director (VCD), the operation fails with a timeout.
• Changing a VM Storage Policy or expanding a disk fails with error:
  
  Total available resources could not be determined for reservation
  
  
• The /opt/vmware/vcloud-director/logs/vcloud-container-debug.log shows and error of the form:
  
  com.vmware.vcloud.common.reservation.ReservationSystemException
Caused by java.util.concurrent.TimeoutException
  
  
• Database queries for the dgp_view_vdc_sprofile_metrics view take significantly longer than expected (e.g., >10 seconds).

Environment

VMware Cloud Director 10.6.x

Cause

An unoptimized query causes the query planner to select an inefficient plan when calculating the available capacity in the Storage Policy. This leads to query execution times that exceed the default 10 second timeout.

Resolution

This issue is currently under investigation by engineering for fix in a future VMware Cloud Director release.

To confirm if the issue is present, run the following steps:

1. SSH to the Primary Cell as root.
2. Using the name of an example Organization VDC and Storage Policy retrieve their IDs as vdc_id and lr_id respectively:
   
   sudo -i -u postgres psql vcloud -c "SELECT org_prov_vdc.name as vdc_name, org_prov_vdc.id as vdc_id, vdc_logical_resource.name as lr_name, vdc_logical_resource.id as lr_id FROM org_prov_vdc INNER JOIN vdc_logical_resource ON vdc_logical_resource.vdc_id = org_prov_vdc.id WHERE org_prov_vdc.name = '<OrgVdcName>' AND vdc_logical_resource.name = '<StoragePolicyName>';"
   
   
3. Take the vdc_id and lr_id values from the output and use them in the following command to test the timing of the affected view:
   
   sudo -i -u postgres psql vcloud -c "\timing on" -c "SELECT actual_storage_used_mb FROM dgp_view_vdc_sprofile_metrics WHERE vdc_id = '<vdc_id from step 2 above>' AND lr_id = '<lr_id from step 2 above>';" -c "\timing off"
   
   
4. If the Time value returned in the command output exceeds the default 10s (10000ms) then the issue will occur.

Workaround

Increase the reservation timeout as documented in Error: "Total available resources could not be determined for reservation" when changing Storage Policies in Cloud Director.
Note: This may not resolve issues where the query takes longer than the new timeout limit.

If the workaround does not resolve the issue in your environment, please Creating and managing Broadcom cases and reference this article (449797).

Additional Information

For updates on a permanent fix, please subscribe to this article by following the steps in Subscribe to a Broadcom knowledge article by article or product.