Tim can be reached at [email protected].
High Performance MySQL
Jeremy D. Zawodny and Derek J. Balling
O'Reilly and Associates, 2004
294 pp., $39.95
ISBN 0-596-00306-4
Like many people, I first started using MySQL because it was a cheap and easily administered tool for development and prototyping. However, I have in the past had some misgivings about using MySQL on serious production systems. Since reading High Performance MySQL, however, I will have no difficulty recommending MySQL for highly demanding applications.
Under the Hood
Through the course of their book, Jeremy Zawodny and Derek Balling look at every stage of MySQL's operation, from query parsing and optimization, back to the storage engine responsible for a particular table, and finally to the operating system and hardware. For the most part, they present enough information for you to understand the issues involved without wasting your time on details that may not even be correct for the current release.
The authors' experience shows through clearly. In addition to presenting numerous concrete examples drawn from Zawodny's work at Yahoo! and elsewhere, they also manage to derive simple lessons from that experience. For example, the authors point out that query and index optimizations can often yield thousand-fold improvements in performance, while hardware upgrades generally offer much more modest increases. They also discuss what types of applications are most likely to become CPU bound or I/O bound.
The one area that was not as well covered as I would like is the impact of application architecture on database performance. I've personally seen enormous performance gains from application-level changes, and would like to read a good general discussion of what kinds of application-level changes are generally most effective and how those benefits compare to or complement the gains from schema, database, or operating-system optimizations.
Making Choices
Choosing a storage engine is a subtle task that the authors explain well. Roughly speaking, the default MyISAM tables provide good performance for read-heavy applications, while the various alternatives provide improved concurrency and transactional support. These trade-offs are introduced in an early chapter, but there are additional issues that come out later in the book. For example, based on the initial discussion, there would seem to be little reason to ever choose BDB over InnoDB, but a later chapter points out that InnoDB tables require more involved backup and restore strategies.
Choosing Your Tools
I especially appreciated the authors' practical approach to diagnosing problems. The book is sprinkled with numerous examples
of EXPLAIN SELECT and SHOW STATUS. The authors also explain how to look at log files and performance counters and compare several simple benchmarking and administrative tools.
Putting It All Together
Of course, no server stands alone. Integrating a MySQL server into a larger system requires that you think about issues such as replication, load-balancing, backup, and security. These are exactly the topics that more introductory books often overlook.
Replication is a particularly key feature. It can be used to greatly improve performance of enterprise applications by putting a copy of the database close to each group of users. It can also simplify your backup strategy: It's much less disruptive to shut down a slave for backup than to shut down your master server. Of course, replication can also be used to dramatically improve performance by spreading query load across more hardware.
Finally, the authors give a good overview of MySQL security issues, including a tour of MySQL's privilege tables and a quick overview of how to use SSH and SSL to protect MySQL sessions. No doubt many readers will want more, but the sysadmin who has already spent some time thinking about security will find this section a good source of useful ideas.
Conclusion
This book is not a substitute for a good MySQL reference. You'll need the MySQL documentation or a book such as Managing and Using MySQL (O'Reilly and Associates, 2002) in which to look up detailed command syntax and configuration details.
But the clear presentation and practical, no-nonsense approach make this an excellent book. If you're currently managing a MySQL installation, you'll find plenty of practical advice that you can put to work immediately. If, like me, you've ever had misgivings about using MySQL in a critical application, this book will help you to better understand exactly how capable MySQL really is.
TPJ
