About a year ago, I started a study of emergency incidents that our customers filed with us. What I found was really surprising, and defied conventional wisdom. I learned a lot about preventing emergencies. I just published the outcome as a white paper, including checklists that you can use and modify for your own servers. (Analysis of the nature and causes of emergencies is due to be published in the next issue of IOUG’s quarterly SELECT journal.)

A customer called with an emergency issue: A server that normally runs many MySQL instances wouldn’t start them up. Not only would it not start all of them, it wouldn’t even start the first one. The multiple instances were started through the mysql_multi init script. Perhaps you already know what was wrong!

It turns out that this server’s /etc/init.d/mysql_multi wouldn’t start unless it found the text “mysqld_multi” in the /etc/my.cnf file. Not a [mysqld_multi] config file section, but the text string “mysqld_multi”. It was using this text as a proxy for “I found a [mysqld_multi] configuration section.” This was a rather brittle test, as you can imagine.

After reading the source, I determined that the my.cnf file was fine and the configuration should not be changed, and I could not understand what had changed since it was previously working. Perhaps an automated upgrade or a similar change to the system had …

I’ve been working with an application which does a lot of CREATE and DROP table for Innodb tables and we’ve discovered DROP TABLE can take a lot of time and when it happens a lot of other threads stall in “Opening Tables” State. Also contrary to my initial suspect benchmarking create/drop table was CPU bound rather than IO bound.

I’ve got an oprofile run and saw the following:

15753796 56.0725  no-vmlinux               no-vmlinux               /no-vmlinux
11834143 42.1213  mysqld                   mysqld                   buf_LRU_invalidate_tablespace
168823    0.6009  mysql                    mysql                    completion_hash_update(hashtable*, char*, unsigned int, char*)
53667     0.1910  oprofiled                oprofiled                /usr/bin/oprofiled
42116     0.1499  mysqld                   mysqld                   buf_calc_page_new_checksum
32107     0.1143  mysqld                   mysqld …

Xaprb (Baron) recently blogged about how InnoDB performs a checkpoint , I thought it might be useful to explain another important mechanism that affects both response time and throughput – The transaction log.

The InnoDB transaction log handles REDO logging, this is the mechanism that provides the A (Atomic) C (Consistent)  and D (Durability) in ACID. The transaction log keeps a complete record of every change that occurs to the pages inside the database. Instead of logging whole pages (Value / Physical logging) where both the before and after image of a page is logged or by logging logical changes  (Logical logging) to the dataset, InnoDB uses  …

If you’ve used MySQL’s mysqlbinlog tool, you’ve probably seen something like the following in the output: “exec_time=0″ What is the exec_time? It seems to be the query’s execution time, but it is not.

In most cases, the exec_time will be similar to the query execution time on the server where the query originally ran. If you’re running replication, then on the replication master, the exec_time will look believable. But on replicas, exec_time can be completely different. Suppose you execute an UPDATE that takes one second to run on the master. This gets logged into the binary log and copied to replicas for replay. Now suppose that you have also set log_slave_updates on the replica. When the statement executes in the replication thread, it will be logged to the replica’s binary log so it can be replicated to other servers in turn.

If you use mysqlbinlog to examine the replica’s binary log, you might see a very …