This topic describes how to use the TPC-H benchmark to test the online analytical processing (OLAP) performance of PolarDB for MySQL 8.0.1 and 8.0.2 Cluster Edition Edition clusters. You can follow the instructions in this topic to test the performance of your database.
Parallel query
PolarDB for MySQL 8.0 Cluster Edition Edition provides a parallel query feature to reduce the response time of queries. This feature is automatically enabled when the volume of data queried exceeds a specified threshold. For more information, see Overview.
At the storage layer of a PolarDB for MySQL 8.0.1 cluster, data shards are distributed to different threads. Multiple threads perform parallel computing and return the results in sequence to the leader thread. Then, the leader thread merges the results and returns the final result to the user. This improves query efficiency.
In PolarDB for MySQL 8.0.2, the linear acceleration capabilities of parallel queries are brought to a new level and multi-phase parallel computing capabilities are provided. Cost-based optimization enables more flexible parallel execution plans and overcomes the performance bottleneck of a single leader thread and the imbalanced loads of workers that may exist in parallel queries of PolarDB for MySQL 8.0.1. Specifically, data shards are still distributed to different threads at the storage layer. Multiple threads perform parallel computing and distribute the data shards to the parallel worker group in the next phase. Then, the leader thread merges the computing results and returns the final result to the user. This greatly improves parallel execution capabilities.
Parallel queries are achieved based on the parallel processing capabilities of multi-core CPUs. The following figure shows parallel processing in a cluster with eight cores and 32-GB memory.
The following tables show the values of parallel query parameters that are set for the OLAP tests of PolarDB 8.0.1 and 8.0.2 clusters. For more information about how to configure the parallel query parameters, see Configure cluster and node parameters. For more information about the test tools, methods, and results, see Performance test for parallel queries and Test results.
8.0.1
Parameter
Default value
Valid value (Option 1)
loose_parallel_degree_policy
REPLICA_AUTO
TYPICAL
loose_max_parallel_degree
0
16
8.0.2
Parameter
Default value
Valid value (Option 1)
loose_parallel_degree_policy
REPLICA_AUTO
TYPICAL
loose_parallel_query_switch
''
'force_run_using_planning_dop=on'
loose_optimizer_switch
''
'hash_join_cost_based=off'
loose_max_parallel_degree
0
16
If parallel_degree_policy is set to TYPICAL, PolarDB ignores the resource usage of databases (such as CPU utilization) and sets the degree of parallelism (DOP) to the value of the max_parallel_degree parameter.
Test results
The following table describes the test results of PolarDB 8.0.1:
Query
Duration of the default value (Unit: seconds)
Duration of the valid value (option 1) (Unit: seconds)
Q1
2495.05
86.63
Q2
13.18
1.46
Q3
345.56
28.67
Q4
63.78
6.26
Q5
251.48
258.55
Q6
241.7
16.4
Q7
174.91
171.85
Q8
560.82
572.63
Q9
450.68
42.17
Q10
895.75
270.29
Q11
30.03
11.6
Q12
266.14
17.65
Q13
780.74
127.1
Q14
72.04
7.57
Q15
261.77
56.55
Q16
40.69
24.29
Q17
57.75
13.04
Q18
257.66
136.79
Q19
19.17
1.52
Q20
143.97
49.72
Q21
293.99
37.79
Q22
18.81
2.15
The following table describes the test results of PolarDB 8.0.2:
Query
Duration of the default value (Unit: seconds)
Duration of the valid value (option 1) (Unit: seconds)
Q1
573.34
89.65
Q2
3.62
0.66
Q3
96.89
28.52
Q4
18.66
6.41
Q5
150.93
28.93
Q6
68.13
17.13
Q7
114.56
17.39
Q8
333.36
54.32
Q9
136.26
42.15
Q10
325.19
128.55
Q11
11.49
4.53
Q12
121.68
17.54
Q13
281.1
126.2
Q14
23.34
7.59
Q15
77.22
33.02
Q16
12.15
8.96
Q17
4.51
1.13
Q18
325.05
133.07
Q19
5.37
1.5
Q20
70.31
90.75
Q21
171.81
29.87
Q22
6.05
2.16
