Layer7 API Gateway: MySQL performance issue
Article ID: 103539
Updated On:
Products
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
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");
Feedback
