DBサーバー(MySQL)のチューニングしようとしたときのメモ

過去にDBサーバー(MySQL)のチューニングを検討したときのメモ MySQLのチューニング機会があれば参考にしたいと思う。詳細な作業手順は割愛してる。 追記：実対応は社内の事情もあり未実施

対応前

背景

• 関わっていたDBサーバーは構築当初のピュアな状態からチューニングされていなかった
• DBサーバはマスター/スレイブ構成だった
  • Master：1台
  • Slave：3台

問題

• 繁忙期にレスポンスが著しく遅くなっていた
• とある時間帯のバッチタイミングでレプリケーション遅延が定常的に発生していた
• SlaveDBのCPUのio wait も通常30%、高負荷時には70%であった
• MySQLの設定も推奨設定から乖離していた

仮説

• innodb_buffer_pool_size
  • innodb_buffer_pool_sizeは30%となっているのでバッファを増やすとパフォーマンスが改善される可能性があった
    • 推奨は全メモリの50%~80%
    • Oracleは80%推奨としていた　※参考
• innodb_file_per_table
  • 「共有領域」管理となっていたが推奨は「テーブル個別領域」管理
  • 領域全体の更新より更新対象のテーブルの領域の更新のみのほうが早い
• query_cache_size
  • MySQLの公式ドキュメントによるとテーブルの更新が頻繁にある場合を除き、クエリーキャッシュ(現query_cache_sizeは0)を有効にすることでselectのパフォーマンス向上する可能性があった　 ※参考
  • MySQL8.0ではクエリキャッシュは廃止されたらしく、早いクエリを書く方向で改善すべきである　 ※参考

対応

やること

• innodb_buffer_pool_sizeは3GB(30%)→6GB(75%)に変更

やらないこと

• query_cache_sizeはこのまま
• innodb_file_per_tableをONにする
  • my.cnf上は未指定だがデフォルト値がONのため設定済み

その他

• MySQLTunerで１度計測したほうが良さそう
  • ログもしっかりとる設定にしたほうが良いか

注意

• innodb_file_per_tableの設定変更について
  • 今までのデータがテーブル個別に分割されるわけではない。 設定後にCREATEされたテーブルには反映されるが、データすべてに対応するには各テーブルをデータごと流し込む必要がある。
  • メンテナンス時間を設け、いったんスキーマ全体をdumpしリストアすることで、全データに反映する手段が良さそう。