10,000 Tables Can’t Be Wrong: Designing a Highly Scalable MySQL Architecture for Write-intensive Applications

The MySQL storage engine is super fast when working with small tables and low write contention but is easily bogged down when an application grows and many simultaneous write requests cause table locks to back up. Often the InnoDB storage engine is the solution that application designers and DBAs choose when contention becomes an issue; ScienceLogic took a different tack to develop a unique architecture for its scalable EM7 Meta-Appliances for IT Operations Management.

EM7 Meta-Appliances can gather hundreds of statistics every minute from each of thousands of devices. These need to be stored, analyzed, and summarized, with reports generated showing status and trends and alerts generated for any anomalies found. All of this must be done while providing end users immediate, responsive access to both the summarized and detailed data.

Given the growth patterns and the nature of the access that was required to the data, the ScienceLogic team chose to engineer their IT Management application to create databases and highly segmented tables on the fly and as required. The large number of tables ensures that only one thread is ever writing to a particular table at a time, even with a write to read ratio of 9:1.

Some of the EM7 systems in use by larger customers now have tens of thousands of MyISAM tables performing hundreds of millions of database writes daily and continue to scale and perform extremely well.

Using real-life customer examples, we will talk about the problem being addressed, why this approach was chosen, how it was implemented, specific databases tuning requirements with this architecture and the results obtained. We will include some small code examples using Python.

We will also discuss the pros and cons of the most common storage engines to illustrate the decision path that we took to get to this architecture.