MySQL Innodb concurrency involves parameters

1 parameter action

MySQL's various plug-in engines will handle and optimize transactions and threads. In the InnoDB engine, the number of threads that are always trying to keep the operating system in InnoDB (tentatively named Innodb_thread) should be less than or equal to the number of threads that the system can provide to InnoDB to process transactions (tentatively named System_innodb_thread). In most cases, Innodb_thread does not specify a limit value, but rather how many direct requests it wants.

When Innodb_thread is greater than System_innodb_thread, the duration is longer, causing the server's thread resources to be used by the database, the CPU may be high, or even cause downtime.

At this point, InnoDB can provide a parameter to limit the number of concurrent threads (requests that can be processed at the same moment), and when the number of concurrent threads reaches the concurrent thread limit, a new request needs to sleep for a period of time before the next request. If sleep is requested or no extra threads are available to perform it, then it will go into the FIFO queue and wait for execution. Note here that the waiting thread does not count toward Innodb_thread. The innodb_thread_concurrency parameter thus comes.

Innodb_thread_concurrency can be used to adjust the limit of the number of concurrent threads, using Innodb_thread_sleep_delay to tune when concurrent thread arrives Innodb_thread_ The time to sleep when concurrency. When the request is accepted by InnoDB, a consumer voucher innodb_concurrency_tickets (default 5,000 times) is obtained, and when more than one SQL is executed in the request, each time it is executed, the consumption is tickets, and before the number of times is exhausted, The thread does not need to check for the concurrency limit value again when it requests again.

At the same time, innodb_commit_concurrency also controls the number of multithreaded concurrent commits. If the innodb_thread_concurrency set a bit large innodb_commit_concurrency should make corresponding adjustments, otherwise it will cause a lot of thread blocking.

So, there are several parameter settings associated with concurrency:innodb_thread_concurrency, Innodb_thread_sleep_delay, Innodb_concurrency_tickets, innodb_ Commit_concurrency.

Parameters similar to innodb_thread_concurrency are thread_concurrency, but it has been identified as obsolete in the 5.6 version of the official document, and this parameter was abolished in 5.7.2, so we do not involve testing and describing this parameter.

2 parameter Setting 2.1 innodb_thread_concurrency2.1.1 default value

Innodb_thread_concurrency default is 0, which means there is no limit on the number of concurrent threads, and all requests directly request thread execution. Note: when innodb_thread_concurrency is set to 0 o'clock, the Innodb_thread_sleep_delay setting is ignored and does not work. If the database does not have performance issues, use the default values.

2.1.2 Greater than 0

When innodb_thread_concurrency>0, it indicates a concurrency limit, and when a new request is initiated, it checks whether the current number of concurrent threads has reached the innodb_thread_concurrency limit, and if so, You need to sleep for a while (the settings for sleep are described in the next section), and then request again, and if the current number of concurrent requests reaches the limit, then it will go into the FIFO queue and wait for execution. When entering into the kernel execution, will get a consumer voucher ticket, then this thread, in the following multiple entry InnoDB do not need to repeat the above inspection steps, when the number of consumption, then this thread will be expelled, waiting for the next time to enter the InnoDB, Re-assign ticket.

2.1.3 Recommended configuration (from official website)

• When the number of concurrent user threads is less than 64, it is recommended to set innodb_thread_concurrency=0;
• If the load is unstable, sometimes low, and sometimes high to peak, it is recommended to set the innodb_thread_concurrency=128 first, and by constantly reducing this parameter, 96, 80, 64, etc. until you find the number of threads that can provide the best performance, for example, Suppose the system usually has 40 to 50 users, but the number of periodic increases to 60, 70, or even 200. You will find that performance is stable at 80 concurrent user settings, and performance is degraded if it is above this number. In this case, it is recommended to set the Innodb_thread_concurrency parameter to 80 to avoid affecting performance;
• If other apps are allowed on the DB server and you need to limit MySQL thread usage, you can set the number of threads that can be assigned to DB, but it is not recommended to run other applications on the DB, and this is not recommended, as this may result in the database not being optimally used by the hardware;
• Setting high values may cause performance degradation due to internal contention for system resources;
• In most cases, the best value is less than and close to the number of virtual CPUs;
• Monitor and analyze the db regularly, because as the database load changes and the business increases, innodb_thread_concurrency also needs to be dynamically adjusted.

2.2 Innodb_thread_sleep_delay

5.6.3 version, you need to test repeatedly to determine the Innodb_thread_sleep_delay value, and fixed to a value, after the 5.6.3 version, because Innodb automatically adjust the Innodb_thread_sleep_delay parameters:

• Innodb_adaptive_max_sleep_delay: Maximum sleep time, in microseconds

You can limit the maximum value of Innodb_thread_sleep_delay by setting the parameter innodb_adaptive_max_sleep_delay, without setting the Innodb_thread_sleep_delay value. Let InnoDB automatically follow the load to adjust, when the system load is high, InnoDB dynamically adjust Slee time can make the database stable operation.

2.3innodb_commit_concurrency

This value can only be the default value of 0,mysql without restricting concurrent commits. A greater than 0 means that n transactions are allowed to commit at the same point in time, and the range of n is 0-1000.  

Note: mysqld runtime, the value of innodb_commit_concurrency is not changed from 0 to 0, or 0 to 0, but allows to change from N to M (n and M are greater than 0)

2.4 Innodb_concurrency_tickets

The default is 5000 (based on 5.6,5.7).

If the Innodb_concurrency_tickets setting is smaller, it can be used for a system with more small things, so that the thread will be able to go back out quickly and provide it to other requests, and for large transactions, it may loop into the waiting queue and wait for the execution to complete. This consumes more time and resources , and if the innodb_concurrency_tickets is set up, it is suitable for systems with large transactions that are frequently operating, so that large transactions do not require frequent queue waiting queues and can be handled with fewer requests, but for small transactions, It means that they wait longer before they can queue into the kernel for execution. Therefore, when innodb_thread_concurrency>0, the need to adjust the innodb_concurrency_tickets up and down to achieve the best performance. It can be viewed through the queue of show engine InnoDB status, as well INFORMATION_SCHEMA.INNODB_TRX as TRX_CONCURRENCY_TICKETS viewing the number of purchases.

MySQL Innodb concurrency involves parameters