Hi,
we are using an Alfresco Community 5.0 in which the registered (nominal) users are around 2k. In some cases we get an exception from Alfresco logs that I reported below.
Here the configuration:
- Server Alfresco Ubuntu 14 64 bit/12GB RAM/8 vcpu cores
- Server MySQL Ubuntu 14 64 bit/12GB RAM/8 vcpu cores
Alfresco global properties about database is the following:
db.driver=com.mysql.jdbc.Driver
db.username=alfresco
db.password=*****
db.name=alfresco
db.url=jdbc:mysql://*****:3306/alfresco?useUnicode=yes&characterEncoding=UTF-8
db.pool.max=275
db.pool.validate.query=SELECT 1
Database max_connections is about 300.
Please, do you have any suggestion about hot to solve ot make the proper tuning on Alfresco/database?
2017-02-07 08:50:51,325 ERROR [post.cleanup.PostCleaner] [DefaultScheduler_Worker-5] Exception during cleanup of posts org.springframework.dao.CannotAcquireLockException: ### Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
### The error may involve alfresco.activities.delete_activity_posts_older_than_date-Inline ### The error occurred while setting parameters ### SQL: delete from alf_activity_post where post_date < ? and status = ? ### Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction ; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:265)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)
at com.sun.proxy.$Proxy12.delete(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:268)
at org.alfresco.repo.domain.activities.ibatis.ActivityPostDAOImpl.deletePosts(ActivityPostDAOImpl.java:102)
at org.alfresco.repo.activities.post.cleanup.PostCleaner.executeWithLock(PostCleaner.java:146)
at org.alfresco.repo.activities.post.cleanup.PostCleaner.execute(PostCleaner.java:117)
at org.alfresco.repo.activities.post.cleanup.PostCleanupJob.execute(PostCleanupJob.java:50)
at org.quartz.core.JobRunShell.run(JobRunShell.java:216)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:563)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2826)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1302)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:165)
at sun.reflect.GeneratedMethodAccessor432.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358) ... 8 more
