Rate limiting for InnoDB

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.