勉強したことのメモ

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からデータを持ってくる際に、 ランダムな表示を行ってほしいと言われた。 ...

LEFT JOIN

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

phpMyAdminやAdminer上だけ文字化け

レンタルサーバーのプログラムでWebページ上では文字化けしていないのに、phpM ...

MySQLで文字列の置換

MySQLで文字列の置換を行いたかった。以下に方法をメモ。   置換方 ...

phpmyadminからプロセスの停止

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

お名前.comの共用サーバー(SD)にphpmyadminを入れる

公式で書いてあるやつは3.4.2だけど、もうちょっと新しいバージョンを入れたかっ ...

WordPressで「データベース接続確立のエラー」

WordPressで設定ファイル(wp-config.php)は触っていないのに ...

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

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

MySQLでユニークキーをはる

MySQLでseqとcodeみたいなものをあわせて ユニークキーをはりたかった。 ...

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

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