一个查询任务在单个BE结点上使用的内存默认不超过2GB,如果超过,可能会出现Memory limit exceeded。查看内存限制:
mysql> SHOW VARIABLES LIKE "%mem_limit%";+----------------+------------+| Variable_name | Value |+----------------+------------+| exec_mem_limit | 2147483648 || load_mem_limit | 0 |+----------------+------------+2 rows in set (0.00 sec)exec_mem_limit的单位是byte,可通过set命令改变exec_mem_limit的值:
set exec_mem_limit = 8589934592;该命令只针对当前会话,如需永久有效,则要添加global参数:
set global exec_mem_limit = 8589934592;修改超时时间
默认最长查询时间为300s,如果超时未完成,则会被取消掉,查看配置:
mysql> SHOW VARIABLES LIKE "%query_timeout%";+---------------+-------+| Variable_name | Value |+---------------+-------+| query_timeout | 300 |+---------------+-------+1 row in set (0.00 sec)设置方法同exec_mem_limit。
查询重试和高可用
mysql> SELECT SUM(example_site_visit.cost) FROM example_site_visit JOIN example_site_visit2 WHERE example_site_visit.user_id = example_site_visit2.user_id;+----------------------------------+| sum(`example_site_visit`.`cost`) |+----------------------------------+| 612 |+----------------------------------+1 row in set (0.12 sec)mysql> select example_site_visit.user_id, sum(example_site_visit.cost) from example_site_visit join example_site_visit2 where example_site_visit.user_id = example_site_visit2.user_id group by example_site_visit.user_id;+---------+----------------------------------+| user_id | sum(`example_site_visit`.`cost`) |+---------+----------------------------------+| 10004 | 310 || 10000 | 70 || 10001 | 2 || 10002 | 200 || 10003 | 30 |+---------+----------------------------------+5 rows in set (0.16 sec)子查询
mysql> SELECT SUM(cost) FROM example_site_visit2 WHERE user_id IN (SELECT user_id FROM example_site_visit WHERE user_id > 10003);+-------------+| sum(`cost`) |+-------------+| 111 |+-------------+1 row in set (0.07 sec)Join查询
mysql> ALTER TABLE tbl1_j SET ("colocate_with" = "group2");Query OK, 0 rows affected (0.00 sec)如果该表之前没有指定过Group,则该命令检查Schema,并将该表加入到该Group(Group 不存在则会创建);
如果该表之前有指定其他Group,则该命令会先将该表从原有Group中移除,并加入新Group(Group 不存在则会创建)。
删除表的colocate组
ALTER TABLE tbl SET ("colocate_with" = "");另外,当对一个具有Colocation属性的表进行增加分区(ADD PARTITION)、修改副本数时,Doris会检查修改是否会违反Colocation Group Schema,如果违反则会拒绝。
四、Bucket Shuffle Join
(2)MinMax Filter: 包含最大值和最小值,从而过滤小于最小值和大于最大值的数据,MinMax Filter的过滤效果与join on clause中Key列的类型和左右表数据分布有关:
当join on clause中Key 列的类型为int/bigint/double等时,极端情况下,如果左右表的最大最小值相同则没有效果,反之右表最大值小于左表最小值,或右表最小值大于左表最大值,则效果最好;
当join on clause中Key 列的类型为varchar等时,应用MinMax Filter往往会导致性能降低。
(11)不支持PlanNode.Conjuncts生成的Runtime Filter下推,与HashJoinNode的eqJoinConjuncts和otherJoinConjuncts不同,PlanNode.Conjuncts生成的Runtime Filter在测试中发现可能会导致错误的结果,例如IN 子查询转换为join时,自动生成的join on clause将保存在PlanNode.Conjuncts中,此时应用Runtime Filter可能会导致结果缺少一些行。
SQL函数
查看内置函数:
mysql> show builtin functions in test;+--------------------------------+| Function Name |+--------------------------------+| %element_extract% || abs || acos || add || add_months || adddate || aes_decrypt |......| years_add || years_diff || years_sub || yearweek |+--------------------------------+302 rows in set (0.00 sec)查看某内置函数的具体信息:
mysql> show full builtin functions in test like 'year';+----------------+-------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------+| Signature | Return Type | Function Type | Intermediate Type | Properties |+----------------+-------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------+| year(DATETIME) | INT | Scalar | NULL | {"symbol":"_ZN5doris18TimestampFunctions4yearEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE","object_file":"","md5":""} |+----------------+-------------+---------------+-------------------+------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.05 sec)更多请参见官网。
参考:
https://blog.csdn.net/qq_37475168/article/details/125754794