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