关于mysql copying to tmp table on disk状态说明和引起的问题
更新日期:
mysql copying to tmp table on disk状态说明和引起的问题
缘起:
最近在处理一个项目中需求场景时,需要并发读mysql,当时在调并发线程数过程中,报出了“ java.sql.SQLException: Incorrect key file for table ‘/tmp/#sql_1ab8_6.MYI’; try to repair it” 的错误,这个错误的基本原因是mysql 磁盘已满。
后来网络上查询了一下问题,这里稍微总结一下。
状态说明:
- mysql控制台 show processlist命令查看连接主机(show processlist经常用于查看慢查询等操作),在state栏偶尔会发现copying to tmp table on disk状态的连接
copying to tmp table on disk和copying to tmp table是两个不同状态一个向磁盘
临时目录写,一个不会。临界值取决于tmp_table_sizes属性大小,如果超过上限会将数据写到磁盘上,从而会有物理磁盘的读写操作,导致影响性能。查看和设置tmp_table_sizes命令:SHOW VARIABLES LIKE ‘tmp_table_size%’;和SET GLOBAL tmp_table_size=100000000;
这个状态出现的原因是查询数据超出了mysql参数tmp_table_size的限制导致。
- 如果查询数据大小超出tmp_table_size设置的大小,会进入下面三个状态流程中:
- mysql首先会将查询数据保存到磁盘的临时表中,而上面的/tmp/#sql_1ab8_6.MYI就是文件保存的绝对路径。state=copying to tmp table on disk
- 其次,当保存完后,会将数据进行排序,state = sorting result
- 最后才是将数据发送给客户端,state = sending data
- 而经过了这三个步骤,也间接的导致了慢查询的产生。所以查询语句最好不要出现“copying to tmp table on disk”状态
- sending data之后临时表空间会重新释放,tmp目录下的文件会被rm掉。 state = removing tmp table
- 未超出tmp_table_size大小的查询一般会直接进入sending data状态(也会出现copying to tmp table状态但是不想磁盘写)。
引起的问题:
当出现copying to tmp table on disk时很可能会间接引起
- 磁盘空间满(磁盘空间不足的情况下)
- I/O超时(time过大,慢查询导致)
解决:
针对自身项目情况做了如下解决:
- 调优连接数据库的并发线程数
- 这里由于某月份表一天数据可能将近150W,故采取了并发多个线程扫表的方式,调整扫表线程数有效利用临时表释放空间的解决方法。或者每个线程获取一定少数据直接绕过copying to tmp table on disk状态
- 避免直接操作mysql,源数据备份到缓存(比如redis),业务逻辑直接扫缓存。
- 修改tmp_table_size参数