mysql ddl 与临时文件

来源：互联网发布：mac安装win10出现错误编辑：程序博客网时间：2024/05/18 03:38

本篇博客主要通过测试的方式对比说明明几种常见mysql ddl操作对临时表的使用情况(所有测试都是基于mysql 5.6.26版本)。测试将分为两个大部分，第一部分测试非 online ddl对临时表的使用情况，第二部分测试几种online ddl对临时表的使用

一：非online ddl

1. modify column

1.1 执行前后tmpdir空间使用情况对比

执行 modify column命令前mysqld    15163     mysql    5u      REG              253,0           0    1705027 /tmp/ibyZaEuD (deleted)mysqld    15163     mysql    6u      REG              253,0           0    1709281 /tmp/ibvS2yru (deleted)mysqld    15163     mysql    7u      REG              253,0           0    1709283 /tmp/ibwFauol (deleted)mysqld    15163     mysql    8u      REG              253,0           0    1711007 /tmp/ibLzqVO4 (deleted)mysqld    15163     mysql   13u      REG              253,0           0    1711035 /tmp/ibphDN2X (deleted)执行后(之前前后没有变化，/tmp 目录下也没有观察到生产新的临时文件，说明非online ddl没有使用tmpdri目录)mysqld    15163     mysql    5u      REG              253,0           0    1705027 /tmp/ibyZaEuD (deleted)mysqld    15163     mysql    6u      REG              253,0           0    1709281 /tmp/ibvS2yru (deleted)mysqld    15163     mysql    7u      REG              253,0           0    1709283 /tmp/ibwFauol (deleted)mysqld    15163     mysql    8u      REG              253,0           0    1711007 /tmp/ibLzqVO4 (deleted)mysqld    15163     mysql   13u      REG              253,0           0    1711035 /tmp/ibphDN2X (deleted)

1.2 命令执行前后df -h情况对比

df -hFilesystem            Size  Used Avail Use% Mounted on/dev/mapper/VolGroup-LogVol01                       34G  6.9G   25G  22% /tmpfs                 1.9G   12K  1.9G   1% /dev/shm/dev/sda1             190M   33M  148M  19% /boot...执行modify column后，命令未结束之前test_s所在目录(/data)空间使用率一直在增长

1.3 命令执行前后test_s表所在目录文件情况

执行前-rw-rw---- 1 mysql mysql       25918 Jan 23 10:45 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 23 11:32 test_s.ibd执行中表所在目录下查看发现(其中#sql-3b3b_2.ibd文件大小一直在增加)-rw-rw---- 1 mysql mysql       25918 Jan 23 10:45 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 23 11:32 test_s.ibd-rw-rw---- 1 mysql mysql       25918 Jan 23 14:08 #sql-3b3b_2.frm-rw-rw---- 1 mysql mysql  6194987008 Jan 23 14:20 #sql-3b3b_2.ibd在命令接近完成时连续多次执行ls -lrt发现-rw-rw---- 1 mysql mysql       25918 Jan 23 10:45 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 23 11:32 test_s.ibd-rw-rw---- 1 mysql mysql       25918 Jan 23 14:08 #sql-3b3b_2.frm-rw-rw---- 1 mysql mysql 15179186176 Jan 23 14:44 #sql-3b3b_2.ibd  >>命令完成后test_s.ibd大小为15179186176......-rw-rw---- 1 mysql mysql 14331936768 Jan 23 11:32 #sql2-3b3b-2.ibd >>注意此时应该是在进行rename操作-rw-rw---- 1 mysql mysql       25918 Jan 23 14:08 test_s.frm-rw-rw---- 1 mysql mysql 15179186176 Jan 23 14:44 test_s.ibdmodify命令完成后查看test_s表所在目录-rw-rw---- 1 mysql mysql       25918 Jan 23 14:08 test_s.frm-rw-rw---- 1 mysql mysql 15179186176 Jan 23 14:45 test_s.ibd  >>test_s.ibd大小变为15179186176

##总结：mysql执行非online ddl时不会在tmpdir目录下生成临时表，而是在表所在目录生成以两个“#sql-”为前缀的临时表(一个frm一个ibd)，并且这两个临时表是可以通过ls 命令查看到的，非online ddl所需的临时空间大小大致为所操作表的大小。

二：online ddl

1. add column

1.1 执行前后tmpdir空间使用情况对比

执行前mysqld    15163     mysql    5u      REG              253,0           0    1705027 /tmp/ibyZaEuD (deleted)mysqld    15163     mysql    6u      REG              253,0           0    1709281 /tmp/ibvS2yru (deleted)mysqld    15163     mysql    7u      REG              253,0           0    1709283 /tmp/ibwFauol (deleted)mysqld    15163     mysql    8u      REG              253,0           0    1711007 /tmp/ibLzqVO4 (deleted)mysqld    15163     mysql   13u      REG              253,0           0    1711035 /tmp/ibphDN2X (deleted)执行中(比执行前/tmp目录下多出几个被标记为deleted的，并且大小一直在增大)mysqld    15163     mysql    5u      REG              253,0           0    1705027 /tmp/ibyZaEuD (deleted)mysqld    15163     mysql    6u      REG              253,0           0    1709281 /tmp/ibvS2yru (deleted)mysqld    15163     mysql    7u      REG              253,0           0    1709283 /tmp/ibwFauol (deleted)mysqld    15163     mysql    8u      REG              253,0           0    1711007 /tmp/ibLzqVO4 (deleted)mysqld    15163     mysql   13u      REG              253,0           0    1711035 /tmp/ibphDN2X (deleted)mysqld    15163     mysql   21u      REG              253,0           0    1711046 /tmp/ibgMEUXQ (deleted)mysqld    15163     mysql   22u      REG              253,0  6137315328    1721136 /tmp/iby9PKuZ (deleted)mysqld    15163     mysql   23u      REG              253,0   360710144    1721137 /tmp/ibxuuB17 (deleted)mysqld    15163     mysql   24u      REG              253,0   128974848    1721139 /tmp/ibvZmsyg (deleted)mysqld    15163     mysql   25u      REG              253,0   115343360    1721151 /tmp/ibBKuj5o (deleted)mysqld    15163     mysql   26u      REG              253,0           0    1721152 /tmp/ibF8QaCx (deleted)在命令执行6分钟后大小定格在(此时df -h目录使用率也不在变化)mysqld    15163     mysql    5u      REG              253,0           0    1705027 /tmp/ibyZaEuD (deleted)mysqld    15163     mysql    6u      REG              253,0           0    1709281 /tmp/ibvS2yru (deleted)mysqld    15163     mysql    7u      REG              253,0           0    1709283 /tmp/ibwFauol (deleted)mysqld    15163     mysql    8u      REG              253,0           0    1711007 /tmp/ibLzqVO4 (deleted)mysqld    15163     mysql   13u      REG              253,0           0    1711035 /tmp/ibphDN2X (deleted)mysqld    15163     mysql   21u      REG              253,0           0    1711046 /tmp/ibgMEUXQ (deleted)mysqld    15163     mysql   22u      REG              253,0 11622416384    1721136 /tmp/iby9PKuZ (deleted)mysqld    15163     mysql   23u      REG              253,0   662700032    1721137 /tmp/ibxuuB17 (deleted)mysqld    15163     mysql   24u      REG              253,0   235929600    1721139 /tmp/ibvZmsyg (deleted)mysqld    15163     mysql   25u      REG              253,0   184549376    1721151 /tmp/ibBKuj5o (deleted)mysqld    15163     mysql   26u      REG              253,0 11622416384    1721152 /tmp/ibF8QaCx (deleted)

1.2 命令执行前后df -h情况对比

执行前  df -hFilesystem            Size  Used Avail Use% Mounted on/dev/mapper/VolGroup-LogVol01                       34G  6.9G   25G  22% /tmpfs                 1.9G   12K  1.9G   1% /dev/shm/dev/sda1             190M   33M  148M  19% /boot/dev/mapper/data_vg-data_lv                      296G  180G  102G  64% /data执行中(/data/使用率没有变化，根目录使用率一直上升,最后定格在)df -hFilesystem            Size  Used Avail Use% Mounted on/dev/mapper/VolGroup-LogVol01                       34G   30G  2.0G  94% /tmpfs                 1.9G   12K  1.9G   1% /dev/shm/dev/sda1             190M   33M  148M  19% /boot/dev/mapper/data_vg-data_lv                      296G  180G  102G  64% /data

1.3 命令执行前后test_s表所在目录文件情况

命令执行前后对比test_s表所在目录文件执行前-rw-rw---- 1 mysql mysql       25918 Jan 23 16:28 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 23 17:08 test_s.ibd执行中多出1个以"#sql-"开头的frm临时文件，一个以#sql-ib开头的ibd临时文件，但是这两个临时文件大小一直不变(如果此时没有其他队test_s表的操作，test_s.frm和test_s.ibd文件大小也是一直不变的)-rw-rw---- 1 mysql mysql       25918 Jan 23 16:28 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 23 17:08 test_s.ibd-rw-rw---- 1 mysql mysql       25972 Jan 24 08:58 #sql-3b3b_c.frm-rw-rw---- 1 mysql mysql      147456 Jan 24 08:58 #sql-ib113-483112083.ibd......当/tmp下临时文件大小稳定不变一段时间后，我们发现test_s目录下#sql-ib113-483112083.ibd文件渐渐增长-rw-rw---- 1 mysql mysql       25918 Jan 23 16:28 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 23 17:08 test_s.ibd-rw-rw---- 1 mysql mysql       25972 Jan 24 08:58 #sql-3b3b_c.frm-rw-rw---- 1 mysql mysql  3124756480 Jan 24 09:25 #sql-ib113-483112083.ibd接近完成时-rw-rw---- 1 mysql mysql       25918 Jan 23 16:28 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 23 17:08 test_s.ibd-rw-rw---- 1 mysql mysql       25972 Jan 24 08:58 #sql-3b3b_c.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 24 09:40 #sql-ib113-483112083.ibd  >>注意新的ibd文件一般不会同旧的大小一致，这里只是巧合-rw-rw---- 1 mysql mysql       25918 Jan 23 16:28 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 23 17:08 #sql-ib114-483112084.ibd  >>这时在进行rename操作了-rw-rw---- 1 mysql mysql       25972 Jan 24 08:58 #sql-3b3b_c.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 24 09:40 test_s.ibd...-rw-rw---- 1 mysql mysql       25918 Jan 23 16:28 test_s.frm-rw-rw---- 1 mysql mysql       25972 Jan 24 08:58 #sql-3b3b_c.frm  >>这时临时的ibd文件已经不存在-rw-rw---- 1 mysql mysql 14331936768 Jan 24 09:40 test_s.ibd...-rw-rw---- 1 mysql mysql       25972 Jan 24 08:58 test_s.frm-rw-rw---- 1 mysql mysql 14331936768 Jan 24 09:40 test_s.ibd  >>此时add column这个online ddl动作已经完成

##总结，在进行online ddl操作时，会在表所在数据库目录下生成#sql中间表(同非online ddl不一样的是ibd中间表前缀为#sql-ib)，同时也会在tmpdir目录下生成临时文件(但是只能通过lsof命令观察)。在进行add column，optimize table等online ddl时，在tmpdir目录下，大致需要两倍表大小的空间。在表所在数据库下大概需要一倍表大小的空间

2. add index

2.1 执行前后tmpdir空间使用情况对比

2.2 命令执行前后df -h情况对比

2.3 命令执行前后test_s表所在目录文件情况

##目前发现 mysql中online ddl操作和order by操作(内存中无法完成的排序)都会在tmpdir目录下生成临时文件(losf观察)

0 0