High Performance MySQL, 2nd Edition

Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
1. MySQL Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
MySQL’s Logical Architecture 1
Concurrency Control 3
Transactions 6
Multiversion Concurrency Control 12
MySQL’s Storage Engines 14
2. Finding Bottlenecks: Benchmarking and Profiling . . . . . . . . . . . . . . . . . . . . . 32
Why Benchmark? 33
Benchmarking Strategies 33
Benchmarking Tactics 37
Benchmarking Tools 42
Benchmarking Examples 44
Profiling 54
Operating System Profiling 76
3. Schema Optimization and Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Choosing Optimal Data Types 80
Indexing Basics 95
Indexing Strategies for High Performance 106
An Indexing Case Study 131
Index and Table Maintenance 136
Normalization and Denormalization 139
Speeding Up ALTER TABLE 145
Notes on Storage Engines 149

4. Query Performance Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Slow Query Basics: Optimize Data Access 152
Ways to Restructure Queries 157
Query Execution Basics 160
Limitations of the MySQL Query Optimizer 179
Optimizing Specific Types of Queries 188
Query Optimizer Hints 195
User-Defined Variables 198
5. Advanced MySQL Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204
The MySQL Query Cache 204
Storing Code Inside MySQL 217
Cursors 224
Prepared Statements 225
User-Defined Functions 230
Views 231
Character Sets and Collations 237
Full-Text Searching 244
Foreign Key Constraints 252
Merge Tables and Partitioning 253
Distributed (XA) Transactions 262
6. Optimizing Server Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Configuration Basics 266
General Tuning 271
Tuning MySQL’s I/O Behavior 281
Tuning MySQL Concurrency 295
Workload-Based Tuning 298
Tuning Per-Connection Settings 304
7. Operating System and Hardware Optimization . . . . . . . . . . . . . . . . . . . . . . . 305
What Limits MySQL’s Performance? 306
How to Select CPUs for MySQL 306
Balancing Memory and Disk Resources 309
Choosing Hardware for a Slave 317
RAID Performance Optimization 317
Storage Area Networks and Network-Attached Storage 325
Using Multiple Disk Volumes 326
Network Configuration 328