记一次大库大表的治理过程
一、背景
部门中一核心应用,因为各种原因其依赖的MySQL数据库一直处于高水位运行,无论是硬件资源,还是磁盘使用率或者QPS等都处于较高水位,急需在大促前完成对应的治理,降低各项指标,以保障在大促期间平稳运行,以期更好的支撑前端业务。
二、基本情况
2.1、数据库
目前该数据库是一主两从,且都是零售的物理机,运行多年已都是过保机器。同时因为CPU和磁盘较大,已无同规格的物理机可以增加一个从库。同时其中一个从库的内存减半且磁盘还是机械盘,出故障风险极高且IO性能低导致查询偏慢,出现过多次因性能问题切到另一个从库的情况。
以下是其3台机器的硬件资源信息,MySQL版本、部署机房和硬件配置情况。其中135机器硬盘容量128T是统计显示有误,可以认为也是16T。因为磁盘做了RAID0,因此实际容量在7T左右。
域名 | 主/从 | CPU | 内存 | 容量 | 机房 | DISK(/export)使用率(%) | Memory使用率(%) | 数据库版本 | |
1x.x.x.36 | xxx_m.mysql.jddb.com | 主 | 64 | 256G | 16T | 汇天云端机房 | 66.3% | 87.7% | 5.5.14 |
1x.x.x.73 | xxx_sb.mysql.jddb.com | 从 | 64 | 256G | 16T | 汇天云端机房 | 66.6% | 85.2% | 5.5.14 |
1x.x.x.135 | xxx_sa.mysql.jddb.com | 从 | 64 | 128G | 128T | 廊坊机房 | 76.5% | 57.2% | 5.5.14 |
2.2、磁盘空间
截止到2月底,各数据库磁盘空间占用情况如下:
IP | 主从 | 使用大小(G) | 已用比例(%) | 剩余空间(G) | 周增长量(G) | 预计报警(d) | 预计可用(d) | binlog(G) | 日志(G) |
1x.x.x.36 | M | 5017 | 69 | 2151 | 9 | 617.1 | 1735.8 | 159.45543 | 6 |
1x.x.x.73 | S | 5017 | 71 | 2151 | 14.8 | 333.2 | 1012.7 | 158.52228 | 1 |
1x.x.x.135 | S | 5017 | 4 | 129000 | 14.4 | 2986 | 8958 | 158.13548 | 0 |
从上表咱们可以看出,各数据库的磁盘空间占用已处于较高水位,急需需要治理,通过结转或删除数据来降低磁盘占用比例。
2.3、表空间
数据库存在大表其中一个原因是多条业务线共用一个应用,同时代码层面抽象的部分不够抽象,扩展部分又不容易扩展,导致数据都糅合和一起。
以下是所有的表空间占用情况,可以明显看到大部分的表数据量都在千万行以上,特别是前7张表的表空间占用都在100个G以上,数据行数也都在亿级以上,最多的是status表,30亿行数据,典型的大库大表。
2.4、QPS情况
黄色的为主库的QPS,可以看出主库的查询量远大于从库,由于各种原因,应用代码里只有少部分的查询是走的从库,急需将部分流量大的查询接口从主库切到从库去查询;
2.5、慢SQL
不论是主库还是从库,都有偶发的慢SQL查询,引发磁盘繁忙,影响系统稳定性。
三、治理目标
治理目标:将表空间占用大于100G的7张表(xxx_status、xxx_main、xxx_exception、xxx_product_code、xxx_item、freights_info、xxx_extend)先进行集中结转,保留一年数据后进行常态化结转,按天结转,将数据量保持在365天;
治理目标:将主库的高频查询切换到从库查询,使主库白天QPS降低30%,近一个月上午峰值平均在20k,下午峰值平均在25k;治理的目标为:上午峰值15k,下午峰值18k;
治理目标:10s以上的彻底消除;5s以上的,消除80%;1s以上的消除60%;底数是过去一个月(1s以上慢sql);
四、治理方案
4.1、大表数据结转
根据这7张表的业务属性不同,结转的类型也不相同;比如对于历史数据无意义的,可以将历史数据直接删除,比如xxx_exception;另外一类是纯历史数据,比如流水数据xxx_status表,结转方式是同步大数据平台后就可以删除;最后是业务主数据,是需要同步大数据平台和需要结转至历史库的,比如main、item和extend表等;
表名 | 表空间GB | 索引空间GB | 大数据 | 结转类型 | 开始值 | 完成值 |
xxx_status | 991.65 | 265.29 | 是 | 删除 | 2020-04-30 01:00:00 | 2022-01-01 |
xxx_main | 611.80 | 149.91 | 是 | 结转 | 2021-09-30 | 2022-01-01 |
xxx_exception | 382.80 | 24.65 | 否 | 删除 | 2018-05-16 20:30:04 | 2022-01-01 |
xxx_product_code | 244.18 | 61.54 | 是 | 删除 | 23亿 | |
xxx_item | 208.66 | 85.46 | 是 | 结转 | 2016-12-29 13:20:33 | 2022-01-01 |
xxx_freights_info | 128.78 | 109.03 | 是 | 结转 | 2018-11-29 13:26:00 | |
xxx_extend | 127.36 | 26.07 | 是 | 结转 | 2019-03-29 14:30:00 | 2022-01-01 |
以下的统计表格是在同步大数据平台后集中删除和结转的空间释放情况,在1个月内对数据量在1亿以上并且占用空间在100G以上的7张大表进行了删除和结转后删除,使数据在保留365天的业务承诺时间范围内,降低了470G(10%)的磁盘空间占用;
PS:红色数字部分为负值,也就是磁盘的释放空间。
4.2、拦截无参数查询
运单主档查询偶发会有无任何参数的查询,引发严重慢SQL,造成数据库磁盘繁忙度严重飚高,极大地影响了其他业务操作,而由于入口众多和交叉调用,如果在入口做参数校验工作量及风险都比较大,所以采用MyBatis的插件机制在dao层做拦截,直接拒绝掉无参数的查询,上线后就再没有出现过因无参查询而出现慢SQL而导致的磁盘繁忙情况;
mybatis-config.xml里的plugin配置:
ParameterInterceptor关键代码如下:
源代码如下:
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.lang.reflect.InvocationTargetException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
/**
* MyBatis拦截器,用于拦截SQL查询无入参的场景,避免全表查询
*
*/
@Intercepts({
@Signature(type = Executor.class, method = "query",
args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public