2017年07月28日

MySQL5.1から5.7へのアップデートに伴うディスク消費量の増加、innodb_file_per_tableの影響

MySQL5.1から5.5、5.6、5.7へのアップデート作業を行った際、ディスク消費量が増加していました。これは、MySQL5.6へのアップデートによってinnodb_file_per_tableがデフォルトでONに変更された影響でした。

1)MySQL5.1の段階

・innodb_file_per_tableの設定値はデフォルト(OFF)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+

# du --max-depth=1 -h -x /var/lib/mysql
・・・
988K /var/lib/mysql/mysql
270M /var/lib/mysql/testdb
・・・
1.5G /var/lib/mysql

# ls -al /var/lib/mysql
-rw-rw----. 1 mysql mysql 5242880 7月 25 14:00 2017 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 7月 25 14:00 2017 ib_logfile1
-rw-rw----. 1 mysql mysql 1134559232 7月 25 14:00 2017 ibdata1


2)MySQL5.5へアップデート後

・innodb_file_per_tableの設定値はデフォルト(OFF)
・ディスク容量は同じ。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF |
+-----------------------+-------+

# du --max-depth=1 -h -x /var/lib/mysql
・・・
988K /var/lib/mysql/mysql
270M /var/lib/mysql/testdb
・・・
1.5G /var/lib/mysql

# ls -al /var/lib/mysql
-rw-rw----. 1 mysql mysql 5242880 7月 25 14:15 2017 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 7月 25 14:15 2017 ib_logfile1
-rw-rw----. 1 mysql mysql 1134559232 7月 25 14:15 2017 ibdata1


3)5.5アップデート前のダンプ(add-drop-tableオプション付き)をインポート、mysql_upgrade実施後

・innodb_file_per_tableの設定値は5.1の場合と同様でOFFなので、ディスク容量はほとんど同じ。

# du --max-depth=1 -h -x /var/lib/mysql
・・・
1016K /var/lib/mysql/mysql
270M /var/lib/mysql/testdb
・・・
1.5G /var/lib/mysql

# ls -al /var/lib/mysql
-rw-rw----. 1 mysql mysql 5242880 7月 25 14:31 2017 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 7月 25 14:30 2017 ib_logfile1
-rw-rw----. 1 mysql mysql 1134559232 7月 25 14:31 2017 ibdata1


4)MySQL5.6アップデート後

・innodb_file_per_tableがデフォルトでONなので、アップデートによって変更されている。
・innodb_file_per_tableはONになったが、テーブルを作成していないので、ディスク容量は変わっていない。既存のテーブルには影響はない。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+

# du --max-depth=1 -h -x /var/lib/mysql
・・・
1016K /var/lib/mysql/mysql
270M /var/lib/mysql/testdb
・・・
1.5G /var/lib/mysql

# ls -al /var/lib/mysql
-rw-rw----. 1 mysql mysql 50331648 7月 25 14:44 2017 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 7月 25 14:43 2017 ib_logfile1
-rw-rw----. 1 mysql mysql 1134559232 7月 25 14:44 2017 ibdata1


5)5.6アップデート前のダンプ(add-drop-tableオプション付き)をインポート、mysql_upgrade実施後

・ダンプをインポートする際にテーブルを削除して再作成されているので、innodb_file_per_tableの機能によって、各テーブル毎に.ibdファイルが作成されたので、ディスク容量が増加。
・システムファイルスペース(ibdata1)内の各テーブルデータは各.ibdファイルに移行されて未利用領域は増加しているが、OSから見えるファイルサイズは縮小されない。


# du --max-depth=1 -h -x /var/lib/mysql
・・・
1.7M /var/lib/mysql/mysql
1.8G /var/lib/mysql/testdb
・・・
3.1G /var/lib/mysql

# ls -al /var/lib/mysql
-rw-rw----. 1 mysql mysql 50331648 7月 25 15:15 2017 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 7月 25 15:03 2017 ib_logfile1
-rw-rw----. 1 mysql mysql 1134559232 7月 25 15:15 2017 ibdata1


6)5.7アップデート後の再起動後

・ib_buffer_poolファイルが作成されている。(5.6のときからあったかも)
innodb_buffer_pool_filenameのデフォルト:ib_buffer_pool

・ibtmp1ファイルが作成されている。
innodb_temp_data_file_pathのデフォルト:ibtmp1:12M:autoextend

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html

# du --max-depth=1 -h -x /var/lib/mysql
・・・
1.7M /var/lib/mysql/mysql
1.8G /var/lib/mysql/testdb
・・・
3.1G /var/lib/mysql

# ls -al /var/lib/mysql
-rw-r-----. 1 mysql mysql 811 7月 26 13:21 2017 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 7月 26 13:21 2017 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 7月 26 13:20 2017 ib_logfile1
-rw-rw----. 1 mysql mysql 1134559232 7月 26 13:21 2017 ibdata1
-rw-r-----. 1 mysql mysql 12582912 7月 26 13:21 2017 ibtmp1


7)5.7アップデート前のダンプ(add-drop-tableオプション付き)をインポート、mysql_upgrade実施後

・"mysql"データベースのディレクトリの容量が増加している。


# du --max-depth=1 -h -x /var/lib/mysql
・・・
12M /var/lib/mysql/mysql
1.8G /var/lib/mysql/testdb
・・・
3.1G /var/lib/mysql

# ls -al /var/lib/mysql
-rw-r-----. 1 mysql mysql 13034 7月 26 13:49 2017 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 7月 26 13:50 2017 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 7月 26 13:45 2017 ib_logfile1
-rw-rw----. 1 mysql mysql 1134559232 7月 26 13:49 2017 ibdata1
-rw-r-----. 1 mysql mysql 12582912 7月 26 13:50 2017 ibtmp1
posted by ほのらー at 09:45| Comment(0) | MySQL | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント: