Root Cause Analysis of MySQL InnoDB Thread Concurrency Blocking and Nanosleep Behavior

The author, a MySQL DBA, received a report that a test application showed abnormal behavior despite normal CPU, memory, and I/O metrics. The database configuration was MySQL 8.0 on a single‑node server with 8 CPUs, 16 GB RAM, innodb_thread_concurrency set to 16, and other default parameters.

Initial analysis suggested that slow SQL statements were consuming server resources, but CPU and I/O usage remained low. Examination of SHOW PROCESSLIST revealed many sessions in the executing state, and a few in updating. No errors were found in MySQL error logs, disk usage, or system messages.

Diagnostic commands were executed to collect detailed information:

Diagnostic Item

SQL Command

Connection status

show processlist;

Thread status

select * from performance_schema.threads where processlist_info\G

Transaction info

select * from information_schema.innodb_trx\G

InnoDB status

show engine innodb status\G

Stack trace

pstack <mysqld-pid>

After a database restart the application recovered, prompting a deeper investigation.

Stack and Source Code Analysis

Correlation of connection, thread, and stack data showed that 13 of the 29 blocked sessions were stuck in the nanosleep function. The relevant stack trace was:

#0  in nanosleep from /lib64/libpthread.so.0
#1  in srv_conc_enter_innodb
#2  in ha_innobase::index_read
#3  in ha_innobase::index_first
#4  in handler::ha_index_first
#5  in IndexScanIterator<false>::Read
#6  in Query_expression::ExecuteIteratorQuery
#7  in Query_expression::execute
#8  in Sql_cmd_dml::execute
#9  in mysql_execute_command
#10 in dispatch_sql_command
#11 in dispatch_command
#12 in do_command
#13 in handle_connection

The source code of srv_conc_enter_innodb shows a loop that, when the number of active InnoDB threads reaches innodb_thread_concurrency, puts the transaction into a sleeping state using nanosleep. The simplified logic is:

|-index_read(...)
  |-ret = innobase_srv_conc_enter_innodb(...)
    |-if (srv_thread_concurrency) {
        |-if (trx->n_tickets_to_enter_innodb > 0) --trx->n_tickets_to_enter_innodb
        |-else {
            |-err = srv_conc_enter_innodb_with_atomics(trx)
            |-for (;;) {
                |-if (srv_thread_concurrency == 0) return DB_SUCCESS;
                |-if (srv_conc.n_active < srv_thread_concurrency) {
                    |-enter InnoDB, set tickets, return DB_SUCCESS;
                |-else {
                    |-trx->op_info = "sleeping before entering InnoDB";
                    |-sleep_in_us = srv_thread_sleep_delay;
                    |-if (srv_adaptive_max_sleep_delay > 0 && sleep_in_us > srv_adaptive_max_sleep_delay)
                        |-sleep_in_us = srv_adaptive_max_sleep_delay;
                    |-std::this_thread::sleep_for(...);
                    |-trx->op_info = "";
                    |-++n_sleeps;
                }
            }
        }
    }
  |-ret = row_search_mvcc(...)
  |-innobase_srv_conc_exit_innodb(...)

Four scenarios were identified based on the value of innodb_thread_concurrency and ticket availability, illustrated with diagrams in the original article.

Root Cause

The test database had innodb_thread_concurrency=16. During the incident, slow SQL statements consumed all InnoDB tickets, causing other transactions to exceed the concurrency limit and be forced into nanosleep. This resulted in many sessions appearing to be executing simple queries while actually waiting for InnoDB resources.

Solution

Temporary relief was achieved by restarting MySQL. A permanent fix includes:

Setting innodb_thread_concurrency=0 (or a value appropriate to the workload) to disable the artificial concurrency cap.

Optimising the slow SQL that exhausted tickets.

Monitoring executing sessions and the transaction state sleeping before entering InnoDB to quickly detect recurrence.

Additional recommendations: for low‑concurrency workloads (<64 active threads) use 0; for heavy workloads experiment with values such as 128, then decrement to find the optimal setting, as overly high values can degrade performance.

Keywords: #MySQL# #source code#