mysql ddl 与临时文件

MySQL DDL与临时表

最新推荐文章于 2024-09-11 08:18:08 发布

原创最新推荐文章于 2024-09-11 08:18:08 发布 · 1.3k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#online ddl #tmpdir

Mysql 专栏收录该内容

220 篇文章

订阅专栏

本文对比测试了几种常见的MySQL DDL操作对于临时表的使用情况。非在线DDL操作不会在/tmp目录生成临时表，而是在表所在目录生成以#sql-为前缀的临时表。在线DDL操作不仅在表所在目录生成临时表，还在/tmp目录生成临时文件。

本篇博客主要通过测试的方式对比说明明几种常见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 -h
Filesystem            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.ibd

modify命令完成后查看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 -h
Filesystem            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 -h
Filesystem            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观察)