All Products
Search
Document Center

AnalyticDB for MySQL:Query scenarios

Last Updated:Dec 30, 2023

This topic describes the test procedure and result of each query scenario.

Scenario: Online report query

Note

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

Note

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)

Note

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