Alfresco Lock wait timeout exceeded exception

[massimo_callist]

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

[massimo_callist]

Fixed following these guidelines:

Alfresco tuning part1