High Performance MySQL

Preface

We had several goals in mind for this book. Many of them are derived from thinking about that mythical perfect MySQL book neither of us had read but kept looking for on bookstore shelves. Others come from a lot of experience helping other users put MySQL to work in their environments.

We wanted a book that wasn’t just a SQL primer. We wanted a book with a title that didn’t start or end in some arbitrary time frame (“...in Thirty Days,” “Seven Days To a Better...”) and didn’t imply that the reader was a moron of some sort because he was reading our book.

Most of all we wanted a book that would help the reader take her MySQL skills to the next level. Every book we read focused almost exclusively on SQL command syntax or covered MySQL only at a very basic level. None really helped us to understand the deeper issues. We wanted a book that went deeper and focused on real-world problems. How can you set up a cluster of MySQL servers capable of handling millions upon millions of queries and ensure that things keep running even if a couple of the servers die?

We decided to write a book that focused not just on the needs of the MySQL application developer but also on the rigorous demands of the MySQL administrator, who needs to keep the system up and running no matter what his programmers or users may throw at the server.

Having said that, we assume that you are already relatively experienced with MySQL and, ideally, have read an introductory book on MySQL. In several chapters, we’ll refer to common Unix tools for monitoring system performance, such as top, vmstat, and sar. If you’re not already familiar with them (or their equivalent on your operating system), please take a bit of time to learn the basics. It will serve you well when we look at system performance and bottlenecks.

The Basic Layout of This Book

We fit a lot of complicated topics in this book. Here we’ll explain how we put them together in an order that hopefully makes them easy for you to learn.

Back to Basics

The first two chapters are dedicated to the basics—things you’ll need to be familiar with before you get to additional configuration details.

Chapter 1, reviews some rudimentary configuration basics. This book assumes a pretty good command of foundational MySQL administration, but we’ll go over the fundamentals briefly before digging deeper into the world of MySQL.

After that, Chapter 2, covers the various storage engines, or table types, that are part of MySQL. This is important because storage engine selection is one of the few things that can be nontrivial to change after you create a table. We review the various benefits (and potential pitfalls) of the various storage engines, and try to provide enough information to help you decide which engine is best for your particular application and environment.

Things to Reference as You Read the Rest of the Book

The next two chapters cover things you’ll find yourself referencing time and again throughout the course of the book.

Chapter 3, discusses the basics of benchmarking—determining what sort of workloads your server can handle, how fast it can perform certain tasks, and so on. You’ll want to benchmark your application both before and after a major change, so you can judge how effective your changes are. What seems to be a positive change may turn out to be a negative one under real-world stress.^[1]

In Chapter 4, we cover the various nuances of indexes. Many of the things we discuss in later chapters hinge on how well your application puts MySQL’s indexes to work. A firm understanding of indexes and how to optimize their use is something you’ll find yourself returning to repeatedly throughout the process.

Places to Tune Your Application

The next two chapters discuss areas in which the MySQL administrator, application designer, or MySQL programmer can make changes to improve performance of a MySQL application.

In Chapter 5, we discuss how the MySQL programmer might improve the performance of the MySQL queries themselves. This includes basics, such as how the query parser will parse the queries provided, as well as how to optimize queries for ideal performance.

Once the queries are optimized, the next step is to make sure the server’s configuration is optimized to return those queries in the fastest possible manner. In Chapter 6, we discuss some ways to get the most out of your hardware, and to suggest hardware configurations that may provide better performance for larger-scale applications.

Scaling Upward After Making Changes

Once you’ve got a server up and running as best it can, you may find that one server simply isn’t enough. In Chapter 7, we discuss replication—that is, getting your data copied automatically to multiple servers. When combined with the load-balancing lessons in Chapter 8, this will provide you with the groundwork for scaling your applications in a significant way.

Make Sure All That Work Isn’t for Naught

Once you have configured your application, gotten it up and running, and replicated your database across multiple servers, your next task as a MySQL administrator is to keep it all going.

In Chapter 9, we discuss various backup and recovery strategies for your MySQL databases. These strategies help minimize your downtime in the event of inevitable hardware failure and ensures that your data survives such catastrophes.

Finally, Chapter 10, provides you with a firm grasp of some of the security issues involved in running a MySQL server. More importantly, we offer many suggestions to allow you to prevent outside parties from harming the servers you have spent all this time trying to configure and optimize.

The Miscellany

There’s a couple things we delve into that either don’t “fit” in a particular chapter or are referenced often enough by multiple chapters that they deserve a bit of special attention all to themselves.

In Appendix A, we cover the output of the SHOW STATUS and SHOW INNODB STATUS commands. We attempt to decipher for the average administrator what all those variables mean and offer some ways to find potential problems based on their values relative to each other.

Appendix B, covers a program called mytop, which Jeremy wrote as an easy-to-use interface to what your MySQL server is presently doing. It functions much like the Unix top command and can be invaluable at all phases of the tuning process to find which MySQL threads are using the most resources.

Finally, in Appendix C, we discuss phpMyAdmin, a web-based tool for administration of a MySQL server. phpMyAdmin can simplify many of the administrator’s routine jobs and allow users to issue queries against the database without having to build a client or have shell access to the server.

Software Versions and Availability

Writing a MySQL book has proven to be quite a challenge. One reason is that MySQL is a moving target. In the two-plus years since Jeremy first wrote the outline for this book, numerous releases of MySQL have appeared. MySQL 4.0 went from testing to stable, and as we go to press, MySQL 4.1 and 5.0 are both available as alpha versions. We had to revise the older text occasionally to remove references to limitations that were fixed after the fact.^[2]

We didn’t use a single version of MySQL for this book. Instead, we used a handful of MySQL 4.0 and 4.1 releases, while occasionally looking back at how things used to be in the 3.23 days. MySQL 5.0 is still in so much flux that we simply could not attempt to cover it in the first edition. The same is true for the (currently) new MySQL Administrator GUI tool.

Throughout this book, we assume a baseline version of MySQL 4.0.14 and have made an effort to note features or functionality that may not exist in older releases or that may exist only in the 4.1 series. However, the definitive reference for mapping features to specific versions is the MySQL documentation itself. We expect that you’ll find yourself visiting the annotated online documentation (http://www.mysql.com/doc/) from time to time as you read this book.

Another great aspect of MySQL is that it runs on all of today’s popular platforms: Mac OS X, Windows, Linux, Solaris, FreeBSD: you name it! However, our experience is heavily skewed toward Linux and FreeBSD. When possible, we’ve tried to note differences Windows users are likely to encounter, which tend to come in two flavors. First, file paths are completely different. Chapter 1 contains numerous references to C:\mysql and the location of configuration files on Windows.

Perl is the other rough spot when dealing with MySQL on Windows. MySQL comes with several useful utilities that are written in Perl and certain chapters in this book present example Perl scripts that form the basis of more complex tools you’ll build. However, Windows doesn’t come with Perl. In order to use these scripts, you’ll need to download a Windows version of Perl from ActiveState and install the necessary add-on modules (DBI and DBD::mysql) for MySQL access.

Conventions Used in This Book

The following typographical conventions are used in this book:

Plain text

Indicates menu titles, menu options, menu buttons, and keyboard accelerators (such as Alt and Ctrl).

Italic

Indicates new terms, example URLs, example email addresses, usernames, hostnames, filenames, file extensions, pathnames, directories, and utilities.

Constant width

Indicates elements of code, configuration options, variables, functions, modules, the contents of files, or the output from commands.

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.

Using Code Examples

This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You don’t 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 doesn’t require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code doesn’t require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but don’t require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: "High Performance MySQL: Optimization, Backups, Replication, and Load Balancing, by Jeremy D. Zawodny and Derek J. Balling. Copyright 2004 O’Reilly Media, Inc., 0-596-00306-4.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at [e-mail].

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

[phone] (in the United States or Canada)

[phone] (international or local)

[phone] (fax)

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at:

http://www.oreilly.com/catalog/hpmysql/

To comment or ask technical questions about this book, send email to:

[e-mail]

For more information about our books, conferences, Resource Centers, and the O’Reilly Network, see our web site at:

http://www.oreilly.com

The authors maintain a site called:

http://highperformancemysql.com

There you will find new information on MySQL releases, updates to the tools shown in the book, and possibly other goodies such as question-and-answer forums. Visit regularly!

Acknowledgments

A book like this doesn’t come into being without help from literally dozens of people. Without their assistance, the book you hold in your hands would probably still be a bunch of sticky notes on the side of our monitors. This is the part of the book where we get to say whatever we like about the folks who helped us out, and we don’t have to worry about music playing in the background telling us to shut up and go away, as you might see on TV during an awards show.

We couldn’t have completed this project without the constant prodding, begging, pleading, and support from our editor, Andy Oram.^[3] If there is one person most responsible for the book in your hands, it’s Andy. We really do appreciate the weekly nag sessions.

Andy isn’t alone, though. At O’Reilly there are a bunch of other folks who had some part in getting those sticky notes converted to a cohesive book that you’d be willing to read, so we also have to thank the production, illustration, and marketing folks for helping to pull this book together. And, of course, thanks to Tim O’Reilly for his continued commitment to producing some of the industry’s finest documentation for popular open source software.

Finally, we’d both like to give a big thanks to the folks who agreed to look over the various drafts of the book and tell us all the things we were doing wrong: our reviewers. They spent part of their 2003 holiday break looking over roughly formatted versions of this text, full of typos, misleading statements, and outright mathematical errors. In no particular order, thanks to Brian “Krow” Aker, Mark “JDBC” Matthews, Jeremy “the other Jeremy” Cole, Mike “VBMySQL.com” Hillyer, Raymond “Rainman” De Roo, Jeffrey “Regex Master” Friedl, Jason DeHaan, Dan Nelson, Steve “Unix Wiz” Friedl, and last but not least, Kasia “Unix Girl” Trapszo.

From Jeremy

I would again like to thank Andy for agreeing to take on this project and for continually beating on us for more chapter material. Derek’s help was essential for getting the last 20-30% of the book completed so that we wouldn’t miss yet another target date. Thanks for agreeing to come on board late in the process and deal with my sporadic bursts of productivity, and for handling XML grunt work, Chapter 10 Appendix C, and all the other stuff I threw your way.

I also need to thank my parents for getting me that first Commodore 64 computer so many years ago. They not only tolerated the first 10 years of what seems to be a life-long obsession with electronics and computer technology, but quickly became supporters of my never-ending quest to learn and do more.

Next I’d like to thank a group of people I’ve had the distinct pleasure of working with while spreading MySQL religion at Yahoo during the last few years. Jeffrey Friedl and Ray Goldberger provided encouragement and feedback from the earliest stages of this undertaking. Along with them, Steve Morris, James Harvey, and Sergey Kolychev put up with my seemingly constant experimentation on the Yahoo! Finance MySQL servers, even when it interrupted their important work. Thanks also to the countless other Yahoos who have helped me find interesting MySQL problems and solutions. And, most importantly, thanks for having the trust and faith in me needed to put MySQL into some of the most important and visible parts of Yahoo’s business.

Adam Goodman, the publisher and owner of Linux Magazine, helped me ease into the world of writing for a technical audience by publishing my first feature-length MySQL articles back in 2001. Since then, he’s taught me more than he realizes about editing and publishing and has encouraged me to continue on this road with my own monthly column in the magazine. Thanks, Adam.

Thanks to Monty and David for sharing MySQL with the world. Speaking of MySQL AB, thanks to all the other great folks there who have encouraged me in writing this: Kerry, Larry, Joe, Marten, Brian, Paul, Jeremy, Mark, Harrison, Matt, and the rest of the team there. You guys rock.

Finally, thanks to all my weblog readers for encouraging me to write informally about MySQL and other technical topics on a daily basis. And, last but not least, thanks to the Goon Squad.

From Derek

Like Jeremy, I’ve got to thank my family, for much the same reasons. I want to thank my parents for their constant goading that I should write a book, even if this isn’t anywhere near what they had in mind. My grandparents helped me learn two valuable lessons, the meaning of the dollar and how much I would fall in love with computers, as they loaned me the money to buy my first Commodore VIC-20.

I can’t thank Jeremy enough for inviting me to join him on the whirlwind book-writing roller coaster. It’s been a great experience and I look forward to working with him again in the future.

A special thanks goes out to Raymond De Roo, Brian Wohlgemuth, David Calafrancesco, Tera Doty, Jay Rubin, Bill Catlan, Anthony Howe, Mark O’Neal, George Montgomery, George Barber, and the myriad other people who patiently listened to me gripe about things, let me bounce ideas off them to see whether an outsider could understand what I was trying to say, or just managed to bring a smile to my face when I needed it most. Without you, this book might still have been written, but I almost certainly would have gone crazy in the process.

---

^[1] Management folks also tend to like metrics they can point at and say, “See, this is how much our system improved after we spent $39.95 on that O’Reilly book! Wasn’t that a great investment?”

^[2] Note to budding authors: write as fast as you can. The longer you drag it out, the more work you have to do.

^[3] Then again, if there’s a second edition on the horizon, one might argue that this project is not complete.