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