What would be a good bonecp configuration for high concurrency

Question

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!

score 8 · Answer 1 · answered May 27 '14 at 07:44

8

As the author of BoneCP, I advise you to have a look at this pool instead: https://github.com/brettwooldridge/HikariCP

because BoneCP is now both old and superseeded by HikariCP which offers superior performance.

answered May 27 '14 at 07:44

wwadge

3,506
1
19
28

score 3 · Accepted Answer · answered May 27 '14 at 11:23

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).

score 1 · Answer 3 · answered Sep 15 '16 at 15:12

Before using boneCP you have to have good understanding of all the configurations like belows

  # Whether auto commit should be used
  autoCommit = true

  # If non null, the transaction isolation level to use.
  isolation = null

  # Whether the database should be treated as read only
  readOnly = false

  # Whether opened statements should be automatically closed
  closeOpenStatements = true

  # The pool partition count
  partitionCount = 1

  # The maximum number of connections per partition
  maxConnectionsPerPartition = 50

  # The minimum number of connections per partition
  minConnectionsPerPartition = 5

  # The increment to acquire connections in
  acquireIncrement = 1

  # The acquire retry attempts
  acquireRetryAttempts = 10

  # The delay to wait before retrying to acquire a connection
  acquireRetryDelay = 1 second

  # The connection timeout
  connectionTimeout = 1 second

  # The idle age to expire connections
  idleMaxAge = 10 minutes

  # The maximum a connection should live for
  maxConnectionAge = 1 hour

  # Whether JMX reporting should be disabled
  disableJMX = true

  # Whether statistics should be kept
  statisticsEnabled = false

  # How frequently idle connections should be tested
  idleConnectionTestPeriod = 1 minute

  # Disable connection tracking
  disableConnectionTracking = true

  # The time limit for executing queries. 0 means no time limit.
  queryExecuteTimeLimit = 0

  # Whether the connection should be reset when closed
  resetConnectionOnClose = false

  # Whether unresolved transations should be detected
  detectUnresolvedTransactions = false

  # An SQL statement to execute to test if a connection is ok after it is created.
  # Null turns this feature off.
  initSQL = null

  # An SQL statement to execute to test if a connection is ok before giving it out of the pool.
  # Null turns this feature off.
  connectionTestStatement = null

  # Whether SQL statements should be logged
  logStatements = true

Tune your application by changing above params and takes the one that best suit for you. above setting works good for my application[Java Play framework]

What would be a good bonecp configuration for high concurrency

3 Answers3