High Performance MySQL Proven Strategies for Operating at Scale, 4th Edition (Silvia Botros, Jeremy Tinley) (Z-Library)

Silvia Botros & Jeremy Tinley Foreword by Jeremy Cole Fourth Edition High Performance MySQL Proven Strategies for Operating at Scale

Praise for High Performance MySQL, 4th Edition I love that this new edition shifts the book’s emphasis to a modern, pragmatic mindset of team players delivering business value. It moves past the previous editions’ myopic focus on gnarly internals and theory to wring out diminishing returns, toward a more holistic perspective. The ‘how databases work’ material is still covered thoroughly, but now with a fresh, humanistic take that is greatly needed. —Baron Schwartz, Lead author of High Performance MySQL, 2nd and 3rd editions High Performance MySQL has been a staple of the MySQL world since the first edition 17 years ago. MySQL moves ever onward, and Silvia and Jeremy have done an excellent job bringing this essential work up to date with modern MySQL. —Jeremy Cole Updated to meet modern practices, this latest edition is rich with solid advice for MySQL administrators and developers. —Shlomi Noach, Database Engineer, PlanetScale

High Performance MySQL has a new focus. It’s no longer about squeezing every ounce of power from MySQL. We now have a large ecosystem of tools and providers. Silvia and Jeremy beautifully cover how MySQL fits into the new picture. This book is a must-have if you run MySQL in any form. —Sugu Sougoumarane, CTO of PlanetScale, cocreator of Vitess Silvia and Jeremy did a fantastic job keeping the original spirit of the book strong while updating it to cover the rapidly changing MySQL space. —Peter Zaitsev, Founder and CEO of Percona and coauthor of High Performance MySQL, 3rd edition

Silvia Botros and Jeremy Tinley Foreword by Jeremy Cole High Performance MySQL Proven Strategies for Operating at Scale FOURTH EDITION Boston Farnham Sebastopol TokyoBeijing

978-1-492-08051-0 [LSI] High Performance MySQL by Silvia Botros and Jeremy Tinley Copyright © 2022 Silvia Botros and Jeremy Tinley. 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 corporate@oreilly.com. Editors: Virginia Wilson and Andy Kwan Production Editor: Elizabeth Faerm Copyeditor: Shannon Turlington Proofreader: Kim Cofer Indexer: Judith McConville Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea November 2021: Fourth Edition Revision History for the Fourth Edition 2021-11-17: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781492080510 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. High Performance MySQL, 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(s) and do not represent the publisher’s views. While the publisher and the author(s) have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the author(s) disclaim all responsibil‐ ity 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.

Table of Contents Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv 1. MySQL Architecture. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 MySQL’s Logical Architecture 1 Connection Management and Security 2 Optimization and Execution 3 Concurrency Control 3 Read/Write Locks 4 Lock Granularity 4 Transactions 6 Isolation Levels 8 Deadlocks 9 Transaction Logging 10 Transactions in MySQL 11 Multiversion Concurrency Control 13 Replication 15 Datafiles Structure 16 The InnoDB Engine 16 JSON Document Support 17 Data Dictionary Changes 17 Atomic DDL 18 Summary 18 2. Monitoring in a Reliability Engineering World. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 The Impact of Reliability Engineering on DBA Teams 20 Defining Service Level Goals 20 v

What Does It Take to Make Customers Happy? 22 What to Measure 23 Defining SLIs and SLOs 23 Monitoring Solutions 24 Monitoring Availability 25 Monitoring Query Latency 27 Monitoring for Errors 27 Proactive Monitoring 29 Measuring Long-Term Performance 36 Learning Your Business Cadence 36 Tracking Your Metrics Effectively 37 Using Monitoring Tools to Inspect the Performance 37 Using SLOs to Guide Your Overall Architecture 38 Summary 39 3. Performance Schema. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Introduction to Performance Schema 41 Instrument Elements 42 Consumer Organization 44 Resource Consumption 45 Limitations 46 sys Schema 46 Understanding Threads 46 Configuration 48 Enabling and Disabling Performance Schema 48 Enabling and Disabling Instruments 48 Enabling and Disabling Consumers 50 Tuning Monitoring for Specific Objects 51 Tuning Threads Monitoring 51 Adjusting Memory Size for Performance Schema 52 Defaults 53 Using Performance Schema 53 Examining SQL Statements 53 Examining Read Versus Write Performance 62 Examining Metadata Locks 63 Examining Memory Usage 64 Examining Variables 66 Examining Most Frequent Errors 70 Examining Performance Schema Itself 71 Summary 73 vi | Table of Contents

4. Operating System and Hardware Optimization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 What Limits MySQL’s Performance? 75 How to Select CPUs for MySQL 76 Balancing Memory and Disk Resources 76 Caching, Reads, and Writes 76 What’s Your Working Set? 77 Solid-State Storage 78 An Overview of Flash Memory 78 Garbage Collection 79 RAID Performance Optimization 79 RAID Failure, Recovery, and Monitoring 81 RAID Configuration and Caching 83 Network Configuration 86 Choosing a Filesystem 87 Choosing a Disk Queue Scheduler 89 Memory and Swapping 90 Operating System Status 92 Other Helpful Tools 95 Summary 96 5. Optimizing Server Settings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 How MySQL’s Configuration Works 100 Syntax, Scope, and Dynamism 101 Persisted System Variables 103 Side Effects of Setting Variables 103 Planning Your Variable Changes 104 What Not to Do 105 Creating a MySQL Configuration File 106 Minimal Configuration 107 Inspecting MySQL Server Status Variables 108 Configuring Memory Usage 109 Per-Connection Memory Needs 109 Reserving Memory for the Operating System 109 The InnoDB Buffer Pool 110 The Thread Cache 111 Configuring MySQL’s I/O Behavior 112 The InnoDB Transaction Log 113 Log Buffer 113 The InnoDB Tablespace 115 Other I/O Configuration Options 118 Configuring MySQL Concurrency 119 Safety Settings 120 Table of Contents | vii

Advanced InnoDB Settings 122 Summary 124 6. Schema Design and Management. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Choosing Optimal Data Types 126 Whole Numbers 127 Real Numbers 127 String Types 128 Date and Time Types 135 Bit-Packed Data Types 136 JSON Data 139 Choosing Identifiers 142 Special Types of Data 144 Schema Design Gotchas in MySQL 145 Too Many Columns 145 Too Many Joins 145 The All-Powerful ENUM 145 The ENUM in Disguise 146 NULL Not Invented Here 146 Schema Management 146 Schema Management as Part of the Data Store Platform 147 Summary 154 7. Indexing for High Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Indexing Basics 156 Types of Indexes 156 Benefits of Indexes 161 Indexing Strategies for High Performance 162 Prefix Indexes and Index Selectivity 162 Multicolumn Indexes 165 Choosing a Good Column Order 167 Clustered Indexes 170 Covering Indexes 178 Using Index Scans for Sorts 180 Redundant and Duplicate Indexes 182 Unused Indexes 185 Index and Table Maintenance 186 Finding and Repairing Table Corruption 186 Updating Index Statistics 187 Reducing Index and Data Fragmentation 188 Summary 189 viii | Table of Contents

8. Query Performance Optimization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 Why Are Queries Slow? 191 Slow Query Basics: Optimize Data Access 192 Are You Asking the Database for Data You Don’t Need? 192 Is MySQL Examining Too Much Data? 194 Ways to Restructure Queries 198 Complex Queries Versus Many Queries 198 Chopping Up a Query 199 Join Decomposition 200 Query Execution Basics 201 The MySQL Client/Server Protocol 202 Query States 204 The Query Optimization Process 205 The Query Execution Engine 217 Returning Results to the Client 218 Limitations of the MySQL Query Optimizer 219 UNION Limitations 219 Equality Propagation 220 Parallel Execution 220 SELECT and UPDATE on the Same Table 220 Optimizing Specific Types of Queries 221 Optimizing COUNT() Queries 221 Optimizing JOIN Queries 223 Optimizing GROUP BY with ROLLUP 223 Optimizing LIMIT and OFFSET 223 Optimizing SQL_CALC_FOUND_ROWS 225 Optimizing UNION 225 Summary 226 9. Replication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227 Replication Overview 227 How Replication Works 229 Replication Under the Hood 230 Choosing Replication Format 230 Global Transaction Identifiers 231 Making Replication Crash Safe 232 Delayed Replication 233 Multithreaded Replication 234 Semisynchronous Replication 237 Replication Filters 237 Replication Failover 239 Planned Promotions 239 Table of Contents | ix

Unplanned Promotions 240 Trade-Offs of Promotion 240 Replication Topologies 241 Active/Passive 241 Active/Read Pool 242 Discouraged Topologies 244 Replication Administration and Maintenance 247 Monitoring Replication 247 Measuring Replication Lag 248 Determining Whether Replicas Are Consistent with the Source 249 Replication Problems and Solutions 251 Binary Logs Corrupted on the Source 251 Nonunique Server IDs 251 Undefined Server IDs 252 Missing Temporary Tables 252 Not Replicating All Updates 252 Excessive Replication Lag 252 Oversized Packets from the Source 254 No Disk Space 254 Replication Limitations 254 Summary 255 10. Backup and Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257 Why Backups? 258 Defining Recovery Requirements 259 Designing a MySQL Backup Solution 260 Online or Offline Backups? 261 Logical or Raw Backups? 263 What to Back Up 265 Incremental and Differential Backups 266 Replication 268 Managing and Backing Up Binary Logs 269 Backup and Recovery Tools 269 MySQL Enterprise Backup 269 Percona XtraBackup 270 mydumper 270 mysqldump 270 Backing Up Data 270 Logical SQL Backups 270 Filesystem Snapshots 272 Percona XtraBackup 278 Recovering from a Backup 281 x | Table of Contents

Restoring Logical Backups 282 Restoring Raw Files from Snapshot 283 Restoring with Percona XtraBackup 284 Starting MySQL After Restoring Raw Files 285 Summary 286 11. Scaling MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 What Is Scaling? 287 Read- Versus Write-Bound Workloads 289 Understanding Your Workload 289 Read-Bound Workloads 290 Write-Bound Workloads 291 Functional Sharding 291 Scaling Reads with Read Pools 292 Managing Configuration for Read Pools 294 Health Checks for Read Pools 295 Choosing a Load-Balancing Algorithm 297 Queuing 298 Scaling Writes with Sharding 299 Choosing a Partitioning Scheme 300 Multiple Partitioning Keys 302 Querying Across Shards 302 Vitess 303 ProxySQL 306 Summary 311 12. MySQL in the Cloud. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 Managed MySQL 313 Amazon Aurora for MySQL 314 GCP Cloud SQL 317 MySQL on Virtual Machines 318 Machine Types in Cloud 318 Choosing the Right Machine Type 319 Choosing the Right Disk Type 320 Additional Tips 322 Summary 324 13. Compliance with MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325 What Is Compliance? 326 Service Organization Controls Type 2 326 Sarbanes–Oxley Act 326 Payment Card Industry Data Security Standard 327 Table of Contents | xi

Health Insurance Portability and Accountability Act 327 Federal Risk and Authorization Management Program 327 General Data Protection Regulation 327 Schrems II 328 Building for Compliance Controls 328 Secrets Management 329 Separation of Roles and Data 332 Tracking Changes 333 Backup and Restore Procedures 338 Summary 341 A. Upgrading MySQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343 B. MySQL on Kubernetes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353 xii | Table of Contents

Foreword A fresh copy of High Performance MySQL has been the first book to get plopped down on the desk of every newly-hired DBA, systems engineer, or database-minded developer since it came out nearly two decades ago. When Jeremy Zawodny and Derek Balling set out to write a book about running MySQL at scale, to bring clarity and structure to years of mystery, it was destined to become an instant classic in the MySQL world. Through the years and several updates, some of the content of the original and the subsequent updates has held up, and some not so much. MySQL itself has advanced, the MySQL community has changed a lot, and the ways in which we use MySQL have changed. Now in the 4th edition, Silvia and Jeremy undertake a thankless and gargantuan task to update this classic for the modern era— and they are just the pair for the task. In my time (now more than 20 years!) in the MySQL community, the one consistent thing has been, well, inconsistency. Everyone uses MySQL (and databases in general) in slightly different ways, and they each have different expectations of it. Everyone makes some good decisions, some well-intentioned but questionable decisions, and, always, their share of bad ones. Sometimes, progress is easy, but sometimes it takes sage advice and a new way of thinking about the problem learned straight from an expert. Silvia and Jeremy are just such experts. Everything from MySQL architecture, optimi‐ zation, replication, backups, and more, stood to benefit from them sharing their extensive experience in the trenches with MySQL. In this new 4th edition, many sub‐ jects got new treatment, a lot of outdated material was removed, errors were correc‐ ted, and a new and fresh style was brought to the material. Like the original (now vintage, and quaintly small) 1st edition, the 4th edition prom‐ ises to help carry the newest generation of developers, DBAs, and their bosses into xiii

the new world of MySQL; sometimes with excitement, but perhaps sometimes kick‐ ing and screaming. Thanks, Silvia and Jeremy, for your hard work to nurture the next generation of MySQL geeks who will be keeping the world’s data safe and the world’s top websites and other data-driven systems performing at their peak. Congratulations on getting this done through COVID and everything else. The rest of us will make sure to get all the new DBAs a copy. — Jeremy Cole near Reno, Nevada October 2021 xiv | Foreword

Preface The official documentation maintained by Oracle gives you the knowledge necessary to install, configure, and interact with MySQL. This book serves as a companion to that documentation, helping you understand how best to leverage MySQL as a pow‐ erful data platform for your use case. This edition also expands on the growing role of compliance and security as parts of operating a database footprint. New realities such as privacy laws and data sover‐ eignty have changed how companies build their products, and that naturally introdu‐ ces new complexities in how the technical architecture evolves. Who This Book Is For This book is first and foremost for engineers looking to grow their expertise in run‐ ning MySQL. This edition assumes its audience is familiar with the basic principles of why you want to use a relational database management system (RDBMS). We also assume some experience with general system administration, networking, and oper‐ ating systems. We will offer you proven strategies for running MySQL at scale with a modern archi‐ tecture and more up-to-date tooling and practices. Ultimately, we hope that the knowledge you gain from this book of MySQL’s internals and scaling strategies will help you in scaling the data storage layer at your organiza‐ tion. And we hope that your newfound insight will help you to learn and practice a methodical approach to designing, maintaining, and troubleshooting an architecture that is built on MySQL. xv

1 Famously, people often used MySQL as a queue and then learned the hard way why it was bad. The most cited reasons were the overhead of polling for new queue actions, the management of locking records for process‐ ing, and the unwieldy size of queue tables as data grows over time. What Is Different in This Edition High Performance MySQL has been a part of the database engineering community for years, with past editions released in 2004, 2008, and 2012. In these previous editions, the goal was always to teach developers and administrators how to optimize MySQL for every drop of performance by focusing on deep internal design, explaining what various tuning settings mean, and arming the user with the knowledge to be effective in changing these settings. This edition maintains the same goal but with a different focus. Since the third edition, the MySQL ecosystem has seen a lot of changes. Three new major versions have been released. The tooling landscape expanded significantly beyond Perl and Bash scripts and into full-fledged tooling solutions. Entirely new open source projects have been built that change how organizations manage scaling MySQL. Even the traditional database administrator (DBA) role has evolved. There’s an old joke in the industry that says that DBA stands for “Don’t Bother Asking.” DBAs had a reputation for being speed bumps in the software development life cycle (SDLC), not explicitly because of any curmudgeonly attitude, but simply because databases weren’t evolving as fast as the rest of the SDLC around them. With books like Database Reliability Engineering: Designing and Operating Resilient Database Systems by Laine Campbell and Charity Majors (O’Reilly), it has become the new reality that technical organizations look to database engineers more as enablers of business growth and less as the sole operators of all databases. Where once a DBA’s primary day-to-day involved schema design and query optimization, they now are responsible for teaching those skills to developers and managing systems that allow developers to deploy their own schema changes quickly and safely. With these changes, the focus should no longer be on optimizing MySQL to get a few percentage points faster. We think that High Performance MySQL is now about giving people the information they need to make educated decisions about how to best use MySQL. This begins by understanding how MySQL is designed, which gives way to understanding what MySQL is and is not good at.1 Modern releases of MySQL offer reasonably sane defaults, and there’s very little tuning you need to do unless you’re experiencing a very specific scaling problem. Modern teams are now dealing with schema changes, compliance issues, and sharding. We want High Performance MySQL to be a comprehensive guide to how modern companies run MySQL at scale. xvi | Preface

Conventions Used in This Book The following typographical conventions are used in this book: Italic Indicates new terms, URLs, email addresses, filenames, and file extensions. Constant width Used for program listings, as well as within paragraphs to refer to program ele‐ ments such as variable or function names, databases, data types, environment variables, statements, and keywords. Constant width bold Shows commands or other text that should be typed literally by the user. Constant width italic Shows text that should be replaced with user-supplied values or by values deter‐ mined by context. This icon signifies a tip or suggestion. This icon signifies a general note. This icon indicates a warning or caution. Preface | xvii

O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technology and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit http://oreilly.com. How to Contact Us Please address comments and questions concerning this book to the publisher: O’Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-998-9938 (in the United States or Canada) 707-829-0515 (international or local) 707-829-0104 (fax) We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/hiperfmysql_2e. Email bookquestions@oreilly.com to comment or ask technical questions about this book. For news and information about our books and courses, visit http://oreilly.com. Find us on Facebook: http://facebook.com/oreilly Follow us on Twitter: http://twitter.com/oreillymedia Watch us on YouTube: http://www.youtube.com/oreillymedia xviii | Preface