Professional Website Performance: Optimizing the Front-End and Back-End

Chapter 9

MySQL in the Network

WHAT’S IN THIS CHAPTER?

• Setting up and managing MySQL replication
• Discovering advanced features of replication
• Partitioning data in MySQL
• Setting up sharding
• Discovering alternatives to MySQL

Many websites start with the web server and MySQL sharing a single server. This works for a while, but (hopefully) there comes a time when a single server just isn’t powerful enough. The next step (particularly on database-heavy sites) is usually to put MySQL on its own server. Separating these two distinct functional areas is easy, and often requires little more than changing the MySQL connection string in the web code. As the site continues to grow, more CPU and RAM are thrown at the database server.

This “monolithic monster” approach to MySQL actually works surprisingly well and is a long-term solution for many websites. Continuing advances in CPU and RAM capabilities mean that even if a high-end server is reaching saturation point now, something bigger and better will soon be available.

Simply throwing CPU and memory at a single machine has diminishing returns, however. You aren’t increasing the data transfer rates from disk or from memory, or decreasing disk seek times, and so on. Sure, you can update these parts of the system, too (although the rate of advance in memory speed and disk speed is not nearly so fast), but you still end up with a system where the bottlenecks are more pronounced than ever.

NOTE It’s also worth remembering that core speeds ...