wlad please provide descriptions for these variables. You can add them here and reassign to me to add in the KB if you wish.

Hi Ian,

To understand these variables, it's essential to first grasp how asynchronous IO works in InnoDB. Since this cannot be explained in just a few words, I'll start with a simplified overview of the process.

Phases of Asynchronous IO in InnoDB

Each asynchronous IO operation goes through multiple stages:

• SUBMITTED – The IO operation is initiated.
• For asynchronous writes, this typically occurs in the buffer pool flushing code.
• For asynchronous reads, this may happen during buffer pool loading at startup or in prefetching logic (though I am less familiar with the specifics of the latter).

• COMPLETED_IN_OS – The operating system notifies InnoDB that the I/O operation is complete.
• If using libaio or io_uring, a dedicated thread handles this notification.
• The completed IO operation is then submitted to InnoDB’s internal thread pool (tpool).

• EXECUTING_COMPLETION_TASK – A tpool thread processes the completion task for the IO operation.

• COMPLETED – The IO operation is fully handled.

Resource Constraints and Queuing Mechanisms

Waiting for IO Slots

The total number of pending asynchronous IO operations is limited by:

total_count = number_of_IO_threads * 256  

Where number_of_IO_threads refers to either innodb_io_read_threads or innodb_io_write_threads.

Each IO operation is associated with an IO slot, which contains necessary metadata such as the file handle, operation type, offset, length, and any OS error codes. Initially, all total_count slots are free, but as pending IO requests accumulate, slots get occupied. If all slots are in use, additional IO requests must wait for a free slot.

Queuing Mechanism

The number of completion tasks (EXECUTING_COMPLETION_TASK stage) that can run in parallel is also limited by innodb_io_read_threads or innodb_io_write_threads. If too many IO operations complete simultaneously, they cannot all be processed in parallel and must be queued, respecting the thread limit.

Description of Variables

Now, with this background, here’s what the status variables represent (focusing on read operations, but the same exact logic applies to write operations):

• innodb_async_reads_pending – The number of read IO operations currently in progress (from SUBMITTED to COMPLETED).
• innodb_async_reads_tasks_running – The number of read IO operations currently in the EXECUTING_COMPLETION_TASK state.
• innodb_async_reads_total_count – The total number of read completion tasks that have finished execution.
• innodb_async_reads_queue_size – The current size of the queue (see Queuing Mechanism).
• innodb_async_reads_wait_slot_sec – The total wait time for a free IO slot (see Waiting for IO Slots).

Let me know if you need further clarification.

Thanks wlad, that's very helpful. And Innodb_async_reads_total_enqueues refers to the total number of completed read operations that were queued?

@Ian . Innodb_async_reads_total_enqueues - total number of read operations that were queued (see Queuing Mechanism) . Not necessarily all of them are finished (i.e it this number already includes current queue size, aka Innodb_async_reads_queue_size)