Layer7 API Gateway: MySQL performance issue

Article Id: 103539

Status: Published

Updated On: 22-07-2020 08:41

Products:

STARTER PACK-7 , CA Rapid App Security , CA API Gateway , CA API Gateway

Issue/Introduction:

The following errors are reported in the log file.

2018-06-15T20:59:20.932+0100 WARNING 40 org.hibernate.util.JDBCExceptionReporter: SQL Error: 1205, SQLState: 41000
2018-06-15T20:59:20.933+0100 SEVERE 40 org.hibernate.util.JDBCExceptionReporter: Lock wait timeout exceeded; try restarting transaction
2018-06-15T20:59:20.934+0100 WARNING 40 com.l7tech.server.service.ServiceMetricsServicesImpl: Couldn't save MetricsBin
org.springframework.dao.CannotAcquireLockException: Hibernate flushing: Could not execute JDBC batch update; SQL [insert into service_metrics (nodeid, published_service_goid, resolution, period_start, start_time, end_time, attempted, authorized, completed, back_min, back_sum, back_max, front_min, front_sum, front_max, interval_size, service_state, goid) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
   at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:261)
   at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:805)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:791)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:664)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
   at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
   at com.sun.proxy.$Proxy107.doFlush(Unknown Source)
   at com.l7tech.server.service.ServiceMetricsServicesImpl$Flusher.flush(ServiceMetricsServicesImpl.java:640)
   at com.l7tech.server.service.ServiceMetricsServicesImpl$Flusher.run(ServiceMetricsServicesImpl.java:594)
   at java.lang.Thread.run(Thread.java:724)

Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
   at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2045)
   at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1468)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:268)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
   at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656)
   ... 10 more

Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
   at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)
   at com.mysql.jdbc.LoadBalancedMySQLConnection.execSQL(LoadBalancedMySQLConnection.java:155)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2444)
   at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1997)
   ... 22 more

Environment:

ENV: 8.x, 9.x

Resolution:

The problem appears to be caused by this error :


Over 95 percent of the buffer pool is occupied by lock heaps or the adaptive hash index

Above message indicates that you need to increase the buffer pool size. In order to do so, please follow this:

Linux File Name = my.cnf

1. Edit my.cnf configuration file.
2. Search for: innodb_buffer_pool_size
3. Increase the value for this property.
Example: to set the value to 2 Gig
innodb_buffer_pool_size=2G
4. Restart Gateway and MySql Service.

Additional Information:

It is also advised to check service_metrics and service_metrics_details tables

The data in `service_metrics` is being used by the Policy Manager’s dashboard. It is possible to stop the service metrics altogether by using the Cluster-Wide Property "serviceMetrics.enabled", see online help system for details.

The folowing query will show data for 1 min, 5 mins, 1 hour, 1 day, 1 week or 30 days. You can use delete SQL command to purge the relevant time intervals.

SELECT
COUNT(*) "count",
"TOTAL" as "age",
NOW() "now"
FROM
`service_metrics`

UNION ALL

SELECT
COUNT(*),
"over 1 minutes",
NOW()
FROM
`service_metrics`
WHERE
`start_time` < CONCAT(UNIX_TIMESTAMP() - 60, "000")

UNION ALL

SELECT
COUNT(*),
"over 5 minutes",
NOW()
FROM
`service_metrics`
WHERE
`start_time` < CONCAT(UNIX_TIMESTAMP() - 300, "000")

UNION ALL

SELECT
COUNT(*),
"over 1 hour",
NOW()
FROM
`service_metrics`
WHERE
`start_time` < CONCAT(UNIX_TIMESTAMP() - 3600, "000")

UNION ALL

SELECT
COUNT(*),
"over 1 day",
NOW()
FROM
`service_metrics`
WHERE
`start_time` < CONCAT(UNIX_TIMESTAMP() - 86400, "000")

UNION ALL

SELECT
COUNT(*),
"over 1 week",
NOW()
FROM
`service_metrics`
WHERE
`start_time` < CONCAT(UNIX_TIMESTAMP() - 604800, "000")

UNION ALL

SELECT
COUNT(*),
"over 30 days",
NOW()
FROM
`service_metrics`
WHERE
`start_time` < CONCAT(UNIX_TIMESTAMP() - 2592000, "000");