ClickHouse: version 21.6 is much slower than 21.4
I got the binary from follwing url then strip it
wget -c https://builds.clickhouse.tech/master/aarch64/clickhouse
kylin@kylin-gtj:~$ strip clickhouse
kylin@kylin-gtj:~$ ll cl*
-rw-r--r-- 1 kylin kylin 234645184 5月 13 08:21 clickhouse
kylin@kylin-gtj:~$ cd ch
kylin@kylin-gtj:~/ch$ ll
总用量 242132
drwxrwxr-x 13 kylin kylin 4096 5月 11 16:28 ./
drwx------ 39 kylin kylin 4096 5月 13 08:21 ../
-rwxrwxr-x 1 kylin kylin 219328512 3月 26 15:24 clickhouse*
drwxr-x--- 6 kylin kylin 4096 5月 11 13:56 data/
drwxr-x--- 2 kylin kylin 4096 3月 26 15:25 dictionaries_lib/
drwxr-x--- 2 kylin kylin 4096 3月 26 15:25 flags/
drwxr-x--- 2 kylin kylin 4096 3月 26 15:25 format_schemas/
drwxr-x--- 2 kylin kylin 4096 3月 30 09:45 metadata/
drwxr-x--- 2 kylin kylin 4096 4月 29 13:31 metadata_dropped/
-rw------- 1 kylin kylin 28550256 5月 11 16:28 nohup.out
drwxr-x--- 2 kylin kylin 4096 3月 26 15:25 preprocessed_configs/
drwxr-x--- 4 kylin kylin 4096 4月 8 09:47 shadow/
drwxr-x--- 53 kylin kylin 4096 4月 29 13:23 store/
drwxr-x--- 3 kylin kylin 4096 3月 31 12:50 tmp/
drwxr-x--- 2 kylin kylin 4096 3月 26 15:25 user_files/
then rename the old binary file to clickhouse214 and satrt new file and run a query.
kylin@kylin-gtj:~/ch$ mv clickhouse clickhouse214
kylin@kylin-gtj:~/ch$ mv ../clickhouse .
kylin@kylin-gtj:~/ch$ chmod +x clickhouse
kylin@kylin-gtj:~/ch$ nohup ./clickhouse server &
[1] 3369
kylin@kylin-gtj:~/ch$ nohup: 忽略输入并把输出追加到'nohup.out'
kylin@kylin-gtj:~/ch$ mysql --protocol tcp -u default -P 9004
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 21.6.1.6818-ClickHouse
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use pop
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> with t as(
-> select agg,age1,d6,d4,count(*)cnt from ren group by cube(agg,age1,d6,d4)),
-> st as (
-> select agg,age1,d6,d4 from aggs,age1s,d6s,d4s where age1=0 or toUInt8((age1+4)/5)=agg order by d6,d4)
-> select agg,age1,arraySlice(groupArray(cnt),1,10)ac from st left join t using(agg,age1,d6,d4) group by agg,age1 order by agg,age1
-> ;
+------+------+---------------------------------------------------------------------------------------+
| agg | age1 | ac |
+------+------+---------------------------------------------------------------------------------------+
126 rows in set (9.99 sec)
Read 128000189 rows, 2.01 GiB in 9.98721409 sec., 12816405 rows/sec., 205.82 MiB/sec.
mysql> explain with t as( select agg,age1,d6,d4,count(*)cnt from ren group by cube(agg,age1,d6,d4)), st as ( select agg,age1,d6,d4 from aggs,age1s,d6s,d4s where age1=0 or toUInt8((age1+4)/5)=agg order by d6,d4) select agg,age1,arraySlice(groupArray(cnt),1,10)ac from st left join t using(agg,age1,d6,d4) group by agg,age1 order by agg,age1;
+------------------------------------------------------------------------------------------------------------------------------------+
| explain |
+------------------------------------------------------------------------------------------------------------------------------------+
| Expression (Projection) |
| MergingSorted (Merge sorted streams for ORDER BY) |
| MergeSorting (Merge sorted blocks for ORDER BY) |
| PartialSorting (Sort each block for ORDER BY) |
| Expression (Before ORDER BY) |
| Aggregating |
| Expression (Before GROUP BY) |
| Join (JOIN) |
| Expression ((Before JOIN + Projection)) |
| MergingSorted (Merge sorted streams for ORDER BY) |
| MergeSorting (Merge sorted blocks for ORDER BY) |
| PartialSorting (Sort each block for ORDER BY) |
| Expression (Before ORDER BY) |
| Filter (WHERE) |
| Join (JOIN) |
| Expression ((Before JOIN + (Projection + Before ORDER BY))) |
| Filter (WHERE) |
| Join (JOIN) |
| Expression ((Before JOIN + (Projection + Before ORDER BY))) |
| Filter (WHERE) |
| Join (JOIN) |
| Expression (Before JOIN) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (Log) |
| Expression ((Joined actions + (Rename joined columns + (Projection + Before ORDER BY)))) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (Log) |
| Expression ((Joined actions + (Rename joined columns + (Projection + Before ORDER BY)))) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (Log) |
| Expression ((Joined actions + (Rename joined columns + (Projection + Before ORDER BY)))) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (Log) |
| Expression ((Joined actions + (Rename joined columns + (Projection + Before ORDER BY)))) |
| Cube |
| Aggregating |
| Expression (Before GROUP BY) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromMergeTree |
+------------------------------------------------------------------------------------------------------------------------------------+
39 rows in set (0.00 sec)
Read 39 rows, 2.82 KiB in 0.00643683 sec., 6058 rows/sec., 438.30 KiB/sec.
the query took about 10 seconds, but each of the sub-query are all very fast (ren has 128000000 rows, t has 7917 rows, and st has 22302 rows )
mysql> with t as( select agg,age1,d6,d4,count(*)cnt from ren group by cube(agg,age1,d6,d4))select count(*) from t;
+---------+
| count() |
+---------+
| 7917 |
+---------+
1 row in set (1.33 sec)
Read 128000000 rows, 2.01 GiB in 1.32724088 sec., 96440670 rows/sec., 1.51 GiB/sec.
mysql> with st as ( select agg,age1,d6,d4 from aggs,age1s,d6s,d4s where age1=0 or toUInt8((age1+4)/5)=agg order by d6,d4)select count(*) from st;
+---------+
| count() |
+---------+
| 22302 |
+---------+
1 row in set (0.01 sec)
Read 189 rows, 189.00 B in 0.01041345 sec., 18149 rows/sec., 17.72 KiB/sec.
mysql> select count(*) from ren;
+-----------+
| count() |
+-----------+
| 128000000 |
+-----------+
1 row in set (0.00 sec)
Read 1 rows, 4.01 KiB in 0.00057369 sec., 1743 rows/sec., 6.82 MiB/sec.
then I quit the version: 21.6, restart the version 2.14. the same query took about 1.4 seconds.
kylin@kylin-gtj:~/ch$ pgrep clickhouse
3370
kylin@kylin-gtj:~/ch$ kill 3370
kylin@kylin-gtj:~/ch$ pgrep clickhouse
[1]+ 已完成 nohup ./clickhouse server
kylin@kylin-gtj:~/ch$ nohup ./clickhouse214 server &
[1] 3931
kylin@kylin-gtj:~/ch$ nohup: 忽略输入并把输出追加到'nohup.out'
kylin@kylin-gtj:~/ch$ mysql --protocol tcp -u default -P 9004
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 21.4.1.6351-ClickHouse
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use pop
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> with t as( select agg,age1,d6,d4,count(*)cnt from ren group by cube(agg,age1,d6,d4)), st as ( select agg,age1,d6,d4 from aggs,age1s,d6s,d4s where age1=0 or toUInt8((age1+4)/5)=agg order by d6,d4) select agg,age1,arraySlice(groupArray(cnt),1,10)ac from st left join t using(agg,age1,d6,d4) group by agg,age1 order by agg,age1;
+------+------+---------------------------------------------------------------------------------------+
| agg | age1 | ac |
+------+------+---------------------------------------------------------------------------------------+
126 rows in set (1.41 sec)
Read 128000189 rows, 2.01 GiB in 1.40947438 sec., 90814129 rows/sec., 1.42 GiB/sec.
mysql> select count(*) from ren;
+-----------+
| count() |
+-----------+
| 128000000 |
+-----------+
1 row in set (0.00 sec)
Read 1 rows, 4.01 KiB in 0.00055921 sec., 1788 rows/sec., 7.00 MiB/sec.
mysql> with st as ( select agg,age1,d6,d4 from aggs,age1s,d6s,d4s where age1=0 or toUInt8((age1+4)/5)=agg order by d6,d4)select count(*) from st;
+---------+
| count() |
+---------+
| 22302 |
+---------+
1 row in set (0.01 sec)
Read 189 rows, 189.00 B in 0.00977349 sec., 19338 rows/sec., 18.88 KiB/sec.
mysql> with t as( select agg,age1,d6,d4,count(*)cnt from ren group by cube(agg,age1,d6,d4))select count(*) from t;
+---------+
| count() |
+---------+
| 7917 |
+---------+
1 row in set (1.33 sec)
Read 128000000 rows, 2.01 GiB in 1.33491026 sec., 95886595 rows/sec., 1.50 GiB/sec.
mysql> explain with t as( select agg,age1,d6,d4,count(*)cnt from ren group by cube(agg,age1,d6,d4)), st as ( select agg,age1,d6,d4 from aggs,age1s,d6s,d4s where age1=0 or toUInt8((age1+4)/5)=agg order by d6,d4) select agg,age1,arraySlice(groupArray(cnt),1,10)ac from st left join t using(agg,age1,d6,d4) group by agg,age1 order by agg,age1;
+---------------------------------------------------------------------------------------------------------------------------------+
| explain |
+---------------------------------------------------------------------------------------------------------------------------------+
| Expression (Projection) |
| MergingSorted (Merge sorted streams for ORDER BY) |
| MergeSorting (Merge sorted blocks for ORDER BY) |
| PartialSorting (Sort each block for ORDER BY) |
| Expression (Before ORDER BY) |
| CreatingSets (Create sets before main query execution) |
| Aggregating |
| Expression (Before GROUP BY) |
| Join (JOIN) |
| Expression ((Before JOIN + Projection)) |
| MergingSorted (Merge sorted streams for ORDER BY) |
| MergeSorting (Merge sorted blocks for ORDER BY) |
| PartialSorting (Sort each block for ORDER BY) |
| CreatingSets (Create sets before main query execution) |
| Expression (Before ORDER BY) |
| Filter (WHERE) |
| Join (JOIN) |
| Expression ((Before JOIN + Projection)) |
| CreatingSets (Create sets before main query execution) |
| Expression (Before ORDER BY) |
| Filter (WHERE) |
| Join (JOIN) |
| Expression ((Before JOIN + Projection)) |
| CreatingSets (Create sets before main query execution) |
| Expression (Before ORDER BY) |
| Filter (WHERE) |
| Join (JOIN) |
| Expression (Before JOIN) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (Log) |
| CreatingSet (Create set for JOIN) |
| Expression ((Projection + Before ORDER BY)) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (Log) |
| CreatingSet (Create set for JOIN) |
| Expression ((Projection + Before ORDER BY)) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (Log) |
| CreatingSet (Create set for JOIN) |
| Expression ((Projection + Before ORDER BY)) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (Log) |
| CreatingSet (Create set for JOIN) |
| Expression ((Projection + Before ORDER BY)) |
| Cube |
| Aggregating |
| Expression (Before GROUP BY) |
| SettingQuotaAndLimits (Set limits and quota after reading from storage) |
| ReadFromStorage (MergeTree) |
+---------------------------------------------------------------------------------------------------------------------------------+
49 rows in set (0.01 sec)
Read 49 rows, 3.56 KiB in 0.00660192 sec., 7422 rows/sec., 538.58 KiB/sec.
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Comments: 16 (6 by maintainers)
checked on 21.7.3.14. fixed
Minimal query to reproduce:
The right hand side of JOIN does not get required number of threads.
CREATE TABLE t_ren2 ( code UInt64 , r_no varchar(3) , d4 UInt8, d5_birth_year UInt16 , d5_birth_month UInt8 , d6 UInt8 ) ENGINE=MergeTree()order by (code,r_no) ;
insert into t_ren2 with sheng as (select number+11 sheng from numbers(2)), di as (select number+1 di from numbers(20)), xian as (select number+1 xian from numbers(20)), xiang as (select number+1 xiang from numbers(10)), cun as (select number+1 cun from numbers(10)), xiao as (select number+1 xiao from numbers(5)), hu as (select number+1 hu from numbers(80)), t_hu as( select (((((toUInt64(sheng)*100+di)*100+xian)*1000+xiang)*1000+cun)*1000+xiao)*1000+hu code, code%7+1 r_count from sheng,di,xian,xiang,cun,xiao,hu), t as(select number+1 n from numbers(7)) select code,
n r_no,
n%2+1 d4,
(code+n)%100+1921 d5_birth_year,
(code+n)%12+1 d5_birth_month , (code+n)%56+1 d6
from t_hu,t where n<=r_count;
alter table t_ren2 add column age1 UInt8 default (2020-d5_birth_year - case when d5_birth_month>=11 then 2 else 1 end);
alter table t_ren2 update age1=age1 where 1;
alter table t_ren2 add column agg UInt8 default (case when age1-1=0 then 1 when age1-1> 0 and age1-1 <100 then toUInt8((age1-1)/5) when age1-1>=100 then 20 end ) ; alter table t_ren2 update agg=agg where 1;
with taggs as (select number agg from numbers(20+1)), tage1s as (select number age1 from numbers(100+1)), td6s as (select number d6 from numbers(58+1)), td4s as (select number d4 from numbers(2+1)), t as( select agg,age1,d6,d4,count(*)cnt from t_ren2 group by cube(agg,age1,d6,d4)),
st as ( select agg,age1,d6,d4 from taggs,tage1s,td6s,td4s where age1=0 or toUInt8((age1-1)/5)=agg order by d6,d4) select agg,age1,arraySlice(groupArray(cnt),1,10)ac from st left join t using(agg,age1,d6,d4) group by agg,age1 order by agg,age1;
– run in v21.4 costs 0.4s, in v21.7, 2.5s