I think this story has a moral. The source to MySQL is open. Because of that we were able to find the cause of a serious performance regression between MySQL4 and MySQL5. We also may have made MySQL5 29% slower than MySQL4 rather than 58% slower for one of our benchmarks. Note that this difference is for our benchmark that consists of fast SQL insert statements with many expressions and minimal network latency.

As part of upgrading a few database servers from MySQL 4.0 to MySQL 5.0, I ran a benchmark that measures the time to reload all tables of a database. The number of sessions used to perform the reload was configurable, and the performance difference between MySQL 4 and 5 was the greatest when 1 session (no concurrency) was used.

This was quite a shock. I first assumed the problem was caused by InnoDB and the new support for row_format=compact. I even removed support for this feature and reran the tests. That made MySQL5 faster, but not much faster.

I then began using oprofile which is amazing for performance debugging and found that MySQLParse was the top consumer of CPU time. Unfortunately, this is one large function a huge switch statement generated by Bison. Fortunately, I was able to use the rdtsc instruction to isolate the problem.

The grammar for MySQL5 has changed significantly from MySQL4 in a few places. There is good reason for this change as the SQL supported by MySQL5 is much closer to the SQL standard than it used to be. One unexpected effect of this change is that much more CPU time is used to parse expressions (see http://bugs.mysql.com/bug.php?id=29921).

I found a colleague who gets interrupted at work less frequently than I do and asked him to modify the grammar and get back the performance. I think we have something that works. The worst case performance difference for MySQL5 has been cut in half for MyISAM and almost in half for InnoDB.

The bug report has many more numbers. The number of seconds to reload a database with MyISAM tables are listed below. MySQL5 is 58% slower without the fix and 29% slower with it.

MySQL 4.0.26            - 10061 seconds
MySQL 5.0.37 (original) - 15808 seconds
MySQL 5.0.37 (fixed)    - 12995 seconds

What is rate limiting in MySQL and why might you need it? For starters this is limited to the InnoDB engine. InnoDB had performance problems on SMP servers and uses rate limiting as a workaround. There is an excellent description of this at mysqlperformanceblog.com. InnoDB limits the number of threads that can execute concurrently by giving each thread a number of tickets and making threads sleep when they are out of tickets and too many other threads are active.

Unfortunately, the replication thread is not given special treatment. It gets no more tickets than other sessions. On a busy replica where the number of concurrent queries exceeds innodb_thread_concurrency, replication can get far behind as the replication thread spends too much time waiting for tickets. This is easy to fix. Change the code so that the replication thread gets many more tickets per allocation than other threads. The result of this change is much less replication delay on busy slaves.

This feature in InnoDB can also be used to rate limit accounts that are using too many resources on a busy server. The trick is to provide a mechanism to change the number of tickets that a session may get at the account level. When too much work is done by one account and there is no easy way to change applications that use the account, reducing the number of tickets given to sessions for that account is a good way to make that account use less of a server. This requires a mechanism for setting the the equivalent of innodb_concurrency_tickets per account. This can be done by adding new SQL syntax to the parser, adding a system variable that can be set with a comma-separated list of account and number of ticket pairs or by redefining the values of rarely used columns in the mysql.user table. Since I have never been fond of the max_questions and max_updates columns in mysql.user, I would use the value of the column, when not set to 0, to be the per-account value for innodb_concurrency_tickets.