This topic describes the results of TPC Benchmark H (TPC-H) benchmark tests described in the "Test plan" topic for your reference.

Background information

The test results in this topic are the results of tests performed in a Hologres instance that is deployed on Alibaba Cloud.

The following tables describe the number of rows in each table and cluster specifications used for TPC-H benchmark tests.

Test results of a cluster with 100 GB storage capacity

  • The following tables describe the number of rows in each table and cluster specifications used for TPC-H benchmark tests.
    • Number of rows in each table
      Table name Number of rows
      LINEITEM 600,037,902
      ORDERS 150,000,000
      PARTSUPP 80,000,000
      PART 20,000,000
      CUSTOMER 15,000,000
      SUPPLIER 1,000,000
      NATION 25
      REGION 5
    • Cluster specifications
      Computing resources Storage capacity Version Remarks
      64 compute units (CUs)

      (64 CPU cores and 256 GB of memory)

      100 GB r0.10.20 Uses the default configurations of the cluster. The number of shards is 40.
      128 CUs

      (128 CPU cores and 512 GB of memory)

      100 GB r0.10.20 Uses the default configurations of the cluster. The number of shards is 80.
  • Test results
    • Import time in different scenarios

      The import time indicates the amount of time required to import data to Hologres. The amount of time is measured in seconds. The following table describes the import time in different scenarios that provide 64 CUs of computing resources.

      Note If you execute the COPY FROM STDIN statement to import data, data is imported from one file to the corresponding table in Hologres at a time. Concurrent import is unavailable.
      Table name Number of rows Data amount Amount of time used to copy data to Hologres over the Internet Amount of time used to copy data to Hologres in a VPC Amount of time used to import data to Hologres by using a MaxCompute foreign table
      LINEITEM 600,037,902 73.6 GB 3,070.453 694.364 148.165
      ORDERS 150,000,000 16.4 GB 691.060 172.529 37.741
      PARTSUPP 80,000,000 2.3 GB 468.560 107.092 18.488
      PART 20,000,000 11.3 GB 96.342 24.020 8.083
      CUSTOMER 15,000,000 2.3 GB 95.190 22.937 10.363
      SUPPLIER 1,000,000 132 MB 5.057 1.803 1.503
      NATION 25 2 KB 0.580 0.584 0.747
      REGION 5 0.375 KB 0.168 0.153 0.430
      ORDERS_ROW 150,000,000 16.4 GB 717,653 143.800 25.676
      Total 122.4 GB 722,080.410 1,167.282 251.196

      In the following bar chart, a blue bar indicates the amount of time used to copy data to Hologres over the Internet. A green bar indicates the amount of time used to copy data to Hologres in a virtual private cloud (VPC). A gray bar indicates the amount of time used to import data to Hologres by using a MaxCompute foreign table. A smaller y-axis value indicates a faster import speed.

      Note The x-axis represents the table name, and the y-axis represents the import time measured in seconds.

      The following conclusions can be drawn based on the bar chart:

      • If you execute the COPY FROM STDIN statement to import data, the import time is affected by the bandwidth. The amount of time used to copy data to Hologres in a VPC is less than that used over the Internet.
      • The amount of time used to import data by using a MaxCompute foreign table is less than the amount of time used to import data by executing the COPY FROM STDIN statement.
      Import time presented in a bar chart
    • Queries in online analytical processing (OLAP) queries
      The amount of time used to query data in the internal tables of Hologres is measured in seconds. The following table describes the execution time of each query in Hologres instances with different computing resources.
      Query ID Hologres (64 CUs) Hologres (128 CUs)
      1 1.99 1.23
      2 0.61 0.43
      3 1.58 0.72
      4 1.18 0.51
      5 2.65 1.95
      6 0.17 0.11
      7 1.65 0.77
      8 2.43 1.29
      9 5.49 2.66
      10 1.65 0.62
      11 0.36 0.32
      12 1.15 0.63
      13 1.55 0.75
      14 0.27 0.19
      15 0.26 0.20
      16 1.05 0.89
      17 1.18 0.52
      18 11.52 1.83
      19 1.00 0.70
      20 1.10 1.08
      21 3.83 2.37
      22 0.94 0.46
      Total 43.58 20.23

      In the following bar chart, a blue bar indicates the amount of time used to query data in a Hologres instance with 64 CUs of computing resources. A green bar indicates the amount of time used to query data in a Hologres instance with 128 CUs of computing resources. A smaller y-axis value indicates a better performance of the instance in the TPC-H benchmark test. Higher specifications of the instance bring about a shorter execution time for each query.

      Note The x-axis represents the ID of the query in the table, and the y-axis represents the execution time measured in seconds.
      Execution time of each query in Hologres presented in a bar chart
    • Point queries of key-value pairs

      The test results are based on queries in Hologres internal tables. The version of Hologres is V1.1.42. Each type of query runs continuously for 5 minutes. The following table describes the specific values in the test results.

      Scenario Sample SQL statement Instance type Number of concurrent client connections QPS (including the connection duration) QPS (excluding the connection duration) Average query latency
      1
      SELECT  O_ORDERKEY
              ,O_CUSTKEY
              ,O_ORDERSTATUS
              ,O_TOTALPRICE
              ,O_ORDERDATE
              ,O_ORDERPRIORITY
              ,O_CLERK
              ,O_SHIPPRIORITY
              ,O_COMMENT
      FROM    public.orders_row
      WHERE   o_orderkey = {parameter}
      ;
      64 CUs 500 112,435 112,443 4.447 ms
      128 CUs 1,000 242,755 242,772 4.119 ms
      2
      SELECT  O_ORDERKEY
              ,O_CUSTKEY
              ,O_ORDERSTATUS
              ,O_TOTALPRICE
              ,O_ORDERDATE
              ,O_ORDERPRIORITY
              ,O_CLERK
              ,O_SHIPPRIORITY
              ,O_COMMENT
      FROM    public.orders_row
      WHERE   o_orderkey in
      (
          {parameter1},
          {parameter2},
          {parameter3},
          {parameter4},
          {parameter5},
          {parameter6},
          {parameter7},
          {parameter8},
          {parameter9}
      );
      64 CUs 500 27,632 27,634 18.094 ms
      128 CUs 1,000 90,086 90,092 11.100 ms
      The following conclusion can be drawn based on the preceding table: The QPS linearly increases with the instance specifications.
    • Data update

      The test results are based on queries in Hologres internal tables. The version of Hologres is V1.1.42. Each type of query runs continuously for 5 minutes. The following table describes the specific values in the test results.

      Sample SQL statement Instance type Number of concurrent client connections QPS (including the connection duration) QPS (excluding the connection duration) Average query latency
      INSERT INTO public.orders_row(
          o_orderkey
          ,o_custkey
          ,o_orderstatus
          ,o_totalprice
          ,o_orderdate
          ,o_orderpriority
          ,o_clerk
          ,o_shippriority
          ,o_comment
      ) VALUES (
        {parameter}
        ,1
        ,'demo'
        ,1.1
        ,'2021-01-01'
        ,'demo'
        ,'demo'
        ,1
        ,'demo') 
      on conflict(o_orderkey) 
      do update set (
        o_orderkey
        ,o_custkey
        ,o_orderstatus
        ,o_totalprice
        ,o_orderdate
        ,o_orderpriority
        ,o_clerk
        ,o_shippriority
        ,o_comment
      )= ROW(excluded.*);
      64 CUs 500 92,006 92,000 5.435 ms
      128 CUs 1,000 191,761 191,771 5.215 ms
      The following conclusion can be drawn based on the preceding table: The QPS linearly increases with the instance specifications.

Test results of a cluster with 1 TB storage capacity

  • The following tables describe the number of rows in each table and cluster specifications used for TPC-H benchmark tests.
    • Number of rows in each table
      Table name Number of rows Data amount
      LINEITEM 5,999,989,709 752.27 GB
      ORDERS 1,500,000,000 167.11 GB
      PARTSUPP 800,000,000 114.45 GB
      PART 150,000,000 22.94 GB
      CUSTOMER 200,000,000 22.85 GB
      SUPPLIER 10,000,000 1.33 GB
      NATION 25 2.15 KB
      REGION 5 0.38 KB
    • Cluster specifications
      Computing resources Storage capacity Version Remarks
      96 CUs

      (96 CPU cores and 384 GB of memory)

      1000 GB r1.1.24 Uses the default configurations of the cluster. The number of shards is 60.
  • Test results: execution time of each query in Hologres

    The amount of time used to query data in the internal tables of Hologres is measured in seconds. The following table describes the execution time of each query in Hologres.

    Query ID Execution time
    1 12.66
    2 1.43
    3 10.79
    4 8.13
    5 14.51
    6 1.48
    7 8.10
    8 12.62
    9 28.20
    10 12.19
    11 3.27
    12 8.96
    13 12.78
    14 1.10
    15 1.36
    16 5.67
    17 8.37
    18 27.78
    19 9.25
    20 4.11
    21 25.80
    22 4.51
    Total 223.08