Efficient MySQL Performance

Chapter 5. Sharding

On a single instance of MySQL, performance depends on queries, data, access patterns, and hardware. When direct and indirect query optimization—assiduously applied—no longer deliver acceptable performance, you have reached the relative limit of single-instance MySQL performance for the application workload. To surpass that relative limit, you must divide the application workload across multiple instances of MySQL to achieve MySQL at scale.

Sharding a database is the common and widely used technique of scaling out (or, horizontal scaling): increasing performance by distributing the workload across multiple databases. (By contrast, scaling up, or vertical scaling, increases performance by increasing hardware capacity.) Sharding divides one database into many databases. Each database is a shard, and each shard is typically stored on a separate MySQL instance running on separate hardware. Shards are physically separate but logically the same (very large) database.

MySQL at scale requires sharding. I’m going to repeat that sentence several times in this chapter because it’s a fact that engineers hesitate to accept. Why? Because sharding is not an intrinsic feature or capability of MySQL. Consequently, sharding is complex and entirely application-specific, which means there’s no easy solution. But don’t be discouraged: sharding is a solved problem. Engineers have been scaling out MySQL for decades.

This chapter introduces the basic mechanics of sharding to achieve ...