勉強したことのメモ

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

  関連記事

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

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

phpmyadminからプロセスの停止

重たいSQL文を叩いた際、どうにも処理できず メモリ使用率が100%から下がらな ...

MySQLのREGEXPで半角記号を検索したい場合

MySQLのREGEXPで半角記号を検索したい場合 \\を付けてエスケープしない ...

エラー管理まとめ

■エラーログをとる。 error_reporting(E_ALL ^ E_NOT ...

LEFT JOIN

なんとなくで覚えていたMySQLのLEFT JOINを 最近使うことがあり、上手 ...

MySQLで経度緯度から半径○Kmのデータを抽出

経度と緯度をtext型として格納しているMySQLのテーブルがあり、指定した経度 ...

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

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

MySQLでdatetime型にもインデックスを使用

ログ関連のデータを格納するテーブルがあり、集計を行う際に非常に時間がかかった。レ ...

MySQLでパターンマッチの逆

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

MySQLで複数のテーブルを異なるデータベースと異なるカラムでJOIN

ちょっとややこしいんだけど、以下のような条件で3つのテーブルを結合したかった。 ...