Channels ▼
RSS

Web Development

High Performance MySQL


July, 2004: High Performance MySQL

Tim can be reached at kientzle@acm.org.


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


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 
Dr. Dobb's TV