MySQL 基线与容量管理
- 2019 年 10 月 4 日
- 筆記
MySQL基线指标的采集：
|
关键指标 |
|---|
|
qps |
|
tps |
|
connections |
|
slave_lag |
|
cpu_load |
|
disk load |
|
memory usage |
|
系统指标 采集方式 |
|---|
|
df |
|
node_exporter |
|
ps |
|
uptime |
|
vmstat / netstat / dstat / iostat |
|
MySQL指标 采集方式 |
|---|
|
SHOW [TABLE] STATUS |
|
SHOW FULL PROCESSLIST |
|
SHOW OPEN TABLES WHERE In_use> 0 |
|
INFORMATION_SCHEMA 库 |
|
PERFORMANCE_SCHEMA 库 |
|
sys 库 |
|
slow query log |
|
mytop/innotop/pt-toolkit |
|
mysqld_exporter |
MySQL容量管理：
1、数据库大小估计
一种可靠的方式是 使用解压后的备份文件（必须是Xtrabackup的物理备份）来估算当前数据库的体积。 mysqldump这种逻辑备份的方式，不便于直观的比对数据库体积的增长。
或者我们可以使用如下SQL 估算data文件体积：
|
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "DB Size in MB" FROM information_schema.tables; |
|---|
2、二进制日志大小估计
统计一天内二进制日志大小，并将其与expire_logs_days值相乘。设置expire_logs_days非常重要，这样您就可以正确估算大小。
3、其它文件大小估计
3.1 MySQL在大的查询过程中，可能会产生巨量临时表。 【size of largest table * 2 (for tmp/sort files)】
3.2 在做表DDL的时候，操作过程中，需要产生的一个大的临时表，需要占据较大的体积。
3.3 ibdata1 、redolog 、slowlog 、general_log 等文件
3.4 注意tmpdir的路径及占据的体积
3.5 本地备份文件（如果有在本地存放备份文件的话）
3.6 至少预留百分之五的空闲磁盘空间留给操作系统使用
4、relaylog的大小估算
某些场景下（例如数据库体积超过1T），我们可能不再做每周的全量备份，而增加一个延迟复制slave的方式。 这种情况下，需要关注下延迟复制slave所在机器的relay-log的体积（延迟的N小时 * N小时内主库产生的binlog的体积）。
容量预测
根据数据库的历史监控数据（zabbix 或 prometheus） ，我们可以大致预测数据文件的周增长量、binlog的周增长量 以及系统负载波动的趋势。
容量解决之道
1、历史数据归档
根据业务场景，按照时间归档，迁移历史数据到大容量廉价低速的磁盘。降低生产环境数据库体积和负载，较小的表体积，便于数据库备份和DDL操作。
2、数据分片
2.1 客户端分片
sharding-jdbc
2.2 中间件分片
dble、mycat、cetus
参考文章：
https://severalnines.com/blog/capacity-planning-mysql-and-mariadb-dimensioning-storage-size
http://www.jebriggs.com/blog/2010/07/mysql-storage-capacity-planning/
