Efficient MySQL Performance

Table of contents

1. Preface
   1. Conventions Used in This Book
   2. Using Code Examples
   3. O’Reilly Online Learning
   4. How to Contact Us
   5. Acknowledgments
2. 1. Query Response Time
   1. A True Story of False Performance
   2. North Star
   3. Query Reporting
      1. Sources
      2. Aggregation
      3. Reporting
   4. Query Analysis
      1. Query Metrics
      2. Metadata and the Application
      3. Relative Values
      4. Average, Percentile, and Maximum
   5. Improving Query Response Time
      1. Direct Query Optimization
      2. Indirect Query Optimization
   6. When to Optimize Queries
      1. Performance Affects Customers
      2. Before and After Code Changes
      3. Once a Month
   7. MySQL: Go Faster
   8. Summary
   9. Practice: Identify Slow Queries
3. 2. Indexes and Indexing
   1. Red Herrings of Performance
      1. Better, Faster Hardware!
      2. MySQL Tuning
   2. MySQL Indexes: A Visual Introduction
      1. InnoDB Tables Are Indexes
      2. Table Access Methods
      3. Leftmost Prefix Requirement
      4. EXPLAIN: Query Execution Plan
      5. WHERE
      6. GROUP BY
      7. ORDER BY
      8. Covering Indexes
      9. Join Tables
   3. Indexing: How to Think Like MySQL
      1. Know the Query
      2. Understand with EXPLAIN
      3. Optimize the Query
      4. Deploy and Verify
   4. It Was a Good Index Until…
      1. Queries Changed
      2. Excessive, Duplicate, and Unused
      3. Extreme Selectivity
      4. It’s a Trap! (When MySQL Chooses Another Index)
   5. Table Join Algorithms
   6. Summary
   7. Practice: Find Duplicate Indexes
4. 3. Data
   1. Three Secrets
      1. Indexes May Not Help
      2. Less Data Is Better
      3. Less QPS Is Better
   2. Principle of Least Data
      1. Data Access
      2. Data Storage
   3. Delete or Archive Data
      1. Tools
      2. Batch Size
      3. Row Lock Contention
      4. Space and Time
      5. The Binary Log Paradox
   4. Summary
   5. Practice: Audit Query Data Access
5. 4. Access Patterns
   1. MySQL Does Nothing
   2. Performance Destabilizes at the Limit
   3. Toyota and Ferrari
   4. Data Access Patterns
      1. Read/Write
      2. Throughput
      3. Data Age
      4. Data Model
      5. Transaction Isolation
      6. Read Consistency
      7. Concurrency
      8. Row Access
      9. Result Set
   5. Application Changes
      1. Audit the Code
      2. Offload Reads
      3. Enqueue Writes
      4. Partition Data
      5. Don’t Use MySQL
   6. Better, Faster Hardware?
   7. Summary
   8. Practice: Describe an Access Pattern
6. 5. Sharding
   1. Why a Single Database Does Not Scale
      1. Application Workload
      2. Benchmarks Are Synthetic
      3. Writes
      4. Schema Changes
      5. Operations
   2. Pebbles, Not Boulders
   3. Sharding: A Brief Introduction
      1. Shard Key
      2. Strategies
      3. Challenges
   4. Alternatives
      1. NewSQL
      2. Middleware
      3. Microservices
      4. Don’t Use MySQL
   5. Summary
   6. Practice: Four-Year Fit
7. 6. Server Metrics
   1. Query Performance Versus Server Performance
   2. Normal and Stable: The Best Database Is a Boring Database
   3. Key Performance Indicators
   4. Field of Metrics
      1. Response Time
      2. Rate
      3. Utilization
      4. Wait
      5. Error
      6. Access Pattern
      7. Internal
   5. Spectra
      1. Query Response Time
      2. Errors
      3. Queries
      4. Threads and Connections
      5. Temporary Objects
      6. Prepared Statements
      7. Bad SELECT
      8. Network Throughput
      9. Replication
      10. Data Size
      11. InnoDB
   6. Monitoring and Alerting
      1. Resolution
      2. Wild Goose Chase (Thresholds)
      3. Alert on User Experience and Objective Limits
      4. Cause and Effect
   7. Summary
   8. Practice: Review Key Performance Indicators
   9. Practice: Review Alerts and Thresholds
8. 7. Replication Lag
   1. Foundation
      1. Source to Replica
      2. Binary Log Events
      3. Replication Lag
   2. Causes
      1. Transaction Throughput
      2. Post-Failure Rebuild
      3. Network Issues
   3. Risk: Data Loss
      1. Asynchronous Replication
      2. Semisynchronous Replication
   4. Reducing Lag: Multithreaded Replication
   5. Monitoring
   6. Recovery Time
   7. Summary
   8. Practice: Monitor Subsecond Lag
9. 8. Transactions
   1. Row Locking
      1. Record and Next-Key Locks
      2. Gap Locks
      3. Secondary Indexes
      4. Insert Intention Locks
   2. MVCC and the Undo Logs
   3. History List Length
   4. Common Problems
      1. Large Transactions (Transaction Size)
      2. Long-Running Transactions
      3. Stalled Transactions
      4. Abandoned Transactions
   5. Reporting
      1. Active Transactions: Latest
      2. Active Transactions: Summary
      3. Active Transaction: History
      4. Committed Transactions: Summary
   6. Summary
   7. Practice: Alert on History List Length
   8. Practice: Examine Row Locks
10. 9. Other Challenges
    1. Split-Brain Is the Greatest Risk
    2. Data Drift Is Real but Invisible
    3. Don’t Trust ORM
    4. Schemas Always Change
    5. MySQL Extends Standard SQL
    6. Noisy Neighbors
    7. Applications Do Not Fail Gracefully
    8. High Performance MySQL Is Difficult
    9. Practice: Identify the Guardrails that Prevent Split-Brain
    10. Practice: Check for Data Drift
    11. Practice: Chaos
11. 10. MySQL in the Cloud
    1. Compatibility
    2. Management (DBA)
    3. Network and Storage…Latency
    4. Performance Is Money
    5. Summary
    6. Practice: Try MySQL in the Cloud
12. Index
13. About the Author