2012年11月11日日曜日

テーブルスペース肥大化の対処方法(ibdata*)

前回テーブルスペースについて調べてみた後に、
運用中のサーバーの設定をやファイルを確認してみたら・・・innodb_file_per_tableが使われていない!!!:(;゙゚'ω゚'):

という事なので、早速設定の方を試してみましたワッショイヽ(゚∀゚)メ(゚∀゚)メ(゚∀゚)ノワッショイ

■手順1.現状のibdata1のサイズの確認をしてみる
# ls -alt /var/lib/mysql/|grep ib
-rw-rw----  1 mysql mysql 277899902976 11月 10 16:16 2012 ibdata1
-rw-rw----  1 mysql mysql      5242880  4月 16 15:06 2012 ib_logfile0
-rw-rw----  1 mysql mysql      5242880  4月  6 12:05 2012 ib_logfile1

277899902976・・・(;゜ Д゜) …!?
277,899,902,976byteっていくつでしたっけ?もちろん277MBですよね?(;゚ Д゚) …!?

チガイマスネ。277GBですね・・・orz

とりあえず、my.cnfにinnodb_file_per_tableだけを追加してやってみた結果。
#設定前のサイズ
# ls -alt /var/lib/mysql/|grep ib
-rw-rw----  1 mysql mysql 277899902976 Nov 10 21:08 ibdata1
-rw-rw----  1 mysql mysql      5242880 Apr 16  2012 ib_logfile0
-rw-rw----  1 mysql mysql      5242880 Apr  6  2012 ib_logfile1
設定直後には/var/lib/mysq/データベース名/に*.ibdファイルが作られていないが、
ALTER TABLE テーブル名 ENGINE InnoDB;

のコマンドを実行すると、ibdファイルが作成される。
# ls -alt /var/lib/mysql/データベース名/|grep ibd
-rw-rw----  1 mysql mysql     163840 Nov 10 20:19 user.ibd
こちらから引用です。
ALTER TABLE 実行中もテーブルからREADが出来るからだ。
テーブルが壊れた場合にはREPAIR TABLEコマンドを使うし、最適化したい場合にはOPTIMIZE TABLEコマンドを使うのだが、
これらのコマンドはWRITEだけでなくREADもブロックしてしまう。従って、メンテナンス中にWRITEは出来なくてもREADだけは可能にしたい、
というような場合には、まずはALTER TABLEを試して見るといいだろう。

ALTER TABLEは操作が正常に完了するまでは元のテーブルに対して一切の変更を加えない。
たとえコマンド実行中にマシンがクラッシュしても、テンポラリテーブルの残骸が残るだけで、元のテーブルは元通りである。 そういう意味では、ALTER TABLEはとても安全な操作であるとも言える。
innodb_file_per_table設定後に作られたこのファイルは
ALTERコマンドを利用するとデフラグを行ってくれるため、
定期的に実行することで*.ibdのサイズは小さくなる。(ibdata1のサイズは変わらない)
# ls -alt /var/lib/mysql/
total 271977488
-rw-rw----  1 mysql mysql 277899902976 Nov 10 22:14 ibdata1
なので、元々あったibdataのサイズは変わらない・・・ガ━━(;゚Д゚)━━ン!!

そこで、ibdata1のサイズを小さくしたい場合には、
ibdata1のサイズを変更した場合には元々あったibdataやib_logfile0を一度消す必要がある。

消すという事は、全データを消すことになるので、
復元する為にデータベースをダンプし、リストアを行う必要があるみたいです。

単純に、my.cnfの設定で、値を変えた場合のエラーは次のような内容でした。
#デフォルトの設定(自動拡張有効で、初期化で10MBの領域を確保する)
innodb_data_file_path = ibdata1:10M:autoextend
↓
#自動拡張有効で、初期化で30Gの領域を確保する
#innodb_data_file_path = ibdata1:30G:autoextend

#エラー内容
121110 21:03:24 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
121110 21:03:24 [Note] Plugin 'FEDERATED' is disabled.
121110 21:03:24 [ERROR] Function 'rpl_semi_sync_master' already exists
121110 21:03:24 [Warning] Couldn't load plugin named 'rpl_semi_sync_master' with soname 'semisync_master.so'.
121110 21:03:24 InnoDB: The InnoDB memory heap is disabled
121110 21:03:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins
121110 21:03:24 InnoDB: Compressed tables use zlib 1.2.3
121110 21:03:24 InnoDB: Using Linux native AIO
121110 21:03:24 InnoDB: Initializing buffer pool, size = 4.0G
121110 21:03:24 InnoDB: Completed initialization of buffer pool
InnoDB: Error: auto-extending data file ./ibdata1 is of a different size
InnoDB: 1152 pages (rounded down to MB) than specified in the .cnf file:
InnoDB: initial 1966080 pages, max 0 (relevant if non-zero) pages!
121110 21:03:24 InnoDB: Could not open or create data files.
121110 21:03:24 InnoDB: If you tried to add new data files, and it failed here,
121110 21:03:24 InnoDB: you should now edit innodb_data_file_path in my.cnf back
121110 21:03:24 InnoDB: to what it was, and remove the new ibdata files InnoDB created
121110 21:03:24 InnoDB: in this failed attempt. InnoDB only wrote those files full of
121110 21:03:24 InnoDB: zeros, but did not yet use them in any way. But be careful: do not
121110 21:03:24 InnoDB: remove old data files which contain your precious data!
121110 21:03:24 [ERROR] Plugin 'InnoDB' init function returned error.
121110 21:03:24 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
121110 21:03:24 [Note] Semi-sync replication initialized for transactions.
121110 21:03:24 [Note] Semi-sync replication enabled on the master.
121110 21:03:24 [ERROR] Unknown/unsupported storage engine: InnoDB
121110 21:03:24 [ERROR] Aborting

121110 21:03:24 [Note] unregister_replicator OK
121110 21:03:24 [Note] /usr/libexec/mysqld: Shutdown complete

121110 21:03:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

それでは、本題のibdataをリセットしてみます(゚д゚)(。_。)(゚д゚)(。_。) ウンウン

今回は、レプリケーションを既に行っているmaster,slave環境で、
ibdataをリセットしたかったので、レプリケーションの設定を再度やり直す方法にしました。

■手順1.念のためmasterの更新系の処理をロックする(参照系はロックされない)
# FLUSH TABLES WITH READ LOCK;
■手順2.masterのロックとテーブルスペースへの反映が正常に完了しているか確認
こちらから引用。
Log sequence numberは、ログバッファへの更新が行われたトータルのバイト数、
Log flushed up toはWALへの書き込みが行われたバイト数、
Last checkpoint atは最後にチェックポイントが行われたバイト数
innodb_flush_log_at_trx_commit=1ならば、Log sequence numberとLog flushed up toは非常に近い値になる。
ログからテーブルスペースへ書き込み中の可能性もあるので確認する
SHOW ENGINE INNODB STATUSの"Log sequence number"と"Log flushed up to"の値が同じであることを確認する。
#5.5より前のバージョンはSHOW INNODB STATUS\G
SHOW ENGINE INNODB STATUS\G
---
LOG
---
Log sequence number 694320234293
Log flushed up to   694320234293
Last checkpoint at  694320234293
0 pending log writes, 0 pending chkp writes
11390094 log i/o's done, 0.00 log i/o's/second
mysql> show status LIKE '%Key_blocks_not_flushed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
+------------------------+-------+
1 row in set (0.00 sec)
値が一致しない場合、または念のために、
ログファイル内のデータを全てテーブルスペースへ反映させる方法でシャットダウンしておいた方が良いかも?
#
mysql> SELECT @@global.innodb_fast_shutdown;
+-------------------------------+
| @@global.innodb_fast_shutdown |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

#ログファイル内のデータを全てテーブルスペースへ反映させてからシャットダウンする設定に変更
mysql> SET GLOBAL innodb_fast_shutdown=0;

#停止
/etc/init.d/mysqld stop

#起動
/etc/init.d/mysqld start
■手順3.masterサーバーのデータをダンプする
--all-database 全てのデータベースのからデータをdumpする --master-data=2をつけておく。
slaveのセットアップで利用できるバイナリーログのファイル名と開始位置を出力する。 --default-character-set=binary
--single-transaction
InnoDBを利用している場合、テーブルへの参照&更新をブロックすることなく、
mysqldumpコマンド開始時点のスナップショットを取る。(MyISAMなどでは利用できない)
--flush-logs
バイナリーログのローテーションを行う
--quick
#全てのデータベースをダンプする場合
# mysqldump -u root -p --all-database --master-data=2 --default-character-set=binary --single-transaction --flush-logs --quick | gzip --best -c > /data/tmp/all_database_20121111.sql.gz
■手順4.データベースの削除 ダンプでフルバックアップを取ってあるので、DROP DATABASEで全部消しておいた方が良いかも。
消す場合には、mysqlがデフォルトで作成するデータベースは残しておく。
(information_schema、performance、mysql、test)

DROP DATABASEをせずに、手順9のibdata*,ib_logfile*をmvまたはrmでした場合に、
全データは無くなるが、データベースのディレクトリが残ってしまい、リストアがうまく行かない場合がある。 各データベースのdatabaseのディレクトリが残ってしまっている。(*.ibdが残っていた)

そのせいかリストアした際に、次のようなエラーが出てしまった。
ERROR 1005 (HY000) at line 39: Can't create table 'データベース名.access_log' (errno: 1)
テーブルがあるせいなのかと思い、SHOW TABLEで確認してみるけど、
全て消えてしまって無くなっていました。

SHOW DATABASESでは名前があったのでDROP DATABASEを試してみるけど、 次のようなエラーが発生で消せませんでした。
mysql> DROP DATABASE データベース名;
ERROR 1010 (HY000): Error dropping database (can't rmdir './データベース名', errno: 39)
仕方ないので強引に/var/lib/mysql/データベース名/のディレクトリを
全部mvで退避させてから実行してみると問題なくリストアが進みました!(`・ω・´)シャキーン ■手順5.masterの更新系のロック解除とMySQLの停止
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
■手順6.slave側のテーブルスペースへの反映の確認
#5.5より前のバージョンはSHOW INNODB STATUS\G
SHOW ENGINE INNODB STATUS\G
---
LOG
---
Log sequence number 672811663077
Log flushed up to   672811663077
Last checkpoint at  672811663077
0 pending log writes, 0 pending chkp writes
9667 log i/o's done, 0.00 log i/o's/second

mysql> show status LIKE '%Key_blocks_not_flushed%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
+------------------------+-------+
1 row in set (0.05 sec)
masterと同様に値が一致しない場合には、手順2の方法でSET GLOBAL innodb_fast_shutdown=0;を利用する。
■手順7.slaveの停止
slaveの停止
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
■手順8.master側のmy.cnfの設定を変更
# vi /etc/my.cnf

#デフォルト値
innodb_data_file_path = ibdata1:10M:autoextend
#拡張時に64MBの領域を確保する
innodb_autoextend_increment=64
#テーブルスペースをテーブル単位で作成する
innodb_file_per_table
■手順9.ibdata*,ib_logfile*を退避させる
# mv /var/lib/mysql/ibdata* /tmp/
# mv /var/lib/mysql/ib_logfile* /tmp/
※innodb_log_group_home_dirを設定している場合には、ib_logfileはその場所にあります。
デフォルトではdatadirで設定した場所になります(datadirのデフォルト設定は/var/lib/mysql/)
■手順10.MySQLを起動させる
# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
■手順11.ibdataが初期化された確認する
# ls -alt /var/lib/mysql/
total 2286160
drwxr-xr-x 11 mysql mysql      12288 Nov 11 12:43 .
-rw-rw----  1 mysql mysql  268435456 Nov 11 12:43 ib_logfile0
-rw-rw----  1 mysql mysql   77594624 Nov 11 12:43 ibdata1
-rw-rw----  1 mysql mysql  268435456 Nov 11 12:43 ib_logfile1
77594624ということは、77,594,624byte。77MBになった(゚∀゚)キタコレ!!
無事に277GBが77MBのダイエットに成功!!これはダイエット本として発売するしかっ(ΦωΦ)フフフ…
■手順12.各テーブルごとに作られる.ibdファイルが作られているか確認する
# ls -alt /var/lib/mysql/データベース名/|grep ibd
total 1988
drwxr-xr-x 11 mysql mysql 12288 Nov 11 12:43 ..
この時点では.ibdファイルは作られていないΣ(゚Д゚ υ) アリャ ※ibdataなどを初期化したのでデータが全部消えてしまっている。 ■手順13.先程ダンプしたファイルをリストアする。
#先程ダンプしたファイルの解凍
# gunzip /data/tmp/all_database_20121111.sql.gz

#リストア
# mysql -u root --default-character-set=utf8 < /data/tmp/all_database_20121111.sql
■手順14.再度*.ibdファイルがあるか確認してみる
# ls -alt /var/lib/mysql/データベース名/|grep ibd
-rw-rw----  1 mysql mysql 3317694464 Nov 11 13:14 *****.ibd
-rw-rw----  1 mysql mysql  234881024 Nov 11 13:14 *****.ibd
-rw-rw----  1 mysql mysql   58720256 Nov 11 13:07 *****.ibd
-rw-rw----  1 mysql mysql     196608 Nov 11 13:06 *****.ibd

キタ――(゚∀゚)――!!
これで、無事にibdataのリセットに成功しました(`・ω・´)シャキーン
後は、定期的にALTER TABLEを行うことでデフラグを解消してくれて、
ibdファイルのサイズは小さくしてくれます。
■手順15.slave側のibdataのリセット。
手順6でslaveの停止をしているので、手順7以降を同様にslaveでも行う。

■手順16.レプリケーションの開始設定
slaveのibdataなどを削除したため、
再度レプリケーションの設定が必要となります。
手順12で利用したダンプファイルからmasterの情報を利用して、 slaveの設定を行います。
#
head -100 /data/tmp/all_database_20121111.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000028', MASTER_LOG_POS=107;
上記のコマンドで出力されたLOG_FILEとLOG_POSの値を、
CHANGE MASTER TOのMASTER_LOG_FILE、MASTER_LOG_POSの各値に設定する。
#レプリケーションの設定
mysql> CHANGE MASTER TO
    -> MASTER_HOST='masterサーバーのIPアドレス',
    -> MASTER_USER='slaveユーザーのアカウント名',
    -> MASTER_PASSWORD='slaveユーザーのパスワード',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000028',
    -> MASTER_LOG_POS=107,
    -> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)

#同期開始
mysql> SLAVE START;
以上(`・ω・´)ゞビシッ!!
ibdataにあったものが個々のテーブルの*.ibdファイルに分散され、ibdataは小さくなりましたが、
運用を続けているとibdataはやっぱり肥大化してしまうみたいです。
(ibdataファイルにはデータは入らなくなったけど、各*.ibdのメタデータを保存してるため)
ただ、今回の設定をしておくことで、その肥大化の速度を緩やかにすることが出来る!
と言ったことなのかな?
設定の注意点としては、
innodb_file_per_tableを設定してもibdataは必要なので消してはいけません!
innodb_log_group_home_dirを設定してはいけません!!
ド━━━━m9(゚∀゚)━━━━ン!!

参考URL

0 件のコメント:

コメントを投稿