Author: Sveta Smirnova
Publisher: O'Reilly, 2012
Pages: 266
ISBN: 978-1449312008
Audience: Database developers and administrators
Rating: 4.5
Reviewed by: Kay Ewbank

A book that tells you "what to do when queries don’t work" sounds like a useful addition to the bookshelf.

Sveta Smirnova is a principal technical support engineer at Oracle, so talks to a lot of people with problems using MySQL, and in this book she discusses some of the things she’s encountered in her work. The premise is that there are a number of common problems that can be avoided or minimized, and the book takes you through them one by one showing the best way to set up MySQL to prevent them happening or to weed them out when they do occur.

The book starts with a chapter looking at basic problems such as getting the wrong results and queries running slowly, including aspects such as how to track down incorrect SQL when it’s constructed by code, how to use the query log to see what’s going on, and how you can get information back to an application using the relevant API. One of the aims of this chapter is to show you techniques that could help no matter what the problem, such as using the query log or the Explain statement, or how to narrow down the problem by careful analysis.

Chapter 2 looks at concurrency problems with locks and transactions. There’s a useful section on the MySQL troubleshooting tools that you can use when this sort of problem occurs, and the coverage of how locking and concurrency affects performance is well written.

Chapter 3 is dedicated to the ways that your choice of server options in the my.cnf configuration file can cause problems. There’s a good description of how the variables such as max_allowed_packet can trip you up, and a sensible discussion of the best way to make changes - use session rather than global variables and change one thing at a time. Following on from this, in Chapter 4, Smirnova goes through the MySQL environment - RAM, processors and cores, disk I/O, and network bandwidth. These are obviously harder to fix if they are causing problems, but still worth looking at.

Chapter 5 is dedicated to troubleshooting replication. Anyone who’s had to deal with the mess when replication goes wrong will know this isn’t something to wish on anyone. Smirnova starts with what happens when there are problems with the I/O thread, then moves on to problems with the SQL thread. She looks at circular replication, non-replication, incomplete or altered SQL statements, and what happens when the slave lags behind the master. In each case the advice is more about how to check for the problem and how to avoid the problems in the first place rather than putting it right, but it’s good advice anyway.

Chapter 6 covers troubleshooting techniques and tools, with coverage of the tools Smirnova describes as essential such as the slow query log, MySQL proxy, the command line client, and the MySQL Sandbox. In each case she shows how she uses the tools to track down problems. The final chapter is a guide to best practices - backups, testing and problem prevention.

This is a very good book, and if you’re running a MySQL server it’s worth reading. You may well know some of what’s covered, but seeing it all written down logically is still useful. The writing style is pretty dense and heavy on code, but still understandable and refreshingly short on waffle.