背景
因为这边公司做数据分析的需求,mysql并不满足数据的速度条件,所有需要先mysql同步到clickhouse,因为同步到clickhouse的压力过大,为了不影响主库,所以采用搭建主从的方式,通过从库进行数据同步,为了保证多个从库同步所产生的IO不受影响,我采用每个从库用独立的硬盘,那么硬盘IO就能最大程度减少影响
mysql版本:8.0.18
问题
主从搭建完成后,过几天机器开始报警,存储不够。。。。。。,检测到有一些表的比主库大,有部分还大10倍,这,我们知道如果进行大量增删改过程中,会导致表产生碎片,主从服务器上同张表的碎片率不同也会导致表空间相差很大
那么为什么一致的表结构以及索引等都一样,为啥产生的碎片相差这么大。。。 这是一个值的深思的问题
问题思路
直觉是表碎片,那么要相信直觉,先查看表碎片
SELECT
t.TABLE_SCHEMA ,t.TABLE_NAME,t.ENGINE,
t.TABLE_ROWS,
CONCAT(ROUND(data_length/( 1024*1024*1024 ), 2), 'G') DATA,
CONCAT(ROUND(index_length/( 1024*1024*1024 ), 2), 'G') IDX,
CONCAT(ROUND(( data_length + index_length ) / ( 1024*1024*1024 ), 2), 'G') 'TOTAL SIZE',
ROUND(index_length/data_length, 2) IX_FRAC,
CONCAT(ROUND(( data_free/1024/1024),2), 'MB') AS data_free
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA='库名' and t.table_name='表名'
ORDER BY data_length + index_length DESC;
在主库跟从库都执行一次,你会发现,很离谱很过份,我真想说句话MMP的。。。。。。
解决方案
- 尽量保障主库跟从库的配置文件一致(最好把主库的mysql程序包复制过去)
- 只能自己去整理表碎片了
# 推荐
optimize table 表名 或 optimize table 库名.表名
alter table 表名 engine=innodb 或 alter table 库名.表名 engine=innodb
# 我这边是从库,所以直接停同步执行
# 如果是主库的话,请注意:执行前要先检查有无未结束事务或其他未释放锁