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
ENV: 8.x, 9.x
Over 95 percent of the buffer pool is occupied by lock heaps or the adaptive hash index
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");