mysql临时文件产生多引起mysql 挂起的问题处理

来源：互联网发布：unity3d 机器人仿真编辑：程序博客网时间：2024/05/17 09:15

1.问题描述
开发人员反应uat1环境mysql挂了，检查mysql服务器，发现磁盘可用空间为20K，空间已用完
[apps@lvxl087 tmp]$ df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/mapper/vg_root-LogVol01
                       36G 5.7G   28G 18% /
tmpfs                 7.8G     0 7.8G   0% /dev/shm
/dev/sda1             190M   33M 148M 19% /boot
/dev/mapper/data-lvmdata1
                      200G 200G   20K 100% /apps

2.问题分析
进一步检查，发现/apps/tmp占用空间达188G。
cd /apps/tmp
ls -ltr
-rw-rw---- 1 apps apps 11628183552 Sep 29 19:43 #sql_46dc_32.MAD
-rw-rw---- 1 apps apps 17861533696 Sep 29 19:44 #sql_46dc_8.MAD
-rw-rw---- 1 apps apps 21080182784 Sep 29 20:48 #sql_46dc_0.MAD
-rw-rw---- 1 apps apps 8874029056 Sep 29 20:48 #sql_46dc_48.MAD
-rw-rw---- 1 apps apps 8032731136 Sep 29 20:48 #sql_46dc_56.MAD
-rw-rw---- 1 apps apps 10133520384 Sep 29 20:48 #sql_46dc_40.MAD
-rw-rw---- 1 apps apps 12396552192 Sep 29 20:48 #sql_46dc_28.MAD
-rw-rw---- 1 apps apps 1125965824 Sep 29 20:48 #sql_46dc_60.MAD
-rw-rw---- 1 apps apps 8417992704 Sep 29 20:48 #sql_46dc_52.MAD
-rw-rw---- 1 apps apps 523075584 Sep 29 20:48 #sql_46dc_72.MAD
-rw-rw---- 1 apps apps 13344399360 Sep 29 20:48 #sql_46dc_24.MAD
-rw-rw---- 1 apps apps 14563278848 Sep 29 20:48 #sql_46dc_20.MAD
-rw-rw---- 1 apps apps 17045856256 Sep 29 20:48 #sql_46dc_12.MAD
-rw-rw---- 1 apps apps 9619939328 Sep 29 20:48 #sql_46dc_44.MAD
-rw-rw---- 1 apps apps 10790330368 Sep 29 20:48 #sql_46dc_36.MAD
-rw-rw---- 1 apps apps 774905856 Sep 29 20:48 #sql_46dc_68.MAD
-rw-rw---- 1 apps apps 16087908352 Sep 29 20:48 #sql_46dc_16.MAD
-rw-rw---- 1 apps apps 19005124608 Sep 29 20:48 #sql_46dc_4.MAD

检查mysql配置文件中确认/apps/tmp为mysql的临时目录：
vi my.cnf
tmpdir=/apps/tmp

是什么原因引起的呢？

mysql的临时目录是用于mysql分组和排序时产生临时文件的位置，基于这原因，需要找到是什么SQL引起的问题

show processlist查看到有多个会话在执行如下类似语句，会话状态为：Copying to tmp table

| 12010832 | glsuat          | 10.74.91.128:55390 | m_g_uat | Query   |    5215 | Copying to tmp table         | select
                gtyr.id,
                gsn.vir_container_number,
        gsn.delivery_order_number,
        gsn.shp_no |    0.000 |

从这个也证明了是有部分SQL执行大量分组或排序操作。

3.问题处理
测试环境mysql现在不能使用，如果能直接干掉所有状态为'Copying to tmp table'会话连接，就能让mysql恢复正常是最好的，执行下面SQL，将所有kill的会话信息保存到本地，然后执行。

dba@10.16.30.161:3306: (none) 09:18:43>select concat("kill ", id,";") from information_schema.processlist where user='guat' and state='Copying to tmp table' into outfile '/tmp/1.sql';
ERROR 1021 (HY000): Disk full (/apps/tmp/#sql_46dc_144.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")

同样提示磁盘空间不足。由于批量kill会话无法完成，先删除所有临时文件：

[apps@lvxl087 tmp]$ rm *
[apps@lvxl087 tmp]$ df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/mapper/vg_root-LogVol01
                       36G 5.7G   28G 18% /
tmpfs                 7.8G     0 7.8G   0% /dev/shm
/dev/sda1             190M   33M 148M 19% /boot
/dev/mapper/data-lvmdata1
                      200G 200G   20K 100% /apps
临时文件删除后，磁盘空间没有释放。

在允许的条件下，关闭mysql:
[apps@lvxl087 tmp]$ mysqladmin -u root -p shutdown
Enter password:

以下看到空间有释放：
[apps@lvxl087 tmp]$ df -h
Filesystem            Size Used Avail Use% Mounted on
/dev/mapper/vg_root-LogVol01
                       36G 5.7G   28G 18% /
tmpfs                 7.8G     0 7.8G   0% /dev/shm
/dev/sda1             190M   33M 148M 19% /boot
/dev/mapper/data-lvmdata1
                      200G   11G 190G   6% /apps

启动mysql：
/apps/svr/mariadb10.1/bin/mysqld_safe --defaults-file=/apps/conf/mysql/mariadb10.1_3306.cnf &

4 问题扩展
4.1 通过慢日志分析大量分组和排序性能较差的SQL

对慢日志分析，确认如下两支SQL排在TOP 前三位，且有大量group by操作。
[apps@lvxl087 mysql]$ /apps/svr/mariadb10.1/bin/mysqldumpslow -s c -t 10 slow3306.log_bak|more

Count: 18 Time=1547.17s (27849s) Lock=0.00s (0s) Rows_sent=0.8 (15), Rows_examined=34400.8 (619215), Rows_affected=0.0 (0), glsua
t[glsuat]@5hosts
select
count(N)
from gtyr
................

where N=N GROUP BY order_number) gsn on gsn.detail_id=goy.detail_id
WHERE N=N

Count: 12 Time=936.20s (11234s) Lock=0.00s (0s) Rows_sent=13.8 (165), Rows_examined=64423.4 (773081), Rows_affected=0.0 (0), glsu
at[guat]@2hosts
select
gtyr.id,
................

order by gst.req_no,goy.so_no
limit N , N

对其中一条SQL检查执行计划：

select
                gtyr.id,
   .................................;

+------+-------------+------------+--------+---------------------------------------+-------------------+---------+-----------------------------------------+-------+-------------------------------------------------+
| id   | select_type | table      | type   | possible_keys                         | key               | key_len | ref                                     | rows | Extra                                           |
+------+-------------+------------+--------+---------------------------------------+-------------------+---------+-----------------------------------------+-------+-------------------------------------------------+
|    1 | PRIMARY     | gtyr       | ALL    | NULL                                  | NULL              | NULL    | NULL                                    | 14983 | Using temporary; Using filesort                 |
|    1 | PRIMARY     | goy        | eq_ref | id                                    | id                | 152     | gtyr.ycon_id                            |     1 | Using where                                     |
|    1 | PRIMARY     | gst        | ref    | idx_gsst_task_no                      | idx_gsst_task_no | 195     | gtyr.sub_task_no                        |     1 | Using where                                     |
|    1 | PRIMARY     | gss        | ref    | unique_booking_no,idx_gsss_booking_no | unique_booking_no | 195     | gtyr.booking_no                         |     1 | Using where                                     |
|    1 | PRIMARY     | <derived2> | ref    | key0                                  | key0              | 5       | goy.booking_req_detail_id               |   259 | Using where                                     |
|    2 | DERIVED     | gssn       | ALL    | NULL                                  | NULL              | NULL    | NULL                                    |   585 | Using temporary; Using filesort                 |
|    2 | DERIVED     | gsnd       | ALL    | NULL                                  | NULL              | NULL    | NULL                                    | 2465 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+------------+--------+---------------------------------------+-------------------+---------+-----------------------------------------+-------+-------------------------------------------------+

从上面看，该SQL是6个表的左外连接，sql中只有连接条件，没有限制条件，，表gtyr、gssn和gsnd都是全表扫描，且gsnd和gtyr为Using temporary; Using filesort。
由于没有加任何限制条件，需要请开发人员修改SQL，增加条件来优化。
4.2 上面使用重启mysql的方法来释放磁盘空间，其实可以只要找出产生大量临时文件的会话，将其它杀掉就能释放磁盘空间。
4.3 与排序有关的参数调整可以减少磁盘临时文件产生
检查相关参数大小：
| tmp_table_size             | 134217728 |
| sort_buffer_size           | 8388608 |
根据OS总内存大小和会话数，还可将上述参数调大。
临时表：
        a．缺省存储引擎为MySQL服务器默认引擎，引擎类型只能是：memory（heap）、myisam、merge、innodb（memory临时表由于表的增大可能会转变为myisam临时表）
        b．可以通过参数 tmp_table_size 来设定临时表大小。
        c．到达tmp_table_size设定的内存上限后将在磁盘上创建临时文件
        d．表定义和数据都保存在内存中
        e．可以包含TEXT, BLOB等字段
临时表一般比较少用，通常是在应用程序中动态创建或者由MySQL内部根据SQL执行计划需要自己创建。
MySQL服务器使用内部临时表
        在某些情况下，mysql服务器会自动创建内部临时表。查看查询语句的执行计划，如果extra列显示“using temporary”即使用了内部临时表。内部临时表的创建条件：
        * group by 和 order by中的列不相同
        * order by的列不是引用from 表列表中的第一表
        * group by的列不是引用from 表列表中的第一表
        * 使用了sql_small_result选项
        * 含有distinct 的 order by语句
        初始创建内部myisam临时表的条件：
        * 表中存在text、blob列
        * 在group by中的列有超过512字节
        * 在distinct查询中的列有超过512字节
        * 在union、union all联合查询中，select 列列表中的列有超过512字节的

0 0