![Page 1: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/1.jpg)
美团数据库运维平台介绍
侯军伟
![Page 2: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/2.jpg)
自我介绍
• 侯军伟
• 美团网数据库负责人
• MySQL、Redis、Redis Cluster
• 微博:@曾经的阿飞
![Page 3: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/3.jpg)
大纲
• 自动化运维平台
• 慢查询系统
• 备份系统
• 运维报表
• Percona使用经验
![Page 4: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/4.jpg)
自动化运维平台
自助DDL
授权申请
慢查询系统
DBA Weekly Report CPU IO 慢查询 监控
RD DB Weekly Report 废弃表 废弃索引 冗余索引
容量管理
历史Processlist查询
历史Innodb Status查询
元信息
DBManager
工具 报表
![Page 5: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/5.jpg)
![Page 6: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/6.jpg)
自助DDL
![Page 7: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/7.jpg)
类型
检查
备份
Drop
O S C
Size
Create Alter&Drop
Drop Alter
Delay
执行
![Page 8: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/8.jpg)
pt-online-schema-change
New Data
Old Data
New Table
触发器
Insert ignore into newtable select from oldtable where pk between () lock in share mode
主从表结构异构,会导致Slave表结构被覆盖
添加唯一索引,如果存在重复数据会丢失
![Page 9: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/9.jpg)
DBManager
![Page 10: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/10.jpg)
慢查询
![Page 11: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/11.jpg)
慢查询
• pt-query-digest
• Box Anemometer
![Page 12: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/12.jpg)
MySQL Slow Log Pt-query-digest
MySQL Anemometer MySQL Slow Log Pt-query-digest
MySQL Slow Log Pt-query-digest
![Page 13: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/13.jpg)
![Page 14: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/14.jpg)
备份系统
• Xtrabackup
• 自动扩容
![Page 15: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/15.jpg)
统计分析报表
自助DDL
授权申请
慢查询系统
DBA Weekly Report CPU IO 慢查询 监控
RD DB Weekly Report 废弃表 废弃索引 冗余索引
容量管理
历史Processlist查询
历史Innodb Status查询
元信息
DBManager
工具 报表
![Page 16: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/16.jpg)
DBA Weekly Report
• 慢查询TOP10
• 磁盘消耗速度TOP10
• Innodb_pages_read TOP10
• Innodb_row_lock_time TOP10
• 监控被disable的主机
• 自增ID溢出检测
![Page 17: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/17.jpg)
![Page 18: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/18.jpg)
DB Weekly Report
• 包含内容
– 持续一周不在访问表
– 持续一周不在使用的索引
– 冗余的索引
• 实现方式
– percona user stat
– pt-duplicate-key-checker
![Page 19: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/19.jpg)
![Page 20: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/20.jpg)
• 历史processlist查询
• 历史的show engine innodb status查询
![Page 21: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/21.jpg)
容量管理
团购/外卖/猫眼/酒店
![Page 22: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/22.jpg)
容量管理
• Benchmark
• Tcpcopy
• 挖掘历史数据估算节假日峰值
• 监控数据:max(avg(15m))
![Page 23: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/23.jpg)
Percona使用经验
• Percona Server – User Statistics – Response Time Distrub – Thread pool – innodb_deadlocks – max_binlog_files
• Percona Toolkit – pt-query-digest – pt-kill – pt-online-schema-change – pt-duplicate-key-checker
![Page 24: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/24.jpg)
One More Thing……
![Page 25: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/25.jpg)
Redis Cluster使用经验
![Page 26: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/26.jpg)
• cluster-node-timeout 15000
• 添加slave节点
• Flushall操作
![Page 27: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/27.jpg)
• 使用redis-trib.rb创建集群
• 添加slave节点
• 小心master和slave分布在同一台机器上(issue 2204)
![Page 28: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/28.jpg)
• 慢查询
• 避免神秘主义
– 一个节点内存消耗远大于其他节点
– 不恰当使用monitor导致client output buffer占用了大量的内存
![Page 29: 侯军伟 - Huodongjia.com...2018/04/09 · •Benchmark •Tcpcopy •挖掘历史数据估算节假日峰值 •监控数据:max(avg(15m)) Percona使用经验 •Percona Server](https://reader033.vdocuments.us/reader033/viewer/2022060906/60a0ca72404a7b7ac774e278/html5/thumbnails/29.jpg)