在 2017 年里, 项目上的 MySQL 出了几个问题, 记录总结一下, 给自己一个教训!
废话不多说了, 直接进入问题主题:
问题一: MySQL 单表千万级数据量 "add" 字段异常分析
问题描述:
项目一个新功能, 需要对有 1200w + 的表 "tb_bw_mobile" 新增 2 个字段 "batch_num""data_status"; 执行 SQL 月 5-6 分钟后, 查询数据, 发现未增加字段; 约 10-13 分钟后 limit 110100 条数据, 均为发现有新增的 2 个字段; 此时查询表结构, 发现表结构未变化; 约 24 分钟后, MySQL 命令行端抛出异常"ERROR 1034 (HY000): Incorrect key file for table 'tb_ec_bw_mobile'; try to repair it"; 再此核实表结构数据, 发现均未有任何改变;
当时就蒙蔽了, 怎么会提示 "try to repair it", 表结构怎么会有问题? 通知测试人员, 将生产的表结构和 10w 条数据导出来在测试环境进行测试, 结果并没有什么问题; 此时查看 MySQL 日志, 发现集群出现了异常
执行 SQL 时抛出异常, 尝试 4 次修改当前执行事务未 306; 修改失败后, 当前事务在从节点的状态从 SYNCD->OPEN->CLOSED, 再看后面的信息, MySQL 从节点服务器异常
此时重启 mysql 服务, 查看 mysql 进程; 基于 rsync 服务以及 4444 端口来同步恢复数据; 同时查看进程的线程和 io, 确定是在做数据同步; 约 30 分钟后, 查询 mysql 日志及系统日志, mysql 数据同步成功且成功加入集群集群正常提供服务
** 此时回过头来查看执行 sql 抛出的异常"ERROR 1034 (HY000): Incorrect key file for table 'tb_ec_bw_mobile'; try to repair it"; 在官网中看到解决办法, 需创建一张临时表做数据迁移, 而不是直接对千万级数据直接 add 字段;
问题二: MySQL 临时表问题
问题描述:
MySQL 应用所在云主机系统磁盘容量不断减小, 在 tmp 目录下不断生成以 MADMAI 为后缀的文件, 导致前端 web 服务 mgr 在 1min 对 mysql 发起请求的连接高于 1000+; 此时查看 mysql 的进程, 发现有 Copy data to tmp 的相关事务在执行, 原因是黑白名单功能把 tb_ec_contact 和 tb_ec_bw_mobile 两张千万级数据表关联起来
问题分析:
Mysql 临时表分为内存临时表和磁盘临时表; 一般情况下, MySQL 会先创建内存临时表, 但是内存临时表超过配置指定的值后, MySQL 会将内存临时表导出到磁盘临时表; 使用临时表的场景: 对 Innodb 表进行 alter 和 create 操作或者是 sql 语句效率低下事务执行过慢, 未做优化, 以下为网关说明;
需要注意的是: 此配置默认路径是 / tmp 目录, 生成以 MADMAI 为后缀的文件, 在短时间内很容易将系统磁盘占慢, 导致依赖 mysql 的程序运行异常; 文件删除后系统磁盘空间也不会释放, 因为 mysql 的线程正在运行, 除非停止 mysql 服务;(可以使用 lsof 查看); 但是好处是如果线程执行完了, 会将生成的文件自动删除, 释放系统磁盘空间;
此参数配置不能在线进行全局修改, 只能先停止 mysql 服务, 在配置文件中写明相关参数重启服务才行; 但是临时表的空间和行数可在线修改;
- show global variables like %tmp%;
- tmpdir ## 指明 mysql 磁盘临时表存储路径
- slave_load_tmpdir=/data/mysql/slave_tmpdir ## 指明从节磁盘临时表路径
- max_tmp_tables=128 ## 指明支持多少个内存临时表
- tmp_table_size=33554432 ## 指明临内存时表的大小
- max_heap_table_size=33554432 ## 指明内存临时表表支持多少行
来源: http://blog.51cto.com/wangtianci/2092459