パワー不足の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%’」というクエリを大量に発行している事
などが解決すべき課題がたくさんあります。
現状を整理した事で、次回からは一つずつ改善対策を実施していきたいと思います。
...