This topic describes the test procedure and result of each query scenario.
Scenario: Online report query
This implementation of TPC-H is derived from the TPC-H Benchmark and is not comparable to published TPC-H Benchmark results, as this implementation does not comply with all the requirements of the TPC-H Benchmark.
Background information
In online report query scenarios, typical analysis and computing requirements include TopN results from joins within a single table or across multiple tables. In this example, a decision analysis system for the retail industry is used to demonstrate the performance of AnalyticDB for MySQL. The data model is implemented based on TPC-H. For more information, see TPC-H dataset.
Test environments
An AnalyticDB for MySQL elastic cluster of version 3.1.3.4.
An open source ClickHouse instance with 32 cores and a kernel version of 20.3.
1 TB of data is tested.
Test results
1. The following code is used to query the number of active users that make transactions within the specified period of time:
select count(*) -- Aggregate the records. from ( select c_name, c_phone, o_totalprice from customer, orders where c_custkey = o_custkey and o_orderdate < date '1993-09-23' -- Specify the date range over which you want to query orders. and o_orderdate > date '1993-03-23' ) a;
The following table lists the test results.
Service
Time consumed
AnalyticDB for MySQL
0.7 seconds
Open source ClickHouse
2.5 seconds
2. The following code is used to query the details of the orders to be delivered. The orders are sorted by priority, such as by unit price. The data can be used to track the delivery of orders.
select l_orderkey, sum(l_extendedprice*(1-l_discount)) as revenue, -- Aggregate the potential revenues. o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'MACHINERY' -- Perform predicate filtering. and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-23' and o_orderdate > date '1995-02-23' -- Specify the date range over which you want to query orders. and l_shipdate > date '1995-03-23' and l_shipdate < date '1996-03-23' group by -- Perform grouping operations. l_orderkey, -- Specify the order identifier. o_orderdate, -- Specify the order date. o_shippriority -- Specify the delivery priority. order by revenue desc, -- Sort the orders in descending order of potential revenue. o_orderdate; limit 100;
The following table lists the test results.
Service
Time consumed
AnalyticDB for MySQL
1.2 seconds
Open source ClickHouse
10.7 seconds
3. The following code is used to query the revenues of the component suppliers in a specific region. The data can be used when you decide whether a distribution center needs to be established in a specific region.
select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue -- Aggregate the potential revenues. from customer, orders, lineitem, supplier, nation, region -- Associate the six tables. where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'EUROPE' -- Specify the region of the suppliers. and o_orderdate >= date '1996-01-01' -- Specify the date range over which you want to query orders. and o_orderdate < date '1996-01-01' + interval '1' year and l_shipdate > date '1996-02-23' and l_shipdate < date '1996-03-23' group by n_name order by -- Sort the suppliers in descending order of revenue. Take note of the difference between the GROUP BY and ORDER BY clauses. revenue desc limit 10
The following table lists the test results.
Service
Time consumed
AnalyticDB for MySQL
1.3 seconds
Open source ClickHouse
3.6 seconds
Scenario: Interactive analysis
This implementation of TPC-H is derived from the TPC-H Benchmark and is not comparable to published TPC-H Benchmark results, as this implementation does not comply with all the requirements of the TPC-H Benchmark.
Background information
In interactive analytics scenarios, business personnel perform exploratory data queries by using business intelligence (BI) tools or self-service query platforms. The query statements must support multi-dimensional combinations, multi-table joins, aggregate queries, subqueries, and filter conditions. The test in interactive analysis scenarios is implemented based on TPC-H. For more information, see TPC-H dataset.
Test environments
An AnalyticDB for MySQL elastic cluster
A high-performance cluster with four C8 instances (96 cores and 768 GB of memory)
An elastic cluster with six nodes (96 cores and 384 GB of memory)
An elastic cluster with one node (8 cores and 32 GB of memory)
Test results (unit: seconds, test data volume: 1 TB)
TPC-H query
Time consumed by the high-performance cluster
Time consumed by the 96-core elastic cluster
Q1
81.59
106.43
Q2
6.57
12.83
Q3
47.87
44.1
Q4
60.13
63
Q5
45.48
59.23
Q6
11.33
2.05
Q7
55.98
38.65
Q8
56.4
77.29
Q9
147.62
132.96
Q10
48.06
41.99
Q11
11.19
15
Q12
40.53
20.71
Q13
59.79
62.93
Q14
37.67
5.33
Q15
28.53
12.68
Q16
10.18
9.89
Q17
48.63
73.22
Q18
54.88
82.86
Q19
32.07
60.67
Q20
29.53
39.87
Q21
123.89
143.43
Q22
17.41
21.96
Total
1055.33
1127.07
Test results (unit: seconds, test data volume: 10 GB)
TPC-H query
Time consumed by the 8-core elastic cluster
Q1
12.916
Q2
0.905
Q3
3.693
Q4
5.589
Q5
5.156
Q6
0.169
Q7
2.910
Q8
4.815
Q9
14.284
Q10
3.747
Q11
0.892
Q12
1.347
Q13
3.613
Q14
0.404
Q15
0.967
Q16
0.927
Q17
9.932
Q18
9.215
Q19
1.428
Q20
1.798
Q21
11.708
Q22
1.147
Total
97.562
Scenario: Data warehouse extract-transform-load (ETL)
This implementation of TPC-DS is derived from the TPC-DS Benchmark and is not comparable to published TPC-DS Benchmark results, as this implementation does not comply with all the requirements of the TPC-DS Benchmark.
Background information
In ETL scenarios, data is cleaned, converted, processed, and computed in batches in data warehouses. ETL scenarios are characterized by large amounts of scanned data, large numbers of associated tables, complex computational logic, and long computing time. The test in ETL scenarios is implemented based on TPC-DS. For more information, see TPC-DS dataset.
Test environments
An AnalyticDB for MySQL elastic cluster
An elastic cluster with 12 nodes (192 cores and 768 GB of memory)
Test results (unit: milliseconds)
TPC-DS query
Time consumed by the 192-core elastic cluster
1
3,389
2
23,697
3
9,162
4
137,346
5
27,759
6
8,957
7
17,755
8
9,952
9
12,759
10
16,026
11
87,537
12
2,036
13
28,970
14
145,158
15
6,865
16
15,636
17
21,550
18
12,039
19
13,438
20
4,007
21
2,513
22
5,733
23
418,050
24
28,407
25
20,617
26
8,835
27
17,002
28
18,918
29
19,380
30
2,016
31
26,526
32
4,541
33
14,992
34
11,627
35
15,882
36
11,007
37
4,589
38
29,910
39
7,924
40
14,371
41
367
42
6,814
43
9,072
44
5,766
45
2,691
46
18,635
47
46,180
48
19,307
49
54,883
50
13,762
51
26,672
52
6,768
53
8,524
54
12,323
55
6,680
56
15,015
57
22,917
58
11,187
59
35,661
60
14,910
61
24,657
62
4,923
63
9,444
64
144,757
65
22,203
66
15,136
67
138,622
68
23,379
69
15,156
70
18,614
71
17,067
72
18,801
73
11,977
74
54,000
75
37,597
76
31,804
77
17,376
78
78,188
79
18,075
80
55,100
81
2,958
82
4,531
83
2,000
84
1,554
85
11,139
86
3,221
87
29,937
88
52,394
89
8,704
90
2,971
91
1,800
92
1,969
93
29,375
94
7,822
95
11,929
96
7,935
97
17,152
98
7,351
99
9,159
Total
2,595.79 seconds