|
突然接到开发打来电话说MySQL数据库执行SQL报错，让排查一下原因。 登陆MySQL服务器看了一眼错误日志： 140904 12:06:20 [ERROR] /usr/local/mysql5.5/bin/mysqld: Incorrect key file for table '/tmp/#sql_5608_1.MYI'; try to repair it 140904 12:06:20 [ERROR] Got an error from unknown thread, /tmp/mysql5.5_install/tmp/mysql-5.5.30/storage/myisam/mi_write.c:223 看到/tmp下的问题，先看一眼tmpdir： mysql> show variables like 'tmpdir'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | tmpdir | /tmp | +---------------+-------+ 1 row in set (0.00 sec) 说/tmp下有表需要修复，通过tmpdir可知，是临时排序表所致。在网上查了一下，说产生这个问题是由于tmpdir目录下空间不足。 查看一下磁盘空间： [root@localhost data]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 15G 31G 33% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data /tmp属于/ 根目录下，空间还有31G，觉得不可能。 查看慢日志，找到一条很可疑的语句，拿50万的数据来排序，还有的语句拿200多万的数据来排序，而且从时间上也与开发说的时间很吻合： # Time: 140904 16:13:50 # User@Host: staff_reader[staff_reader] @ [10.35.15.68] # Query_time: 133.653564 Lock_time: 0.000186 Rows_sent: 5384178 Rows_examined: 20886933 use hotel_inventory; SET timestamp=1409818430; SELECT col1,col2,col3....,col7 FROM table1 nolock WHERE available_time >= '2014-09-03' AND available_time <= DATE_ADD('2014-09-03', interval 91 day) order by hotel_id,room_type_id,available_time; 看一下该语句访问的表的具体情况： 表行数为20886994： mysql> select count(*) from table1; +----------+ | count(*) | +----------+ | 20886994 | +----------+ 表大小为7G： [root@localhost]# du -sch *|grep sum_inv_room.ibd 6.9G sum_inv_room.ibd 该机器上有4个实例： [root@192.168.97.149 hotel_inventory]# ps -ef|grep mysqld_safe root 21185 1 0 2013 ? 00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6025/etc/my6025.cnf root 22525 1 0 2013 ? 00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6027/etc/my6027.cnf root 24275 1 0 2013 ? 00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6024/etc/my6024.cnf root 37864 1 0 2013 ? 00:00:00 /bin/sh /usr/local/mysql5.5/bin/mysqld_safe --defaults-file=/data/mysql6026/etc/my6026.cnf root 60463 5879 0 16:18 pts/6 00:00:00 grep mysqld_safe 总大小有30G左右，都是同时抽取数据： [root@192.168.97.149 hotel_inventory]# du -sch /data/mysql602*/data/hotel_inventory/* |grep sum_inv_room.ibd 6.9G /data/mysql6024/data/hotel_inventory/sum_inv_room.ibd 7.1G /data/mysql6025/data/hotel_inventory/sum_inv_room.ibd 7.9G /data/mysql6026/data/hotel_inventory/sum_inv_room.ibd 6.5G /data/mysql6027/data/hotel_inventory/sum_inv_room.ibd 既然看到这些现象，那么我肯定就是这条语句同时执行所致。这时候给开发反馈，而开发说[staff_reader] @ [10.35.15.68] 这个用户不是他们那边的。没办法了，继续找。我就手动那上面那条语句去执行，发现根本没产生多少临时文件。这时候可以断定不是这个语句造成的了。 所以这个时候只能等问题再次重现。看了一下磁盘空间，正常情况下，/ 根目录可用空间是31G，然后我就写了个脚本一直监测可用空间是否是31G，如果不是，就给我QQ邮箱发邮件。 过了一会，果然收到邮件了，这时候上服务器看磁盘空间，在持续增长： 查看磁盘空间情况： [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 30G 17G 65% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 33G 14G 71% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 35G 11G 77% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# du -sch * 88K mha4mysql-node-0.53 2.1G #sql_5608_0.MYD 4.0K #sql_5608_0.MYI 3.6G #sql_5608_1.MYD 4.0K #sql_5608_1.MYI 1.9G #sql_5b44_0.MYD 4.0K #sql_5b44_0.MYI 4.9G #sql_5b44_1.MYD 4.0K #sql_5b44_1.MYI 2.0G #sql_621a_0.MYD 4.0K #sql_621a_0.MYI 749M #sql_621a_1.MYD 4.0K #sql_621a_1.MYI 2.2G #sql_9753_0.MYD 4.0K #sql_9753_0.MYI 4.2G #sql_9753_1.MYD 4.0K #sql_9753_1.MYI 22G total [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 37G 9.5G 80% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 41G 5.2G 89% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 46G 29M 100% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 46G 22M 100% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 46G 0 100% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data 空间满的同时，看到/data/mysql6025/log/mysqld.err中又出现错误： 140904 16:03:59 [ERROR] /usr/local/mysql5.5/bin/mysqld: Incorrect key file for table '/tmp/#sql_5608_1.MYI'; try to repair it 140904 16:03:59 [ERROR] Got an error from unknown thread, /tmp/mysql5.5_install/tmp/mysql-5.5.30/storage/myisam/mi_write.c:223 通过慢日志来看，又出现了上面那条很可疑的语句。可是之前已经排除了其的可能性，那就只能继续找，发现一条很简短的语句，看起来没什么，但是执行时间很长： SELECT MIN(sum_inv_room_id), MAX(sum_inv_room_id) FROM (select s.*, 20140909 as dw_insert_dt_wid from sum_inv_room s WHERE (1 = 1) ) AS t1; 出错的这段时间，基本上就这两条SQL在跑。 报错之后，磁盘空间逐渐恢复： ........................................................ [root@192.168.97.149 tmp]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 38G 8.6G 82% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data [root@192.168.97.149 log]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 49G 15G 31G 33% / tmpfs 32G 0 32G 0% /dev/shm /dev/sda1 985M 44M 891M 5% /boot /dev/sda4 219G 6.5G 201G 4% /opt /dev/sdb1 1.4T 176G 1.2T 14% /data 最后空间使用率恢复正常。 于是手动拿这个语句执行了一下： SELECT MIN(col1), MAX(col1) FROM (select s.*, 20140909 as dw_insert_dt_wid from table1 s WHERE (1 = 1) ) AS t1; 果然看到磁盘空间立马飙升，找到原因所在。单条语句执行，看到其产生的临时文件最大到了10多G。一共31G的空间，4个实例，都产生10多G，当然扛不住。 （至于具体为什么这样的语句会产生这么大的临时文件，本人对其中的原理不是太理解，先做此记录。） 经过与开发沟通，开发说那个是定时抽取数据。而且机器上的4个实例是同时开始的。 所以给出以下解决办法： 1.将MySQL中参数tmpdir设置到大的目录下去，但是这个需要参数是read_only变量，需要重启数据库 2.将这4个语句串行执行 3.修改抽取数据的SQL 最后开发选择了第2种方法，将定点抽取数据的SQL串行执行，问题暂时得到解决，至少半个多月都没再出现这个问题了。 (责任编辑：IT) |