1

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?

  • You might found what your looking for here – Nelz Aug 19 '15 at 19:59
  • Cannot tell you what to do, but I can confirm that this was brought up before (I think on an Apache mailing list). There seems to be a condition that the Tomcat pool does not properly limit the number of connections. It is not a db setting problem. – eckes Aug 28 '17 at 9:08
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?

  • Unfortunately I have no control over clearDB plan. I have bought their highest plan from heroku and they offer max_user_connections=40 for this plan. – Cristian Boariu Aug 19 '15 at 20:01
  • max_user_connections is in a config file. If you have root access to the server, you can change it. Otherwise complain to heroku and/or clearDB, neither of which am I familiar with. – Rick James Aug 19 '15 at 20:11
  • Thanks a lot. But do you think that if we only increase maxActive things should improve? – Cristian Boariu Aug 19 '15 at 20:15
  • Go into mysql and do SHOW PROCESSLIST; If you see more than a few that are not "Sleep", then either (1) you do have a lot of active users, or (2) you have a lot of slow queries. The latter can be addressed by showing us the query, plus SHOW CREATE TABLE for the table(s) involved. – Rick James Aug 19 '15 at 20:17
  • Maybe that 3rd party software is trying to limit (via licensing) the number of your users connecting by funneling all activity through a single MySQL user. Do you really think you have 40+ "users" out there? – Rick James Aug 19 '15 at 21:06
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.