文章目录
  1. 1. mysql copying to tmp table on disk状态说明和引起的问题
    1. 1.1. 缘起:
    2. 1.2. 状态说明:
    3. 1.3. 引起的问题:
    4. 1.4. 解决:

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参数
文章目录
  1. 1. mysql copying to tmp table on disk状态说明和引起的问题
    1. 1.1. 缘起:
    2. 1.2. 状态说明:
    3. 1.3. 引起的问题:
    4. 1.4. 解决: