High Performance MySQL, 4th Edition

Chapter 11. Scaling MySQL

Running MySQL in a personal project, or even in a young company, is very different from running it in a business with an established market and “hockey stick growth.” In a high-velocity business setting, traffic can grow orders of magnitude year over year, the environment becomes more complex, and the accompanying data needs accelerate rapidly. Scaling up MySQL is very different from other types of servers largely because of the stateful nature of the data. Compare this to a web server, where the widely accepted model of adding more behind a load balancer is typically all you need to do.

In this chapter, we explain what scaling means and walk you through the different axes where you may need to scale. We explore why read scaling is essential and show you how to accomplish it safely, with strategies like queuing for making scaling writes more predictable. Finally, we cover sharding data sets to scale writes using tools like ProxySQL and Vitess. By the end of this chapter, you should be able to identify what seasonal pattern your system has, how to scale reads, and how to scale writes.

What Is Scaling?

Scaling is the system’s ability to support growing traffic. The criteria for whether a system scales well or scales poorly can be measured by cost and simplicity. If it is excessively expensive or complicated to increase your system’s ability to scale, you likely will expend significantly more effort remediating this as you hit limitations.

Capacity is a ...