We limit the maximum number of concurrent connections per database account via the max_user_connections option. We are more interested in limiting the maximum number of concurrent queries and transactions per account to reasonable values while allowing much larger numbers of connections. That is not supported in official MySQL so we added support for it. Chip and I have been working on this for a few months and it is almost ready for production.
If there were a connection pool in official MySQL we would have used it. But that feature does not exist in a public and robust source tree and adding a connection pool would have required a much larger diff.
InnoDB prior to MySQL 5.5 can use at most 1023 undo slots and each transaction uses at most 2 undo slots. One undo slot is used for INSERT statements and another for UPDATE statements. So the 1023 undo slots can be consumed by 511 to 1023 concurrent transactions. The code we changed in MySQL counts statements at the common entry point for statements and makes statements wait in a queue when an account exceeds its limit. While waiting there is (or will soon be) code that notices when a client has disconnected before the statement began running.
The code also counts the number of undo slots concurrently used per account and that value is limited to be approximately less than or equal to the max_concurrent_transactions value. The code that increments the count of undo slots in use per-account runs long after a statement starts and there are many places in the code where a transaction can start. When an INSERT, UPDATE, DELETE or REPLACE statement is about to start the statement is allowed to run as long as the current count of undo slots in use for that account does not exceed the limit. Enforcement is fuzzy so an account can use 20 undo slots when its limit is 10. The error ER_TRANSACTION_CONTROL_LIMIT is returned when the limit has been exceeded. Errors are already returned when the global limit is reached with or without this patch.
The patch adds the columns max_concurrent_queries and max_concurrent_transactions to the mysql.user table. The GRANT command also supports with max_concurrent_transactions X and with max_concurrent_queries X.
The goal in this case to keep a too-busy account from making things slower for other accounts. I ran a few sysbench tests to confirm that my recent changes to this code haven't broken anything. The test was run using 8 to 1024 concurrent clients updating 8 tables with 1M rows each. Each update transaction is one UPDATE statement that updates one row by primary key. The binlog was enabled (sync_binlog=1, innodb_flush_log_at_trx_commit=1).
The test was done for four configurations and the results follow. The results for ac=128 match those for itc=128. The results for ac=128,xc=128 are also similar until more than 128 concurrent clients are used. At that point there will be many ER_TRANSACTION_CONTROL_LIMIT errors. Future tests results will show how these help throughput for one account remain steady while another account misbehaves.
- itc=0 - innodb_thread_concurrency=0, there are no constraints on concurrency
- itc=128 - innodb_thread_concurrency=128, use InnoDB to limit server to ~128 threads in InnoDB
- ac=128 - use max_concurrent_queries to limit mysqld to 128 concurrent queries
- ac=128,xc=128 - use max_concurrent_queries as above and use max_concurrent_transactions to limit InnoDB to 128 concurrent undo slots
Update transactions per second
8 16 32 64 128 256 512 1024 concurrent connections
1778 4502 4586 4648 4551 4468 4423 4421 itc=0
1867 4482 4603 4657 4534 5053 4930 4942 itc=128
1816 4623 4733 4729 4714 4655 4557 4522 ac=128
1775 4498 4594 4640 4557 1917 3241 2447 ac=128,xc=128
Protecting one account from another
I ran several tests to determine the benefit of using max_concurrent_queries and max_concurrent_transactions to protect throughput for one account when there is another account that generates too much load. For all tests the protected account ran the workload as described above for 128 concurrent clients. For all tests listed below except one the misbehaving account also used the workload described above for 128 clients. Several configurations were tested:
- only - only the protected account ran the workload
- itc=0 - no concurrency protection as innodb_thread_concurrency=0 was used
- itc=132 - innodb_thread_concurrency=132 was used so both accounts suffered equally
- mcq=128,4 - max_concurrent_queries was 128 for the protected account and 4 for the other account
- mcq=128,128 - max_concurrent_queries=128 was for both accounts
- mct=128,4 - max_concurrent_transactions was 128 for the protected account and 4 for the other account
- mct=128,128 - max_concurrent_transactions was 128 for both accounts
Updates per second for the protected account