慢SQL分析
最近更新时间：2024.05.06 15:59:31
首次发布时间：2024.05.06 15:59:31
1. 审计日志
1.1 创建审计日志库与表
CREATE DATABASE starrocks_audit_db__; CREATE TABLE starrocks_audit_db__.starrocks_audit_tbl__ ( `queryId` VARCHAR(64) COMMENT "查询的唯一ID", `timestamp` DATETIME NOT NULL COMMENT "查询开始时间", `queryType` VARCHAR(12) COMMENT "查询类型（query, slow_query, connection）", `clientIp` VARCHAR(32) COMMENT "客户端IP", `user` VARCHAR(64) COMMENT "查询用户名", `authorizedUser` VARCHAR(64) COMMENT "用户唯一标识，既user_identity", `resourceGroup` VARCHAR(64) COMMENT "资源组名", `catalog` VARCHAR(32) COMMENT "Catalog名", `db` VARCHAR(96) COMMENT "查询所在数据库", `state` VARCHAR(8) COMMENT "查询状态（EOF，ERR，OK）", `errorCode` VARCHAR(512) COMMENT "错误码", `queryTime` BIGINT COMMENT "查询执行时间（毫秒）", `scanBytes` BIGINT COMMENT "查询扫描的字节数", `scanRows` BIGINT COMMENT "查询扫描的记录行数", `returnRows` BIGINT COMMENT "查询返回的结果行数", `cpuCostNs` BIGINT COMMENT "查询CPU耗时（纳秒）", `memCostBytes` BIGINT COMMENT "查询消耗内存（字节）", `stmtId` INT COMMENT "SQL语句增量ID", `isQuery` TINYINT COMMENT "SQL是否为查询（1或0）", `feIp` VARCHAR(128) COMMENT "执行该语句的FE IP", `stmt` VARCHAR(1048576) COMMENT "原始SQL语句", `digest` VARCHAR(32) COMMENT "慢SQL指纹", `planCpuCosts` DOUBLE COMMENT "查询规划阶段CPU占用（纳秒）", `planMemCosts` DOUBLE COMMENT "查询规划阶段内存占用（字节）" ) ENGINE = OLAP DUPLICATE KEY (`queryId`, `timestamp`, `queryType`) COMMENT "审计日志表" PARTITION BY RANGE (`timestamp`) () DISTRIBUTED BY HASH (`queryId`) BUCKETS 3 PROPERTIES ( "dynamic_partition.time_unit" = "DAY", "dynamic_partition.start" = "-30", --表示只保留最近30天的审计信息，可视需求调整。 "dynamic_partition.end" = "3", "dynamic_partition.prefix" = "p", "dynamic_partition.buckets" = "3", "dynamic_partition.enable" = "true", "replication_num" = "3" --若集群中BE个数不大于3，可调整副本数为1，生产集群不推荐调整。 );
1.2 安装审计日志插件
1.2.1 下载 AuditLoader 安装包。
该插件兼容目前在维护的所有 StarRocks 版本。
1.2.2 解压安装包并找到对应版本的审计日志插件
解压auditloader.zip生成以下文件：
auditloader.jar：审计插件代码编译后得到的程序 jar 包。
plugin.properties：插件属性文件，用于提供审计插件在 StarRocks 集群内的描述信息，无需修改。
plugin.conf：插件配置文件，用于提供插件底层进行 Stream Load 写入时的配置参数，需根据集群信息修改。通常只建议修改其中的
user和password信息。
1.2.3 修改配置文件plugin.conf
# StarRocks user. user=xxx # StarRocks user's password password=xxx
1.2.4 重新打包以上文件
zip -q -m -r auditloader.zip auditloader.jar plugin.conf plugin.properties
1.2.5 安装该日志插件
将压缩包分发至所有 FE 节点运行的机器。请确保所有压缩包都存储在相同的路径下，否则插件将安装失败。分发完成后，请复制压缩包的绝对路径。
cp auditloader.zip /data01/starrocks3/plugins/
在StarRocks中执行以下命令安装。
INSTALL PLUGIN FROM "/data01/starrocks3/plugins/auditloader.zip";
查看安装状态
SHOW PLUGINS MySQL [demo]> show plugins; +---------------------------+-------+-------------------------------------------------------------------------+---------+-------------+----------------------------------------------+--------+--------------------------------------------+-----------+------------+ | Name | Type | Description | Version | JavaVersion | ClassName | SoName | Sources | Status | Properties | +---------------------------+-------+-------------------------------------------------------------------------+---------+-------------+----------------------------------------------+--------+--------------------------------------------+-----------+------------+ | __builtin_AuditLogBuilder | AUDIT | builtin audit logger | 0.12.0 | 1.8.31 | com.starrocks.qe.AuditLogBuilder | NULL | Builtin | INSTALLED | {} | | AuditLoader | AUDIT | load audit log to starrocks, and user can view the statistic of queries | 3.0.0 | 1.8.0 | com.starrocks.plugin.audit.AuditLoaderPlugin | NULL | /data01/starrocks3/plugins/auditloader.zip | INSTALLED | {} | +---------------------------+-------+-------------------------------------------------------------------------+---------+-------------+----------------------------------------------+--------+--------------------------------------------+-----------+------------+
1.3 查找10s以上的SQL
select * from starrocks_audit_db__.starrocks_audit_tbl__ where queryTime > 10000 limit 10;
2. 慢SQL优化
2.1 打开StarRocks的web 界面
http://xxx.xxx.xxx.xxx:8030/query
注意
WEB浏览器需与FE 节点网络相通。如不通，FE节点需要绑定公网IP。
2.2 连接到StarRocks服务，执行SQL前先执行以下语句打开profile
set enable_profile = true;
2.3 执行查询的SQL
样例SQL: TPCDS Q1
SELECT l_returnflag, l_linestatus, sum(l_quantity) AS sum_qty, sum(l_extendedprice) AS sum_base_price, sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge, avg(l_quantity) AS avg_qty, avg(l_extendedprice) AS avg_price, avg(l_discount) AS avg_disc, count(*) AS count_order FROM lineitem WHERE l_shipdate <= date '1998-12-01' - interval '90' DAY GROUP BY l_returnflag, l_linestatus ORDER BY l_returnflag, l_linestatus
2.4 打开StarRocks web ui 的Query Profile页面，找到刚刚执行的SQL
2.5 在SQL的Profile页面分析查询慢的部分
2.5.1 通过profile可以看到总时间为12s，其中Fragment 0的Pipeline (id=0)的InputEmptyTime耗时占比大部分时间，说明依赖的Pipeline执行慢。
Pipeline (id=0): - PendingTime: 12s457ms - InputEmptyTime: 12s461ms - __MAX_OF_InputEmptyTime: 12s461ms - __MIN_OF_InputEmptyTime: 12s461ms - FirstInputEmptyTime: 1s557ms - __MAX_OF_FirstInputEmptyTime: 12s461ms - __MIN_OF_FirstInputEmptyTime: 71.864us - FollowupInputEmptyTime: 10s904ms - __MAX_OF_FollowupInputEmptyTime: 12s461ms - __MIN_OF_FollowupInputEmptyTime: 0ns Pipeline (id=1): - ActiveTime: 116.114us - BlockByInputEmpty: 2 - BlockByOutputFull: 0 - BlockByPrecondition: 0 - DegreeOfParallelism: 1 - DriverPrepareTime: 52.421us - DriverTotalTime: 12s463ms - OverheadTime: 15.322us - PeakDriverQueueSize: 0 - PendingTime: 12s455ms - InputEmptyTime: 12s463ms - FirstInputEmptyTime: 12s463ms - FollowupInputEmptyTime: 123.778us - OutputFullTime: 0ns - PendingFinishTime: 0ns - PreconditionBlockTime: 0ns
2.5.2 Fragment 1的的最后一个Pipeline (id=5)的PendingTime耗时占比高。
- PendingTime: 12s451ms - __MAX_OF_PendingTime: 12s456ms - __MIN_OF_PendingTime: 12s447ms - InputEmptyTime: 12s454ms - __MAX_OF_InputEmptyTime: 12s456ms - __MIN_OF_InputEmptyTime: 12s450ms - FirstInputEmptyTime: 12s454ms - __MAX_OF_FirstInputEmptyTime: 12s456ms - __MIN_OF_FirstInputEmptyTime: 12s450ms - FollowupInputEmptyTime: 2.322us - __MAX_OF_FollowupInputEmptyTime: 4.837us - __MIN_OF_FollowupInputEmptyTime: 0ns
2.5.3 Fragment 2的Pipeline (id=0)的ActiveTime较高，PendingTime较低，说明Pipeline (id=0)执行耗时较高。
Pipeline (id=0): - ActiveTime: 11s606ms - __MAX_OF_ActiveTime: 11s967ms - __MIN_OF_ActiveTime: 11s239ms - PendingTime: 391.728ms - __MAX_OF_PendingTime: 722.636ms - __MIN_OF_PendingTime: 167.023ms Pipeline (id=1): - ActiveTime: 7s808ms - PendingTime: 1s462ms - __MAX_OF_PendingTime: 1s678ms - __MIN_OF_PendingTime: 1s251ms Pipeline (id=2): - ActiveTime: 250.294us
而Pipeline (id=0)中表达式计算时间占比较高，说明因行数比较多，导致表达式计算时间较久
PROJECT (plan_node_id=1): CommonMetrics: - OperatorTotalTime: 8s728ms - __MAX_OF_OperatorTotalTime: 9s30ms - __MIN_OF_OperatorTotalTime: 8s291ms - PullRowNum: 5.916B (5915582721) - __MAX_OF_PullRowNum: 192.590M (192590370) - __MIN_OF_PullRowNum: 177.111M (177110504) UniqueMetrics: - CommonSubExprComputeTime: 4s346ms - __MAX_OF_CommonSubExprComputeTime: 4s547ms - __MIN_OF_CommonSubExprComputeTime: 4s71ms
而Pipeline (id=1)中AGGREGATE_STREAMING_SINK执行时间较长，说明
AGGREGATE_STREAMING_SINK (plan_node_id=2): - OperatorTotalTime: 7s597ms - __MAX_OF_OperatorTotalTime: 7s978ms - __MIN_OF_OperatorTotalTime: 7s190ms