Scenario: Online report query

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

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: 100 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)

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