This is a review of Oreilly’s excellent new book, MySQL Troubleshooting by Sveta Smirnova.
Having read more than half of Oreilly’s MySQL books, I can say that the bar for quality is very high in this genre. The MySQL reference manual is indispensible, but to really learn MySQL, you need to read the Oreillys. These classics in particular, are handy to read:
MySQL High Availability
High Performance MySQL, 3rd Edition
MySQL Cookbook, 2nd Edition
MySQL in a Nutshell, 2nd Edition
SQL and Relational Theory, 2nd Edition
I expected to breeze through MySQL Troubleshooting but I often found myself short on knowledge and reviewing concepts in other books, especially MySQL High Availability and High Performance MySQL. MySQL Troubleshooting will benefit experienced administrators the most. At times, our author provides seemingly contrived problems and offers paths to detailed and strange minutiae of MySQL troubleshooting. At other times, topics like backup receive short shrift. In the end, though, our author proves to be a worthy guide, a good writer and an expert in this domain.
We learn how to set turn on query logging:
mysql> SET GLOBAL general_log=’on’;
…which only works for mysql 5.1 and later. For earlier versions, we need a line in /etc/my.cnf like this:
…and restart mysql. Being stuck with an older MySQL, editing /etc/my.cnf to turn on query logging looks necessary. As with most Oreillys, doing the things in the book is always the way to go. Get yourself a MySQL command-line somewhere and keep it for the whole book.
Long listings of MySQL logging output are to be found in this book. Some of this logging can be painful to read but part of the story here is that MySQL troubleshooting often involves painstaking log analysis. I found myself skimming the logging at times and reading only the explanation found below the logging output. The skimming does not pay–you need to read alot of logging output to understand the concepts driving the troubleshooting effort being illustrated. With a printed book, the reader can highlight or circle the parts of the logging output that really matter, the parts that are referred to in the explanation. But not with a PDF. Perhaps Oreilly should rethink printing 2 pages of logging output without any boldfacing/highlighting that draws attention to the important parts. Oreilly does not usually do this with code and probably shouldn’t. But I don’t find myself skimming code snippets like I do with logging output. Maybe in the new PDF-books-are-everywhere-world Oreilly should markup the pages a little more. I liked the “line-numbering” mechanism Mark Pilgrim uses in his (free at one time) “Dive Into Python” books. In any case, log analysis is performed in this book and you’ll need to squint to grasp the concepts.
I was trying to make a point before I started bellyaching about ebooks: The logging output and its analysis are a great strength of the book. Much of this material will be new even to seasoned admins.
We learn some tricky SQL. From the book:
“MySQL thought we wanted to run a dependent subquery”.
After reading about dependent subqueries and seeing how easy they are to create-with-mistakes, I’m convinced there are alot of dependent-subquery sufferers out there. That said, the SQL query troubleshooting got a bit tiring quickly. If you want to learn SQL, pick up OReilly’s excellent “Learning SQL” or Chapters 5,6 & 7 of “Learning MySQL” or Head First SQL or (if you’ve got 6 months) C.J. Date’s Relational Theory book.
Unfortunately, starting with Chapter 1, proficiency with the basics of master-slave replication setup and operation is assumed. We were warned about this in the Preface. (Another thing we remember during Chapter 1 is that we were also warned that the programming examples will be based mostly on the C API). Fear not–replication is easy. Reading the first 43 pages of MySQL High Availability, or Chapter 10, Replication, in “High Performance MySQL, 3rd Edition” will have all the basics you will need to follow along with most of the replication troubleshooting covered in this book.
Chapter 2 shows some table and row locking problems very clearly. We manufacture a locked table and a locked row and look at what “SHOW PROCESSLIST;” thinks about it. You’ll need a mysql client command line to really enjoy the fun. Run “SHOW ENGINE INNODB STATUS”. I also took a very long time pondering this quote about how performance relates to INSERTS with indexes and locks, from Chapter 2:
“when row locks are in use, indexes can increase overall application performance, especially when an index is unique, because while updating such an indexed field, insert would not block access to the whole table.”
I still don’t understand it. I have the PDF version, and right now I want to circle the confusing quote and fold the corner of the page so that I can return later. The nice thing about the PDF version is that the book is packed with links to context-specific help. When I click on them, it takes me right to the content that I want to read more about. I took many trips to the MySQL Reference Manual while reading this book. With a hardcopy book, I would have taken zero trips to the web. The PDF being able to search the text is also nice–but some of the text in my PDF was not searchable–that is not cool.
If you think you know everything, think again! In this book we learn that these statements:
mysql> CREATE TABLE t1(f1 INT) ENGINE=InnoDB;
mysql> SELECT * FROM t1;
mysql> INSERT INTO t1 VALUES(100);
mysql> CREATE TABLE t2 LIKE t1;
mysql> INSERT INTO t1 VALUES(200);
…result in t1 having one row with f1=100. That’s because “CREATE TABLE” implies a COMMIT.
How about this hack:
mysql -A test
mysql> CREATE TABLE innodb_monitor(f1 INT) ENGINE=InnoDB;
…which mysql notices and starts outputting to the error logfile. This allows you to find the query that is holding the lock (and the query waiting for the lock) by lining up timestamp information from the slow-query-log. Nice.
Chapter 3, Effects of Server Options, excels at providing bite-sized troubleshooting. We learn that its possible to get a MyISAM table from this query:
CREATE TABLE t1(f1 INT) ENGINE=InnoDB;
Ouch! The discussion on max_allowed_packet should have run longer. The author tells us that: “In my day-to-day job, I see many users who are affected by ignoring the value of max_allowed_packet”. She can add me to the list of many users. We get a poor explanation of how to address this problem:
“If you start getting syntax errors for valid queries, check whether their size exceeds max_allowed_packet.”
The Mysql Reference manual tells us this:
“The largest possible packet that can be transmitted to or from a MySQL 5.1 server or client is 1GB.”
…but if query-size and packet-size are the same, then how would anyone insert a large 2Gig blob value? I would have appreciated more depth on troubleshooting problems with max_allowed_packet. Google returns 853,000 results for searches on “max_allowed_packet”, so we are not alone.
At about halfway through the book, I had a disturbing revelation. This quote pushed me over the edge:
“Both the –no-defaults and –defaults-file options must be specified as the first options passed to mysqld. Otherwise, the server will not recognize them”
…that was one too many mysql gotchas for my stomach. This book makes mysql look bad. But better to be bad-and-known-about than bad-and-not-known-about.
Chapter 3 is packed with awesome server option explanations and tips…my favorite part are these queries:
SELECT (@@query_cache_size + @@innodb_additional_mem_pool_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size)/(1024*1024);
SELECT @@max_connections * (@@global.net_buffer_length + @@thread_stack + @@global.query_prealloc_size + @@binlog_cache_size) / (1024 * 1024)
…which tell you, in megabytes, how much memory the running mysqld needs to allocate these buffers. The entire memory discussion is excellent and important now that terabyte-sized machines are available.
Chapter 4, MySQL’s Environment, is mostly about performance and is packed with great tips. Topics include CPUs, memory and disk. No surprises here–disk and memory help, CPUs don’t. I was surprised to read on page 151 that:
“Clients almost always connect to the MySQL server over the network”
…perhaps so, but it would be nice to know how or why the author believes that. Whether and why to run MySQL locally or on another box is an important decision that deserves more attention than we get here. Perhaps you’ll be better off running mysqld locally with the database running on dedicated disks for the datafile, the log file and the binlog.
The slowest part of Chapter 4 is when we learn about these latencies associated with a typical query on a typical server:
“The client sends a command to the server that takes a half of RTT (network round-trip-time).
The WHERE clause of the UPDATE is executed, and mysqld reads the disk.
mysqld does an fsync call to prepare for the transaction because autocommit is on.
mysqld does an fsync call to write into a binary logfile.
mysqld does an fsync call to commit changes.
The client receives the result from the server, which is another aspect of RTT.”
Chapter 5, Troubleshooting Replication, is again packed with great tips, lucid explanation and links to other documentation and software. Some of the treatment is too brisk, especially the explanation of “SHOW SLAVE STATUS” and multi-master troubleshooting. There is an excellent example of using mysqlbinlog to check the master’s binlog and the slave’s relay log for corruption. Brushing up on replication concepts is a good idea before reading this chapter–Chapters 2 and 3 of “MySQL High Availability” is a good re-read for this purpose, especially page 87′s mysqlbinlog demonstration.
Chapter 6, Troubleshooting Techniques and Tools, starts with the author explaining why simple tools will be preferred in this chapter. How nice. No explaining necessary. The simplicity stops when the author suggests three ways of addressing slow queries, one of which is:
“write a MySQL plug-in for auditing purposes”
…How flattering of her to think I’m talented enough to write and compile a mysql plugin to deal with a slow query! Seriously, though, by following her link to http://dev.mysql.com/doc/refman/5.5/en/writing-audit-plugins.html, writing such a plugin looks easy.
The third solution, write a scriptable proxy, looks like lots of fun too. We get a dip into Lua programming.
These solutions are very cool. But aren’t slow queries always caused by a new query of a big table on a column without an index? Troubleshooting this simple problem could have used more than the advice: “add an index” that it gets in this chapter.
We learn how to use “mysql –column-type-info test”, how to (safely) operate on copies of tables, how to use Giuseppe Maxia’s Mysql sandbox tool (a totally wild and crazy thing that actually works), how to spit out and analyze a core file, how to extract goodies from INFORMATION_SCHEMA, mysqlslap, sysbench, Gypsy, mysql-test-run, and more.
The book crosses into the absurd when our author commands us:
“At some point, of course, you have to dive into the MySQL source code itself.”
Holy cow! Actually, dear author, no, I don’t need to dive into source code. Your book will do just fine with these eyeballs.
The InnoDB Monitor section was very tough reading for those of us low on InnoDB details like the least-recently-used algorithm. Even so, playing around is easy to do by simply creating tables innodb_monitor, innodb_lock_monitor, innodb_table_monitor and innodb_tablespace_monitor. Note to self: come back later.
I always thought mtop and Mysql Workbench were pretty cool, but they get little coverage here.
The climax of the book occurs on page 206:
“General Steps to Take in Troubleshooting”
It takes a leader with some nerve to attempt such a section title with MySQL. The advice is good.
The section on the MySQL Test Framework is good but my local CentOS 5.5 linux box did not have the mtr command. I installed it with “yum install mysql-test.i386″ (or yum install mysql-test.x86_64). The book calls the command “mtr”. But on my linux box, mtr is a little network program called “my traceroute”. The mysql-test binary is called “mysqltest” on CentOS5.
Chapter 7, Best Practices, wanted to be longer. It is surprisingly basic and easy reading after Chapter 6 when we were writing Lua and analyzing core dumps. Table 7-1 compares backup tools nicely.
The book misses opportunities to share wisdom in too many places. For example, when discussing the issue of troubleshooting MySQL permission problems, the author offers detailed information about how to track down the problem. But the wisdom of MySQL permission problems, I’ve found, is to avoid using the power at all. MySQL has always come with the ability to extremely fine tune permissions–admins can enforce rules such that a certain user at a certain host can access a certain column in a certain table in a certain database and nothing else. But elaborate MySQL permission settings is rarely necessary and always asking for trouble. Most shops will only need to carefully grant proper access to apache and a few developers. MySQL security is more about simplicity, backups and documentation. But when the author does try to pass along wisdom, she does so very well, like in the “Haste Makes Waste” section, where we are told to start mysqld with the –no-defaults option and then set each of the local custom options, one at a time, measuring performance with each setting change. Another piece of advice we all ignore is to ignore warnings. Repeatedly, when we see a warning in the book, we track it down. Tolerate zero warnings is the wisdom.
The book could have used a few more “refresher” paragraphs. A quick refresher about replication would have helped.
MySQL Troubleshooting is an excellent book. I congratulate the author and Oreilly for sharing it with us.