MySQL Parallel Query Tuning
Application scenario: In MySQL query scenarios, only one thread can be scheduled for a single SQL query, and the multi-core CPU cannot be efficiently used. The performance of a single query is poor and cannot meet the performance requirements.
Technical principle: Parallel data read and processing accelerate query performance.
Performance metric: The query performance is more than doubled. (The performance improvement result is related to the degree of parallelism.) See the following table about the comparison of the Kunpeng 5250 processor's query performance before and after parallel tuning.
|
TPC-H (100 GB) |
Based on Open Source MySQL (Unit: s) |
Optimized Performance Using 8 Parallel Threads (Unit: s) |
|---|---|---|
|
Query 1 |
1433.936 |
241.53425 |
|
Query 3 |
34.578 |
84.17875 |
|
Query 4 |
423.378 |
23.0905 |
|
Query 5 |
60.976 |
180.92275 |
|
Query 6 |
405.554 |
77.13725 |
|
Query 7 |
168.744 |
170.77975 |
|
Query 8 |
468.347 |
17.47975 |
|
Query 9 |
462.33 |
108.01425 |
|
Query 10 |
125.827 |
80.97225 |
|
Query 12 |
263.934 |
83.17075 |
|
Query 14 |
385.567 |
96.70275 |
|
Query 19 |
20.121 |
5.78025 |
|
Query 21 |
853.048 |
406.74575 |
|
Total (13) |
5106.34 |
1576.509 |
|
Type |
Operator |
|---|---|
|
Filter conditions |
=, >, <, >=, <=, like, between...and, in |
|
Aggregate |
count, sum, avg, min, and max |
|
Join |
left join, right join, and join |
|
Sorting and grouping |
having, group by, order by, and limit |
|
Subqueries |
Subqueries that can be converted into simple queries by the optimizer |
Restrictions:
The following query statements cannot be executed concurrently: For more constraint information, see MySQL Parallel Query Tuning Feature Guide.
- The table to be queried is a system table, temporary table, or non-InnoDB table.
- Multi-table subquery
- Using full-text index
- Special fields (BLOB, JSON, and GEOMETRY) contained
- Query statements within a transaction at the serializable isolation level
- Query statement with the distinct keyword