layer7 ca api gateway restart of ssg services takes a long time to initialize

Products

• CA API Gateway

Issue/Introduction

Gateway is starting slowly at startup its observed that the ssg_0_0.log ON Gateway 9 has errors like,

com.l7tech.server.service.ServiceMetricsServicesImpl: Couldn't save MetricsBin
org.springframework.dao.CannotAcquireLockException: Could not execute JDBC batch update; SQL [insert into service_metrics (nodeid, end_time, interval_size, back_max, front_max, back_min, front_min, attempted, authorized, completed, period_start, resolution, published_service_goid, service_state, start_time, back_sum, front_sum, goid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.LockAcquisitionException: Could not execute JDBC batch update

On Gateway 10/11 you may see 

2023-04-27T08:01:16.111-0400 INFO    1 com.l7tech.server.globalresources.SchemaResourceManager: (Re)registering XML Schemas for hardware.
2023-04-27T08:10:00.897-0400 INFO    1 com.l7tech.server.PcApiConnectorActivationListener: Deleting old Process Controller API port file to /opt/SecureSpan/Gateway/node/default/var/processControllerPort

 

Environment

Release : 9.4

Component :

Resolution

Service metrics has grown to a large size and other nodes in the cluster are causing long locks.

1) backup the database

mysql ssg > /somepath/somefile.sql

Gateway 9.x * See new Gateway 10/11x below

2) Truncate service metrics for 9 only.

SET FOREIGN_KEY_CHECKS = 0;

truncate table service_metrics_details;

optimize table service_metrics_details;

truncate table service_metrics;

optimize table service_metrics;

SET FOREIGN_KEY_CHECKS = 1;

Gateway 10/11x

2) Truncate service metrics for 10/11.

SET FOREIGN_KEY_CHECKS = 0;

truncate table service_metrics_details;

analyze table service_metrics_details;

truncate table service_metrics;

analyze table service_metrics;

SET FOREIGN_KEY_CHECKS = 1;