make order by more scalable / faster (MCOL-894)

[MCOL-3581] Multi-threaded order-by for 1.2 Created: 2019-10-31  Updated: 2019-11-08  Resolved: 2019-11-08

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: None
Fix Version/s: 1.2.6

Type: Sub-Task Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 2019-06

 Description   

This ticket is to track multi-threaded order-by added into 1.2. This is for use cases where ColumnStore is executing the order by, such as subqueries.

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2019-10-31 ]

drrtuy has implemented this in 1.2 and will port it to 1.4 which will be tracked in MCOL-894.

Comment by Daniel Lee (Inactive) [ 2019-11-08 ]

Build tested: 1.2.6-1

[dlee@master centos7]$ cat gitversionInfo.txt
engine commit:
5db61b9

I did not observe and performance change when changing infinidb_orderby_threads from 1 to 8.
The same test in 1.4.1-1 has a noticeable performance gain.
It did not make a different when I set infinidb_ordered_only to on or off

1.2.5-1

[root@localhost ~]# mcsmysql tpch10
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.16-MariaDB-log Columnstore 1.2.5-1

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [tpch10]> select l_orderkey from lineitem order by l_orderkey limit 10;
------------

l_orderkey

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

1
1
1
1
1
1
2
3
3
3

------------
10 rows in set (1 min 20.821 sec)

MariaDB [tpch10]> select l_orderkey, l_comment from lineitem order by l_comment limit 10;
-----------------------+

l_orderkey l_comment

-----------------------+

28832352 about the
7868261 about the
7873062 about the
7865349 about the
7864577 about the
7882560 about the
24644000 about the
7865091 about the
20456834 about the
16260771 about the

-----------------------+
10 rows in set (1 min 45.069 sec)

1.2.6-1

infinidb_orderby_threads

MariaDB [tpch10]> set infinidb_orderby_threads=1;
Query OK, 0 rows affected (0.000 sec)

MariaDB [tpch10]> select l_orderkey from lineitem order by l_orderkey limit 10;
------------

l_orderkey

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

1
1
1
1
1
1
2
3
3
3

------------
10 rows in set (1 min 22.179 sec)

MariaDB [tpch10]> select l_orderkey, l_comment from lineitem order by l_comment limit 10;
-----------------------+

l_orderkey l_comment

-----------------------+

2099971 about the
519554 about the
525381 about the
4709378 about the
4727808 about the
4738112 about the
2624033 about the
516389 about the
1568610 about the
1597216 about the

-----------------------+
10 rows in set (1 min 46.981 sec)

MariaDB [tpch10]> set infinidb_orderby_threads=8;
Query OK, 0 rows affected (0.000 sec)

MariaDB [tpch10]> select l_orderkey from lineitem order by l_orderkey limit 10;
------------

l_orderkey

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

1
1
1
1
1
1
2
3
3
3

------------
10 rows in set (1 min 23.097 sec)

MariaDB [tpch10]> select l_orderkey, l_comment from lineitem order by l_comment limit 10;
-----------------------+

l_orderkey l_comment

-----------------------+

519554 about the
516389 about the
1568610 about the
3705219 about the
3669733 about the
620550 about the
525381 about the
2624033 about the
3707879 about the
3703911 about the

-----------------------+
10 rows in set (1 min 46.995 sec)

Comment by Daniel Lee (Inactive) [ 2019-11-08 ]

It turned out the performance would realize when subquery is used. Modified test queries to run as subqueries and performance is virtually the same as 1.4.1-1.

MariaDB [tpch10]> set infinidb_orderby_threads=1;
Query OK, 0 rows affected (0.000 sec)

MariaDB [tpch10]> select * from ( select l_orderkey from lineitem order by l_orderkey limit 10) a;
------------

l_orderkey

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

1
1
1
1
1
1
2
3
3
3

------------
10 rows in set (4.360 sec)

MariaDB [tpch10]> select * from (select l_orderkey, l_comment from lineitem order by l_comment limit 10) a;
----------------------+

l_orderkey l_comment

----------------------+

8907489 Tiresias
17668260 Tiresias
42581319 Tiresias
17615556 Tiresias
17323302 Tiresias
21883973 Tiresias
56633829 Tiresias
42593603 Tiresias
9386147 Tiresias
753413 Tiresias

----------------------+
10 rows in set (35.330 sec)

MariaDB [tpch10]> set infinidb_orderby_threads=8;
Query OK, 0 rows affected (0.001 sec)

MariaDB [tpch10]> select * from ( select l_orderkey from lineitem order by l_orderkey limit 10) a;
------------

l_orderkey

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

1
1
1
1
1
1
2
3
3
3

------------
10 rows in set (2.670 sec)

MariaDB [tpch10]> select * from (select l_orderkey, l_comment from lineitem order by l_comment limit 10) a;
----------------------+

l_orderkey l_comment

----------------------+

753413 Tiresias
35694661 Tiresias
33292934 Tiresias
6527073 Tiresias
52480608 Tiresias
58061957 Tiresias
53788672 Tiresias
44041447 Tiresias
8907489 Tiresias
3948419 Tiresias

----------------------+
10 rows in set (16.507 sec)

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