'ERROR: Canceling statement due to statement timeout' reported while updating Organization VDC metrics."

Products

• VMware Cloud Director

Issue/Introduction

• Organization VDC metrics are not getting updated on Data Centers tab in tenant UI.
• The following error messages can be seen in /opt/vmware/vcloud-director/logs/vcloud-container-debug.log.

ERROR    | Backend-activity-pool-7622 | JDBCExceptionReporter          | ERROR: canceling statement due to statement timeout
  Where: SQL statement "UPDATE cached_dgp_vdc_metrics
SET
cpu_used_mhz = dgp_vdc_metrics_view.cpu_used_mhz,
cpu_res_used_mhz = dgp_vdc_metrics_view.cpu_res_used_mhz,
mem_used_mb = dgp_vdc_metrics_view.mem_used_mb,
mem_res_used_mb = dgp_vdc_metrics_view.mem_res_used_mb,
storage_used_mb = dgp_vdc_metrics_view.storage_used_mb,
number_media = dgp_vdc_metrics_view.number_media,
number_disks = dgp_vdc_metrics_view.number_disks,
cpu_overhead_mhz = dgp_vdc_metrics_view.cpu_overhead_mhz,
mem_overhead_mb = dgp_vdc_metrics_view.mem_overhead_mb,
storage_overhead_mb = dgp_vdc_metrics_view.storage_overhead_mb,
number_datastores = dgp_vdc_metrics_view.number_datastores,
available_networks = dgp_vdc_metrics_view.available_networks,
used_networks_in_vdc = dgp_vdc_metrics_view.used_networks_in_vdc
FROM dgp_vdc_metrics_view WHERE dgp_vdc_metrics_view.org_vdc_id = cached_dgp_vdc_metrics.org_vdc_id
AND
( cached_dgp_vdc_metrics.cpu_used_mhz != dgp_vdc_metrics_view.cpu_used_mhz OR
cached_dgp_vdc_metrics.cpu_res_used_mhz != dgp_vdc_metrics_view.cpu_res_used_mhz OR
cached_dgp_vdc_metrics.mem_used_mb != dgp_vdc_metrics_view.mem_used_mb OR
cached_dgp_vdc_metrics.mem_res_used_mb != dgp_vdc_metrics_view.mem_res_used_mb OR
cached_dgp_vdc_metrics.storage_used_mb != dgp_vdc_metrics_view.storage_used_mb OR
cached_dgp_vdc_metrics.number_media != dgp_vdc_metrics_view.number_media OR
cached_dgp_vdc_metrics.number_disks != dgp_vdc_metrics_view.number_disks OR
cached_dgp_vdc_metrics.cpu_overhead_mhz != dgp_vdc_metrics_view.cpu_overhead_mhz OR
cached_dgp_vdc_metrics.mem_overhead_mb != dgp_vdc_metrics_view.mem_overhead_mb OR
cached_dgp_vdc_metrics.storage_overhead_mb != dgp_vdc_metrics_view.storage_overhead_mb OR
cached_dgp_vdc_metrics.number_datastores != dgp_vdc_metrics_view.number_datastores OR
cached_dgp_vdc_metrics.available_networks != dgp_vdc_metrics_view.available_networks OR
cached_dgp_vdc_metrics.used_networks_in_vdc != dgp_vdc_metrics_view.used_networks_in_vdc )"
PL/pgSQL function populate_cached_vdc_metrics(integer) line 21 at SQL statement |

Environment

VMware Cloud Director 10.x

Resolution

This issue is resolved in VMware Cloud Director version 10.5, If not feasible to upgrade you can follow the below workaround:

To work around this issue, follow the procedure below:

1. Take a backup of the Cloud Director database, for more details see Backup and Restore of VMware Cloud Director Appliance in the Cloud Director documentation Link
   
   
2. SSH into the primary cell using the root user credentials.
   
   
3. Connect to the VCD database using the following command:
   
   
   su - postgres -c '/opt/vmware/vpostgres/current/bin/psql -d vcloud'
   
   
4. Run the following query:
    
   CREATE OR REPLACE VIEW dgp_view_vdc_datastore_count AS
SELECT ovdc.vdc_id, count(1) AS number_datastores
FROM (
  SELECT DISTINCT vdc_storage_lr.vdc_id, sub.datastore_id
  FROM (
    SELECT pds.datastore_id, pvdc_storage_lr.id
    FROM pvdc_sclass_scontainer pds
    JOIN prov_vdc_logical_resource pvdc_storage_lr
    ON pvdc_storage_lr.prov_vdc_id = pds.prov_vdc_id
    AND pvdc_storage_lr.name::text = pds.storage_profile_name::text
    AND pvdc_storage_lr.lr_type::text = 'STORAGE_CLASS'::text
  ) sub
  JOIN vdc_logical_resource vdc_storage_lr
  ON vdc_storage_lr.prov_vdc_lr_id = sub.id
) ovdc
GROUP BY ovdc.vdc_id;

For versions already upgraded to 10.5 or above, use the below workaround:

1. Take a backup of the Cloud Director database. For more details, refer to the Backup and Restore of VMware Cloud Director Appliance in the Cloud Director documentation Link.
   
   
2. SSH into the primary cell using the root user credentials.
   
   
3. Connect to the VCD database using the following command:
   
   su - postgres -c '/opt/vmware/vpostgres/current/bin/psql -d vcloud'
   
    
4. Run the following queries to force the information to be updated:

   
SELECT populate_cached_pvdc_metrics();

   SELECT populate_cached_sc_metrics();

   SELECT populate_cached_vdc_metrics();

   SELECT populate_cached_vdc_sp_metrics();

    

Note: If the issue is still not resolved, please open a Support Request with Broadcom Technical Support and note this Article ID (335229) in the problem description. For more information, see Creating and managing Broadcom support cases.

 

Additional Information