How to change innodb_thread_concurrency in MySQL

I will give an example of changing innodb_thread_concurrency in MySQL.
Since InnoDB uses operating system threads to process user transactions, the innodb_thread_concurrency parameter allows you to limit them. By default in new MySQL versions the value is 0, which means that there is no limit on the number of simultaneously executed threads and this is correct for modern servers. If you want to limit, then when the limit is reached, the extra threads will wait a certain number of microseconds specified in the innodb_thread_sleep_delay parameter, and then try to get into the queue. Also in MySQL 5.6.3 and higher, the innodb_adaptive_max_sleep_delay parameter was added which allows you to specify the maximum number of microseconds for the innodb_thread_sleep_delay parameter and then InnoDB automatically adjusts innodb_thread_sleep_delay.

I will give an example of viewing the current value:

show variables like 'innodb_thread_concurrency';

If the value is not 0, then I recommend setting it:

set global innodb_thread_concurrency=0;

The changes will take effect without restarting the MySQL server.
So that the changes are not discarded after restarting MySQL, we will also indicate them in the configuration file:

[mysqld]
innodb_thread_concurrency=0

See my other articles about MySQL
How to change innodb_write_io_threads
How to change innodb_read_io_threads

Leave a comment

Leave a Reply

Discover more from IT Blog

Subscribe now to keep reading and get access to the full archive.

Continue reading