Layer7 API Gateway: MySQL performance issue

book

Article ID: 103539

calendar_today

Updated On:

Products

STARTER PACK-7 CA Rapid App Security 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");