Some colleagues asked a few MySQL parameter settings. Here we will record the following: innodb_thread_concurrency, which is used to limit the number of threads that can enter the innodb layer when they enter innod.
Some colleagues asked a few MySQL parameter settings. Here we will record the following: innodb_thread_concurrency, which is used to limit the number of threads that can enter the innodb layer when they enter innod.
Some colleagues asked a few MySQL parameter settings. Here is a simple record:
Innodb_thread_concurrency, used to limit the number of threads that can enter the innodb Layer
When read_row/write_row/update_row/delete_row is called at the innodb layer, innodb_srv_conc_enter_innodb is checked.
If it is full, it will wait for innodb_thread_sleep_delay to try it once in milliseconds.
If it fails again, it will enter a FIFO queue sleep
After the innodb layer completes the operation, innodb_srv_conc_exit_innodb is called to exit the innodb layer.
When the thread enters, it obtains a period of time slice innodb_concurrency_tickets. Within the time slice range, this thread does not need to be detected and directly enters innodb
Theoretically, we can set innodb_thread_concurrency to (number of CPUs + number of disks) * 2, but this depends on the specific application scenario.
Innodb_commit_concurrency, used to limit the number of threads in the innodb layer commit stage. In most cases, the default value is sufficient.
--------------------------
Innodb_write_io_threads
Number of threads used to write dirty pages
Innodb_read_io_threads
Number of threads used to read file blocks from a disk
Thread_cache_size
After the thread is used, it is not exited, but stored in the list, which reduces the overhead of repeatedly creating threads,
The setting of this value should be related to memory: mem = (thread_cache_size) * (thread_stack)
Max_connections
The maximum number of connection requests allowed by the MySQL layer. Note that setting too large may cause a large overhead: thread switching overhead. Currently, a popular optimization solution is to use a thread pool to store request queues and establish a limited number of work threads to process connections. In other words, even if max_connections is set to a large value, innodb_thread_concurrency is also applied.