[#MCOL-4691] Major Regression: Selects with aggregates 2x slower in 5.x than in 1.2 (due to collation support)

[MCOL-4691] Major Regression: Selects with aggregates 2x slower in 5.x than in 1.2 (due to collation support) Created: 2021-04-21 Updated: 2023-10-27 Resolved: 2023-10-27
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	PrimProc
Affects Version/s:	None
Fix Version/s:	Icebox

Type:

Bug

Priority:

Critical

Reporter:

Gregory Dorman (Inactive)

Assignee:

Leonid Fedorov

Resolution:

Won't Fix

Votes:

Labels:

performance

Attachments:

Снимок экрана от 2021-04-23 14-27-23.png

Снимок экрана от 2021-04-23 14-27-30.png

Issue Links:

Blocks
is blocked by	MCOL-4791	Fix ColumnCommand fudged data type fo...	Stalled
is blocked by	~~MCOL-4801~~	Replace Row methods getStringLength()...	Closed
is blocked by	~~MDEV-26572~~	Improve simple multibyte collation pe...	Closed
Relates
relates to	~~MCOL-2000~~	varchar specified sizing is not in ch...	Closed
relates to	MCOL-5043	Reduce a number of pre-spawned ExeMgr...	Stalled
relates to	~~MCOL-5044~~	Improve PP thread pool with a fair sc...	Closed
relates to	MCOL-4593	Multiple concurrent queries with aggr...	Stalled

Sub-Tasks:

Key

Summary

Type

Status

Assignee

MCOL-4717

Conduct experiments measuring the ove...

Sub-Task

Closed

Leonid Fedorov

Sprint:

2021-7, 2021-8, 2021-9, 2021-10, 2021-11, 2021-12

Description

First pointed out by Quinnstreet, now confirmed by drrtuy. This requires profiling in both releases and identification of where the extra time is eaten.

While it also shows lack of user scaling in both releases, that part is not the focus of this ticket (there is related ticket for that). This one is only about raw difference between releases. drrtuy has the environment and a reproduction.

tail_num is VARCHAR(6). The table's charset is utf8.

In 1.2 tests

root@ip-172-31-23-117:/data/slapit#  ./sysbench_series.sh  slap4.lua

select s from (select count(*) as s from flights group by tail_num)sub;

#users                                        Average time

1          avg:                                133.84

2          avg:                                141.78

3          avg:                                175.30

4          avg:                                215.92

5          avg:                                251.24

6          avg:                                281.05

7          avg:                                327.09

8          avg:                                366.62

9          avg:                                411.97

10          avg:                                456.93

11          avg:                                506.55

in 5.2.2 tests

select s from (select count(*) as s from flights group by tail_num)sub;

root@ip-172-31-3-254:/data/slapit# ./sysbench_series.sh slap4.lua

1          avg:                                  381.95

2          avg:                                  284.49

3          avg:                                  336.59

4          avg:                                  427.69

5          avg:                                  527.38

6          avg:                                  622.61

7          avg:                                  720.63

8          avg:                                  830.55

9          avg:                                  927.50

10          avg:                                 1032.59

Comments

Comment by Alexander Barkov [ 2021-05-12 ]

Performance comparsion of this query:

select s from (select count(*) as s from flights group by tail_num)sub;

for various collations in develop-5:

Collation          Time(sec)

------------------ ---------

latin1_nopad_bin   0.204

latin1_swedish_ci  0.216

utf8_nopad_bin     0.401

utf8_general_ci    0.415

Note, this query (notice the SMALLINT column taxi_in):

select s from (select count(*) as s from flights group by taxi_in)sub;

takes 0.124 seconds.

Comment by Roman [ 2021-06-17 ]

My results comparing 1.2.5 and 5ebac6772 are different for smallint for a single-node setup.

1.2.5

Latency histogram (values are in milliseconds)

       value  ------------- distribution ------------- count

     123.285 |**                                       2

     125.525 |**                                       2

     127.805 |****************                         20

     130.128 |**************************************** 51

     132.492 |********                                 10

     134.899 |**                                       2

     137.350 |***                                      4

     139.846 |*                                        1

     186.540 |*                                        1

     196.894 |*                                        1

     200.472 |**                                       3

     204.114 |**                                       3

Latency (ms):

         min:                                123.37

         avg:                                135.67

         max:                                203.06

         95th percentile:                    200.47

develop-5 5ebac6772

Latency histogram (values are in milliseconds)

       value  ------------- distribution ------------- count

      92.424 |***                                      2

      97.555 |***                                      2

      99.327 |*****************                        10

     101.132 |******************************           18

     102.969 |*****                                    3

     108.685 |*******                                  4

     110.659 |**                                       1

     114.717 |**                                       1

     123.285 |**                                       1

     127.805 |**                                       1

     130.128 |**************************************** 24

     137.350 |***                                      2

     155.801 |**                                       1

     158.632 |**                                       1

     161.514 |***                                      2

     164.449 |*****************                        10

     167.437 |**                                       1

     170.479 |********                                 5

     173.577 |******************                       11

Latency (ms):

         min:                                   92.22

         avg:                                  130.15

         max:                                  174.54

         95th percentile:                      173.58

Comment by Roman [ 2021-06-17 ]

These are results for varchar(6) with utf8 and default collation taken in a single-node setup.

1.2.5

Latency histogram (values are in milliseconds)

       value  ------------- distribution ------------- count

     142.387 |****                                     1

     150.290 |************                             3

     153.021 |********************                     5

     155.801 |************                             3

     158.632 |********************                     5

     161.514 |********************************         8

     164.449 |********************                     5

     167.437 |********                                 2

     170.479 |********************************         8

     173.577 |********************                     5

     176.731 |****************                         4

     179.942 |********                                 2

     183.211 |****                                     1

     193.380 |****                                     1

     200.472 |****                                     1

     204.114 |************                             3

     211.599 |****************                         4

     219.358 |**************************************** 10

     223.344 |**************************************** 10

     227.402 |************************************     9

     231.534 |********************************         8

     253.346 |****                                     1

     257.950 |****                                     1

Latency (ms):

         min:                                143.31

         avg:                                192.26

         max:                                256.29

         95th percentile:                    231.53

develop-5 5ebac6772

Latency histogram (values are in milliseconds)

       value  ------------- distribution ------------- count

     277.214 |********                                 3

     282.251 |*****                                    2

     287.379 |***********                              4

     292.601 |*****                                    2

     297.917 |*******************                      7

     303.330 |*******************                      7

     308.842 |********************************         12

     314.453 |********                                 3

     320.167 |*************                            5

     325.984 |***********                              4

     331.907 |********                                 3

     337.938 |***                                      1

     344.078 |****************                         6

     350.330 |*****                                    2

     363.176 |*********************                    8

     369.775 |**************************************** 15

     376.494 |*********************                    8

     383.334 |*******************                      7

     390.299 |***                                      1

Latency (ms):

         min:                                  275.84

         avg:                                  335.17

         max:                                  387.07

         95th percentile:                      383.33

Comment by Roman [ 2021-06-17 ]

There are a number of CHARSET_INFO::strnncollsp calls in develop-5 at the commit 5ebac6772 that were added as part of an effort to bring collation aware ops. W/o these calls the GROUP BY timings is even faster comparing with 1.2.5.
The results are for VARCHAR(6)/latin1/latin1_nopad_bin and as per bar request they are for PATCHED develop-5 5ebac6772.

Latency (ms):

         min:                                  127.73

         avg:                                  180.05

         max:                                  301.23

From the first glance at profiling results the calculation of a string length brings the only visible difference b/w the fast hack patch and the original develop-5 at 5ebac6772.

Comment by Roman [ 2021-06-18 ]

Here are the results for develop 96f2a55 VARCHAR(6)/latin1/latin1_nopad_bin:

Latency (ms):

         min:                                  175.55

         avg:                                  225.69

         max:                                  368.59

         95th percentile:                      267.41

Comment by David Hall (Inactive) [ 2022-05-25 ]

While all this slow down is created by our support of charset and collations, some improvement is expected from MCOL-5043 and ~~MCOL-5044~~. Links have been added.

Generated at Thu Feb 08 02:52:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MCOL-4691] Major Regression: Selects with aggregates 2x slower in 5.x than in 1.2 (due to collation support) Created: 2021-04-21 Updated: 2023-10-27 Resolved: 2023-10-27