StarRocks 最佳实践

本文从部署、建模、导入、查询和监控五个模块给大家分享下 StarRocks 的最佳使用方法,希望能帮助到大家使用 StarRocks 更快、更稳、更好的实现业务需求。

部署

  1. 容量规划

  2. 【建议】参考 StarRocks集群配置推荐 做容量规划

  3. 基础环境配置

【必须】参考 检查环境配置 | StarRocks,尤其关注swap关闭、overcommit设置为1、ulimit配置合理

  1. 机器配置

  • FE节点

    • 【建议】 8C32GB

    • 【必须】数据盘>=200GB,建议 SSD

  • BE节点

    • 【建议】CPU:内存比,1:4,生产最小配置必须是 8C32GB+

    • 【建议】单节点磁盘容量建议10TB,数据盘建议最大单盘2TB,建议SSD或者NVME(如果是HDD,建议吞吐>150MB/s,IOPS>500)

    • 【建议】集群中节点 同构 (机器规格一样,避免木桶效应)

  1. 部署方案

  • 【必须】生产环境必须最小集群规模 3FE+3BE(建议FE和BE独立部署),如果混合部署, 必须配置be.conf 中的mem_limit 为减去其他服务后剩余内存量,例如机器内存40G,上面已经部署了FE,理论上限会用8G,那么配置下mem_limit=30G (40-8-2),2g是给系统预留

  • 【必须】生产必须 FE 高可用部署 ,1 Leader + 2 Follower,如果需要提高读并发,可以扩容Observer节点

  • 【必须】生产必须使用负载均衡器连接集群进行读写,一般常用Nginx、Haproxy、F5等

建模

  1. 建表规范

  • 仅支持UTF8编码

  • 不支持修改表中的列名(即将支持)

  • VARCHAR最大长度1048576

  • KEY列不能使用FLOAT、DOUBLE类型

  • 数据目录名、数据库名、表名、视图名、用户名、角色名 大小写敏感 ,列名和分区名 大小写不敏感

  • 主键模型中,主键长度不超过128字节

  1. 模型选择

  • 如果想要保留明细,建议使用明细模型

  • 如果有明确主键,主键非空,写少读多,非主键列要利用索引,建议使用主键模型

  • 如果有明确主键,主键可能为空,写多读少,建议使用更新模型

  • 如果只想保留聚合数据,建议使用聚合模型

  1. 排序列和前缀索引选择

DUPLICATE KEY、AGGREGATE KEY、UNIQUE KEY中指定的列,3.0版本以前,主键模型中排序列通过PRIMARY KEY指定,3.0版本起,主键模型中排序列通过ORDER BY指定。

前缀索引是在排序列基础上引入的稀疏索引,进一步提升查询效率,全部加载在内存中

  • 经常作为查询条件的列,建议选为排序列,例如经常用user_id过滤,where user_id=234,可以把user_id放在第一列

  • 排序列建议选择3-5列,过多会增大排序开销,降低导入效率

  • 前缀索引不超过36字节,不能超过3列,遇到varchar会截断,前缀索引中不能包含 float 或 double 类型的列

因此可以结合实际业务查询场景,在确定 key 列以及字段顺序时,要充分考虑前缀索引带来的优势。尽可能将经常需要查询的key列字段,放置在前面,字段数据类型尽量选择 date 日期类型或者 int 等整数类型。

举例:

CREATE TABLE site_access
(
    site_id BIGINT DEFAULT '10',
    city_code INT,
    site_name VARCHAR(50),
    pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(site_id,city_code,site_name)
DISTRIBUTED BY HASH(site_id);

在 site_access 表中,前缀索引为 site_id( 8 Bytes ) + city_code( 4 Bytes ) + site_name(前 24 Bytes)

  • 如果查询条件只包含 site_idcity_code 两列,如下所示,则可以大幅减少查询过程中需要扫描的数据行:
select sum(pv) from site_access where site_id = 123 and city_code = 2;
  • 如果查询条件只包含 site_id 一列,如下所示,可以定位到只包含 site_id 的数据行:
select sum(pv) from site_access where site_id = 123;
  • 如果查询条件只包含 city_code 一列,如下所示,则需要扫描所有数据行,排序效果大打折扣:
select sum(pv) from site_access where city_code = 2;
  • 如果 site_id和city_code联合查询和单独city_code的查询占比不相上下,可以考虑创建同步物化视图调整列顺序来达到查询性能提升,物化视图中的city_code放到第一列
create materialized view site_access_city_code_mv as
select
    city_code,
    site_id,
    site_name,
    pv
from
    site_access;

Bad case

REATE TABLE site_access_bad
(
    site_name VARCHAR(20),
    site_id BIGINT DEFAULT '10',
    city_code INT,
    pv BIGINT DEFAULT '0'
)
PRIMARY KEY(site_id)
DISTRIBUTED BY HASH(site_id)
ORDER BY(site_id,city_code);

在 site_access_bad 表中,前缀索引只有 site_name

  1. 分区选择

  • 【建议】值不会变化的时间列经常用于where过滤,使用该列创建分区

  • 【建议】有数据淘汰需求的场景建议选择动态分区

  • 【必须】数据更新有明显的冷热特征的,必须创建分区,例如经常更新最近一周的数据,可以按天分区

  • 【必须】单个分区数据量必须不要超过100GB

  • 【必须】超过50G或者5KW的表建议创建分区

  • 【建议】按需创建分区,不要提前创建大量空分区,避免元数据太多占用fe的内存

  • 当前支持时间类型(Range分区、表达式分区)、字符串(List分区)、数字(Range分区、List分区)

  • 默认最大支持1024个分区,可以通过参数调整,不过一般情况下不需要调整

  1. 分桶选择

  • 生产必须使用 3 副本

  • 分桶个数判断

    • 【必须】单个桶按照1GB预估,原始数据按照10GB(导入starrocks后,压缩比7:1~10:1)预估

      • 当按照以上策略估算出来的分桶个数小于be个数的时候,最终分桶个数以be个数为准,例如6个be节点,按照1GB每个桶预估分桶个数为1,最终分桶个数取6
    • 【必须】非分区表不要使用动态分桶,按照实际数据量估算分桶个数

    • 【必须】如果分区表的各个分区的数据差异很大,建议不要使用动态分桶策略

  • 分桶裁剪和数据倾斜如何抉择?

    • 【建议】如果分桶列是where中经常用到的列,且分桶列的重复度比较低(例如用户id、事物id等),则可以利用该列作为分桶列

    • 【建议】如果查询条件中有city_id和site_id,city_id取值只有几十,如果仅仅使用city_id分桶,则可能出现某些桶的数据量会比较大,出现数据倾斜,这个时候可以考虑使用city_id和site_id联合作为分桶字段,不过这样做的缺点是如果查询条件中只有city_id的时候,没办法利用分桶裁剪

    • 【建议】如果没有合适的字段作为分桶字段打散数据,可以利用random分桶,不过这样的话没办法利用分桶裁剪的属性

  • 【必须】2个或多个超过KW行以上的表join,建议使用colocate,具体参考 Colocate Join | StarRocks

  1. 字段类型

  • 【建议】不要使用null属性

  • 【必须】时间类型和数字类型的列选择正确的类型,计算的开销会比较大,例如时间类型的数据“2024-01-01 00:00:00”不要使用VARCHAR存储,这样没办法利用到starrocks内部的zonemap索引,没办法加速过滤

  1. 索引选择

  • bitmap索引

    • 适合基数在10000-100000左右的列

    • 适合等值条件 (=) 查询或 [NOT] IN 范围查询的列

    • 不支持为 FLOAT、DOUBLE、BOOLEAN 和 DECIMAL 类型的列创建 Bitmap 索引。

    • 城市、性别这些基数在255以下的列不需要创建bitmap索引,因为starrocks内部有低基数字典,会针对这些case自动创建低基数字典用于加速

    • 明细模型和主键模型,所有列可以创建bitmap索引,聚合模型和更新模型,只有Key列支持创建bitmap索引

  • blommfilter索引

    • 适合基数在100000+的列,列的重复度很低

    • 适合 in= 过滤条件的查询

    • 不支持为 TINYINT、FLOAT、DOUBLE 和 DECIMAL 类型的列创建 Bloom filter 索引

    • 主键模型和明细模型中所有列都可以创建 Bloom filter 索引;聚合模型和更新模型中,只有维度列(即 Key 列)支持创建 Bloom filter 索引

导入

  1. 使用建议

  • 【必须】生产禁止使用insert into values() 导数据

  • 【必须】建议导入批次间隔5s+,也就是攒批写入,尤其是实时场景

  • 【建议】主键模型更新场景,建议开启索引落盘,磁盘强制SSD、NVME或者更高性能的磁盘

  • 【建议】比较多ETL(insert into select)的场景,建议开启spill落盘功能,避免内存超过限制

  1. 数据生命周期

  • 【建议】使用truncate删除数据,不要使用delete

  • 【必须】完整的update语法只能用于3.0版本以后的主键模型,禁止高并发 update,建议每次update操作需要间隔分钟以上

  • 【必须】如果使用delete删除数据,需要带上where条件,并且禁止并发执行delete,例如要删除id=1,2,3,4,……1000,禁止delete xxx from tbl1 where id=1这样的语句执行1000条,建议delete xxx from tbl1 where id in (1,2,3…,1000)

  • 【必须】drop操作默认会进入FE 回收站,默认保留86400(s),也就是1天(这个期间可以recover恢复,避免误操作),受参数catalog_trash_expire_second控制,超过1天后会进入BE的trash目录,默认保留259200(s),也就是3天(2.5.17,3.0.9,3.1.6之后默认值改为了86400,也就是1天),受参数trash_file_expire_time_sec控制,如果drop后需要尽快释放磁盘,可以调小fe和be的trash保留时间

查询

  1. 高并发场景

  • 【建议】尽可能利用分区分桶裁剪,具体参考上文的分区和分桶选择部分

  • 【必须】调大客户的并发限制,可以设置为1000,默认100,SET PROPERTY FOR ‘jack’ ‘max_user_connections’ = ‘1000’;

  • 【必须】开启page cache、query cache

  1. 数据精度

  • 【必须】如果需要精确结果的,强制使用decimal类型,不要使用float、double类型
  1. SQL查询

  • 【必须】避免select *,建议指定需要查询的列,例如select col0,col1 from tb1

  • 【必须】避免全表扫描,建议增加过滤的谓词,例如select col0,col1 from tb1 where id=123,select col0,col1 from tb1 where dt>‘2024-01-01’

  • 【必须】避免大数据量的下载,如果要使用,强制使用分页,select col0,col1,col2,…,col50 from tb order by id limit 0,50000

  • 【必须】分页操作需要加上order by,要不然是无序的

  • 【建议】避免使用一些不必要的函数或者表达式

    • 谓词中含cast, 可以移除
-- bad case
select l_tax 
from lineitem 
where cast(l_shipdate as varchar) > substr('1990-01-02 12:30:31',1,10);

-- good case
select l_tax 
from lineitem 
where l_shipdate > '1990-01-02';
  • 过度使用函数处理表达式
-- bad case
select count(1) 
from lineitem 
where l_shipdate >= regexp_extract("TIME:1996-01-02 20:00:00", "(\\d{4}-\\d{2}-\\d{2})", 1);

-- good case
select count(1) 
from lineitem 
where l_shipdate >= "1996-01-02"
-- bad case
select count(1) 
from lineitem 
where DATE_FORMAT(l_shipdate,'%Y-%m-%d') >= "1996-01-02"

-- good case
select count(1) 
from lineitem 
where l_shipdate >= "1996-01-02"
  • JOIN

    • 【必须】关联的字段类型匹配,虽然starrocks已经在内部做了隐式转换来达到最优的性能,不过建议大家使用类型一致的字段join,避免使用float、double类型join,可能会导致结果不准确

    • 【必须】关联字段建议不要使用函数或者表达式,例如 join on DATE_FORMAT(tb1.col1,’%Y-%m-%d’)=DATE_FORMAT(tb2.col1,’%Y-%m-%d’)

    • 【必须】2个或多个KW行以上的表join,推荐colocate join

    • 【建议】避免笛卡尔积

      • 查询多个表需要指定连接条件
-- bad case
SELECT *
FROM table1, table2;

-- good case
SELECT *
FROM table1, table2 ON table1.column1 = table2.column1;
* 正确关联子查询

  * 在子查询中,确保外部查询和子查询之间的列有明确的关联
-- bad case
SELECT *
FROM table1
WHERE column1 IN (SELECT column2 FROM table2);

-- good case
SELECT *
FROM table1
WHERE column1 IN (SELECT column2 FROM table2 WHERE table1.column3 = table2.column3);
* 使用AND条件而不是OR
-- bad case
SELECT *
FROM table1
JOIN table2
WHERE (table1.column1 = table2.column1 OR table1.column2 = table2.column2);

-- good case
SELECT *
FROM table1
JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2;
  1. 使用物化视图加速查询

  • 精确去重

以下示例基于一张广告业务相关的明细表 advertiser_view_record ,其中记录了点击日期 click_time 、广告代码 advertiser 、点击渠道 channel 以及点击用户 ID user_id

CREATE TABLE advertiser_view_record(
    click_time DATE,
    advertiser VARCHAR(10),
    channel VARCHAR(10),
    user_id INT) distributed BY hash(click_time);

该场景需要频繁使用如下语句查询点击广告的 UV。

SELECT advertiser, channel, count(distinct user_id)FROM advertiser_view_record
GROUP BY advertiser, channel;

如需实现精确去重查询加速,您可以基于该明细表创建一张物化视图,并使用 bitmap_union() 函数预先聚合数据。

CREATE MATERIALIZED VIEW advertiser_uv ASSELECT advertiser, channel, bitmap_union(to_bitmap(user_id))FROM advertiser_view_record
GROUP BY advertiser, channel;

物化视图创建完成后,后续查询语句中的子查询 count(distinct user_id) 会被自动改写为 bitmap_union_count (to_bitmap(user_id)) 以便查询命中物化视图。

  • 异步物化视图最多支持3层嵌套
  1. 利用cache 加速查询

  • 【建议】Page cache,建议开启,可以加速数据扫描场景,如果内存有冗余,可以尽可能调大限制,默认是mem_limit*20%

  • 【建议】Query cache,建议开启,可以加速单表或多表JOIN的聚合场景

    • 查询中不能包含 randrandomuuidsleep 等不确定性 (Nondeterminstic) 函数
  • 【建议】Data Cache,用于存算分离和湖分析场景,建议这两个场景下默认开启

监控

  1. 【必须】通过审计插件把fe.audit.log的数据导入一个表方便进行分析慢查询。

通过 Audit Loader 管理 StarRocks 中的审计日志 @ audit_loader

  1. 【必须】参考 https://docs.starrocks.io/zh/docs/2.5/administration/Monitor_and_Alert/ 部署prometheus+grafana

  2. 【建议】利用资源隔离大查询熔断,小查询保底

# shortquery_group 资源组用于核心业务重保
CREATE RESOURCE GROUP shortquery_group
TO 
    (user='rg1_user1', role='rg1_role1', db='db1', query_type in ('select'), source_ip='192.168.x.x/24'),
WITH (
    'type' = 'short_query',
    'cpu_core_limit' = '10',
    'mem_limit' = '20%'
);

# bigquery_group 用于大查询熔断,避免大查询将集群资源打满
CREATE RESOURCE GROUP bigquery_group
TO 
 (user='rg1_user2', role='rg1_role1', query_type in ('select')),
WITH (
    "type" = 'normal',
    'cpu_core_limit' = '10',
    'mem_limit' = '20%',
    'big_query_cpu_second_limit' = '100',
    'big_query_scan_rows_limit' = '100000',
    'big_query_mem_limit' = '1073741824'
);
  1. 大查询定位

查看当前FE上正在运行的查询

SQL命令: show proc '/current_queries'

返回结果包括以下几列:

  • QueryId

  • ConnectionId

  • Database:当前查询的DB

  • User:用户

  • ScanBytes:当前已扫描的数据量,单位Bytes

  • ProcessRow:当前已扫描的数据行数

  • CPUCostSeconds:当前查询已使用的CPU时间,单位秒。此为多个线程累加的CPU时间,举个例子,如果有两个线程分别占用1秒和2秒的CPU时间,那么累加起来的CPU时间为3秒。

  • MemoryUsageBytes:当前占用的内存。如果查询涉及到多个BE节点,此值即为该查询在所有BE节点上占用的内存之和。

  • ExecTime:查询从发起到现在的时长,单位为毫秒。

mysql> show proc '/current_queries';
+--------------------------------------+--------------+------------+------+-----------+----------------+----------------+------------------+----------+
| QueryId                              | ConnectionId | Database   | User | ScanBytes | ProcessRows    | CPUCostSeconds | MemoryUsageBytes | ExecTime |
+--------------------------------------+--------------+------------+------+-----------+----------------+----------------+------------------+----------+
| 7c56495f-ae8b-11ed-8ebf-00163e00accc | 4            | tpcds_100g | root | 37.88 MB  | 1075769 Rows   | 11.13 Seconds  | 146.70 MB        | 3804     |
| 7d543160-ae8b-11ed-8ebf-00163e00accc | 6            | tpcds_100g | root | 13.02 GB  | 487873176 Rows | 81.23 Seconds  | 6.37 GB          | 2090     |
+--------------------------------------+--------------+------------+------+-----------+----------------+----------------+------------------+----------+
2 rows in set (0.01 sec)

查看某个查询在每个BE节点上的资源消耗

SQL命令: show proc '/current_queries/${query_id}/hosts'

返回结果有多行,每行描述该查询在对应BE节点上的执行信息,包括以下几列:

  • Host:BE节点信息

  • ScanBytes:已经扫描的数据量,单位Bytes

  • ScanRows:已经扫描的数据行数

  • CPUCostSeconds:已使用的CPU时间。

  • MemUsageBytes:当前占用的内存。

mysql> show proc '/current_queries/7c56495f-ae8b-11ed-8ebf-00163e00accc/hosts';
+--------------------+-----------+-------------+----------------+---------------+
| Host               | ScanBytes | ScanRows    | CpuCostSeconds | MemUsageBytes |
+--------------------+-----------+-------------+----------------+---------------+
| 172.26.34.185:8060 | 11.61 MB  | 356252 Rows | 52.93 Seconds  | 51.14 MB      |
| 172.26.34.186:8060 | 14.66 MB  | 362646 Rows | 52.89 Seconds  | 50.44 MB      |
| 172.26.34.187:8060 | 11.60 MB  | 356871 Rows | 52.91 Seconds  | 48.95 MB      |
+--------------------+-----------+-------------+----------------+---------------+
3 rows in set (0.00 sec)
5赞

大佬能不能多写一点~ :pleading_face: 出个系列连载~

1赞

【必须】非分区表不要使用动态分桶,按照实际数据量估算分桶个数 大佬,对于这个我有个疑问,动态分桶是不指定分桶数量的意思,自动分桶,如果是小表,只有百行数据是不是不适用这个要求呢? :thinking:

【建议】和【必须】有个对应的解释就更好了

必须就是如果不这么做不行

我的意思是说【建议】和【必须】对应的条文有解释,说明这样做的原因是什么

soga!感谢你的建议,我们之后看看怎么补上~ :two_hearts:

2赞


这个地方是不是手误了?

1赞

是的,已改