All Products
Search

This Product

    PolarDB:IMCI performance

    Document Center

    PolarDB:IMCI performance

    Last Updated:Nov 23, 2023

    This topic describes how to use the TPC Benchmark-H (TPC-H) to test the online analytical processing (OLAP) performance of a PolarDB MySQL 8.0.1 cluster of Cluster Edition when the In-Memory Column Index (IMCI) feature is enabled.

    PolarDB for MySQL IMCIs

    PolarDB for MySQL provides the IMCI feature to improve the OLAP performance in scenarios where complex queries are performed on large amounts of data. PolarDB for MySQL provides a one-stop hybrid transaction/analytical processing (HTAP) solution based on IMCI to implement integrated real-time transaction processing and data analysis.

    The following figure shows how the IMCI feature works in PolarDB for MySQL.HTAP

    For more information, see Overview.

    Usage notes

    • You can determine whether to enable the IMCI feature based on your business requirements. After you enable the IMCI feature, you can add read-only column store nodes to process OLAP query requests. This isolates the computing resources for online transaction processing (OLTP) and OLAP. For more information about how to add a read-only column store node, see Add a read-only column store node.

    • To test the IMCI performance of a PolarDB cluster, you must create a custom cluster endpoint for the read-only column store node and specify to route requests from the custom cluster endpoint to the read-only column store node. The endpoint is used to test IMCI performance. For more information, see Manual request distribution between row store and column store nodes for independent OLTP and OLAP services.

    • You do not need to create a secondary index for each table used in TPC-H-based IMCI performance tests. For more information, see OLAP performance test.

    Performance tests

    TPCH 100 GB

    Test environment 1: a cluster that has 32 cores and 256 GB of memory

    In the following test, 100 GB of data that is generated based on TPC-H and a cluster that has 32 cores and 256 GB of memory are used to compare query performance when the IMCI feature is enabled and disabled.

    The following table shows the test results.

    Query

    Query time on one machine

    with the IMCI feature disabled (seconds)

    Query time on one machine

    with the IMCI feature enabled (seconds)

    Query time on two machines

    with the IMCI feature enabled (seconds)

    Query time on three machines

    with the IMCI feature enabled (seconds)

    Query time on four machines

    with the IMCI feature enabled (seconds)

    Acceleration ratio between column store nodes and row store nodes

    Acceleration ratio in multi-node parallel query mode

    Q1

    2495.05

    4.67

    2.62

    2.15

    1.5

    534.27

    0.78

    Q2

    13.18

    0.72

    0.53

    0.49

    0.45

    18.31

    0.4

    Q3

    345.56

    2.44

    1.37

    1.01

    0.81

    141.62

    0.75

    Q4

    63.78

    1.46

    0.66

    0.52

    0.37

    43.68

    0.99

    Q5

    251.48

    2.16

    1.41

    1.15

    0.98

    116.43

    0.55

    Q6

    241.7

    0.54

    0.3

    0.24

    0.18

    447.59

    0.75

    Q7

    174.91

    2.42

    1.46

    1.11

    0.88

    72.28

    0.69

    Q8

    560.82

    2.09

    1.41

    1.16

    1.01

    268.33

    0.52

    Q9

    450.68

    14.29

    8.76

    6.28

    4.74

    31.54

    0.75

    Q10

    895.75

    4.47

    3.17

    2.85

    2.42

    200.39

    0.46

    Q11

    30.03

    1.06

    0.83

    0.77

    0.74

    28.33

    0.36

    Q12

    266.14

    1.70

    0.92

    0.7

    0.53

    156.55

    0.8

    Q13

    780.74

    5.83

    7.04

    6.84

    3.56

    133.92

    0.41

    Q14

    72.04

    1.10

    0.61

    0.46

    0.38

    65.49

    0.72

    Q15

    261.77

    2.47

    1.4

    1.06

    0.82

    105.98

    0.75

    Q16

    40.69

    2.25

    1.84

    1.49

    1.19

    18.08

    0.47

    Q17

    57.75

    2.92

    3.93

    3

    2.51

    19.78

    0.29

    Q18

    257.66

    12.72

    6.16

    3.79

    2.46

    20.26

    1.29

    Q19

    19.17

    2.13

    1.15

    0.96

    0.68

    9.00

    0.78

    Q20

    143.97

    1.22

    1.2

    1

    0.8

    118.01

    0.38

    Q21

    293.99

    6.92

    3.37

    2.38

    1.82

    42.48

    0.95

    Q22

    18.81

    1.41

    1.33

    1.16

    0.98

    13.34

    0.36

    TOTAL

    7735.67

    77.01

    51.47

    40.58

    29.81

    100.45

    0.65

    • The following figure shows the comparison of the query time before and after the IMCI feature is enabled.

      image.png

    • The following figure shows the comparison of the query time on different numbers of machines after the IMCI feature is enabled.

      image.png

    Test environment 2: a cluster that has 8 cores and 32 GB of memory

    In the following test, 100 GB of data that is generated based on TPC-H, a cluster that has 8 cores and 32 GB of memory, and four machines are used to compare query performance when the IMCI feature is enabled.

    The following table shows the test results.

    Query

    Query time (seconds)

    Q1

    4.74

    Q2

    0.79

    Q3

    2.35

    Q4

    1.13

    Q5

    2.51

    Q6

    0.54

    Q7

    2.51

    Q8

    2.07

    Q9

    11.07

    Q10

    8.01

    Q11

    1.00

    Q12

    1.49

    Q13

    7.11

    Q14

    0.92

    Q15

    2.24

    Q16

    2.62

    Q17

    5.44

    Q18

    8.78

    Q19

    5.75

    Q20

    1.64

    Q21

    5.99

    Q22

    1.69

    TOTAL

    80.39

    TPCH 1 TB

    Test environment 1: a cluster that has 32 cores and 256 GB of memory

    In the following test, 1 TB of data that is generated based on TPC-H, a cluster that has 32 cores and 256 GB of memory, and multiple machines are used to compare query performance when the IMCI feature is enabled and disabled.

    The following table shows the test results.

    Query

    Query time on one machine (seconds)

    Query time on two machines (seconds)

    Query time on three machines (seconds)

    Query time on four machines (seconds)

    Acceleration ratio in multi-node parallel query mode

    Q1

    108.1

    62.02

    31.19

    14.64

    1.85

    Q2

    25.91

    8.76

    6.66

    5.35

    1.21

    Q3

    73.97

    17.69

    11.34

    9.17

    2.02

    Q4

    56.83

    8.74

    5.85

    4.64

    3.06

    Q5

    76.47

    37.46

    12.25

    10.09

    1.89

    Q6

    40.94

    2.87

    2.24

    1.72

    5.95

    Q7

    87.22

    41.56

    12.55

    9.59

    2.27

    Q8

    84.36

    41.95

    10.16

    7.49

    2.82

    Q9

    328.76

    147.24

    103.65

    58.67

    1.40

    Q10

    117.14

    70.29

    42.82

    35.91

    0.82

    Q11

    10.73

    7.83

    6.95

    6.37

    0.42

    Q12

    81.59

    36.30

    7.40

    5.82

    3.50

    Q13

    93.83

    85.52

    78.57

    63.84

    0.37

    Q14

    52.92

    15.04

    5.04

    3.82

    3.47

    Q15

    100.4

    13.54

    10.55

    8.47

    2.96

    Q16

    23.09

    17.99

    12.87

    9.95

    0.58

    Q17

    97.71

    41.69

    30.34

    24.41

    1.00

    Q18

    478.74

    221.07

    103.14

    63.64

    1.88

    Q19

    97.24

    44.67

    30.21

    21.56

    1.13

    Q20

    58.7

    14.86

    8.97

    7.25

    2.03

    Q21

    188.61

    40.68

    28.70

    22.46

    2.10

    Q22

    16.76

    13.84

    13.04

    10.16

    0.41

    TOTAL

    2300.02

    991.64

    574.49

    405.02

    1.42

    The following figure shows the comparison of the query time on different numbers of machines after the IMCI feature is enabled.

    image.png

    Test environment 2: a cluster that has 8 cores and 32 GB of memory

    In the following test, 1 TB of data that is generated based on TPC-H, a cluster that has 8 cores and 32 GB of memory, and four machines are used to compare query performance when the IMCI feature is enabled.

    Query

    Query time (seconds)

    Q1

    86.54

    Q2

    21.27

    Q3

    50.14

    Q4

    31.33

    Q5

    54.61

    Q6

    29.45

    Q7

    56.51

    Q8

    62.12

    Q9

    166.23

    Q10

    132.54

    Q11

    14.04

    Q12

    48.35

    Q13

    96.33

    Q14

    36.37

    Q15

    75.13

    Q16

    29.59

    Q17

    87.61

    Q18

    205.75

    Q19

    66.84

    Q20

    46.58

    Q21

    117.78

    Q22

    19.71

    TOTAL

    1534.82

    TPCH 3 TB

    Test environment: a cluster that has 32 cores and 256 GB of memory

    In the following test, 3 TB of data that is generated based on TPC-H, a cluster that has 32 cores and 256 GB of memory, and multiple machines are used to compare query performance when the IMCI feature is enabled.

    The following table shows the test results.

    Query

    Query time on two machines (seconds)

    Query time on four machines (seconds)

    Query time on six machines (seconds)

    Query time on eight machines (seconds)

    Query time on 12 machines (seconds)

    Q1

    207.5972

    114.1061

    73.47038

    57.16459

    15.99434

    Q2

    16.59763

    9.126449

    6.531764

    5.256679

    8.056107

    Q3

    129.0127

    70.05587

    50.75962

    20.77513

    15.96027

    Q4

    87.88678

    48.23547

    11.22043

    9.06577

    6.153393

    Q5

    145.3669

    80.90937

    55.35411

    20.8387

    16.81331

    Q6

    64.77278

    33.61284

    3.626543

    3.1164

    2.393298

    Q7

    158.332

    82.21748

    57.402

    19.28089

    14.15242

    Q8

    171.7152

    89.18184

    61.07931

    47.21762

    11.49657

    Q9

    695.1544

    291.9174

    190.4007

    143.2241

    70.81171

    Q10

    318.9998

    260.2054

    263.955

    209.1835

    207.9049

    Q11

    26.04949

    20.9185

    18.32898

    17.69759

    16.45135

    Q12

    120.3137

    62.77258

    42.20166

    11.08824

    7.982793

    Q13

    388.5152

    320.5632

    151.0701

    90.68545

    96.00401

    Q14

    93.1189

    58.5961

    22.68045

    21.14225

    19.99422

    Q15

    150.9475

    79.81816

    18.32744

    16.26336

    11.92104

    Q16

    72.26089

    36.46251

    24.70481

    18.45767

    12.8234

    Q17

    233.1315

    196.8246

    112.588

    113.5308

    109.7312

    Q18

    1163.623

    537.1255

    305.1325

    237.8622

    69.33614

    Q19

    152.0749

    81.25671

    54.06552

    41.54125

    26.91918

    Q20

    118.8899

    77.28576

    55.68256

    30.76349

    30.0725

    Q21

    336.2304

    177.2311

    52.50164

    41.73258

    29.24006

    Q22

    52.38819

    50.87576

    40.99282

    37.71081

    37.43663

    TOTAL

    4902.97928

    2779.29879

    1672.07638

    1213.59912

    837.648829

    The following figure shows the comparison of the query time on different numbers of machines after the IMCI feature is enabled.

    image.png