MySQL :: MySQL 5.7 Reference Manual :: 8.4.4 How MySQL Uses Internal Temporary Tables
MySQL :: MySQL 5.7 Reference Manual :: 5.1.4 Server System Variables
テンポラリーテーブル用(こっちはCREATE TEMPORARY TABLEで作るやつも含んだ言い方だと思いねえ)のテーブルスペースはREDOロギングを無効化してあったりといいこともたくさんあるんだけれど、mysqldを再起動しない限りibtmp1ファイルは決して小さくならない。
こんなエラーを見たことはないだろうか。俺はある。設定が古いサーバーだと特によくある。
mysql57> SELECT @@internal_tmp_disk_storage_engine; +------------------------------------+ | @@internal_tmp_disk_storage_engine | +------------------------------------+ | MYISAM | +------------------------------------+ 1 row in set (0.00 sec) mysql57> SELECT * FROM (SELECT * FROM t1 WHERE val LIKE 'abc%') AS t1, (SELECT * FROM t1 WHERE val LIKE 'def%') AS t2 ORDER BY t1.num; ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_2974_0.MYI'; try to repair it $ tail error.log .. 2015-04-30T14:59:45.558360+09:00 87 [ERROR] /usr/mysql/5.7.7/bin/mysqld: Incorrect key file for table '/tmp/#sql_2974_0.MYI'; try to repair it 2015-04-30T14:59:45.573411+09:00 87 [ERROR] Got an error from unknown thread, /home/yoku0825/mysql-5.7.7-rc/storage/myisam/mi_write.c:223
暗黙のテンポラリーテーブルがtmpdirに作られて、容量が足りなくなるとこうなる。
$ ll /tmp
合計 792816
-rw-r----- 1 yoku0825 yoku0825 402935808 4月 30 14:59 2015 #sql_2974_0.MYD
-rw-r----- 1 yoku0825 yoku0825 1024 4月 30 14:59 2015 #sql_2974_0.MYI
-rw-rw-r-- 1 yoku0825 yoku0825 408888897 4月 30 14:45 2015 md5
drwx------ 2 yoku0825 tmux 4096 4月 17 15:45 2015 tmux-3012
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_00-lv_root
42G 40G 33M 100% /
暗黙のテンポラリーテーブルがDiskを食いきってクエリーがアボートされるとストレージ容量は元に戻る。
internal_tmp_disk_storage_engine= MyISAMのとき(MySQL 5.7.5とそれ以前)はこんな動作だった。
ところがInnoDBになるとibtmp1にこのデータを書くし、ibtmp1はmysqldの再起動まで容量が空かないので、
mysql57> SELECT @@internal_tmp_disk_storage_engine; +------------------------------------+ | @@internal_tmp_disk_storage_engine | +------------------------------------+ | INNODB | +------------------------------------+ 1 row in set (0.03 sec) mysql57> SELECT * FROM (SELECT * FROM t1 WHERE val LIKE 'abc%') AS t1, (SELECT * FROM t1 WHERE val LIKE 'def%') AS t2 ORDER BY t1.num; ERROR 1114 (HY000): The table '/tmp/#sql_2974_0' is full $ tail error.log .. 2015-04-30T15:04:22.549484+09:00 87 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./ibtmp1, desired size 67108864 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html 2015-04-30T15:04:22.590070+09:00 87 [Warning] InnoDB: 1048576 bytes should have been written. Only 794624 bytes written. Retrying again to write the rem
クエリーがアボートしても容量は開放されていない。
$ ll /usr/mysql/5.7.7/data/ibtmp1
-rw-r----- 1 yoku0825 yoku0825 485236736 4月 30 15:04 2015 /usr/mysql/5.7.7/data/ibtmp1
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_00-lv_root
42G 40G 17M 100% /
ベンチマーク取りながらうっかり100%突っ込んだ人とかならわかると思うんだけれど、DiskがFullな状態で更新かけるとbinlogに書き込もうとしたときにそこで詰まって
mysql57> INSERT INTO t1 VALUES (0, 'test');
何も言わずにmysqldは応答を返さない。
(gdb) bt #0 0x00000038862aca3d in nanosleep () at ../sysdeps/unix/syscall-template.S:82 #1 0x00000038862ac8b0 in __sleep (seconds=0) at ../sysdeps/unix/sysv/linux/sleep.c:138 #2 0x0000000000e9a111 in wait_for_free_space (filename=0x7f3d6f428690 "./bin.000002", errors=) at /home/yoku0825/mysql-5.7.7-rc/mysys/errors.c:85 #3 0x0000000000e9147c in my_write (Filedes=19, Buffer=0x7f3d5772e0c2 "", Count=108, MyFlags=52) at /home/yoku0825/mysql-5.7.7-rc/mysys/my_write.c:90 #4 0x0000000000e7f579 in inline_mysql_file_write (info=0x1cfc0c8, need_append_buffer_lock= ) at /home/yoku0825/mysql-5.7.7-rc/include/mysql/psi/mysql_file.h:1136 #5 my_b_flush_io_cache (info=0x1cfc0c8, need_append_buffer_lock= ) at /home/yoku0825/mysql-5.7.7-rc/mysys/mf_iocache.c:1537 #6 0x0000000000e2a317 in MYSQL_BIN_LOG::flush_cache_to_file (this=0x1cfbdc0, end_pos_var=0x7f3d6fda9520) at /home/yoku0825/mysql-5.7.7-rc/sql/binlog.cc:8187 #7 0x0000000000e39a14 in MYSQL_BIN_LOG::ordered_commit (this=0x1cfbdc0, thd=0x7f3d55ffa000, all= , skip_commit= ) at /home/yoku0825/mysql-5.7.7-rc/sql/binlog.cc:8467 #8 0x0000000000e39c62 in MYSQL_BIN_LOG::write_binlog_and_commit_engine (this=0x1cfbdc0, thd=0x7f3d55ffa000, all= ) at /home/yoku0825/mysql-5.7.7-rc/sql/binlog.cc:7858 #9 0x0000000000e3a1d4 in MYSQL_BIN_LOG::commit (this= , thd=0x7f3d55ffa000, all= ) at /home/yoku0825/mysql-5.7.7-rc/sql/binlog.cc:7643 #10 0x000000000079dc60 in ha_commit_trans (thd=0x7f3d55ffa000, all= , ignore_global_read_lock= ) at /home/yoku0825/mysql-5.7.7-rc/sql/handler.cc:1615 #11 0x0000000000d1c0fe in trans_commit_stmt (thd= ) at /home/yoku0825/mysql-5.7.7-rc/sql/transaction.cc:395 #12 0x0000000000c687ec in mysql_execute_command (thd=0x7f3d55ffa000) at /home/yoku0825/mysql-5.7.7-rc/sql/sql_parse.cc:4591 #13 0x0000000000c6acb0 in mysql_parse (thd=0x7f3d55ffa000, parser_state= ) at /home/yoku0825/mysql-5.7.7-rc/sql/sql_parse.cc:5159 #14 0x0000000000c6c1c6 in dispatch_command (command=COM_QUERY, thd=0x7f3d55ffa000, packet=0x7f3d55c1b011 "INSERT INTO t1 VALUES (0, 'test')", packet_length=33) at /home/yoku0825/mysql-5.7.7-rc/sql/sql_parse.cc:1249 #15 0x0000000000d3a1dc in handle_connection (arg= ) at /home/yoku0825/mysql-5.7.7-rc/sql/conn_handler/connection_handler_per_thread.cc:298 #16 0x0000000000f066b7 in pfs_spawn_thread (arg=0x7f3d693a2210) at /home/yoku0825/mysql-5.7.7-rc/storage/perfschema/pfs.cc:2147 #17 0x00000038866079d1 in start_thread (arg=0x7f3d6fdac700) at pthread_create.c:301 #18 0x00000038862e88fd in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:115 $ tail error.log 2015-04-30T15:12:50.592458+09:00 87 [ERROR] Disk is full writing './bin.000002' (Errcode: 28 - No space left on device). Waiting for someone to free space... 2015-04-30T15:12:50.592539+09:00 87 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2015-04-30T15:22:50.705673+09:00 87 [ERROR] Disk is full writing './bin.000002' (Errcode: 28 - No space left on device). Waiting for someone to free space... 2015-04-30T15:22:50.705730+09:00 87 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2015-04-30T15:32:50.831917+09:00 87 [ERROR] Disk is full writing './bin.000002' (Errcode: 28 - No space left on device). Waiting for someone to free space... 2015-04-30T15:32:50.831991+09:00 87 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2015-04-30T15:42:50.963577+09:00 87 [ERROR] Disk is full writing './bin.000002' (Errcode: 28 - No space left on device). Waiting for someone to free space... 2015-04-30T15:42:50.963636+09:00 87 [ERROR] Retry in 60 secs. Message reprinted in 600 secs 2015-04-30T15:52:51.098086+09:00 87 [ERROR] Disk is full writing './bin.000002' (Errcode: 28 - No space left on device). Waiting for someone to free space... 2015-04-30T15:52:51.098142+09:00 87 [ERROR] Retry in 60 secs. Message reprinted in 600 secs
ああ、
デフォルトのまま使うと間違ってでっかい暗黙のテンポラリーテーブル作るクエリー投げちゃった、てへ☆ が笑い事でなくなるので、
innodb_tmp_data_file_pathをautoextendじゃない形でサイズを決め打っておくか(innodb_data_file_pathと同じノリで記述できる。暗黙のデフォルトはibtmp1:12M:autoextend。コールドパラメーターなのでMySQLの再起動が必要) または internal_tmp_disk_storage_engineをMyISAMにセットしておくか（こっちはオンラインでOK）
秘伝のmy.cnfにinnodb_tmp_data_file_pathを足しておくか。。
【2020/06/19 14:53】
この記事はMySQL 5.7を対象としたもので、MySQL 8.0.13とそれ以降ではこれは発生しないっぽくなっている（瞬間的に容量を食うのは一緒だが、mysqldを再起動しなくても容量が解放できるようになっている）
日々の覚書: MySQL 8.0.13とそれ以降ではibtmp1は肥大化しない（あるいは、 /var/lib/mysql/#innodb_temp ディレクトリの正体）
0 件のコメント :
コメントを投稿