All Products
Search
Document Center

PolarDB:IMCI performance

Last Updated:Nov 28, 2024

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 among row store and column store nodes in OLTP and OLAP separated scenarios.

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

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 node 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 node 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