勉強したことのメモ

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テーブルもリストアする必要がある。以下過去記事参照。

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

 

事前準備が大切

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

 - MySQL

  関連記事

msyql-image
MySQLで日時や日付で範囲検索する際の速度向上

結論としてはUNIXタイムスタンプ型にする。 MySQLでdatetime型で2 ...

msyql-image
mysqlで正規化できない時はシリアライズ使う

アンケートか何かに機能追加している際、配列を カンマ区切りで結合して文字列でDB ...

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

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

msyql-image
MySQLでWhere句を使ってカラム指定でデータコピー

MySQLで大量のデータを保存しているテーブルがあり、そのデータを絞った上で尚且 ...

images
WordPressサイトのロードアベレージが高い

あるWordPressサイトのロードアベレージが先月ぐらいまでは通常0.5前後で ...

msyql-image
MySQLでパターンマッチの逆

LIKE検索の逆ってなったのでメモ。 NOT LIKE '%あああ%' が正解み ...

msyql-image
MySQLでテーブルの詳細情報を取得

MySQLでテーブルの詳細情報を取得 desc テーブル名; ■サンプル mys ...

msyql-image
MySQLで開始から終了日付内を調べたいとき

等号不等号でもできるけど、BETWEENというのがよさそう。 ■参考サイト ht ...

msyql-image
MySQLでグループ化したものを条件で絞る(HAVING)

正規化したテーブルがあってその中には idとtagのカラムがある。 でtagの方 ...

msyql-image
MySQLで検索した件数の取得

ページングとかで必要になる総件数について、 いつもググってしまっていたのでメモ。 ...