作者 ｜ 房廠
項目概覽
背景
慢 SQL 即執行時間超過 long_query_time 設定閾值的 SQL 語句，可通過 select @@long_query_time 查看數據庫具體的慢查詢閾值。另外慢 SQL 不僅僅包括 select 語句，也包括 delete,insert 等 DML 語句。
慢查詢 SQL 的危害包括:
- 性能: 慢 SQL 的執行時間過長，則會導致用戶的等待時間過長，直接影響用戶體驗;
- 穩定性: 當 db 出現慢查詢，一旦有其他的 DDL 操作，可能會造成整個數據庫的等待;另一方面，慢 SQL 會拖垮數據庫，導致正常執行的 SQL 也會變成慢 SQL。在字節的線上事故管理平臺搜索慢 SQL 關鍵字可以看到很多由于慢 SQL 導致的事故，危害性較大。
成果
發布慢 SQL 月報，整理最佳實踐，頭部泳道推動改進等取得了慢 SQL 數下降了近 50%，慢 SQL 周運行次數下降了一個數量級的成效;
慢 SQL 配置&告警訂閱持續配置率從 18% 提升到 70% 左右，持續優化中。
名詞解釋
- RDS：Relational Database Service,即字節關系型數據庫服務。提供的關系數據庫服務，使用的數據庫產品主要以開源 MySQL 數據庫為主。字節云關系型數據庫服務(RDS)專注于為業務提供穩定可靠，彈性伸縮的在線數據庫服務。
- Mars：客增性能平臺名稱。
- 風神 Aeolus：字節自研敏捷 BI 平臺，提供靈活易用的數據查詢，高效美觀的報表制作，與豐富多元的數據內容。
設計方案
1. 架構圖
2. 核心功能
2.1 全面的慢 SQL 度量看板
以字節 RDS 平臺數據庫的慢 SQL 數據為依據，量化管理客增每日/每周/每月的慢 SQL 數量&運行次數。按照度量看板數據推動大家及時改進存量的慢 SQL，降低數據庫質量風險。例如周維度的運行次數 & 慢 SQL 條數趨勢圖如下所示：
2.2 慢 SQL 治理體系
2.2.1 rds 慢 SQL 閾值配置自動化管理
字節關系型數據庫平臺-RDS 提供慢 SQL 閾值配置的功能：
- 當 SQL 執行時間超過該閾值后，會被自動 kill 終止運行，相當于慢 SQL 的容災配置(如果一條 SQL 執行了 3 個月還在運行，結果不敢想象)
慢 SQL 閾值配置自動化管理是解決業務關聯的數據庫全部配置了慢 SQL 閾值信息。該部分通過線上定時巡檢來實現，流程如下：
2.2.2 Mars-慢 SQL 治理平臺
在客增質量工作臺搭建 Mars-客增慢 SQL 治理 Web 頁面，展示相關業務的慢 SQL 現狀以及排期跟進修復情況，目的是讓業務同學更清晰快速了解當前業務相關，提供問題修復效率，方案如下：
慢 SQL 跟進頁面:
2.2.3 慢 SQL 風險評估模型-慢 SQL 分
當業務線存在較多慢 SQL 時，如何精準且合理的分析出哪些慢 SQL 風險最高?
我們基于關系型數據庫的 Quert_time,Lock_time,Rows_sent,Rows_affected,Bytes_sent 等維度建立客增的慢 SQL 風險評估模型，給每條慢 SQL & 每個數據庫打分，按照慢 SQL 分來排序，分數最高的慢 SQL 風險最高。
慢 SQL 模型如下:
2.3 慢 SQL-CI 流水線準入/準出卡口建設
基于 ByteCycle(ByteCycle 字節統一能效中臺)開發慢 SQL 原子節點，提供慢 SQL 相關的卡點能力。bytecycle 基于 psm 維度來構建持續集成流水線，通過提供慢 SQL 原子節點，可以方便用戶插拔式使用。CI 卡點能夠提供大家對慢 SQL 的重視程度以及提高慢 SQL 的改進效率。
2.4 慢 SQL 監控&告警訂閱
目前提供慢 SQL 月報，每日慢 SQL 相關問題修復提醒，sqll kill lark 告警卡片等維度的信息展示和觸發。相關樣式如下:
慢 SQL 月報
每日慢 SQL 問題修復提醒
配置 db 慢查詢閾值后，如果超過該閾值則該語句會被 db 自動 kill，訂閱后會自動將獲取到的 kill 信息發送到對應群中
3. Code 方案
RDS 元信息獲取實現方案
數據表設計
create table cg_rds_external
(
id int unsigned auto_increment primary key comment 'id',
db_name varchar(100) default '' null comment 'db名字',
owners varchar(100) default '' not null comment 'db owners',
region varchar(100) default '' not null comment 'db部署的region',
proxy_port_master varchar(100) default '' not null comment 'master節點的port',
proxy_port_slave varchar(100) default '' not null comment 'slave節點的port',
sync_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '數據同步時間'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 comment 'rds db額外信息';
create table cg_rds_slow_query_config
(
id int unsigned auto_increment primary key comment 'id',
config_id int null comment '慢查詢配置id',
db_name varchar(255) default '' null comment 'db名字',
region varchar(100) default '' not null comment 'db部署的region',
port varchar(100) default '' not null comment '規則中的端口',
db_role varchar(100) default '' not null comment 'master or slave',
max_query_time int null comment '超時閾值，單位是秒',
creator varchar(100) default '' null comment '規則創建人',
create_time varchar(100) default '' null comment '規則創建時間',
sync_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '數據同步時間'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 comment 'rds慢查詢規則配置信息';
create table cg_rds_db_alarm_config
(
id int unsigned auto_increment primary key comment 'id',
region varchar(100) default '' not null comment 'db部署的region',
alarm_id int null comment 'alarm 規則id',
db_name varchar(255) default '' null comment 'db名字',
type varchar(100) default '' not null comment 'alarm type,例如lark',
group_id varchar(100) default '' not null comment 'lark id',
create_time varchar(100) default '' not null comment '規則創建/更新時間',
owner varchar(100) default '' not null comment 'alarm創建人',
sync_time datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '數據同步時間'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 comment 'rds alarm配置表';
慢 SQL 查詢詳情落庫
數據表
create table cg_slow_query_detail_info
(
id int unsigned auto_increment primary key comment 'id',
db_name varchar(255) default '' null comment 'db 名',
db_region varchar(255) default '' null comment 'db的region',
fingerprint_md5 varchar(255) default '' null comment '慢sql標識',
begin_time datetime DEFAULT CURRENT_TIMESTAMP null comment '慢sql的開始執行時間',
max_run_time varchar(255) default '' null comment 'sql執行的最大耗時',
run_count int default 0 null comment 'sql執行次數',
psm_name varchar(255) default '' null comment '發起sql的psm',
avg_query_time varchar(255) default '' null comment '平均耗時',
rds_address varchar(255) default '' null comment '執行sql的rds主機ip:port',
psm_host varchar(255) default '' null comment '發起查詢請求的主機ip',
sync_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '數據同步時間'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4 comment '客增慢sql記錄';
慢 SQL 被 kill 的詳情信息獲取方案
數據表
create table cg_kill_sql_detail_info
(
id int unsigned auto_increment primary key comment 'id',
db_name varchar(255) default '' null comment 'db 名',
db_region varchar(255) default '' null comment 'db的region',
db_role varchar(255) default '' null comment 'db節點: master slave',
begin_time datetime DEFAULT CURRENT_TIMESTAMP null comment '被kill的sql 執行開始時間',
psm_name varchar(255) default '' null comment '發起sql的psm',
sql_detail varchar(2000) default '' null comment 'sql詳情',
db_table_name varchar(255) default '' null comment '該sql的表名,如果多個表，只取第一個',
sync_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '數據同步時間'
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4 comment 'rds被kill的慢sql數據統計';
Metrics 監控規則
rds 報警訂閱的監控只能發現 rds 上執行的 SQL 數據，不能實時發現慢接口。故推薦使用 dbatman 的 metrics 打點來完成慢 SQL 的監控告警工作。
$key = "max:toutiao.ttds.dbatman.latency.max{db=sales_manage,port=*,host=*,dc=*}"
$value = max(q($key, "3m", "1m"))/1000
warn = $value>50
runEvery=1
4. 慢 SQL 治理最佳實踐與標準制定
慢 SQL 治理優化基本可分為如下 3 類:
- 優化 shcema
- 優化索引，盡可能構建三星索引
- 優化查詢，合理的設計查詢
相關細則如下所示:
