MySQLパフォーマンスチューニングとは〜DBチューニング（全体最適化）〜

MySQLのDBチューニング(全体最適化)は、データベース全体のパフォーマンスを向上させるプロセスです。

主にスループットの改善を目的としており、MySQLの設定ファイルのパラメータを環境に適した形に調整することで実現します。

この記事では、チューニング手法の１つであるDBチューニング(全体最適化)方法について、紹介します。

パフォーマンスチューニングの概要は以下の記事で紹介しておりますので、ご参考ください。

MySQLのパフォーマンスを改善するチューニング手法
こんにちは。この記事では、MySQLのパフォーマンスチューニングについて、その概要を紹介します。MySQLのパフォーマンスチューニングは、データベースの効率を最大化し、応答時間を短縮するために重要な要素です。パフォーマンス指標主なパフォーマ...
infnotes.com
2024.09.23
MySQLパフォーマンスチューニングとは〜パフォーマンス測定（スロークエリの特定）〜
こんにちは。この記事では、MySQLのパフォーマンスチューニングの前に実施するパフォーマンス測定のうち、スロークエリの特定について紹介します。MySQLのスロークエリの特定は、データベースのパフォーマンスを向上させるための重要なプロセスです...
infnotes.com
2024.09.24
MySQLパフォーマンスチューニングとは〜パフォーマンス測定（実行計画の確認EXPLAINコマンド）〜
こんにちは。この記事では、MySQLのパフォーマンスチューニングの前に実施するパフォーマンス測定のうち、実行計画の確認で利用するEXPLAINコマンドについて紹介します。MySQLの実行計画は、SQLクエリの実行方法を詳細に示す重要なツール...
infnotes.com
2024.09.24

DBチューニング(全体最適化)とは

DBチューニングでは、MySQLサーバの設定をシステム変数を通じて最適化します。

これらの設定は通常、my.cnfやmy.iniファイルで定義されていますが、MySQLコマンドを使用して直接参照・編集することも可能です。

具体的な設定方法（コマンド）

設定確認コマンド

現在の設定を確認するには、以下のコマンドを実行します。

SHOW VARIABLES;

特定の変数を確認する、以下のコマンドを実行します。

SHOW VARIABLES LIKE '変数名';

設定変更コマンド

変数を設定するには、以下のコマンドを実行します。

SET GLOBAL 変数名 = 値;

上記の設定の場合、サーバ再起動後設定が変更前に戻ってしまいます。
サーバー再起動後も設定を維持する（永続的な設定）には、SET PERSISTを使用します。（MySQL 8.0以降）

SET PERSIST 変数名 = 値;

my.cnfファイルに以下の設定を直接追記することで、永続的な設定を実施することも可能です。

変数名 = 値;

主要なチューニングパラメータ

パフォーマンス向上に特に効果的な主要パラメータには以下があります。

max_connections(同時接続数)

max_connectionsとは、サーバーが同時に受け入れる最大クライアント接続数です。
スループットが低い場合に増やすことで改善が見込めます。
デフォルト値は151です。

max_connectionsの設定コマンド

max_connectionsの設定コマンドは以下の通りです。
変数名：max_connectionsに適切な値を設定します。

SET GLOBAL max_connections = 値;

max_connectionsのチューニングのベストプラクティス

  1. 適切な値の見積もり
    • 予想される同時接続数に基づいて設定します。
    • デフォルト値の151から始め、必要に応じて徐々に増やしていくことが推奨されます。
    • 必要な場合は、100接続ずつ段階的に増やし、「too many connections」エラーが解消されるまで調整します。
    • Max_used_connectionsの値を監視し、max_connectionsの80%を超えないように調整します。
  2. メモリ消費の考慮
    • 接続数が増えるとメモリ消費も増加するため、サーバーのリソースを考慮して設定します。
    • 接続数を大幅に増やす必要がある場合は、サーバーのスペックアップを検討します。
  3. open_files_limitの調整
    • max_connectionsを増やす場合、open_files_limitも適切に増やす必要があります。
  4. コネクションプールの利用:
    • アプリケーション側でコネクションプールを使用し、不要な接続を減らします。
  5. 他のパラメータとのバランス
    • 実際の使用状況を監視し、必要に応じて調整します。
  6. パフォーマンスモニタリング
    • max_connectionsを変更した後は、サーバーのパフォーマンスを継続的にモニタリングし、必要に応じて調整します。
    • システムの成長に合わせて、定期的にmax_connectionsの設定を見直します。
  7. セキュリティの考慮
    • 過度に高い値を設定すると、DoS攻撃のリスクが高まる可能性があるため注意が必要です。

max_connectionsの設定は、アプリケーションの要件とサーバーのリソースのバランスを取りながら、慎重に行う必要があります。
過剰な設定は避け、実際の使用状況に基づいて適切に調整することが重要です。

thread_cache_size

thread_cache_sizeは、MySQLサーバーが再利用のためにキャッシュするスレッドの数を指定する変数です。
レスポンスタイムの改善に寄与します。デフォルトは9です。

クライアント接続が終了した際に、そのスレッドをキャッシュに保存します。
新しい接続が来た時に、キャッシュされたスレッドを再利用することで、スレッドの作成・破棄のオーバーヘッドを削減します。

thread_cache_sizeの設定コマンド

thread_cache_sizeの設定コマンドは以下の通りです。
変数名：thread_cache_sizeに適切な値を設定します。

SET GLOBAL thread_cache_size = 値;

thread_cache_sizeのチューニングのベストプラクティス

  1. デフォルト値を出発点とする
    • MySQL 8.0以降では、デフォルトで自動サイジングされるため、まずはデフォルト値から始めます。
    • 増加が必要な場合は、小さな増分で値を調整し、パフォーマンスへの影響を観察します。
  2. 実際の使用状況を監視
    • Threads_createdステータス変数を監視し、頻繁にスレッドが作成されている場合は値を増やします。
  3. サーバーの接続パターンに基づいて調整
    • 短時間に多数の接続と切断が発生する場合は、より大きな値が有効です。
  4. メモリ使用量とのバランス
    • 大きすぎる値はメモリを無駄に消費するため、適切なバランスを取ります。
  5. max_connectionsとの関係を考慮
    • 通常、thread_cache_sizeはmax_connectionsの10%程度に設定します。
  6. 定期的な見直し
    システムの成長に合わせて、定期的に設定を見直し調整します。

これらのベストプラクティスに従うことで、thread_cache_sizeを適切に設定し、MySQLサーバーのパフォーマンスを最適化することができます。

query_cache_size

query_cache_sizeはMySQLのクエリキャッシュで使用可能なメモリ量を指定する変数です。

クエリ結果をキャッシュするために割り当てられるメモリの総量を定義します。
0に設定するとクエリキャッシュが無効になります。類似クエリが頻繁に実行される環境で有効です。

query_cache_sizeの設定コマンド

query_cache_sizeの設定コマンドは以下の通りです。
変数名：query_cache_sizeに適切な値を設定します。

SET GLOBAL query_cache_size = 値;

query_cache_sizeのチューニングのベストプラクティス

  1. デフォルト値から始める
    • MySQL 5.7以降ではデフォルトで無効（0）になっています。必要な場合のみ有効にします。
    • 小さな値（例：16MB）から始め、パフォーマンスを監視しながら徐々に増やします。
  2. サーバーのリソースを考慮
    • 総メモリの20%を超えないようにします。
  3. クエリキャッシュのヒット率を監視
    • SHOW STATUS LIKE 'Qcache%';を使用して、ヒット率が低い場合は調整または無効化を検討します。
  4. Qcache_lowmem_prunesを確認
    • Qcache_lowmem_prunesはメモリ不足のためにクエリキャッシュから削除(プルーニング)されたクエリの数
    • この値が高い場合、query_cache_sizeを増やすことを検討します。
  5. query_cache_limitとのバランス
    • 大きなクエリ結果がキャッシュされすぎないよう、query_cache_limitも適切に設定します。
    • query_cache_limitは、1つのクエリ結果がキャッシュできる最大サイズを定義します。
  6. 書き込みの多いワークロードでは注意
    • 頻繁な更新がある場合、クエリキャッシュは逆効果になる可能性があります。
  7. パフォーマンススキーマとの関係に注意
    • パフォーマンススキーマを使用している場合、クエリキャッシュのオーバーヘッドが増加する可能性があります。
  8. MySQL 8.0以降では非推奨
    • MySQL 8.0以降ではクエリキャッシュが削除されたため、新しいバージョンへの移行を検討します。

これらのベストプラクティスに従うことで、query_cache_sizeを適切に設定し、MySQLのパフォーマンスを最適化することができます。

ただし、ワークロードや環境に応じて調整が必要な場合があります。

sort_buffer_size

sort_buffer_sizeは、MySQLがソート操作を実行する際に使用するメモリバッファのサイズを指定する変数です。

ソート操作（ORDER BY、GROUP BYなど）に使用されるメモリバッファのサイズを定義します。
各セッションに対して割り当てられます。

大量データのソート処理が必要な場合に増やすことで改善が見込めます。

sort_buffer_sizeの設定コマンド

sort_buffer_sizeの設定コマンドは以下の通りです。
変数名：sort_buffer_sizeに適切な値を設定します。

SET GLOBAL sort_buffer_size = 値;

query_cache_sizeのチューニングのベストプラクティス

  1. デフォルト値から始める
    • 通常、デフォルト値（262144バイト = 256KB）から始めます。
    • 必要に応じて徐々に増やし、パフォーマンスへの影響を観察します。
  2. サーバーリソースを考慮
    • 大きな値を設定すると、多数の同時接続時にメモリ使用量が急増する可能性があります。
  3. ソート操作の頻度を監視
    • SHOW GLOBAL STATUS LIKE 'Sort%';を使用して、ソート操作の頻度を確認します。
  4. filesortの発生を確認
    • EXPLAINを使用してクエリプランを確認し、filesortが発生しているかチェックします。
    • filesortは、MySQLがクエリの結果セットをソートする際に使用する内部的なソート処理メカニズムです。
      インデックスを使用せずに、メモリ内でデータをソートする操作を指します。
      実際には「クイックソート」アルゴリズムが使用されています。
  5. MySQL 8.0.12以降の動的割り当て
    • この版以降では、必要に応じて増分的にメモリを割り当てるため、大きな値を設定しても小さなソートでは過剰なメモリ使用を避けられます。
  6. クエリの最適化を優先
    • インデックスの追加やクエリの書き換えなど、ソート操作を減らす方法を先に検討します。
  7. テンポラリテーブルとの関連性
    • sort_buffer_sizeが小さすぎると、テンポラリテーブルが作成される可能性が高くなります。
    • テンポラリテーブルは、一時的なデータ保存や処理のために使用される特殊なタイプのデータベーステーブルです。
  8. パフォーマンスモニタリング
    • 変更後はPerformance SchemaやPerformance Insightsを使用して、パフォーマンスへの影響を監視します。
  9. システム全体のバランスを考慮
    • 他のバッファ（例：innodb_buffer_pool_size）とのバランスを取りながら調整します。
    • innodb_buffer_pool_sizeは、InnoDBがテーブルやインデックスデータをメモリ内でキャッシュするためのバッファプール(メモリ領域)のサイズを定義する変数です。

これらのベストプラクティスに従いながら、sort_buffer_sizeを適切に設定することで、MySQLのソート操作のパフォーマンスを最適化できます。

ただし、環境やワークロードに応じて調整が必要な場合があります。

DBチューニング時の注意点

  • パラメータの調整はサーバのハードウェアスペックを考慮して行う必要があります。
    過度な設定はメモリ消費を増大させ、逆効果になる可能性があります。
  • 環境のメモリサイズを考慮し、効率的に使用されているか確認することが重要です。
    使用率が低い場合はキャッシュの活用を、逼迫している場合はキャッシュの削減を検討します。
  • テーブルで使用する列の型は、想定される入力に適した型を選択することも重要です。

まとめ

今回は、データベース全体のパフォーマンスを向上させるDBチューニング(全体最適化)について紹介しました。

DBチューニングは全体的なパフォーマンス向上に効果的ですが、多くの場合、個別のSQLクエリの最適化（SQLチューニング）と組み合わせることで、より大きな改善が得られます。

ご参考になれば、幸いです。

以上になります。ありがとうございました。

参考

https://products.sint.co.jp/siob/blog/tuning
https://qiita.com/hp-Genqiita/items/3ef91f6df6c15c620ec6
https://qiita.com/micah/items/f45f7b73cf99454ef8e9
https://a-mochan.hatenablog.com/entry/2020/01/12/235846
https://mita2db.hateblo.jp/entry/2020/05/31/175523
https://knowledge.sakura.ad.jp/11981/
https://releem.com/docs/mysql-performance-tuning/thread_cache_size
https://qiita.com/fururun02/items/e143ae87ec8a1c3884eb
https://dev.mysql.com/doc/refman/8.0/ja/connection-interfaces.html
https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html
https://releem.com/docs/mysql-performance-tuning/thread_cache_size
https://qiita.com/shota0616/items/68274bed508cb10e3355

コメント

タイトルとURLをコピーしました