Details
-
Task
-
Status: In Progress (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
InnoDB adaptive indexes has performance problems when running queries in parallel that uses InnoDB hash indexes (at least on the same tables).
Here is an example of running the same SELECT query with 10-256 concurrent threads.
(The query normally takes about 0.36 seconds):
Total execution with 10 threads time: 1 seconds
Total execution with 32 threads time: 1 seconds
Total execution with 64 threads time: 3 seconds
Total execution with 128 threads time: 5 seconds
Total execution with 256 threads time: 9 seconds
When using --innodb-adaptive-hash-index=1 we get:
Total execution with 10 threads time: 1 seconds
Total execution with 32 threads time: 5 seconds
Total execution with 64 threads time: 9 seconds
Total execution with 128 threads time: 18 seconds
Total execution with 256 threads time: 50 seconds
MySQL 5.7 does not have this issue. It has roughly the same speed with and without the hash indexes.
In the above query, there was a driving table with a 400 rows doing lookups in a child table using 200 lookups on a secondary keys for every row from the driving table.
Using innodb-adaptive-hash-index=1 speeds up the query with 37% when running a query in one thread but it slows down drastically when running 32-256 concurrent threads.
Attachments
Issue Links
- is caused by
-
MDEV-22646 Assertion `table2->cached' failed in dict_table_t::add_to_cache
- Closed
- relates to
-
MDEV-35189 Updating cache for INFORMATION_SCHEMA.INNODB_LOCKS et al is suboptimal
- In Review
-
MDEV-35190 HASH_SEARCH() is duplicating effort before HASH_INSERT() or HASH_DELETE()
- In Review
-
MDEV-35247 ut_fold_ull() is a waste on 64-bit systems
- In Review