Posted by Cashton
Coleman on 4/09/2013 09:07AM
One of the questions that we often get at ClearDB is with regards to how many MySQL connections a customer should consider purchasing to ensure that their application can perform and scale as needed. This is an important question, and getting the answer right early on will ensure that your app will be able to perform without any unexpected interruptions. But how does one establish how many connections is required? Well, it's pretty easy, and involves a simple formula in most cases:
Max MySQL Connections = %(PeakHits / PeakHours / 60 Minutes / 60 Seconds)
For example, if you are running a Wordpress blog that receives up to 100,000 page hits during a 2 hour peak window period, here's how this formula works out for you:
%(100,000 / 2 / 60 / 60) = 14 Concurrent MySQL Connections
In this case, the result ends up running up to the top of our "Stage 2" plans, which support up to 15 concurrent MySQL connections (On Heroku, that's our Punch plan; on AppFog it's our Bronze plan, and on Windows Azure that's our Venus plan), which means that you would want to start with the next plan up at $49.99/mo to ensure that you have enough connections available to grow and scale your data throughput. As always though, if you're not sure, you can start small and scale up as your application grows.
Other applications, such as Rails, Java / Play / JEE5, or any other enterprise grade application framework that uses connection pooling technology will need to perform additional calculations to determine how many connections should be allocated for each actual application instance that will be connecting to the database. For example, a Rails application on Heroku allocates a default connection pool of 5 connections unless it's configured otherwise. For folks who have Punch plans who are using this default connection pool configuration, this means that up to three dynos may be used with this database before the dynos will begin to run out of connections. A good practice would be to determine how many connections are really required for each dyno (e.g., determine how many concurrent requests are able to be served by a dyno, which is typically only one at a time) to ensure that connection concurrency does not introduce complex pain points in which unused (or *underused*) connections lead to max connection problems.
Here is a list of example frameworks in which careful connection pool allocation planning calculations should be considered:
- Java Play! Applications
- Ruby on Rails Applications
- Node.js Applications
- .NET Framework Applications
- Django Applications
- Scala Applications
Always plan for more than just the app's connection allocations!
One gotcha that can trip up some customers is allocating all of the available database connections for application use only without thinking about leaving enough connections available for auxilliary tasks, such as connecting directly to the database for:
- Administration tasks
- Backup tasks
- Processing jobs
In short, be sure to remember to factor in all elements of connectivity to your database for your business, as well as your app!
Hopefully this post has helped you determine how many connections you will need in order to properly determine which ClearDB MySQL database plan you'll need. We look forward to serving your database needs!