パワー不足のmysqlをチューニングしてみます。

こんばんわ。プラットフォーム担当の宮下です。

今回は、バックアップ兼集計用に稼働しているmysqlのレプリケーションンサーバの
パフォーマンス改善していきたいと思います。
これまでにもいくつか対策はしてきましたが、まずは環境をおさらいします。

◇稼働してる環境は、AWSのEC2でlargeインスタンスを使用しています。
　サービスは、OpenPNEを使用したSNSのサービスとなります。対象のMysqlはバックアップとデータ集計が目的で稼働しています。
　（本番サービスでは使用していません）

◇mysqlのバージョンはちょっと古くて「5.0.77」になっています。multiで起動させていていて
　この他に開発環境用のmysqlも稼働していたりします。
　ちなみに今の遅延秒数は、「1512561 秒」でまだ増加中です。

◇これまでに変更した点ですが、
　「innodb_buffer_pool_size」を512Mから2028Mまで増加、
　「innodb_flush_log_at_trx_commit」を2から0に変更、
　（リカバリの精度よりもスループットを優先）
　「key_buffer_size」を32Mから300Mに変更、
　「query_cache_size = 0」にして無効化しました。
　効果は、DISKのI/Oが半分くらい減りましたが、遅延解消とまではいってません。

◇最後にmuninで取得しているデータを確認します。

遅延が日々増えています。たまに減る事もありますが要因は分かりません。

常にDISKのread I/Oが大量に発生しています。

原因ははっきりしていて、DBデータに画像や動画データが格納されている為となります。
　buffer pool sizeでは収まり切らないデータサイズなので常にDISKのread/writeが発生しています。
　またMyISAMとInnodbが混在していてリソースが効率的に利用出来ていない事や、
　OpenPNEが「delete aaa filename LIKE ‘%bbb%’」というクエリを大量に発行している事
　などが解決すべき課題がたくさんあります。

現状を整理した事で、次回からは一つずつ改善対策を実施していきたいと思います。