I have a web application that has to deal with high concurrency, like 100 users querying the same 5 tables (one of them, returns more than 500 rows) and some others users inserting in these tables at the same time.
When there are too many users using, the concurrency is too high, my application hangs and I have to restart tomcat. I could not find much in the logs. When I execute "show process list;" in MySQL, there are processes for each connection and most of them are with status "Query"... before the application hangs, one process by one, goes to "Sleep" status, until all process have this status and the application hangs.
It is very difficult to diagnose what is happening... I'm trying to better synchronize the code, without any success... well, I'm here asking for opinions about if I'm using a good bonecp configuration to be used in this environment:
<property name="bonecp.idleMaxAgeInMinutes">10</property>
<property name="bonecp.maxConnectionAgeInSeconds">3000</property>
<property name="bonecp.idleConnectionTestPeriodInMinutes">5</property>
<property name="bonecp.connectionTestStatement">/* ping */ SELECT 1</property>
<property name="bonecp.partitionCount">2</property>
<property name="bonecp.acquireIncrement">2</property>
<property name="bonecp.maxConnectionsPerPartition">12</property>
<property name="bonecp.minConnectionsPerPartition">5</property>
<property name="bonecp.statementsCacheSize">50</property>
<property name="bonecp.releaseHelperThreads">3</property>
As per my MySQL configuration, I'm using everything default, except this two:
autocommit = 0
innodb_thread_concurrency = 8
(the server has 3 CPU and 1 disk)
Would you guys advise me changing something? THanks!
If you use HikariCP, I recommend starting with the defaults and seeing how that works for you. I also recommend reading the MySQL configuration tips in the wiki. If you have questions, you will get faster replies in our Google Group, but you are of course free to ask here as well (but we check here less often).