Efficient MySQL Performance: Best Practices and Techniques

Description

You'll find several books on basic or advanced MySQL performance, but nothing in between. That's because explaining MySQL performance without addressing its complexity is difficult. This practical book bridges the gap by teaching software engineers mid-level MySQL knowledge beyond the fundamentals, but well shy of deep-level internals required by database administrators (DBAs). Daniel Nichter shows you how to apply the best practices and techniques that directly affect MySQL performance. You'll learn how to improve performance by analyzing query execution, indexing for common SQL clauses and table joins, optimizing data access, and understanding the most important MySQL metrics. You'll also discover how replication, transactions, row locking, and the cloud influenceMySQL performance. Understand why query response time is the North Star of MySQL performanceLearn query metrics in detail, including aggregation, reporting, and analysisSee how to index effectively for common SQL clauses and table joinsExplore the most important server metrics and what they reveal about performanceDive into transactions and row locking to gain deep, actionable insightAchieve remarkable MySQL performance at any scale Copyright Table of Contents Preface Conventions Used in This Book Using Code Examples O’Reilly Online Learning How to Contact Us Acknowledgments Chapter 1. Query Response Time A True Story of False Performance North Star Query Reporting Sources Aggregation Reporting Query Analysis Query Metrics Metadata and the Application Relative Values Average, Percentile, and Maximum Improving Query Response Time Direct Query Optimization Indirect Query Optimization When to Optimize Queries Performance Affects Customers Before and After Code Changes Once a Month MySQL: Go Faster Summary Practice: Identify Slow Queries Chapter 2. Indexes and Indexing Red Herrings of Performance Better, Faster Hardware! MySQL Tuning MySQL Indexes: A Visual Introduction InnoDB Tables Are Indexes Table Access Methods Leftmost Prefix Requirement EXPLAIN: Query Execution Plan WHERE GROUP BY ORDER BY Covering Indexes Join Tables Indexing: How to Think Like MySQL Know the Query Understand with EXPLAIN Optimize the Query Deploy and Verify It Was a Good Index Until… Queries Changed Excessive, Duplicate, and Unused Extreme Selectivity It’s a Trap! (When MySQL Chooses Another Index) Table Join Algorithms Summary Practice: Find Duplicate Indexes Chapter 3. Data Three Secrets Indexes May Not Help Less Data Is Better Less QPS Is Better Principle of Least Data Data Access Data Storage Delete or Archive Data Tools Batch Size Row Lock Contention Space and Time The Binary Log Paradox Summary Practice: Audit Query Data Access Chapter 4. Access Patterns MySQL Does Nothing Performance Destabilizes at the Limit Toyota and Ferrari Data Access Patterns Read/Write Throughput Data Age Data Model Transaction Isolation Read Consistency Concurrency Row Access Result Set Application Changes Audit the Code Offload Reads Enqueue Writes Partition Data Don’t Use MySQL Better, Faster Hardware? Summary Practice: Describe an Access Pattern Chapter 5. Sharding Why a Single Database Does Not Scale Application Workload Benchmarks Are Synthetic Writes Schema Changes Operations Pebbles, Not Boulders Sharding: A Brief Introduction Shard Key Strategies Challenges Alternatives NewSQL Middleware Microservices Don’t Use MySQL Summary Practice: Four-Year Fit Chapter 6. Server Metrics Query Performance Versus Server Performance Normal and Stable: The Best Database Is a Boring Database Key Performance Indicators Field of Metrics Response Time Rate Utilization Wait Error Access Pattern Internal Spectra Query Response Time Errors Queries Threads and Connections Temporary Objects Prepared Statements Bad SELECT Network Throughput Replication Data Size InnoDB Monitoring and Alerting Resolution Wild Goose Chase (Thresholds) Alert on User Experience and Objective Limits Cause and Effect Summary Practice: Review Key Performance Indicators Practice: Review Alerts and Thresholds Chapter 7. Replication Lag Foundation Source to Replica Binary Log Events Replication Lag Causes Transaction Throughput Post-Failure Rebuild Network Issues Risk: Data Loss Asynchronous Replication Semisynchronous Replication Reducing Lag: Multithreaded Replication Monitoring Recovery Time Summary Practice: Monitor Subsecond Lag Chapter 8. Transactions Row Locking Record and Next-Key Locks Gap Locks Secondary Indexes Insert Intention Locks MVCC and the Undo Logs History List Length Common Problems Large Transactions (Transaction Size) Long-Running Transactions Stalled Transactions Abandoned Transactions Reporting Active Transactions: Latest Active Transactions: Summary Active Transaction: History Committed Transactions: Summary Summary Practice: Alert on History List Length Practice: Examine Row Locks Chapter 9. Other Challenges Split-Brain Is the Greatest Risk Data Drift Is Real but Invisible Don’t Trust ORM Schemas Always Change MySQL Extends Standard SQL Noisy Neighbors Applications Do Not Fail Gracefully High Performance MySQL Is Difficult Practice: Identify the Guardrails that Prevent Split-Brain Practice: Check for Data Drift Practice: Chaos Chapter 10. MySQL in the Cloud Compatibility Management (DBA) Network and Storage…Latency Performance Is Money Summary Practice: Try MySQL in the Cloud Index About the Author Colophon