DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

MySQLの一般的なパフォーマンスボトルネックとその修正方法

MySQLの一般的なパフォーマンス問題を診断し解決します。このガイドでは、インデックス作成とクエリ最適化による低速クエリの特定と修正、InnoDBバッファプールなどのメモリ設定のチューニング、ロック競合の管理、リソースボトルネックへの対処について説明します。EXPLAINやスロークエリログなどの組み込みツールを使用して、MySQLデータベースを効率的に運用するための実践的な戦略を学びます。

MySQLの一般的なパフォーマンスボトルネックとその修正方法

MySQLが遅くなったとき、最初の症状は「データベースが遅い」というものではありません。通常は、チェックアウトページがハングする、キューが排出されなくなる、ダッシュボードがタイムアウトする、または80ミリ秒で完了していたリクエストが突然3秒かかるAPIなどです。

時間を無駄にする最速の方法は、待機がどこにあるかを知る前にランダムな設定をチューニングすることです。まずは単純な質問をしましょう。MySQLはクエリ作業、ロック、メモリ、ディスク、CPU、ネットワーク、または接続数の多さのどれを待っているのでしょうか？修正方法は答えによって異なります。

1. 低速クエリ

低速クエリは、おそらく最も一般的なパフォーマンスボトルネックです。非効率的なクエリ設計、インデックスの欠如、大規模なテーブルスキャンなど、さまざまな要因から発生する可能性があります。これらのクエリを特定することが解決への第一歩です。

低速クエリの特定

MySQLスロークエリログは、指定されたしきい値よりも長く実行されるクエリを特定するための貴重なツールです。このログは、my.cnf（またはmy.ini）設定ファイルで有効にして構成できます。

my.cnf設定例:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

この例では:

  • slow_query_log = 1: スロークエリログを有効にします。
  • slow_query_log_file: ログファイルのパスを指定します。
  • long_query_time = 2: しきい値を2秒に設定します。これより長いクエリがログに記録されます。
  • log_queries_not_using_indexes = 1: インデックスを使用しないクエリをログに記録します。これらは最適化の主要な候補となることがよくあります。

ログを有効にした後、その内容を分析できます。mysqldumpslowのようなツールは、ログファイルを要約して並べ替えるのに役立ち、最も問題のあるクエリを特定しやすくします。

低速クエリの最適化

低速クエリが特定されたら、いくつかの戦略を採用できます:

  • インデックス作成: WHERE、JOIN、ORDER BY、GROUP BY句で使用される列に適切なインデックスが作成されていることを確認します。EXPLAINを使用してクエリ実行計画を分析し、欠落しているインデックスを特定します。

    • 例: クエリが大きなordersテーブルでuser_idによって頻繁にフィルタリングする場合、orders(user_id)のインデックスによりパフォーマンスが大幅に向上する可能性があります。
    CREATE INDEX idx_user_id ON orders (user_id);

  • クエリの書き換え: 場合によっては、クエリをより効率的に書き換えることができます。これには、結合の簡略化、SELECT *の回避、サブクエリのより慎重な使用などが含まれます。

    • 例: 相関サブクエリをJOINに置き換えると、パフォーマンスが向上する可能性があります。

  • データベーススキーマ設計: 正規化の問題や（慎重に）非正規化の機会についてデータベーススキーマを確認することも役立ちます。

2. 非効率的なインデックス作成

インデックス作成はクエリパフォーマンスの鍵ですが、設計が不十分であったり、インデックスが多すぎるとボトルネックになる可能性もあります。インデックスはディスク容量を消費し、書き込み操作（INSERT、UPDATE、DELETE）にオーバーヘッドを追加します。

インデックス問題の特定

  • EXPLAIN計画分析: インデックス変更の前後には常にEXPLAINを使用します。大きなテーブルでのフルテーブルスキャン（type: ALL）や、返される行数よりもはるかに多い検査行数に注目します。

    EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

  • 未使用のインデックス: MySQL 5.6以降には、インデックス使用状況を追跡する機能があります。performance_schema.table_io_waits_summary_by_index_usageをチェックして、一度も使用されていない、またはほとんど使用されていないインデックスを特定できます。

  • 冗長なインデックス: 同じ列をカバーするインデックスや、他のインデックスのプレフィックスであるインデックスは冗長である可能性があります。

インデックス作成のベストプラクティス

  • 選択的にインデックスを作成する: クエリパターンに基づいて、本当に必要な場所にのみインデックスを作成します。
  • 複合インデックス: 複数の列でフィルタリングするクエリの場合は、複合インデックスを検討します。複合インデックス内の列の順序が重要です。
  • カバリングインデックス: クエリに必要なすべての列がインデックスの一部であるカバリングインデックスを目指します。これにより、MySQLはテーブルにアクセスせずにインデックスから直接データを取得できます。
  • 定期的なレビュー: 特にスキーマ変更やアプリケーション使用状況の変化後は、定期的にインデックスをレビューします。

3. バッファプールとメモリ設定

InnoDBバッファプールは、InnoDBがデータとインデックスページをキャッシュする重要なメモリ領域です。バッファプールサイズが不十分だと、過剰なディスクI/Oが発生し、操作が大幅に遅くなる可能性があります。

InnoDBバッファプールのチューニング

innodb_buffer_pool_sizeパラメータは、InnoDBパフォーマンスにとって最も重要な設定の1つです。

推奨事項: 専用データベースサーバーの場合、innodb_buffer_pool_sizeを利用可能なRAMの50〜75％に設定することが一般的な開始点です。一部のシステムではより高く設定できますが、オペレーティングシステムがスワップしておらず、接続メモリが制御されている場合に限ります。

my.cnf設定例:

[mysqld]
innodb_buffer_pool_size = 8G

これにより、バッファプールが8ギガバイトに設定されます。

監視: バッファプールの読み取りパターンを観察します。非常に高いヒット率は、ほとんどの読み取りがメモリから処理されていることを意味しますが、すべてのクエリが正常であることを証明するものではありません。これは以下を使用して監視できます:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

ヒット率は (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests として計算できます。

その他のメモリ設定

  • innodb_log_file_size: 書き込みパフォーマンスとリカバリ時間に影響します。ファイルが大きいと書き込みスループットが向上する可能性がありますが、クラッシュ後のリカバリ時間が長くなります。
  • innodb_flush_log_at_trx_commit: 耐久性とパフォーマンスを制御します。デフォルトの1に設定すると完全なACID準拠が保証されますが、遅くなる可能性があります。0または2に設定すると、一部の耐久性保証を犠牲にしてパフォーマンスが向上する可能性があります。

4. ロック問題と並行性

ロックはデータの一貫性に不可欠ですが、適切に管理されないとボトルネックになる可能性があります。過剰なロックは、クエリの競合、タイムアウト、デッドロックを引き起こす可能性があります。

ロック問題の特定

  • SHOW ENGINE INNODB STATUS: このコマンドは、アクティブなトランザクション、保持されているロック、ロック待機など、InnoDBの内部状態に関する詳細情報を提供します。
  • パフォーマンススキーマのロックテーブル: MySQL 8.0では、data_locksやdata_lock_waitsなどのパフォーマンススキーマテーブルを使用します。古いバージョンでは、information_schemaテーブルを介してロック情報が公開されていました。
  • 監視ツール: パフォーマンス監視ツールは、高いロック待機時間やデッドロックを強調表示することがよくあります。

ロック問題の解決

  • ロックを引き起こすクエリの最適化: より短く効率的なクエリは、ロックが保持される時間を短縮します。
  • トランザクション管理: トランザクションは可能な限り短くします。広範なロックを必要とする長時間実行操作をトランザクション内で避けます。
  • ロックの粒度: InnoDBはほとんどの操作に行レベルロックを使用します。これは一般的に並行性に適しています。ただし、クエリがテーブルロックにエスカレートする方法（例：オンラインDDLなしのALTER TABLE）を理解することが重要です。
  • デッドロックの検出と解決: MySQLにはデッドロック検出機能があります。デッドロックが検出されると、InnoDBは通常、関連するトランザクションの1つをロールバックし、もう1つを続行できるようにします。SHOW ENGINE INNODB STATUSからデッドロック情報を分析して原因を理解し、アプリケーションロジックまたはクエリの順序を調整します。

5. リソース競合（CPU、ディスク、ネットワーク）

クエリが最適化され、設定が適切であっても、ハードウェアリソースが不十分であったり、これらのリソースの競合がパフォーマンスを制限する可能性があります。

リソースボトルネックの特定

  • CPU使用率: mysqldプロセスによる高いCPU使用率は、非効率的なクエリ、大量のソート、または処理能力の不足を示している可能性があります。
  • ディスクI/O: 特にバッファプールのヒット率が低い場合の高いディスク読み取り/書き込みアクティビティは、ディスクI/Oがボトルネックであることを示しています。Linuxシステムでは高いiowait時間に注目します。
  • ネットワークスループット: 大量の結果セットが転送されたり、多数のクライアント接続がある場合に、過剰なネットワークトラフィックが発生する可能性があります。

リソースボトルネックへの対処

  • ハードウェアアップグレード: 場合によっては、最も簡単な解決策はCPU、RAM、またはより高速なストレージを追加することです。これは、ワークロードが妥当であることを確認した後でのみ修正として扱います。ハードウェアは悪いクエリを隠すことができますが、それを消すことはほとんどありません。
  • クエリ最適化: 処理および転送されるデータ量を削減します。これにより、CPU、ディスク、ネットワークの負荷が間接的に軽減されます。
  • コネクションプーリング: アプリケーションにコネクションプーリングを実装して、新しい接続を確立するオーバーヘッドを削減し、アクティブな接続数を効果的に管理します。
  • 読み取りレプリカ: 読み取り負荷の高いワークロードの場合は、読み取りレプリカを設定して、プライマリサーバーから読み取り負荷を分散することを検討します。

プレッシャー下で機能するトリアージフロー

インシデントがアクティブな場合、完全なチューニングプロジェクトから始めないでください。まずは簡単な状況を把握します。

アクティブなクエリを確認:

SHOW FULL PROCESSLIST;

多くのセッションが同じクエリでスタックしている場合は、それをキャプチャします。多くのセッションがロックを待機している場合は、ランダムにkillせず、最初にブロックしているトランザクションを特定します。

InnoDBの状態を確認:

SHOW ENGINE INNODB STATUS\G

デッドロック、ロック待機、チェックポイントプレッシャー、長時間実行トランザクションを探します。1時間開いているトランザクションは、パージ作業を妨げ、無関係なクエリを遅くする可能性があります。

サーバーが飽和状態かどうかを確認:

top
vmstat 1
iostat -xz 1
ss -s

I/Oが低いのにCPUが高い場合は、通常、高価なクエリ実行、ソート、解析、または過剰な並行性を示しています。高いiowaitはストレージを示しています。スワップアクティビティは危険信号です。スワッププレッシャー下のMySQLは、予測不能に動作することがよくあります。

次に、過去数分間のスロークエリログを確認します。全期間の最悪のクエリだけではありません。今日のインシデントを引き起こしたクエリは、新しいものであったり、デプロイに関連していたり、ピーク時にのみ現れるトラフィックパターンに関連している可能性があります。

コネクションストーム

一般的なMySQLのボトルネックは、1つの悪いクエリではなく、少量の作業を行うアプリケーション接続が多すぎることです。すべてのWebワーカーが独自の接続を開き、アプリが突然スケールアウトすると、MySQLはセッションのスケジューリングと接続ごとのメモリ割り当てに時間を費やしすぎる可能性があります。

症状は次のとおりです:

  • Threads_connectedが急激に上昇。
  • Threads_runningが高いまま。
  • Too many connectionsなどのアプリケーションエラー。
  • 明らかな低速クエリが1つもないのにCPUが上昇。

有用なチェック:

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

修正は多くの場合アプリケーション層にあります。コネクションプーリングを使用し、適切なプール制限を設定し、タイムアウトを明示的にします。max_connectionsを増やすと時間を稼げますが、各接続が結合、ソート、一時テーブルにメモリを使用する場合、サーバーがさらに深刻にダウンする可能性があります。

一時テーブルとソート

GROUP BY、ORDER BY、DISTINCT、または大規模な結合を含むクエリは、一時テーブルを作成する可能性があります。一部の一時テーブルはメモリに残ります。より大きなものはディスクにあふれます。ディスク一時テーブルは自動的に災害というわけではありませんが、突然の増加はレイテンシのスパイクを説明することがよくあります。

確認:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

次に、クエリ計画を検査します。EXPLAINがUsing temporaryとUsing filesortを示している場合は、インデックスがフィルタと順序を一緒にサポートできるかどうかを尋ねます。例:

SELECT customer_id, created_at, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

(status, created_at)のインデックスは、フィルタリングとソートの両方の作業を削減する可能性があります。tmp_table_sizeを増やすと一部のケースで役立ちますが、これはセッションごとのリスクです。多くのセッションが一度に大きな一時テーブルを割り当てると、メモリがすぐになくなります。

パフォーマンス症状としてのレプリケーションラグ

読み取りがレプリカに送られる場合、プライマリが正常でもレプリケーションラグがデータベースパフォーマンスの問題のように見えることがあります。ユーザーがページを更新しても、自分の変更が表示されません。バックグラウンドジョブが古い行を読み取ります。レポートが一致しません。

MySQLのバージョンに適したツールでレプリカのステータスを確認:

SHOW REPLICA STATUS\G

古いバージョンでは以下を使用:

SHOW SLAVE STATUS\G

ラグは、レプリカでの低速SQL、プライマリからの大きなトランザクション、不十分なレプリカハードウェア、行ごとのメンテナンスジョブ、またはネットワーク問題から発生する可能性があります。修正は、クエリチューニング、大きな書き込みを小さなチャンクに分割、レプリカリソースの改善、または新しい読み取りのルーティング先の変更である可能性があります。

最初に何を変更するか

作業を削減する修正を優先します:

  • 実証済みのホットクエリのインデックスを追加または調整する。
  • より少ない行を読み取るようにクエリを書き換える。
  • ロックを保持するトランザクションを短くする。
  • MySQLが溢れないように接続プールサイズを制限する。
  • 負荷の高いレポートをプライマリから移動する。

容量を増やすだけの修正にはより注意する:

  • max_connectionsを増やす。
  • ソートと結合バッファをグローバルに増やす。
  • 一時テーブル制限を増やす。
  • それらを苦しめるクエリを修正せずにレプリカを追加する。

容量変更には適切な場所がありますが、証拠に基づく必要があります。優れたMySQLトラブルシューティングセッションでは、同じ無駄な作業を行うより大きなサーバーではなく、より少ないデータベース作業量が残ります。