Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
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
- causes
-
MDEV-35649 Crash on macOS on preview-11.8-preview branch
- Closed
- includes
-
MDEV-35312 page_cur_search_with_match() could avoid rec_get_offsets()
- In Testing
- is caused by
-
MDEV-22456 Dropping the adaptive hash index may cause DDL to lock up InnoDB
- Closed
-
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
- Closed
-
MDEV-35190 HASH_SEARCH() is duplicating effort before HASH_INSERT() or HASH_DELETE()
- Closed
-
MDEV-35247 ut_fold_ull() and ut_hash_ulint() are a waste
- Closed
-
MDEV-35485 The test innodb.innodb_buffer_pool_resize occasionally crashes
- Open
- mentioned in
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...