Pivotal Web Services (PWS): All versions
Many applications that use a database will pool connections so that they can avoid the process and time spent creating and tearing down connections as they are used by the application. Exactly how you configure your connection pool depends on a variety of criteria including the database provider. This article will discuss some suggested options when configuring a connection pool with a MySQL database acquired through ClearDb.
When configuring a connection pool for use with a MySQL database obtained from ClearDb, we offer the following suggestions.
- Configure a low initial size. Unless your application is very active, these will likely just idle off and be closed which is a waste.
- Configure min and max idle settings so that there are a low number of idle connections. ClearDb has a low threshold for idle connections, so you don't want your pool keeping a lot of them around.
- 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 which will disconnect them after 90 seconds. Having the pool do this first, allows it to handle the situation more gracefully and prevent the pool from filling up with bad connections.
- Configure the max connection limit so that the pool does not exceed the number of connections allowed by ClearDb (Spark - 4, Boost - 15, Amp - 30 and 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 free connection slots so that you can connect and perform administrative tasks. You can also refer to the advice the ClearDb has documented here.
- 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 "test on borrow". Another valid approach is "test while idle". In this case, the pool will periodically run through each idle connection and test it.
The following problems can occur when a connection pool is misconfigured.
- Your application may fail when it tries to connect to the database. If your pool is not properly evicting idle threads, ClearDb will disconnect them after a very short period of inactivity. If your pool is not validating the connections, it may provide one of these bad connections to your 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 could simply be ineffective. If the pool is too eagerly evicting idle connections, for example, if the max 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's not enough room for more idle connections. Monitoring your pool's usage will provide you with directions on how to properly tune and adjust these values.
- Improperly setting the max connection limit can result in your 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 that applications are returning connections to the pool in a timely fashion. The pool's idle eviction implementation will not take into account anytime the connection was idle while the application was using it. If the application holds the connection while it's idle, this 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. You need to provide a healthy margin of error, which is why we recommend 75 - 80 seconds when the hard limit is 90. Failure to do this could result in connections being disconnected server side and the problem mentioned in the first bullet of this section.
This article does it's best to provide generic advice that can apply to all languages, frameworks, and runtimes which support connection pooling. Each connection pool implementation will be 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 forums or email support.