Suggested Configuration for the Connection Pools using ClearDB

Products

• Services Suite

Issue/Introduction

This articles describes the suggested configuration for the Connection Pools using ClearDB.

Many applications use the connection pools in the database so that they can avoid the process and time spent creating and tearing down the connections. Exactly how you configure your connection pool depends on a variety of criteria including the database provider. This article discusses about some of the suggested options for configuring a connection pool with a MySQL database acquired through the ClearDB.

Resolution

When configuring a connection pool to use with a MySQL database obtained from the ClearDB, we recommend the following:

• Configure a low initial size. Unless your application is very active, this will remain idle and close down.
• Configure minimum and maximum idle settings so that there are a low number of idle connections. ClearDB has a low threshold for idle connections. So, you should not keep your pool having a lot of idle connections.
• Configure the pool to close connections that have been idle longer than 75 - 80 seconds. There is a hard limit imposed by ClearDB on idle connections that disconnects them after 90 seconds. Allowing the pool do this first, handles the situation in a better way and prevent the pool from filling up with bad connections.
• Configure the maximum connection limit so that the pool does not exceed the number of connections allowed by ClearDB. The followoing are the number of connections allowed by the ClearDB:
• Spark: 4
• Boost: 15
• Amp: 30 
• Shock: 40 

If you have multiple instances of your application, divide the total count by the number of application instances as each instance will have its own pool. Also, consider that you may need to leave a couple of free connection slots so that you can connect and perform administrative tasks. You can refer to the ClearDB documentation.

• Configure the pool to validate connections. Most pools offer this feature which allows the pool to filter out any connections that have been closed or are no longer working properly. We suggest that you validate connections prior to passing them out to an application, often called as test-on-borrow. Another valid approach is test-while-idle in which the pool periodically runs through each idle connection and test it.

Risk

The following problems occur when a connection pool is misconfigured:

• When the application fails trying to connect to the database, if the pool is not properly evicting idle threads, ClearDB will disconnect them after a very short period of inactivity. If the pool is not validating the connections, it provides one of the bad connections to the application. When this happens, the application's query will fail with a message about the connection having been closed or the infamous MySQL last packet sent to the server error.
• An improperly configured pool is ineffective, if the pool is evicting idle connections fast. As an example, if the maximum idle setting is too low, you may not get any benefit from it. In other words, connections will be returned to the pool and closed because there is not enough room for more idle connections. Monitoring the usage of the pool will provide with directions on how to properly tune and adjust these values.
• Improperly setting the maximum connection limit results in the application trying to take too many connections. This can then cause other application instances to fail as they try to get new connections because the server has no free slots.
• Make sure the applications are returning connections to the pool in a timely fashion. When the pool is idle, application implementation will not wait considering the connection was idle. If the application holds the connection while it's idle, it can throw off the pool's idle limits.
• Do not set the pool's idle limit to be the same or very close to ClearDB's hard limit of 90. You need to provide a healthy margin of error which is why we recommend 75 - 80 seconds as the hard limit is 90. Failure to do so could result in connections being disconnected on the server side and the problem mentioned in the first bullet of this section.

Additional Information

This article does its best to provide suggestions that can apply to all languages, frameworks, and runtimes which support connection pooling. Each connection pool implementation is different and may have different ways of handling the above settings. You should consult the documentation for your connection pool to see how best to implement the suggestions above. If you need further assistance doing this, you can search the community or use email support.