Cover image for Efficient MySQL Performance

Efficient MySQL Performance

Author Daniel Nichter
Release Date: 2021/12/01
ISBN: 9781098105082
Topic:
0%
17
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

You'll find several books on MySQL basics today, but only one that covers advanced MySQL performance--and nothing in between. That's because explaining MySQL performance without addressing its complexity is difficult. This practical book bridges the gap by teaching developers mid-level MySQL knowledge beyond the fundamentals, but well shy of deep-level internals required by DBAs.

Daniel Nichter shows you how to apply best practices and techniques that directly affect efficient MySQL performance. You'll learn how to focus on query response time, optimize queries and data to increase performance, and monitor and understand the most important MySQL metrics. You'll also discover how not to use MySQL, including situations where this database is clearly the wrong choice.

  • Understand why query response time is the North Star of MySQL performance
  • Learn why indexing, not hardware or MySQL config, is the key to performance
  • Examine query metrics in detail, including aggregation, reporting, and analysis
  • Explore ways of improving query response time through query optimization
  • Understand how to monitor MySQL and learn what the metrics mean

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
    4. Sources
    5. Aggregation
    6. Reporting
    7. Query Analysis
    8. Query Metrics
    9. Metadata and the Application
    10. Relative Values
    11. Average, Percentile, and Maximum
    12. Improving Query Response Time
    13. Direct Query Optimization
    14. Indirect Query Optimization
    15. When to Optimize Queries
    16. Performance Affects Customers
    17. Before and After Code Changes
    18. Once a Month
    19. MySQL: Go Faster
    20. Summary
    21. Practice: Identify Slow Queries
  3. 2. Indexes and Indexing
    1. Red Herrings of Performance
    2. Better, Faster Hardware!
    3. MySQL Tuning
    4. MySQL Indexes: A Visual Introduction
    5. InnoDB Tables Are Indexes
    6. Table Access Methods
    7. Leftmost Prefix Requirement
    8. EXPLAIN: Query Execution Plan
    9. WHERE
    10. GROUP BY
    11. ORDER BY
    12. Covering Index
    13. Join Tables
    14. Indexing: How to Think Like MySQL
    15. Know the Query
    16. Understand with EXPLAIN
    17. Optimize the Query
    18. Deploy and Verify
    19. It Was a Good Index Until…
    20. Queries Changed
    21. Too Many, Dupes, and Unused
    22. Extreme Selectivity
    23. It’s a Trap! (When MySQL Chooses Another Index)
    24. Table Join Algorithms
    25. Summary
    26. Practice: Find Duplicate Indexes
  4. 3. Data
    1. Three Secrets
    2. Indexes May Not Help
    3. Less Data Is Better
    4. Less QPS Is Better
    5. Principle of Least Data
    6. Data Access
    7. Data Storage
    8. Delete or Archive Data
    9. Tools
    10. Batch Size
    11. Row Lock Contention
    12. Space and Time
    13. The Binary Log Paradox
    14. Summary
    15. 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
    5. Read-Write
    6. Throughput
    7. Data Age
    8. Data Model
    9. Transaction Isolation
    10. Read Consistency
    11. Concurrency
    12. Row Access
    13. Result Set
    14. Application Changes
    15. Audit the Code
    16. Offload Reads
    17. Enqueue Writes
    18. Partition Data
    19. Don’t Use MySQL
    20. Better, Faster Hardware?
    21. Summary
    22. Practice: Describe an Access Pattern
  6. 5. Sharding
    1. Why a Single Database Does Not Scale
    2. Application Workload
    3. Benchmarks Are Synthetic
    4. Writes
    5. Schema Changes
    6. Operations
    7. Pebbles, Not Boulders
    8. Sharding: A Brief Introduction
    9. Shard Key
    10. Strategies
    11. Challenges
    12. Alternatives
    13. NewSQL
    14. Middleware
    15. Microservices
    16. Don’t Use MySQL
    17. Summary
    18. Practice: Four-Year Fit
  7. 6. Server Metrics
    1. Query Performance vs. Server Performance
    2. Normal and Stable: The Best Database Is a Boring Database
    3. Key Performance Indicators
    4. Field of Metrics
    5. Response Time
    6. Rate
    7. Utilization
    8. Wait
    9. Error
    10. Access Pattern
    11. Internal
    12. Spectra
    13. Query Response Time
    14. Errors
    15. Queries
    16. Threads and Connections
    17. Temporary Objects
    18. Prepared Statements
    19. Bad SELECT
    20. Network Throughput
    21. Replication
    22. Data Size
    23. InnoDB
    24. Monitoring and Alerting
    25. Resolution
    26. Wild Goose Chase (Thresholds)
    27. Alert on User Experience and Objective Limits
    28. Cause and Effect
    29. Summary
    30. Practice: Review Key Performance Indicators
    31. Practice: Review Alerts and Thresholds
  8. 7. Replication Lag
    1. Foundation
    2. Source to Replica
    3. Binary Log Events
    4. Replication Lag
    5. Causes
    6. Transaction Throughput
    7. Post-Failure Rebuild
    8. Network Issues
    9. Risk: Data Loss
    10. Asynchronous Replication
    11. Semi-synchronous Replication
    12. Reducing Lag: Multi-threaded Replica
    13. Monitoring
    14. Recovery Time
    15. Summary
    16. Practice: Monitor Sub-Second Lag
  9. 8. Transactions
    1. Row Locking
    2. Record and Next-key Locks
    3. Gap Locks
    4. Secondary Indexes
    5. Insert Intention Locks
    6. MVCC and the Undo Logs
    7. History List Length
    8. Common Problems
    9. Large Transactions (Transaction Size)
    10. Long-running Transactions
    11. Stalled Transactions
    12. Abandoned Transactions
    13. Reporting
    14. Active Transactions: Latest
    15. Active Transactions: Summary
    16. Active Transaction: History
    17. Committed Transactions: Summary
    18. Summary
    19. Practice: Alert on History List Length
    20. 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 an 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
18.218.127.141