Efficient MySQL Performance
Published on 30. November 2021
338 pages
978-1-0981-0504-4 (ISBN)
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 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
More details
Other editions
Additional editions
Book
12/2021
O'Reilly
€66.00
Article not available at the moment
Content
- Intro
- 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
