What is wrong in my jdbc config pool?

Question

I am using clearDB with max_connections plan being 40.

This is my jdbc pool configuration:

<bean
   id="dataSource"
   class="org.apache.tomcat.jdbc.pool.DataSource"
   destroy-method="close"

   p:driverClassName="com.mysql.jdbc.Driver"
   p:url="#{ @dbUrl.getJdbc() }"
   p:username="#{ @dbUrl.getUser() }"
   p:password="#{ @dbUrl.getPassword() }"
   p:initialSize="2"
   p:maxActive="10"
   p:maxIdle="5"
   p:minIdle="2"
   p:maxWait="60000"
   p:timeBetweenEvictionRunsMillis="5000"
   p:minEvictableIdleTimeMillis="60000"
   p:testOnBorrow="true"
   p:validationQuery="/* ping */ SELECT 1"
   p:validationInterval="30000"
   p:testWhileIdle="true"
  />

Today we had 197 concurrent users on site and received a lot of

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User 'yyyyy' has exceeded the 'max_user_connections' resource (current value: 40)

which meant that other users were not able to log in and use the site.

It was very frustrating. Might it be because of our maxActive being only 10? What you would propose to refine for this configuration in order to get rid of these error?

Answer 1

Go into my.cnf (or my.ini). Increase (or set) max_user_connections in the [mysqld] section.

Also check max_connections; the 197 may be bumping up against that.

Do you really have a single user connecting more than 40 times? Or is the "connection pooling" inflating things?

Answer 2

I think this link will be of some help to you with max connections.

How to get rid of "maximum user connections" error ? (if you end up copying and pasting the instructions, not that connections is misspelled in the first SQL statement).

Run SHOW VARIABLES LIKE '%max%conn%'; against your database and see what your relevant variables are set at relating to this specific problem.

In the link, Although from your error listed, I am not sure of the unexpected behavior. The error that you posted says that the user exceeded the 'max_user_connections' resource (current value: 40). As you originally stated, you have maxActive set in your code configuration at 40. Am I wrong there?

There a number of configurations set in the MySQL configuration as well as in your code that will allow you to handle connectivity issues that come up even beyond this.

Probably not your problem, but a possible help In the past I've had a problem with code not closing connections and my database getting full of connections. To fix this from the DB side, I ended up setting the global wait_timeout to a lower value (an hour in my case). MySQL defaults the value to 8 hours. This value represents the amount of time a sleeping connection will be allowed to remain connected. You can do that like this set global wait_timeout = xxx. MySQL interprets this in seconds. If you change the global session here, I recommend changing the my.cnf (Linux) or my.ini (Windows).