mysqlのログファイルのサイズを変更する

バイナリデータを格納するために、BLOB型やその拡張のMEDIUMBLOB型を利用するシーンが時折あるかと思います。

BLOB型を利用していて、サイズの大きなデータをDBに格納しようとすると、MySQLのログに以下のようなエラーログが出力されることがあります。

100906 00:00:00 InnoDB: ERROR: the age of the last checkpoint is 9440228,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

これは、MySQLのログファイルのサイズが小さいために発生します。

MySQLは、テーブルスペースの更新に時間がかかるため、直接テーブルスペースを更新するのではなく、最初にログファイルに全ての更新を書き込み、そのあと非同期でテーブルスペースを更新するという仕組みになっています。

このログファイル・サイズはデフォルトで5MBが設定されていますが、ログファイルの合計サイズは、 innodb_log_files_in_group という設定値(デフォルト2)と、innodb_log_file_size との積で、トータル10MBとなります。

そのため、10MB以上のファイルをDB(MEDIUMBLOB/BLOB型のカラム)に格納しようとするとログファイルサイズをオーバーしてしまい、エラーとなるため、ログファイルのサイズを拡張しなければなりません。

では、どのような手順でログファイルのサイズを変更すればよいかを解説します。

MySQLサーバを普通にシャットダウンした場合、テーブルスペースに反映されていないデータがログファイルに残る可能性があるため、ログファイルのサイズを変更する場合は、MySQLサーバの停止と同時にテーブルスペースへログファイルの内容をすべて反映する必要があります。具体的には、innodb_fast_shutdown=0 を設定しなければなりません。

設定値を確認するには、mysqlコマンドでデータベースに接続して以下のコマンドを発行してください。

mysql> show variables like 'innodb_fast_shutdown';

	Variable_name		Value
	innodb_fast_shutdown		1

上記の場合、設定値が1であるため、ログファイルのデータがテーブルスペースに反映されない可能性があります。

そのため、下記のコマンドで設定値を変更します。

mysql> SET GLOBAL innodb_fast_shutdown=0;

ついでに、ログファイルのサイズを確認する場合は、以下のコマンドを発行してください。

mysql> show variables like 'innodb_log_file_size';

	Variable_name		Value
	innodb_log_file_size		5242880

ValueはByte表示ですが、これが5MBとなります。

innodb_fast_shutdownの設定が終わったら、サービスを停止し、それからMySQLサーバを停止します。
＃MySQLサーバを停止する前に、mysqldump等でバックアップを取得しておくことを推奨します。

# /etc/init.d/mysqld stop

続いて、ログファイルを適当な場所に退避します。

mv /var/lib/mysql/ib_logfile* ~/mysql/

これをしないと下記で行う設定追加後に、MySQLサーバが起動しなるかもしれません。

次にログファイルのサイズの設定値を変更を行います。
設定ファイルはディストリビューションにより異なるかもしれませんが、Linuxの場合、基本的には /etc/my.cnf となります。

"[mysqld]" の設定に以下を追加します。

innodb_log_file_size=64M

＃ここでは64MBに設定するとします。

設定したら、MySQLサーバを起動してください。

# /etc/init.d/mysqld start

すると、ログファイルが新規に作成され、サーバが起動します。
本当に64MBのログファイルが作成されたかを確認するには、mysqlのログ( /var/log/mysqld/mysql.log )を確認しましょう。

100906 00:00:00 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
100906 00:00:00 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait...

このログから、64MBのログファイルが2つ作成されたことがわかります。

あとは、下記のコマンドで、設定した値(64MB)に変更していることを確認しましょう。

mysql> show variables like 'innodb_log_file_size';

以上がログファイルのサイズ変更手順となります。

他にも、BLOB型やMEDIUMBLOB型等を利用している場合は、mysqldump時に hexblobオプションやmax_arrow_packetの設定をしないとバックアップデータが情報欠落していたり、バックアップができなかったりしますので、こちらも合わせて確認しておきましょう。

【補足】
今回、解説した内容は、MySQL 5.0/5.1 で試したときの手順となります。