Efficient MySQL Performance: Best Practices and Techniques

✍ Scribed by Daniel Nichter

• Publisher: O'Reilly Media
• Year: 2021
• Tongue: English
• Leaves: 337
• Edition: 1
• Category: Library

✦ Synopsis

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 performance
• Learn query metrics in detail, including aggregation, reporting, and analysis
• See how to index effectively for common SQL clauses and table joins
• Explore the most important server metrics and what they reveal about performance
• Dive into transactions and row locking to gain deep, actionable insight
• Achieve remarkable MySQL performance at any scale

✦ Table of Contents

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