Xaprb

This article shows you how to use a little-known InnoDB feature to find out what is holding the lock for which an InnoDB transaction is waiting. I then show you how to use an undocumented feature to make this even easier with innotop.

Background

One of the most common complaints I’ve heard from DBAs used to other database servers is “I can’t find out who holds the locks that are blocking all these connections and making them time out.” I feel your pain. Before I helped scale my employer’s systems to deal with larger volumes of data, InnoDB lock contention was a serious issue. And as far as I knew, you couldn’t find out who was holding locks. I knew you could see who was waiting for locks to be granted; that’s easy. You just run SHOW INNODB STATUS and look for the following text:

------------
TRANSACTIONS
------------
Trx id counter 0 4874
Purge done for trx's n:o < 0 4869 undo n:o < 0 0
History list length 21
Total number of lock structs in row lock hash table 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 4873, ACTIVE 6 sec, process no 7142, OS thread id 1141152064 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 368
MySQL thread id 9, query id 173 localhost root Sending data
select * from t1 for update
——- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 9 page no 3 n bits 72 index `PRIMARY` of table `test/t1` trx id 0 4873 lock_mode X waiting
…

That’s fine, but who holds the lock? I thought there was no way to find that out.

InnoDB Lock Monitor

Until I learned about the InnoDB Lock Monitor, that is. You enable it by running the following command:

CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;

It’s quite an ugly hack, but it turns out the table name is actually “magical.” It’s a special table name that tells InnoDB to start the lock monitor. You can stop it by dropping the table again.

This little-noticed feature makes InnoDB print out a slightly modified version of what you see with SHOW INNODB STATUS. The “slight modification” is to print out not only the locks the transaction waits for, but also those it holds. For example, here’s the transaction that holds the locks:

---TRANSACTION 0 4872, ACTIVE 32 sec, process no 7142, OS thread id 1141287232
2 lock struct(s), heap size 368
MySQL thread id 8, query id 164 localhost root
TABLE LOCK table `test/t1` trx id 0 4872 lock mode IX
RECORD LOCKS space id 9 page no 3 n bits 72 index `PRIMARY` of table `test/t1` trx id 0 4872 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 000000000d35; asc      5;; 2: len 7; hex 800000002d0110; asc     -  ;;

That’s fine, but there are, ah, limitations. As the manual says, InnoDB periodically prints out this text — essentially spewing InnoDB’s guts — to its standard output. This gets redirected to the server error log in any sane installation. Who’s looking there? And it gets printed out at long intervals, which seems to be about every 16 seconds on the machines I use.

Plus, if you’ve looked at the result, you’ll understand this is not something you want to search through manually looking for data. The output can be absolutely huge. What DBA wants to pore over thousands of hex-dumped rows from the table just to answer the question “who holds that lock?”

All in all, this is not very convenient (yep, I know that’s an understatement).

Slightly more convenient

What’s a little more convenient than combing through all that text by hand is writing a program to parse InnoDB’s status output. You don’t have to, though. That’s what I wrote innotop to do. And I’ve just released version 1.5.2, which at long last has the ability to watch a log file as well as connecting to server(s).

Here’s how this works: you start innotop, and press the L key to switch to Lock mode. This replaces the old Lock Wait mode, which was only able to monitor the InnoDB lock waits you see in the normal output of SHOW INNODB STATUS.

This mode shows you something like the following:

_____________________________ InnoDB Locks __________________________
CXN   ID  Type    Waiting  Wait   Active  Mode  DB    Table  Index
file  12  RECORD        1  00:10   00:10  X     test  t1     PRIMARY
file  12  TABLE         0  00:10   00:10  IX    test  t1
file  12  RECORD        1  00:10   00:10  X     test  t1     PRIMARY
file  11  TABLE         0  00:00   00:25  IX    test  t1
file  11  RECORD        0  00:00   00:25  X     test  t1     PRIMARY

That’s helpful! I can see the locks held and waited for in a nice tabular format. It’s pretty easy to see connection 11 is blocking connection 12.

This is still pretty inconvenient, though. To get access to the server’s error log, I have to run innotop on the database server machine itself. Is there a better way?

Even better

There is, in fact, but I discovered it completely by accident. It’s not documented, but the extra information doesn’t just get printed to the server log. It also shows up in SHOW INNODB STATUS! Now that’s a nice surprise. It means innotop can get lock information from a normal connection instead of monitoring a log file.

After discovering this, I immediately added some more features to innotop. There are now hot-keys in L mode to enable and disable the lock monitor. Now you can press L, press the ‘a’ key to start the lock monitor, see what’s blocking the waiting transaction, press ‘o’ to stop the lock monitor, and you’re done.

Best yet

I’m sure you InnoDB administrators already recognize what an improvement this is over the options you previously had (essentially, you didn’t have any). There’s still a long way to go, though. Locks could be in the INFORMATION_SCHEMA or in a SHOW LOCKS command. I won’t speculate on why they aren’t already.

Of course, the upcoming Falcon storage engine already has better features for debugging lock contention than this. But my guess is it’ll be a long time before Falcon has the market share InnoDB has. All things considered, InnoDB is a pretty nice piece of software.

Conclusion

Download innotop

The conclusion to this whole article is: use innotop if you use InnoDB. Heck, use it if you use MySQL at all. It makes a lot of things a lot easier, not just debugging InnoDB lock contention. Feedback is welcome — just use the Sourceforge bug tracker, forums, and mailing lists.

You might also like:

1. How to monitor InnoDB lock waits
2. How I patched InnoDB to show locks held
3. How to find out who is locking a table in MySQL
4. Version 1.5.2 of the innotop MySQL monitor released
5. A little-known way to cause a database deadlock

Download innotop

Version 1.5.0 of the innotop MySQL and InnoDB monitor is out. This release is the first in the unstable 1.5.0 branch, which will eventually become the stable 1.6 branch. I’m beginning to merge the various branches I’ve made to support some of our needs at my employer. This first release adds some major new features and prepares for some other large improvements and new features.

What’s new

Here’s what’s new:

• Added plugin functionality.
• Added group-by functionality.
• Moved the configuration file to a directory.
• Enhanced filtering and sorting on pivoted tables.
• Many small bug fixes.

Plugins

Plugins let you hook custom code into innotop. Your custom Perl module can extend or change innotop without touching its source code, and all you have to do is drop it into a directory and activate it (sound familiar to you WordPress users?). As an example of how this is useful, about two dozen lines of code lets me add “program” and “unix_pid” columns into the Query List and InnoDB Transaction List modes. These show the originating program and PID for connections by querying tables in which this data is stored. The plugin adds the columns and expressions for them, and then adds the data in by using innotop’s own DBI connections.

There’s an example plugin in the documentation.

Grouping

This functionality lets you apply something like a SQL GROUP BY to a table. There are some built-in rules (press the ‘=’ key in Q or T mode; it’s easier if you hide the header with the ‘h’ key first).

The built-in rules let you group connections or transactions by status. They also automagically show a ‘count’ column, which is there but hidden until the grouping is applied. Now you can see how many connections are in what status. Here’s a screenshot of before and after:

You can toggle this on and off easily with the ‘=’ key on any table. (Most tables don’t have default group-by expressions, though, so you’ll have to read the docs to learn more about that. As with any features, let me know if you have a useful default you want me to include in innotop).

Notes

Don’t be scared by the “unstable” designation. It only means that I’m getting ready for a lot of changes that don’t belong in a stable branch; this release should generally be as good quality as any other. And I don’t want to use a naming scheme like “innotop-6.0-pre-alpha-1_rel5″. When I release a version I don’t think is good quality, I’ll let you know ;-) Generally I’m going to confine that code to the Subversion repository.

As an aside, both this and the MySQL Toolkit project are becoming more popular, and as that happens, I’m also getting busier — among other things, I’m writing a book! I must say SourceForge is great in some ways for helping to manage the project, but a lot of extra work in others. For example, it created a bunch of default forums, trackers, and settings when I created the projects, and that’s been pretty hard to slog through. The documentation system is not useful for my project. I think I’ve finally figured out how to get emails when people submit bug reports. I’m also trying to automate the tedious release process as much as I can, and it’s not proving easy. I don’t mean this to be a litany of woes, because I know they and I are doing our respective bests; it’s more of a commentary on the increased work that comes with a “generic, flexible” system — which is what people always seem to want, until they get it. I’m sure you all know what I mean!

Please go download, use, write plugins, and find and report bugs (via the sourceforge tracker, of course)! And happy innotop-ing.

You might also like:

1. Version 1.5.2 of the innotop MySQL monitor released
2. Version 1.6.0 of the innotop monitor for MySQL released
3. A look at innotop’s new features
4. Version 1.5.1 of the innotop MySQL monitor released
5. What I’ve been doing lately