Details
-
New Feature
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
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
-
-
- Closed
-
- is caused by
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
- mentioned in
-
Page Loading...
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Summary | innodb-adaptive-hash-index does not scale with multiple threads | fix innodb-adaptive-hash-index scalability with multiple threads |
| 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 256 concurrent threads. |
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. |
| Assignee | Marko Mäkelä [ marko ] |
| Attachment | innodb-hashtest.sh [ 74086 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.11 [ 27614 ] | |
| Fix Version/s | 10.6.11 [ 28441 ] |
| Link |
This issue is caused by |
| Status | In Progress [ 3 ] | In Testing [ 10301 ] |
| Assignee | Marko Mäkelä [ marko ] | Michael Widenius [ monty ] |
| Assignee | Michael Widenius [ monty ] | Marko Mäkelä [ marko ] |
| Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Progress [ 3 ] |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue relates to |
| Link |
This issue blocks |
| Link |
This issue is caused by |
| Link |
This issue includes |
| Link |
This issue blocks |
| Assignee | Marko Mäkelä [ marko ] | Vladislav Lesin [ vlad.lesin ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Attachment | ahi.sh [ 74245 ] |
| Fix Version/s | 11.8 [ 29921 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Fix Version/s | 10.6 [ 24028 ] | |
| Fix Version/s | 10.11 [ 27614 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37209 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37214 ] |
| Labels | tech_debt |
| Link |
This issue relates to |
| Assignee | Vladislav Lesin [ vlad.lesin ] | Marko Mäkelä [ marko ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Testing [ 10301 ] |
| Assignee | Marko Mäkelä [ marko ] | Matthias Leich [ mleich ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37229 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37235 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37252 ] |
| Link | This issue is part of TODO-5071 [ TODO-5071 ] |
| Link |
This issue causes |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37261 ] |
| Assignee | Matthias Leich [ mleich ] | Marko Mäkelä [ marko ] |
| Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37274 ] |
| Status | Stalled [ 10000 ] | In Testing [ 10301 ] |
| Assignee | Marko Mäkelä [ marko ] | Matthias Leich [ mleich ] |
| Assignee | Matthias Leich [ mleich ] | Marko Mäkelä [ marko ] |
| Status | In Testing [ 10301 ] | Stalled [ 10000 ] |
| issue.field.resolutiondate | 2025-01-10 15:58:44.0 | 2025-01-10 15:58:43.663 |
| Fix Version/s | 11.8.1 [ 29961 ] | |
| Fix Version/s | 11.8 [ 29921 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37288 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37293 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37307 ] |
| Labels | tech_debt | Preview_11.8 tech_debt |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37326 ] |
| Issue Type | Task [ 3 ] | New Feature [ 2 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37404 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37412 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37420 ] |
| Remote Link | This issue links to "Page (MariaDB Confluence)" [ 37453 ] |