Efficient MySQL Performance Best Practices and Techniques (Daniel Nichter) (Z-Library)

📄 File Format: PDF

💾 File Size: 9.4 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

(This page has no text content)

📄 Page 3

Daniel Nichter Efficient MySQL Performance Best Practices and Techniques Boston Farnham Sebastopol TokyoBeijing

📄 Page 4

978-1-098-10509-9 [LSI] 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 corporate@oreilly.com. Acquisitions Editor: Andy Kwan Development Editor: Corbin Collins Production Editor: Katherine Tozer Copyeditor: Justin Billing Proofreader: Piper Editorial Consulting, LLC Indexer: Amnet Systems LLC Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: Kate Dullea 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.

📄 Page 5

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

📄 Page 6

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

📄 Page 7

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

📄 Page 8

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

📄 Page 9

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

📄 Page 10

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

📄 Page 11

Preface A gap in MySQL literature exists between basic MySQL knowledge and advanced MySQL performance. There are several books about the former, and one book about the latter: High Performance MySQL, 4th Edition, by Silvia Botros and Jeremy Tinley (O’Reilly). This is the first book to bridge the gap. The gap exists because MySQL is complex, and it’s difficult to teach performance without addressing that complexity—the proverbial elephant in the room. But engi‐ neers using (not managing) MySQL should not need to become MySQL experts to achieve remarkable MySQL performance. To bridge the gap, this book is unapologeti‐ cally efficient—pay no attention to the elephant; it’s friendly. Efficient MySQL performance means focus: learning and applying only the best practices and techniques that directly affect remarkable MySQL performance. Focus dramatically narrows the scope of MySQL complexity and allows me to show you a much simpler and faster path through the vast and complex field of MySQL performance. The journey begins with the first sentence of Chapter 1, “Performance is query response time.” From there, we move fast through indexes, data, access patterns, and a lot more. On a scale of one to five—where one is an introduction for anyone, and five is a deep dive for aspiring experts—this book ranges from three to four: deep, but far from the bottom. I presume that you’re an experienced engineer who has basic knowledge of and experience with a relational database (MySQL or otherwise), so I do not explain SQL or database fundamentals. I presume that you’re an accomplished programmer who is responsible for one or more applications that use MySQL, so I continually reference the application and trust that you know the details of your application. I also presume that you’re familiar with computers in general, so I talk freely about hardware, software, networks, and so forth. Since this book focuses on MySQL performance for engineers using MySQL, not managing it, a few references to MySQL configuration are made when necessary but not explained. For help configuring MySQL, ask a DBA where you work. If ix

📄 Page 12

you don’t have a DBA, hire a MySQL consultant—there are many great consultants with affordable contract options. You can also learn by reading the MySQL Reference Manual. The MySQL manual is superb and experts use it all the time, so you’re in good company. 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 elements 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 element signifies a tip or suggestion. This element signifies a general note. This element indicates a warning or caution. x | Preface

📄 Page 13

Using Code Examples Supplemental material (code examples, exercises, etc.) is available for download at https://github.com/efficient-mysql-performance. If you have a technical question or a problem using the code examples, please email bookquestions@oreilly.com. This book is here to help you get your job done. In general, if example code is offered with this book, you may use it in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission. We appreciate, but generally do not require, attribution. An attribution usu‐ ally includes the title, author, publisher, and ISBN. For example: “Efficient MySQL Performance by Daniel Nichter (O’Reilly). Copyright 2022 Daniel Nichter, 978-1-098-10509-9.” If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com. O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technol‐ ogy 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. Preface | xi

📄 Page 14

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/efficient-mysql-performance. 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. Follow us on Twitter: http://twitter.com/oreillymedia Watch us on YouTube: http://youtube.com/oreillymedia Acknowledgments Thank you to the MySQL experts who reviewed this book: Vadim Tkachenko, Fréd‐ éric Descamps, and Fernando Ipar. Thank you to the MySQL experts who reviewed parts of this book: Marcos Albe, Jean-François Gagné and Kenny Gryp. Thank you to many other MySQL experts who have helped me, taught me, and provided opportunities over the years: Peter Zaitsev, Baron Schwartz, Ryan Lowe, Bill Karwin, Emily Slocombe, Morgan Tocker, Shlomi Noach, Jeremy Cole, Laurynas Biveinis, Mark Callaghan, Domas Mituzas, Ronald Bradford, Yves Trudeau, Sveta Smirnova, Alexey Kopytov, Jay Pipes, Stewart Smith, Aleksandr Kuzminsky, Alexander Rubin, Roman Vynar, and—again—Vadim Tkachenko. Thank you to O’Reilly and my editors: Corbin Collins, Katherine Tozer, Andy Kwan, and all the people behind the scenes. And thank you to my wife, Moon, who supported me during the time-consuming process of writing this book. xii | Preface

📄 Page 15

1 Latency is delay inherent in the system. Query response time is not a delay inherent in MySQL; it comprises various latencies: network, storage, and so on. CHAPTER 1 Query Response Time Performance is query response time. This book explores that idea from various angles with a single intent: to help you achieve remarkable MySQL performance. Efficient MySQL performance means focus‐ ing on the best practices and techniques that directly affect MySQL performance—no superfluous details or deep internals required by DBAs and experts. I presume that you’re a busy professional who is using MySQL, not managing it, and that you need the most results for the least effort. That’s not laziness, that’s efficiency. To that end, this book is direct and to the point. And by the end, you will be able to achieve remarkable MySQL performance. MySQL performance is a complex and multifaceted subject, but you do not need to become an expert to achieve remarkable performance. I narrow the scope of MySQL complexity by focusing on the essentials. MySQL performance begins with query response time. Query response time is how long it takes MySQL to execute a query. Synonymous terms are: response time, query time, execution time, and (inaccurately) query latency.1 Timing starts when MySQL receives the query and ends when it has sent the result set to the client. Query response time comprises many stages (steps during query execution) and waits (lock waits, I/O waits, and so on), but a complete and detailed breakdown is neither possible nor necessary. As with many systems, basic trouble‐ shooting and analysis reveal the majority of problems. 1

📄 Page 16

Performance increases as query response time decreases. Improving query response time is synonymous with reducing query response time. This chapter is the foundation. It expounds query response time so that, in subse‐ quent chapters, you can learn how to improve it. There are seven major sections. The first is a true story to motivate and amuse. The second discusses why query response time is the North Star of MySQL performance. The third outlines how query metrics are transformed into meaningful reports: query reporting. The fourth addresses query analysis: using query metrics and other information to understand query execution. The fifth maps out the journey of improving query response time: query optimization. The sixth gives an honest and modest schedule for optimizing queries. The seventh discusses why MySQL cannot simply go faster—why query optimization is necessary. A True Story of False Performance In 2004, I was working the night shift at a data center—2 p.m. to midnight. It was a great job for two reasons. First, the only employees in the data center after 5 p.m. were a handful of engineers monitoring and managing thousands of physical servers for an undisclosed number of customers and websites—probably tens of thousands of websites. It was an engineer’s dream. Second, there were countless MySQL servers that always had problems to fix. It was a gold mine of learning and opportunity. But at the time, there were few books, blogs, or tools about MySQL. (Though that same year, O’Reilly published the first edition of High Performance MySQL.) Consequently, the state of the art for “fixing” MySQL performance problems was “sell the customer more RAM.” For sales and management it always worked, but for MySQL the results were inconsistent. One night I decided not to sell the customer more RAM and, instead, to do a technical deep dive to find and fix the true root cause of their MySQL performance problem. Their database was powering a bulletin board which had slowed to a crawl under the weight of its success—still a common problem today, almost 20 years later. To make a long story short, I found a single query missing a critical index. After properly indexing the query, performance improved dramatically and the website was saved. It cost the customer zero dollars. Not all performance problems and solutions are that straightforward and glamorous. But just shy of 20 years’ experience with MySQL has taught me (and many others) that MySQL performance problems are very often solved by the best practices and techniques in this book. 2 | Chapter 1: Query Response Time

📄 Page 17

North Star I’m a MySQL DBA and a software engineer, so I know what it’s like working with MySQL as the latter. Especially when it comes to performance, we (software engi‐ neers) just want it (MySQL) to work. Between shipping features and putting out fires, who has time for MySQL performance? And when MySQL performance is poor—or worse: when it suddenly becomes poor—the way forward can be difficult to see because there are many considerations: where do we begin? Do we need more RAM? Faster CPUs? More storage IOPS? Is the problem a recent code change? (Fact: code changes deployed in the past can cause performance problems in the future, sometimes days in the future.) Is the problem a noisy neighbor? Are the DBAs doing something to the database? Has the app has gone viral and it’s the good kind of problem to have? As an engineer whose expertise is the application, not MySQL, that situation can be overwhelming. To move forward confidently, start by looking at query response time because it is meaningful and actionable. These are powerful qualities that lead to real solutions: Meaningful Query response time is the only metric anyone truly cares about because, let’s be honest, when the database is fast, nobody looks at it or asks questions. Why? Because query response time is the only metric we experience. When a query takes 7.5 seconds to execute, we experience 7.5 seconds of impatience. That same query might examine a million rows, but we don’t experience a million rows examined. Our time is precious. Actionable There’s so much you can do to improve query response time and make everyone happy again that you’re holding a book about it. (Do people still hold books in the future? I hope so.) Query response time is directly actionable because you own the code, so you can change the queries. Even if you don’t own the code (or have access to it), you can still indirectly optimize query response time. “Improving Query Response Time” on page 27 addresses direct and indirect query optimization. Focus on improving query response time—the North Star of MySQL performance. Do not begin by throwing hardware at the problem. Begin by using query metrics to determine what MySQL is doing, then analyze and optimize slow queries to reduce response time, and repeat. Performance will improve. North Star | 3

📄 Page 18

Query Reporting Query metrics provide invaluable insights into query execution: response time, lock time, rows examined, and so on. But query metrics, like all metrics, are raw values that need to be collected, aggregated, and reported in a way that’s meaningful to (and readable for) engineers. That’s what this section outlines: how query metric tools transform query metrics into query reports. But query reporting is only a means to an end, as discussed in “Query Analysis” on page 11. Looking ahead, query analysis is the real work: analyzing query metrics (as reported) and other information with the goal of understanding query execution. To improve MySQL performance, you must optimize queries. To optimize queries, you must understand how they execute. And to understand that, you must analyze them with pertinent information, including query reports and metadata. But first you need to understand query reporting, since it represents the trove of query metrics that provide invaluable insights into query execution. The next three sections teach you about the following: • Sources: query metrics originate from two sources and vary by MySQL distribu‐ tion and version • Aggregation: query metric values are grouped and aggregated by normalized SQL statements • Reporting: query reports are organized by a high-level profile and a query- specific report Then you’re ready for “Query Analysis” on page 11. This is not a book about database administration, so this section does not discuss the setup and configuration of query metrics in MySQL. I presume this is already done or will be done. If not, don’t worry: ask your DBA, hire a consultant, or learn how by reading the MySQL manual. Sources Query metrics originate from the slow query log or the Performance Schema. As the names indicate, the former is a log file on disk, and the latter is a database with the same name: performance_schema. Although completely different in nature (log file on disk as opposed to tables in a database), both provide query metrics. The important difference is how many metrics they provide: apart from query response time, which both provide, the number of metrics ranges from 3 to more than 20. 4 | Chapter 1: Query Response Time

📄 Page 19

The name slow query log is historical. Long ago, MySQL logged only queries that took greater than N seconds to execute, and the minimum value for N was 1. Old versions of MySQL would not log a query that took 900 milliseconds to execute because that was “fast.” The slow query log really earned its name. Today, the minimum value is zero with a resolution of microseconds. When set to zero, MySQL logs every query executed. Therefore, the name is a little misleading, but now you know why. All things considered, the Performance Schema is the best source of query metrics because it exists in every current version and distribution of MySQL, it works locally and in the cloud, it provides all nine metrics covered in “Query Metrics” on page 11, and it’s the most consistent. Plus, the Performance Schema contains a wealth of other data for deep MySQL analysis, so its usefulness extends far beyond query metrics. The slow query log is a good source, too, but it varies considerably: MySQL As of MySQL 8.0.14, enable system variable log_slow_extra and the slow query log provides six of the nine metrics in “Query Metrics” on page 11, lacking only Rows_affected, Select_scan, and Select_full_join. It’s still a good source, but use the Performance Schema if possible. Before MySQL 8.0.14, which includes MySQL 5.7, the slow query log is bare bones, providing only Query_time, Lock_time, Rows_sent, and Rows_examined. You can still analyze queries with only these four metrics, but the analysis is much less insightful. Consequently, avoid the slow query log before MySQL 8.0.14 and instead use the Performance Schema. Percona Server Percona Server provides a significantly greater number of metrics in the slow query log when system variable log_slow_verbosity is configured: all nine metrics covered in “Query Metrics” on page 11 and more. It also sup‐ ports query sampling (logging a percentage of queries) when system variable log_slow_rate_limit is configured, which is helpful for busy servers. These features make the Percona Server slow query log a great source. See “Slow Query Log” in the Percona Server manual for details. MariaDB Server MariaDB Server 10.x uses the Percona Server slow query log enhancements, but there are two notable differences: system variable log_slow_verbosity is config‐ ured differently in MariaDB, and it does not provide metric Rows_affected. Otherwise, it’s essentially the same and a great source, too. See “Slow Query Log Extended Statistics” in the MariaDB knowledge base for details. Query Reporting | 5

📄 Page 20

The slow query log is disabled by default, but you can enable it dynamically (without restarting MySQL). The Performance Schema should be enabled by default, though some cloud providers disable it by default. Unlike the slow query log, the Perfor‐ mance Schema cannot be enabled dynamically—you must restart MySQL to enable it. Make sure the best query metric source is used and properly configured. Ask your DBA, hire a consultant, or learn how by reading the MySQL manual. The slow query log can log all queries when long_query_time is set to zero, but be careful: on a busy server, this can increase disk I/O and use a significant amount of disk space. Aggregation Query metrics are grouped and aggregated by query. That sounds obvious since they’re called query metrics, but some query metric tools can group by username, hostname, database, and so on. These alternate groupings are exceptionally rare and yield a different type of query analysis, so I don’t address them in this book. Since query response time is the North Star of MySQL performance, grouping query metrics by query is the best way to see which queries have the slowest response time, which forms the basis of query reporting and analysis. There’s one little problem: how do you uniquely identify queries to determine the groups to which they belong? For example, system metrics (CPU, memory, storage, and so on) are grouped by hostname because hostnames are unique and meaningful. But queries don’t have any uniquely identifying properties like hostname. The solu‐ tion: a SHA-256 hash of the normalized SQL statement. Example 1-1 shows how a SQL statement is normalized. Example 1-1. SQL statement normalization SELECT col FROM tbl WHERE id=1 SELECT `col` FROM `tbl` WHERE `id` = ? f49d50dfab1c364e622d1e1ff54bb12df436be5d44c464a4e25a1ebb80fc2f13 SQL statement (sample) Digest text (normalized SQL statement) Digest hash (SHA-256 of digest text) 6 | Chapter 1: Query Response Time