mysql performance issue

Document ID : KB000103539
Last Modified Date : 27/06/2018
Show Technical Document Details
Question:
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
Answer:
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:
Also advised the customer 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");