Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
12.1.1
-
Q4/2025 Server Maintenance
Description
There was a requested investigation into AHI lock up under specific workloads. Although it was not possible to reproduce a complete lock up it was possible to create a test workload where the default AHI configuration resulted in poor performance and high levels of locking. Namely with the TPROC-C workload with use all warehouses, most of the AHI contention is on the STOCK table composite primary key index from the neword (insert, update) and slev (select) stored procedures.
Under this workload switching AHI on and off resulted in performance as follows:
AHI ON
19932 MariaDB tpm
|
CPU all usr%-11.93 sys%-81.33 irq%-0.00 idle%-6.73
|
AHI OFF
969048 MariaDB tpm
|
CPU all usr%-91.04 sys%-6.50 irq%-0.00 idle%-2.21
|
When AHI was partitioned few partitions were being used as partitioning is a hash function on the 64-bit index identifier. So, all entries of a particular index will reside in the same AHI partition. However the hash table size is shrunk proportionally for the number of partitions, so where there is a single heavily used index as in the test workload btr_search_guess_on_hash and btr_sea::partition::insert increase and native_queued_spin_lock_slowpath is the top function. Setting innodb_adaptive_hash_index_parts=1 gives improved performance in this case.
586686 MariaDB tpm
|
CPU all usr%-81.52 sys%-13.11 irq%-0.00 idle%-5.15
|
It is noted that this scenario may not always typically be the case and AHI partitioning may benefit depending on workload, however it was requested to find a scenario where the AHI experiences a lot of locking and the single busy partition in a partitioned AHI satisfies the request as the partitioning creates smaller individual hash table sizes.
DEFAULT (8 partitions)
-------------------
|
ADAPTIVE HASH INDEX
|
-------------------
|
Hash table size 18479729, node heap has 1945 buffer(s)
|
Hash table size 18479729, node heap has 1407 buffer(s)
|
Hash table size 18479729, node heap has 694 buffer(s)
|
Hash table size 18479729, node heap has 39187 buffer(s)
|
Hash table size 18479729, node heap has 1829 buffer(s)
|
Hash table size 18479729, node heap has 132319 buffer(s)
|
Hash table size 18479729, node heap has 938 buffer(s)
|
Hash table size 18479729, node heap has 822 buffer(s)
|
416250.27 hash searches/s, 307215.47 non-hash searches/s
|
 |
29.97% [kernel] [k] native_queued_spin_lock_slowpath
|
15.86% mariadbd [.] ssux_lock_impl<true>::rd_wait()
|
3.42% mariadbd [.] srw_mutex_impl<true>::wait_and_lock()
|
1.26% mariadbd [.] btr_search_guess_on_hash(dict_index_t*,
|
innodb_adaptive_hash_index_parts=64
-------------------
|
ADAPTIVE HASH INDEX
|
-------------------
|
Hash table size 2310107, node heap has 1131 buffer(s)
|
Hash table size 2310107, node heap has 1286 buffer(s)
|
Hash table size 2310107, node heap has 0 buffer(s)
|
Hash table size 2310107, node heap has 414 buffer(s)
|
Hash table size 2310107, node heap has 419 buffer(s)
|
Hash table size 2310107, node heap has 416 buffer(s)
|
Hash table size 2310107, node heap has 403 buffer(s)
|
Hash table size 2310107, node heap has 405 buffer(s)
|
Hash table size 2310107, node heap has 415 buffer(s)
|
Hash table size 2310107, node heap has 398 buffer(s)
|
Hash table size 2310107, node heap has 438 buffer(s)
|
Hash table size 2310107, node heap has 391 buffer(s)
|
Hash table size 2310107, node heap has 427 buffer(s)
|
Hash table size 2310107, node heap has 65649 buffer(s)
|
Hash table size 2310107, node heap has 3 buffer(s)
|
Hash table size 2310107, node heap has 0 buffer(s)
|
-----> REPEATED 0 buffers(s)
|
Hash table size 2310107, node heap has 0 buffer(s)
|
Hash table size 2310107, node heap has 15979 buffer(s)
|
Hash table size 2310107, node heap has 3 buffer(s)
|
Hash table size 2310107, node heap has 16 buffer(s)
|
Hash table size 2310107, node heap has 104 buffer(s)
|
Hash table size 2310107, node heap has 148 buffer(s)
|
46577.08 hash searches/s, 74308.54 non-hash searches/s
|
---
|
65.99% [kernel] [k] native_queued_spin_lock_slowpath
|
6.37% mariadbd [.] ssux_lock_impl<true>::rd_wait()
|
1.73% mariadbd [.] btr_sea::partition::insert(unsigned int, unsigned char const*)
|
1.31% mariadbd [.] srw_mutex_impl<true>::wait_and_lock()
|
innodb_adaptive_hash_index_parts=1
-------------------
|
ADAPTIVE HASH INDEX
|
-------------------
|
Hash table size 147836747, node heap has 180052 buffer(s)
|
387774.60 hash searches/s, 248436.08 non-hash searches/s
|
---
|
15.15% mariadbd [.] ssux_lock_impl<true>::rd_wait()
|
12.91% [kernel] [k] native_queued_spin_lock_slowpath
|
4.83% mariadbd [.] srw_mutex_impl<true>::wait_and_lock()
|
1.51% mariadbd [.] btr_search_guess_on_hash(dict_index_t*, dtuple_t const*, bool, btr_latch_mode, btr_cur_t*, mtr_t*)
|
Attachments
Issue Links
- relates to
-
MDEV-35049 fix innodb-adaptive-hash-index scalability with multiple threads
-
- Closed
-
-
SAMU-322 Loading...