Efficient MySQL Performance: Best Practices and Techniques

337 Pages·2021·9.449 MB·English

by  Daniel Nichter

Efficient MySQL Performance
Best Practices and Techniques
Daniel Nichter
BBeeiijjiinngg BBoossttoonn FFaarrnnhhaamm SSeebbaassttooppooll TTookkyyoo
Efficient MySQL Performance
by Daniel Nichter
Copyright © 2022 Daniel Nichter. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are
also available for most titles (http://oreilly.com). For more information, contact our corporate/institutional
sales department: 800-998-9938 or [email protected].
Acquisitions Editor: Andy Kwan Indexer: Amnet Systems LLC
Development Editor: Corbin Collins Interior Designer: David Futato
Production Editor: Katherine Tozer Cover Designer: Karen Montgomery
Copyeditor: Justin Billing Illustrator: Kate Dullea
Proofreader: Piper Editorial Consulting, LLC
December 2021: First Edition
Revision History for the First Edition
2021-11-30: First Release
See http://oreilly.com/catalog/errata.csp?isbn=9781098105099 for release details.
The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Efficient MySQL Performance, the cover
image, and related trade dress are trademarks of O’Reilly Media, Inc.
The views expressed in this work are those of the author, and do not represent the publisher’s views.
While the publisher and the author have used good faith efforts to ensure that the information and
instructions contained in this work are accurate, the publisher and the author disclaim all responsibility
for errors or omissions, including without limitation responsibility for damages resulting from the use
of or reliance on this work. Use of the information and instructions contained in this work is at your
own risk. If any code samples or other technology this work contains or describes is subject to open
source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use
thereof complies with such licenses and/or rights.
978-1-098-10509-9
[LSI]
Table of Contents
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
1. Query Response Time. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
A True Story of False Performance 2
North Star 3
Query Reporting 4
Sources 4
Aggregation 6
Reporting 8
Query Analysis 11
Query Metrics 11
Metadata and the Application 24
Relative Values 24
Average, Percentile, and Maximum 25
Improving Query Response Time 27
Direct Query Optimization 27
Indirect Query Optimization 28
When to Optimize Queries 29
Performance Affects Customers 29
Before and After Code Changes 29
Once a Month 30
MySQL: Go Faster 30
Summary 32
Practice: Identify Slow Queries 33
iii
2. Indexes and Indexing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Red Herrings of Performance 37
Better, Faster Hardware! 37
MySQL Tuning 39
MySQL Indexes: A Visual Introduction 40
InnoDB Tables Are Indexes 41
Table Access Methods 45
Leftmost Prefix Requirement 49
EXPLAIN: Query Execution Plan 51
WHERE 54
GROUP BY 60
ORDER BY 65
Covering Indexes 71
Join Tables 71
Indexing: How to Think Like MySQL 80
Know the Query 80
Understand with EXPLAIN 81
Optimize the Query 82
Deploy and Verify 83
It Was a Good Index Until… 84
Queries Changed 84
Excessive, Duplicate, and Unused 85
Extreme Selectivity 86
It’s a Trap! (When MySQL Chooses Another Index) 87
Table Join Algorithms 87
Summary 89
Practice: Find Duplicate Indexes 90
3. Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Three Secrets 92
Indexes May Not Help 92
Less Data Is Better 96
Less QPS Is Better 96
Principle of Least Data 97
Data Access 97
Data Storage 104
Delete or Archive Data 115
Tools 115
Batch Size 115
Row Lock Contention 118
iv | Table of Contents
Space and Time 118
The Binary Log Paradox 119
Summary 120
Practice: Audit Query Data Access 121
4. Access Patterns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
MySQL Does Nothing 124
Performance Destabilizes at the Limit 125
Toyota and Ferrari 130
Data Access Patterns 131
Read/Write 133
Throughput 133
Data Age 134
Data Model 136
Transaction Isolation 136
Read Consistency 137
Concurrency 138
Row Access 139
Result Set 139
Application Changes 140
Audit the Code 140
Offload Reads 141
Enqueue Writes 145
Partition Data 146
Don’t Use MySQL 147
Better, Faster Hardware? 148
Summary 150
Practice: Describe an Access Pattern 150
5. Sharding. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Why a Single Database Does Not Scale 152
Application Workload 152
Benchmarks Are Synthetic 155
Writes 156
Schema Changes 157
Operations 158
Pebbles, Not Boulders 159
Sharding: A Brief Introduction 160
Shard Key 161
Strategies 162
Challenges 167
Table of Contents | v
Alternatives 170
NewSQL 170
Middleware 171
Microservices 172
Don’t Use MySQL 172
Summary 173
Practice: Four-Year Fit 173
6. Server Metrics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Query Performance Versus Server Performance 177
Normal and Stable: The Best Database Is a Boring Database 180
Key Performance Indicators 181
Field of Metrics 182
Response Time 182
Rate 183
Utilization 183
Wait 184
Error 185
Access Pattern 186
Internal 186
Spectra 187
Query Response Time 188
Errors 190
Queries 191
Threads and Connections 196
Temporary Objects 199
Prepared Statements 200
Bad SELECT 201
Network Throughput 202
Replication 203
Data Size 203
InnoDB 205
Monitoring and Alerting 223
Resolution 223
Wild Goose Chase (Thresholds) 225
Alert on User Experience and Objective Limits 226
Cause and Effect 228
Summary 230
Practice: Review Key Performance Indicators 231
Practice: Review Alerts and Thresholds 232
vi | Table of Contents
7. Replication Lag. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
Foundation 234
Source to Replica 235
Binary Log Events 237
Replication Lag 238
Causes 240
Transaction Throughput 240
Post-Failure Rebuild 241
Network Issues 241
Risk: Data Loss 241
Asynchronous Replication 242
Semisynchronous Replication 244
Reducing Lag: Multithreaded Replication 246
Monitoring 250
Recovery Time 252
Summary 254
Practice: Monitor Subsecond Lag 255
8. Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Row Locking 260
Record and Next-Key Locks 262
Gap Locks 266
Secondary Indexes 269
Insert Intention Locks 273
MVCC and the Undo Logs 276
History List Length 280
Common Problems 282
Large Transactions (Transaction Size) 282
Long-Running Transactions 283
Stalled Transactions 284
Abandoned Transactions 285
Reporting 286
Active Transactions: Latest 286
Active Transactions: Summary 290
Active Transaction: History 291
Committed Transactions: Summary 292
Summary 294
Practice: Alert on History List Length 295
Practice: Examine Row Locks 296
Table of Contents | vii
9. Other Challenges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
Split-Brain Is the Greatest Risk 297
Data Drift Is Real but Invisible 299
Don’t Trust ORM 299
Schemas Always Change 300
MySQL Extends Standard SQL 301
Noisy Neighbors 301
Applications Do Not Fail Gracefully 302
High Performance MySQL Is Difficult 303
Practice: Identify the Guardrails that Prevent Split-Brain 303
Practice: Check for Data Drift 305
Practice: Chaos 306
10. MySQL in the Cloud. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Compatibility 308
Management (DBA) 310
Network and Storage…Latency 312
Performance Is Money 313
Summary 315
Practice: Try MySQL in the Cloud 315
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
viii | Table of Contents