Concurrent connections to MySQL in Azure

Jan.27,2013

For a customer we needed to migrate a high traffic WordPress site to Azure. Using Azure Websites it was a breeze to migrate the site. We used the W3 Total Cache plugin for caching and serving static content from Blob storage. This seemed to be a simple migration…

But after some performance test with 2 reserved instances we got a lot of “Error establishing a database connection” messages. To find the limitations of the MySQL connections in collaboration with Azure Websites I did a little research.

For the research, I made use of:
– 2 different MySQL database variants from ClearDB. A free version (Mercury, max 20MB of data and max 4 connections) and a paid version (Venus $10 p/m, max 1GB of data and up to 15 connections).

– A standard WordPress site installed via the Azure portal. I didn’t modify the source code, apart from changing the database connection in wp-config.php. For database filling I only added 2 blog post with ~1000 words.

– A self-made Console application to perform a load test on the website. (See Attachment for source code)

The research
Web site mode “Free” and free (Mercury) MySQL database.
I have tested up to 25 concurrent users (seems sufficient for free hosting). No database error occurred.

Web site mode “Shared” and free (Mercury) MySQL database.
Up to 4 concurrent users no errors. With more than 4 concurrent users I got the “Error Establishing a database connection” error messages.

Web site mode “Shared” and paid (Venus) MySQL database.
Up to 5 concurrent users I didn’t get any error messages, with more than 5 concurrent users occasionally an “internal server error” occurred. Because I’m interested in much higher load I didn’t investigate these errors.

Web site mode “Reserved” and paid (Venus) MySQL database.
1 small instance: up to 100 concurrent users tested, no error message.
1 medium instance: up to 100 concurrent users tested, no error message.
1 large instance: up to 300 concurrent users tested, no database error (response time per page rose to 4 seconds, so more users means scaling up).

2 small instances: up to 15 concurrent users no error messages, but with 16 concurrent users (and more) the famous “Error Establishing a database connection” error message occurred.

Conclusion
“Free” sites can only create 4 connections to the database, a higher load will be throttled by your webserver and no database errors will occur.

“Shared” websites can use more than 4 connections to the database and, with more than 4 concurrent users, your visitors will get error messages when you’re using a free database. When you’re using the paid Venus database, you will not get the database error messages. (I cannot explain the server error messages at a higher load).
A single “Reserved” instance can use a maximum of 15 connections to the database. A Venus database has a limit of 15 connections, so this is just right. I didn’t get any “Error Establishing a database connection” error messages. At high load, the response time became longer, but no error messages occurred.

With 2 “Reserved” instances you can get 2 x 15 connections to the database, so with more than 15 concurrent users, database errors will occur with a Venus database.
My expectation is that a Saturn database (price $ 50 per month, max 5GB, 30 connections) will keep you away from connection errors here.

If you have a high traffic website and need more than 2 “Reserved” instances the standard ClearDB offerings will not be sufficient. With 3 instances you will get 45 connections and the biggest offering (Jupiter) has a maximum of 40 connections.

For our customer we asked ClearDB to create a dedicated MySQL cluster ($399.99 per month). This baby can handle a lot of concurrent connections and I haven’t be able to produce a “Error Establishing a database connection” message any more (even with 10 instances)