勉強したことのメモ

webプログラマ見習いが勉強したことのメモ。

MySQLでサイズの大きいファイルのインポート

      2018/11/09

MySQLでサイズの大きいdumpファイルを異なるサーバに移行したかった。また移行だけではなくupdateやインデックスの追加も伴った為、色々苦労した。次回に活かせそうな点等を諸々メモ。

 

想定

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

 

テストサーバに関して

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

 

updateの際の注意

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

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

 

インポートする時の注意

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

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

 

MySQLのチューニング

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

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

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

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

 

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

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

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

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

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

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

https://taitan916.info/blog/?p=3070

 

事前準備が大切

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

 - MySQL

  関連記事

エックスサーバーでMySQLのサイズに注意

エックスサーバーで自動更新のログをMySQLに保存するようなシステムを動かしてい ...

MySQLでカラム内容によって条件分岐させたい

MySQLでカラムの内容によって条件を分岐させたかった。またPHPで処理するので ...

テンポラリテーブルで高速化

5000万件ぐらいデータが入っているテーブルから 日本語検索しないといけないケー ...

MySQLでデータがあれば更新、無ければ挿入

DBにデータがあれば更新、無ければ挿入(新規作成)を 行いたい際に使えるものがあ ...

MySQLでIPアドレスを数値として入れると変になる

MySQLでIPアドレスをINET_ATON関数を用いてINT型に変換し格納する ...

phpMyAdminでテーブルの操作

table_aと同じ構造のtable_bを作りたい時、 手動で作成するか、tab ...

MySQLでランダムに取得

SELECT カラム名 FROM テーブル名 ORDER BY RAND() L ...

異なるVPS間でMySQLの接続を行う

異なるVPS間でMySQL接続を行った際のメモ。 さくらVPS(IP:111.1 ...

お名前SDサーバにMySQLデータのインポート

お名前.comのレンタルサーバにそこそこ大きいサイズのMySQLデータを移行した ...

phpMyAdminより高速で軽量なAdminerが凄い

MySQLをGUIで管理できるphpMyAdmin。特に不満はないんだけど、もっ ...