How to Solve: “Too many connections”; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:

Hi,

in one of my projects I used GPars and its withPool() method to execute some async tasks.
The prime purpose of a task is to fetch some data from external sources and save the resulting data to a database. Very simple.

While the task did not consume much memory and the performance was mostly determined by the response time of some external rest services, I used quite a high parallelism with a pool size of 500.

// GPars
withPool(500) {
   tasksToDo.eachParallel { task ->
      // .. do some long running tasks asynchronously
      // and save the result to a database 

   }
}

Everything worked fine in the local Grails test environment.
As production environment I used Amazon’s AWS beanstalk and a RDS mysql instance as database backend, both as t1.small instances.

But with the Amazon installation i got the following error message:

"Too many connections"; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:

2014-08-20 09:19:54,373 [ForkJoinPool-1-worker-218] ERROR crawler.BasicRssGatherer  - org.springframework.dao.DataAccessResourceFailureException: Hibernate operation: could not prepare statement; .....
.....
Data source rejected establishment of connection,  message from server: "Too many connections"

It turned out, that Amazon RDS instances have a fixed limit of available connections.
You can check this out, by

  • opening the AWS console,
  • go to your RDS instance,
  • open “Parameter Groups” and
  • seek for “max_connections”.

In my case the formula found is: {DBInstanceClassMemory/12582880}, which calculates to:

	 * MODEL      max_connections innodb_buffer_pool_size
	 * ---------  --------------- -----------------------
	 * t1.micro   34                326107136 (  311M)
	 * m1-small   125              1179648000 ( 1125M,  1.097G)
	 * m1-large   623              5882511360 ( 5610M,  5.479G)
	 * m1-xlarge  1263            11922309120 (11370M, 11.103G)
	 * m2-xlarge  1441            13605273600 (12975M, 12.671G)
	 * m2-2xlarge 2900            27367833600 (26100M, 25.488G)
	 * m2-4xlarge 5816            54892953600 (52350M, 51.123G)

It is not possible to adjust these settings. So you can either switch to a bigger and more costly RDS instance, or reduce the pool size, which might reduce the overall performance of your app. This might be Amazon’s approach of monetarization. A third option is to roll out your own mysql installation, where you can set the connection limit by yourself, but you will loose all opportunities of a managed RDS database.

So, be aware that Amazon’s RDS mysql instances have a fixed connection limit, if you are working with high numbers of parallel database accesses. The limit can be calculated by the formula found in the max_connection section as DBInstanceClassMemory/12582880.

HTH
Johannes

Advertisements
This entry was posted in Development and tagged , , . Bookmark the permalink.

One Response to How to Solve: “Too many connections”; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:

  1. drjungle says:

    You cannot change a ‘default’ parameter group but you can change a user created parameter group and have your database use it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s