| Bug #119997 | Setting innodb_use_native_aio to OFF can lead to severe lock contention. | ||
|---|---|---|---|
| Submitted: | 6 Mar 13:12 | Modified: | 19 Mar 3:05 |
| Reporter: | linkang zhang | Email Updates: | |
| Status: | Open | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.40 | OS: | Any |
| Assigned to: | Marcin Babij | CPU Architecture: | Any |
| Tags: | aio, dirty pages, purge | ||
[17 Mar 13:11]
Marcin Babij
Hello Linkang, Thank you for the report. What you report is known and real problem - the Simulated AIO was never highly concurrent nor optimized. Native AIO should be used always on high end machines that require such big number of IO threads. This could be viewed as misconfiguration - either enable native AIO or consider decreasing the number of all thread counts described in the report, or use a system for which MySQL has the native AIO support. This issue will be left open, it may be fixed in the future.
[19 Mar 3:03]
linkang zhang
In fact, when you run MySQL on cloud disks, AIO must be adjusted to 64 or higher, and we often set write_io_threads to more than 100 concurrent threads.
[19 Mar 3:05]
linkang zhang
In cloud storage scenarios, native AIO is not supported, so it is recommended to fix this bug as much as possible.
[20 Mar 15:06]
Jean-François Gagné
linkang zhang: I am curious of the use-case described in this bug, which I tried to summarized below. Can you share a little more about this, including on which hardware and OS it is used ? I am curious in what case you need so many page cleaners and purge threads, probably related to high latency IOs, but would be good to confirm. Also, in which Cloud Scenario are native AIO is not supported ? Maybe this bug report is not the best place to have such discussion, feel free to contact me via email, LinkedIn, MySQL Community Slack or others (all described in https://linktr.ee/jfg.mysql: jfg DOT mysql AT gmail.com, https://www.linkedin.com/in/jfg956/, @jgagne on MySQL Community Slack, and more). - innodb_use_native_aio=OFF - innodb_read_io_threads=64 - innodb_write_io_threads=64 - innodb_page_cleaners=32 - innodb_purge_threads=64 > when you run MySQL on cloud disks, AIO must be adjusted to 64 or higher, and we often set write_io_threads to more than 100 concurrent threads > In cloud storage scenarios, native AIO is not supported
Description: When we set innodb_use_native_aio=OFF, and also set innodb_read_io_threads=64 and innodb_write_io_threads=64, along with innodb_page_cleaners=32 and innodb_purge_threads=64, the printed perf statements reveal severe lock contention. How to repeat: Further investigation shows that the purge thread calls wake_simulated_handler_thread, and the flushing thread calls both AIO::reserve_slot and wake_simulated_handler_thread. /** Wakes up simulated aio i/o-handler threads if they have something to do. */ void os_aio_simulated_wake_handler_threads() { if (srv_use_native_aio) { /* We do not use simulated aio: do nothing */ return; } os_aio_recommend_sleep_for_read_threads = false; for (ulint i = 0; i < os_aio_n_segments; i++) { AIO::wake_simulated_handler_thread(i); } } In the function above, the value of os_aio_n_segments is 129. If there are 64 purge threads, it will call wake_simulated_handler_thread 64 * 129 = 8256 times each time. This wake_simulated_handler_thread，it needs to acquire the global lock for aio, and calling it too many times will lead to severe lock contention. wake_simulated_handler_thread also needs to be called when mysql flush dirty page, if there are 32 page cleaner threads, then wake_simulated_handler_thread will be called 32 * 129 = 4128 times each time. Clearly, this will lead to severe lock contention. And we saw it in perf. You need a good understanding of the purge and flushing processes to understand what I'm saying. Suggested fix: /** Wakes up a simulated AIO I/O-handler thread if it has something to do for a local segment in the AIO array. @param[in] global_segment The number of the segment in the AIO arrays @param[in] segment The local segment in the AIO array */ void AIO::wake_simulated_handler_thread(ulint global_segment, ulint segment) { ut_ad(!srv_use_native_aio); ulint n = slots_per_segment(); ulint offset = segment * n; /* Look through n slots after the segment * n'th slot */ acquire(); const Slot* slot = at(offset); for (ulint i = 0; i < n; ++i, ++slot) { if (slot->is_reserved) { /* Found an i/o request */ release(); os_event_t event; event = os_aio_segment_wait_events[global_segment]; os_event_set(event); return; } } release(); } This function requires acquiring a global lock each time, which can easily lead to performance issues. This large lock can be modified into up to 129 smaller locks, each used by a separate `write_io_thread` or 'read_io_thread' upon wakeup. Or you could try other methods to fix it.