You need to enable JavaScript to run this app.
导航
慢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 写入时的配置参数,需根据集群信息修改。通常只建议修改其中的 userpassword 信息。

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