Max concurrent connections, queries and transactions

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

4551   only

4525   mct=128,4

4413   mcq=128,4

2433   itc=128

2350   itc=0     

2342   mct=128,128

2312   mcq=128,128