MySQLでサイズの大きいファイルをインポートする際の注意点

MySQLでサイズの大きい(3GBほどの)dumpファイルを異なるサーバに移行したかった。また、単純な移行だけではなく特定のカラムを追加した上でupdate文を実行したり、インデックスの追加作業も伴ったため、色々苦労した。次回に活かせそうな点等を諸々メモ。

想定

3000万行ほどのデータベースでファイルサイズは圧縮前で3GBほど。単純に移行だけではなくカラムを追加した上でupdate文の発行を行う作業とインデックスを追加する作業があった。

テストサーバに関して

手持ちでテストサーバがあればそれを使えば良い。ただ、作業を早く終わらせたいので出来るだけスペックが高いサーバーを使用したかった。ConoHaのVPSが1時間あたりの料金で使えるのでメモリ2GBのサーバーを立てた。メモリはそこまで多くないがSSDなのでインポートとかの作業は思った以上に早かった。

updateの際の注意

updateがテストサーバで1秒あたり10件ほどの更新作業がかかっており、そのテーブルは100万件ほどデータが入っていたので、そのままいくと27時間ほどかかってしまう計算になり非常に困った。何とかならないか調べたところ単純にupdateで指定するキーに対してインデックスが設定されていなかった為、時間がかかっていた。

#以下だとtable_idにインデックスを設定する
UPDATE table_name 
SET name = 'hogehoge'
WHERE table_id = 111

インデックスを設定することで数十分ほどで対応完了した。

インポートする時の注意

#mysqlにログインしない
mysql -u user -p table < table.dump

#mysqlにログインする
mysql -u user -p
use table;
source table.dump;

2パターンあるが後者の方を使う。前者の方はタイムアウトする可能性がある(実際にタイムアウトした)。後者だとタイムアウトせずに読み込むことができる。以下参考。

https://torikasyu.com/?p=411

MySQLのチューニング

本番サーバの方でインポート速度が遅かったので以下を参考にMySQLのチューニングを行った。

https://qiita.com/mamy1326/items/9c5eaee3c986cff65a55

# innodb
innodb_buffer_pool_size=4G
innodb_additional_mem_pool_size=20M
innodb_log_buffer_size=64M
innodb_log_file_size=1G
innodb_file_per_table=1

# query cache
query_cache_limit=16M
query_cache_size=512M
query_cache_type=1

# etc
join_buffer_size=256K
max_allowed_packet=8M
read_buffer_size=1M
read_rnd_buffer_size=2M
sort_buffer_size=4M
max_heap_table_size=16M
tmp_table_size=16M
thread_cache_size=100
wait_timeout=300

しかし上記を/etc/my.cnfに記述して再起動をかけてもMySQLが動かない。原因は恐らく以下あたり。

innodb_additional_mem_pool_size=20M
innodb_log_buffer_size=64M
innodb_log_file_size=1G

それとMySQLのバージョンが5.1系でストレージエンジンのデフォルトはMyISAMになっているからっぽい。また、各々のテーブルはInnoDBで設定されているが、デフォルトはMyISAMというなんだかややこしい状況だった為、InnoDBに統一しようと思った。

MyISAMからInnoDBに変更する際の注意点

全データベースのバックアップを先にとっておく。今回は移行する為に元々全データがある状態なので問題なし。以下構文を使用している場合は挙動が異なるらしくプログラム側の変更も必要になるが、こちらも使用していなかったので問題なし。

NSERT IGNORE INTO 
INSERT INTO ~ ON DUPLICATE KEY UPDATE ~
LOAD DATA ~ IGNORE INTO ~
DELETE FROM ~ WHERE id = ~

あとは/etc/my.cnfに以下を記述してMySQLを再起動しようとしても起動しなかった。

default_table_type=InnoDB

/var/lib/mysqlディレクトリにあるib_logfile0、ib_logfile1、ibdata1のファイルが問題らしい。なので、他所に移した上で再起動をかけるとMySQLが動くがテーブルが破損している可能性あり。なのでそれぞれのテーブルを削除した上でバックアップファイルからリストアする。

この時点でMySQLのチューニングは出来ていたようで、リストアの際は数時間かかっていたものが数十分で済むようになった。

尚、Pleskを使用しているサーバの場合はpsaテーブルもリストアする必要がある。以下過去記事参照。

Plesk12で「SQLSTATE[42S02]: Base table~~」エラー時の対応方法

事前準備が大切

作業前は何の根拠も無く2~3時間もあれば終わると思っていたけど丸1日かかってしまった。事前にテストデータを作成し予行演習しておけばサーバのチューニングやupdate部分については解決できていたと思われる。