ibdata1の肥大化対策
お世話になります。
Zabbix2→3に移行して以来、MySQLのibdata1の肥大化が止まらないです。
■旧環境
CentOS6.5
MySQL5.1.73
Zabbix2.0.13
my.cnfの内容（一部抜粋）
[mysqld]
innodb_flush_log_at_trx_commit=1
innodb_file_per_table
innodb_buffer_pool_size=256M
innodb_log_file_size=128M
innodb_log_files_in_group=2
innodb_flush_method=O_DIRECT
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
■新環境
CentOS7.3
MySQL5.6.35
Zabbix3.2.4
my.cnfの内容（一部抜粋）
[mysqld]
innodb_flush_log_at_trx_commit=1
innodb_file_per_table
innodb_buffer_pool_size=2048MB
innodb_log_file_size=128M
innodb_log_files_in_group=2
innodb_flush_method=O_DIRECT
expire_logs_days=5
サーバのメモリ増加に伴い、buffer_pool_sizeを上げました。
監視アイテム数ほとんど増えていないので、innodb周りの設定で何かミスしたとしか思えません。
ignore-builtin-innodb
plugin-load
このあたりがなにか原因になっているのでしょうか…？
各テーブルのエンジンはInnoDBになっていることは確認できています。
よろしくお願いいたします。
fripper - 投稿数: 495
ibdata file に関する設定項目の設定値が異なっているのではないでしょうか？
この設定値を変更する場合は、既存ファイルを消してあげないとMySQLが起動しなくなるなど
色々と敷居が高いようなので、注意することをお薦めします
http://www.ilovex.co.jp/blog/system/projectandsystemdevelopment/mycnfinnodbmysql.html
# InnoDB stores data in one or more data files forming the tablespace.
# If you have a single logical drive for your data, a single
# autoextending file would be good enough. In other cases, a single file
# per device is often a good choice. You can configure InnoDB to use raw
# disk partitions as well - please refer to the manual for more info
# about this.
# innodb のデータ格納方法を指定する、デフォルトは自動拡張
# 10M 毎に自動拡張する
# innodb_data_file_path = ibdata1:10M:autoextend
# 300GB をあらかじめ割り当てる、自動拡張はしない
# innodb_data_file_path=ibdata1:300G
# テーブルスペースを異なるパスに分散する（商用DBでいうところのコンテナの概念に似ているのかな？）
# innodb_data_file_path=ibdata1:300G;/data2/ibdata2:700G
innodb_data_file_path = ibdata1:10M:autoextend
wakaba - 投稿数: 228
広瀬です
InnoDB テーブルスペースファイル(ibdataN)＜Nは数値です＞は起動前に前もって明示的に
指定しないと、先にfripperさんが明示されている通りデフォルトは自動拡張なので、どんどん
肥大化します＜アップデートしたのも一つ要因ですけどね
また、innodb_file_per_tableは後から指定しても何ら意味を持ちません。テーブル定義前に
指定が必要です(ibdataが増加して続けている以上、後から指定したとしか考えられない)
「file-per-table モードが有効な場合、InnoDB は、適切なデータベースディレクトリ内の独自の
tbl_name.ibd ファイルに、新しく作成された各テーブルを格納します。」
上記、MySQL公式サイトに記載の抜粋ですが、「新しく作成された各テーブル」と明記されてます。
Zabbix構築(SQLファイルを投入)した時点で無効だった場合、何らか意味を持たない事となります。
Alterとかでリネームすれば話は別なんですが、話が有らぬ方向に飛ぶので割愛
参考までに・・・私の場合ですが
◆設定の一部
innodb_data_file_path = ibdata1:512M;ibdata2:1G;ibdata3:2G:autoextend
innodb_data_home_dir = /var/lib/mysql/MYZBXDB/idata
innodb_file_per_table = 1
◆InnoDBテーブルスペースファイル
合計 3670028
-rw-r-----. 1 mysql mysql 536870912 6月 1 00:14 2017 ibdata1
-rw-r-----. 1 mysql mysql 1073741824 2月 19 23:32 2017 ibdata2
-rw-r-----. 1 mysql mysql 2147483648 2月 19 23:32 2017 ibdata3
ご覧の通りですが、DB生成したのは今年の2月19日なんですけど(ibdata2、及び3の日付から解る)、
ibdata1しか使われていない訳です。Zabbixのデータ本体は、innodb_file_per_tableで分離
させているので、他のディレクトリにあります。
念のため申し上げておきますが、上記は経験上と監視対象のシステム量を計算した上での
値なので、真似はされない事をおすすめします。規模に合わせて設計なさってください。
◆回避策
InnoDBテーブルスペースファイルは削除したら最後、復旧できなくなりますし、変更も出来ませんので、
フルダンプ取得の上、新たに定義しなおしたMySQL上にリストアするしか無いはずです。
※5.7から確か上記の問題が若干緩和されていたような記憶ありますが、5.6をお使いのようなので、
この辺は変化していなかったはずです
尚、単にリストアするだけではなく、MySQLの動き自体は、file_per_tableが有効に働く状態にある
事、またibdataの指定もMySQL起動前に前もって設定した状態にするなど、やることは結構盛りだくさん
です。また、メリット、デメリットもあるのでその辺も確認する必要ありです
余談：
MySQL、奥深いんですよね・・・しかも5.5以降メチャメチャな速度で変化していくので、追いつくのすら
大変・・・orz