InnoDB Asynchronous I/O
Contents
From MariaDB 10.5, InnoDB uses asynchronous I/O to read from and write to disk asynchronously. This forms part of the InnoDB Background Thread Pool.
Stages
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.
- 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.
Variables
From MariaDB 11.5, a number of status variables were added to give insight into the above operations:
- innodb_async_reads_pending – Number of read IO operations currently in progress (from SUBMITTED to COMPLETED).
- innodb_async_reads_tasks_running – Number of read IO operations currently in the EXECUTING_COMPLETION_TASK state.
- innodb_async_reads_total_count – Total number of read completion tasks that have finished execution.
- innodb_async_reads_queue_size – Current size of the queue (see Queuing Mechanism).
- innodb_async_reads_wait_slot_sec – Total wait time for a free IO slot (see Waiting for IO Slots).
- innodb_async_reads_total_enqueues – Total number of read operations that were queued (see Queuing Mechanism). Includes those still waiting and making up
innodb_async_reads_queue_size.
Similar variables exist for write operations:
