How can you realize MySQL's full power? With High Performance MySQL, you'll learn advanced techniques for everything from choosing the right abstraction layer for databases to designing schemas, indexes, and queries to tuning your server, operating system, and hardware to achieve their full potential. This guide also teaches database administrators safe and practical ways to scale applications through replication, load balancing, high availability, and failover.

Updated to reflect recent advances in cloud- and self-hosted MySQL, InnoDB performance, features, and tools, this revised edition helps you design a data platform that will scale with your business. You'll learn the latest in cloud-hosted MySQL offerings, best practices for database security, and hard-earned lessons in both performance and database stability.

  • Dive into MySQL's architecture, including key facts about its storage engines
  • Learn how server configuration works with your hardware and deployment choices
  • Make query performance part of your software delivery process
  • Examine enhancements to MySQL's replication and high availability
  • Compare different MySQL offerings in managed cloud environments
  • Explore MySQL's full stack optimization from application-side configuration to server tuning
  • Turn traditional database management tasks into automated processes

Table of Contents

  1. Introduction
    1. Why You Picked MySQL
    2. So, How Did You Get Here?
    3. Getting Started
    4. Hitting the Limits
    5. Vertical Scaling
    6. Horizontal Sharding
    7. Meeting Demand
    8. Conclusion
  2. 1. MySQL Architecture
    1. MySQL’s Logical Architecture
    2. Connection Management and Security
    3. Optimization and Execution
    4. Concurrency Control
    5. Read/Write Locks
    6. Lock Granularity
    7. Transactions
    8. Isolation Levels
    9. Deadlocks
    10. Transaction Logging
    11. Transactions in MySQL
    12. Multiversion Concurrency Control
    13. Replication
    14. MySQL’s Storage Engines
    15. The InnoDB Engine
    16. JSON document support
    17. Other Built-in MySQL Engines
    18. Summary
  3. 2. Monitoring in a Reliability Engineering World
    1. The impact of reliability engineering on DBA teams
    2. Defining Service Level Goals
    3. What does it take to make customers happy
    4. What to measure
    5. Defining SLIs and SLOs
    6. Query analysis
    7. Steady state monitoring
    8. Measuring Long Term Performance
    9. Learning your business cadence
    10. Tracking your metrics effectively
    11. Using monitoring tools to inspect the performance
    12. Using SLOs to guide your overall architecture
    13. Summary
  4. 3. Operating System and Hardware Optimization
    1. What Limits MySQL’s Performance?
    2. How to Select CPUs for MySQL
    3. Balancing Memory and Disk Resources
    4. Caching, Reads, and Writes
    5. What’s Your Working Set?
    6. Solid-State Storage
    7. An Overview of Flash Memory
    8. Garbage Collection
    9. RAID Performance Optimization
    10. RAID Failure, Recovery, and Monitoring
    11. RAID Configuration and Caching
    12. Network Configuration
    13. Choosing a Filesystem
    14. Choosing a Disk Queue Scheduler
    15. Memory and Swapping
    16. Operating System Status
    17. Other Helpful Tools
    18. Summary
  5. 4. Optimizing Server Settings
    1. How MySQL’s Configuration Works
    2. Syntax, Scope, and Dynamism
    3. Persisted System Variables
    4. Side Effects of Setting Variables
    5. Planning Your Variable Changes
    6. What Not to Do
    7. Creating a MySQL Configuration File
    8. Minimal Configuration
    9. Inspecting MySQL Server Status Variables
    10. Configuring Memory Usage
    11. Per-Connection Memory Needs
    12. Reserving Memory for the Operating System
    13. The InnoDB Buffer Pool
    14. The Thread Cache
    15. Configuring MySQL’s I/O Behavior
    16. The InnoDB transaction log
    17. Log buffer
    18. How InnoDB flushes the log buffer
    19. How InnoDB opens and flushes log and data files
    20. The InnoDB tablespace
    21. Configuring the tablespace
    22. Old row versions and the tablespace
    23. Other I/O configuration options
    24. Configuring MySQL Concurrency
    25. Safety Settings
    26. Advanced InnoDB Settings
    27. Summary
  6. 5. Optimizing Schema and Data Types
    1. Choosing Optimal Data Types
    2. Whole Numbers
    3. Real Numbers
    4. String Types
    5. Date and Time Types
    6. Bit-Packed Data Types
    7. JSON Data
    8. Choosing Identifiers
    9. Special Types of Data
    10. Schema Design Gotchas in MySQL
    11. Summary
  7. 6. Indexing for High Performance
    1. Indexing Basics
    2. Types of Indexes
    3. Benefits of Indexes
    4. Indexing Strategies for High Performance
    5. Isolating the Column
    6. Prefix Indexes and Index Selectivity
    7. Multicolumn Indexes
    8. Choosing a Good Column Order
    9. Clustered Indexes
    10. Covering Indexes
    11. Using Index Scans for Sorts
    12. Redundant and Duplicate Indexes
    13. Unused Indexes
    14. Indexes and Locking
    15. Index and Table Maintenance
    16. Finding and Repairing Table Corruption
    17. Updating Index Statistics
    18. Reducing Index and Data Fragmentation
    19. Summary
  8. 7. Query Performance Optimization
    1. Why Are Queries Slow?
    2. Slow Query Basics: Optimize Data Access
    3. Are You Asking the Database for Data You Don’t Need?
    4. Is MySQL Examining Too Much Data?
    5. Ways to Restructure Queries
    6. Complex Queries Versus Many Queries
    7. Chopping Up a Query
    8. Join Decomposition
    9. Query Execution Basics
    10. The MySQL Client/Server Protocol
    11. Query states
    12. The Query Optimization Process
    13. The Query Execution Engine
    14. Returning Results to the Client
    15. Limitations of the MySQL Query Optimizer
    16. Correlated Subqueries
    17. UNION Limitations
    18. Index Merge Optimizations
    19. Equality Propagation
    20. Parallel Execution
    21. Loose Index Scans
    22. MIN() and MAX()
    23. SELECT and UPDATE on the Same Table
    24. Optimizing Specific Types of Queries
    25. Optimizing COUNT() Queries
    26. Optimizing JOIN Queries
    27. Optimizing Subqueries
    28. Optimizing GROUP BY and DISTINCT
    29. Optimizing LIMIT and OFFSET
    30. Optimizing SQL_CALC_FOUND_ROWS
    31. Optimizing UNION
    32. Using User-Defined Variables
    33. Case Studies
    34. Building a Queue Table in MySQL
    35. Using User-Defined Functions
    36. Summary
  9. 8. Scaling MySQL
    1. What Is Scaling?
    2. Read Versus Write Bound Loads
    3. Scaling Reads with Read Pools
    4. Managing Configuration for Read Pools
    5. Health Checks for Read Pools
    6. Choosing a Load Balancing Algorithm
    7. Queuing
    8. Sharding
    9. Choosing a Partitioning Scheme
    10. Multiple Partitioning Keys
    11. Querying Across Shards
    12. Vitess
    13. ProxySQL
    14. Summary
  10. Index