「MySQL」と「PostgreSQL」の「SQL性能」「ロック」「レプリケーションの監視」手法を比較する

はじめに

この連載では、オープンソースのリレーショナル・データベース(RDB)の「MySQL」と「PostgreSQL」を使いこなすヒントを、両製品の共通点と違いを確認しながら解説していきます。

現在、それぞれの製品をベースとしたクラウド・データベースは複数存在しています。この連載では製品の比較を多く行いますが、製品機能や仕様の優劣についてを論ずる目的のものではないことはあらかじめご了承ください。

第8回は、MySQLとPostgreSQLの監視について、死活監視やOSリソース、データベース内部のリソースを中心に解説しました。しかし、CPUやメモリに余裕があり、データベース自体も正常に稼働していても、アプリケーションの処理が遅くなることがあります。その原因として考えられるのが、負荷の高いSQL文、トランザクション間のロック競合、レプリケーションの遅延などです。これらは単純な死活監視だけでは把握できないため、データベース内部の統計情報を使って原因を調査する必要があります。

第9回の今回は、MySQLとPostgreSQLについて「SQL文の処理性能」「ロック・待機状態」「レプリケーション」という3つの観点から、監視すべき情報と調査に利用できる機能を見ていきます。

SQL文の性能、ロック、レプリケーションを監視する

データベースやOSが正常に動作していても、特定のSQL文によってアプリケーション全体の応答時間が悪化する場合があります。また、SQL文そのものの処理時間ではなく、別のトランザクションが保持しているロックを待っていることが原因になる場合もあります。

高可用性構成では、プライマリやレプリカ、スタンバイが稼働していることだけでは十分ではありません。データの複製が継続していることや、適用処理が大きく遅れていないことも確認する必要があります。

このような問題を調査する際には、主に以下の情報を確認します。

• SQL文の実行回数と実行時間
• データの読み取り量や処理行数
• 実行計画
• ロックや待機イベント
• レプリケーションの接続状態や適用状況
• レプリカやスタンバイの遅延

単純に1回の実行時間が長いSQL文だけが問題になるとは限りません。1回の処理時間は短くても、大量に実行されるSQL文はデータベース全体に大きな負荷を与える可能性があります。そのため、平均実行時間だけではなく、実行回数や累積の処理時間もあわせて確認します。

MySQLでSQL文の性能、ロック、レプリケーションを監視する

MySQLでは、パフォーマンス・スキーマやsysスキーマを利用すると、SQL文単位の統計、ロックの状態、レプリケーションの接続や適用処理などを確認できます。

SQL文の性能を監視する

データベース全体のリソースに問題がなくても、一部のSQL文によって負荷が高くなっている場合があります。MySQLではパフォーマンス・スキーマのevents_statements_summary_by_digestを利用すると、「SQL文を正規化」した上でSHA-256のハッシュ値としたDigest単位で実行回数や実行時間などを集計できます。

なお、ここでの「正規化」はデータベース設計におけるテーブル構造の正規化ではなく、SQL文の構造は維持しながら条件式の値などのリテラル値を?に置き換えるなどの変換や、スペースの調整を行うMySQL内部での処理です。リテラル値だけが異なるSQL文をまとめて確認できるため、特定の1回の処理ではなく、データベース全体に大きな負荷を与えているSQL文を探す用途に適しています。

【参考URL】
https://dev.mysql.com/doc/refman/26.7/en/performance-schema-statement-summary-tables.html

パフォーマンス・スキーマを直接参照することもできますが、sysスキーマのstatement_analysisビューを利用すると、Digest単位のSQL統計をより簡単に確認できます。statement_analysisには実行回数、総処理時間、平均処理時間、ロック待ち時間、参照行数、フル・テーブル・スキャンの回数などが含まれ、デフォルトでは総処理時間の大きいSQL文から表示されます。

例えば、総処理時間の大きいSQL文を確認するには次のようなSQL文を利用できます。

SELECT query,
       exec_count,
       total_latency,
       avg_latency,
       rows_examined
  FROM sys.statement_analysis
 ORDER BY total_latency DESC
 LIMIT 10;

1回の実行時間が長いSQL文だけではなく、実行時間は短くても非常に多く実行されているSQL文がシステム全体の負荷を高めている場合もあります。そのため、avg_latencyだけではなくtotal_latencyやexec_countをあわせて確認することが重要です。

【参考URL】
https://dev.mysql.com/doc/refman/26.7/en/sys-statement-analysis.html

個々のSQL文を詳しく調査する場合にはEXPLAINやEXPLAIN ANALYZEも利用します。EXPLAINはMySQLが選択した実行計画を確認するための機能です。EXPLAIN ANALYZEではSQL文を実際に実行し、実行計画に加えて各処理に要した時間や実際の行数などを確認できます。

【参考URL】
https://dev.mysql.com/doc/refman/26.7/en/explain.html

もう1つの方法がスロー・クエリ・ログです。long_query_timeを超え、min_examined_row_limitで指定した行数以上を参照したSQL文を記録できるため、特定の遅いSQL文を後から調査する用途に利用できます。パフォーマンス・スキーマによるDigest単位の集計と、スロー・クエリ・ログによる個々のSQL文の記録は目的が異なるため、必要に応じて組み合わせて利用します。

【参考URL】
https://dev.mysql.com/doc/refman/26.7/en/slow-query-log.html

ロック待ちを監視する

パフォーマンス・スキーマでは、ロック競合の状態も確認できます。performance_schema.data_locksには現在保持されているロックや要求されているロック、performance_schema.data_lock_waitsにはロック待ちの関係が記録されます。data_lock_waitsでは、どのロック要求がどの保持済みロックによって待たされているかを確認できます。

さらにINFORMATION_SCHEMA.INNODB_TRXと組み合わせることで、ロックを保持しているトランザクションや実行中のSQL文まで追跡できます。ロック待ちは一時的に発生すること自体が問題とは限りませんが、長時間継続する場合や、多数のトランザクションを待たせている場合には、原因となるSQL文やトランザクションを調査する必要があります。

【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/performance-schema-data-locks-table.html
・https://dev.mysql.com/doc/refman/26.7/en/performance-schema-data-lock-waits-table.html
・https://dev.mysql.com/doc/refman/26.7/en/innodb-information-schema-transactions.html

レプリケーションと高可用性構成の監視

第7回で解説したレプリケーションやInnoDB Clusterを利用している場合には、MySQLサーバー単体だけではなく、データの複製状態も監視する必要があります。

従来型のレプリケーションでは、パフォーマンス・スキーマのreplication_connection_statusやreplication_applier_status, replication_applier_status_by_workerなどから、レプリケーション接続やトランザクション適用処理の状態を確認できます。マルチスレッドで適用している場合にはreplication_applier_status_by_workerを利用することで、各ワーカーの状態やエラーを確認できます。

グループ・レプリケーションの場合には、以下のパフォーマンス・スキーマのテーブルが特に重要です。

• replication_group_members: 各メンバーの状態やロールを確認
• replication_group_member_stats: メンバーごとのトランザクション処理状況を確認
• replication_group_communication_information: グループ通信に関する情報を確認
• replication_connection_status: グループから受信し、適用待ちとなっているトランザクションなどを確認
• replication_applier_status: グループ・レプリケーションの適用チャネルの状態を確認

これらの情報はグループ・レプリケーションの状態確認や、処理が遅れているメンバーの調査などに利用できます。

【参考URL】
https://dev.mysql.com/doc/refman/26.7/en/group-replication-monitoring.html

InnoDB Clusterとして構成している場合には、MySQL ShellのAdminAPIも利用できます。Cluster.status()ではクラスタ全体の状態を確認でき、extendedオプションを指定すると、より詳細な情報を取得できます。

cluster.status({extended: true})

【参考URL】
https://dev.mysql.com/doc/mysql-shell/26.7/en/mysql-innodb-cluster.html

INSTALL COMPONENT 'file://component_telemetry';

PostgreSQLでSQL文の性能、ロック、レプリケーションを監視する

PostgreSQLでは、pg_stat_activityやpg_locksなどのシステム・ビューに加えて、pg_stat_statementsやauto_explainなどを利用することで現在の待機状態からSQL文単位の累積統計まで確認できます。また、ストリーミング・レプリケーションではWALの送信、受信、適用状況を各種統計ビューから確認できます。

セッションと待機状態を監視する

PostgreSQLで最も基本的な監視用ビューの1つがpg_stat_activityです。現在接続しているユーザー、クライアント、実行中のSQL文、トランザクションやSQL文の開始時刻、待機イベントなどを確認できます。

例えば、idle以外のセッションと、そのセッションで実行中または直前に実行されたSQL文を確認するには以下のようなSQL文を利用できます。

SELECT pid,
       usename,
       datname,
       state,
       wait_event_type,
       wait_event,
       query_start,
       query
  FROM pg_stat_activity
 WHERE state <> 'idle'
 ORDER BY query_start;

特に確認したいのがstate, wait_event_type, wait_eventです。stateがactiveでも、SQL文が実際にCPUを使って処理を進めているとは限りません。wait_eventに値がある場合には、I/Oやロック、別プロセスとの通信など、何らかのイベントを待っている可能性があります。PostgreSQLでは待機イベントをIO, Lock, LWLock, Clientなどの種類に分けて確認できます。

また、idle in transactionのセッションにも注意が必要です。トランザクションが開始されたにもかかわらず、何も処理が行われていない状態がidle in transactionとして出力されます。SQL文を実行していなくてもトランザクションを開始した状態が長時間継続すると、VACUUMが不要な行バージョンを回収できないなどの影響が発生する可能性があるため、長時間継続しているセッションは調査対象となります。

【参考URL】
https://www.postgresql.jp/document/18/html/monitoring-stats.html

ロック待ちを監視する

PostgreSQLのロック状況はpg_locksで確認できます。どのセッションがどのロックを取得しているか、またロックを取得できずに待機しているかを確認できます。PostgreSQLのマニュアルでも、pg_locksはロック競合がデータベース性能へ与える影響を調査するための監視手段として紹介されています。

またpg_blocking_pids()を利用すると、指定したプロセスをブロックしているセッションのPIDを取得できます。例えば、ロック待ちしているセッションと、そのセッションをブロックしているPIDを確認するには以下のようなSQL文を利用できます。

SELECT pid,
       usename,
       query,
       wait_event_type,
       wait_event,
       pg_blocking_pids(pid) AS blocking_pids
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;

ロック待ちは一時的に発生すること自体が問題とは限りません。重要なのは、長時間継続しているロック待ちや、アプリケーションの応答時間に影響している競合を検出することです。

ログを使って継続的に監視する場合にはlog_lock_waitsも利用できます。log_lock_waitsを有効にすると、deadlock_timeoutを超えてロック取得を待った場合にログが記録されます。

【参考URL】
・https://www.postgresql.jp/document/18/html/monitoring-locks.html
・https://www.postgresql.jp/document/18/html/functions-info.html
・https://www.postgresql.jp/document/18/html/runtime-config-logging.html

SQL文の性能を監視する

PostgreSQLでSQL文単位の性能統計を収集する代表的な機能がpg_stat_statementsです。PostgreSQLに付属する拡張機能で、サーバーで実行されたSQL文について、実行回数、合計実行時間、平均実行時間、処理行数、バッファへのアクセスなどを集計できます。

利用するには、postgresql.confのshared_preload_librariesにpg_stat_statementsを指定する必要があり、追加や削除にはPostgreSQLの再起動が必要です。

shared_preload_libraries = 'pg_stat_statements'

また、利用するデータベースでCREATE EXTENSIONを実行します。

CREATE EXTENSION pg_stat_statements;

例えば、累積実行時間が長いSQL文を確認するには以下のように実行できます。

SELECT query,
       calls,
       total_exec_time,
       mean_exec_time,
       rows,
       shared_blks_hit,
       shared_blks_read
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 10;

MySQLのパフォーマンス・スキーマと同様に、1回だけ非常に遅いSQL文だけではなく、個々の実行時間は短くても大量に実行されてデータベース全体の負荷を高めているSQL文を見つけるためにも利用できます。calls、 total_exec_time、mean_exec_timeなどを組み合わせて確認することが重要です。

【参考URL】
https://www.postgresql.jp/document/18/html/pgstatstatements.html

問題のあるSQL文を特定した後はEXPLAINを利用して実行計画を確認します。EXPLAIN ANALYZEを利用するとSQL文を実際に実行し、プランナーによる推定行数と実際の処理行数、各処理にかかった時間などを確認できます。

ただし、EXPLAIN ANALYZEは実際に対象のSQL文を実行します。UPDATEやDELETEなどに対して実行するとデータも変更されるため、本番環境での利用には注意が必要です。

【参考URL】
https://www.postgresql.jp/document/18/html/using-explain.html

WALとレプリケーションを監視する

第7回で解説したストリーミング・レプリケーションを利用している場合には、スタンバイが接続されていることだけではなく、WALがどこまで転送、書き込み、適用されているかを監視する必要があります。

プライマリ側では、pg_stat_replicationから直接接続しているスタンバイごとの状態を確認できます。主な項目には以下があります。

• state: WAL senderの状態
• sent_lsn: 最後に送信したWAL位置
• write_lsn: スタンバイが書き込んだWAL位置
• flush_lsn: スタンバイがディスクへフラッシュしたWAL位置
• replay_lsn: スタンバイが適用済みのWAL位置
• write_lag: 書き込みまでの遅延
• flush_lag: フラッシュまでの遅延
• replay_lag: 適用までの遅延
• sync_state: 同期レプリケーション上の状態

ただし、write_lag、 flush_lag、 replay_lagは「現在の遅れを取り戻すまであと何秒かかるか」を表す値ではありません。最近のWALをそれぞれの段階まで処理するのに要した時間を表す値です。完全に追いついてWALの更新がなくなると、しばらくしてNULLになることもあります。監視システムではこの性質を考慮する必要があります。

スタンバイ側ではpg_stat_wal_receiverからWAL Receiverの状態を確認できます。また、クラスタ全体のWAL生成量はpg_stat_walで確認できます。

【参考URL】
https://www.postgresql.jp/document/18/html/monitoring-stats.html

レプリケーション・スロットを利用している場合にはpg_replication_slotsも重要です。レプリケーション・スロットは利用者が必要とするWALを保持するため、利用されなくなったスロットを放置すると大量のWALが保持され、ストレージ容量を圧迫する可能性があります。

pg_replication_slotsでは、スロットが現在利用されているかを示すactive、保持が必要な最も古いWAL位置を示すrestart_lsn、WALの利用可能状態を示すwal_statusなどを確認できます。

【参考URL】
https://www.postgresql.jp/document/18/html/view-pg-replication-slots.html

SQL性能の監視に役立つ追加モジュールとツール

auto_explain

auto_explainは、一定時間以上かかったSQL文の実行計画を自動的にログへ出力するモジュールです。アプリケーション上でいつ発生するか分からない遅いSQL文の実行計画を、手動でEXPLAINを実行せずに収集できます。

例えば以下のように設定すると、3秒以上かかったSQL文の実行計画を記録できます。

session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'

auto_explain.log_analyzeを有効にすると実際の実行統計も記録できますが、実行時のオーバーヘッドが大きくなる可能性があります。特に本番環境では、収集する情報量と性能への影響を確認した上で利用する必要があります。

【参考URL】
https://www.postgresql.jp/document/18/html/auto-explain.html

pgBadger

PostgreSQLのログを後から分析するオープンソース・ツールとして広く利用されているものにpgBadgerがあります。PostgreSQLのログを解析し、遅いSQL文、実行時間の長いSQL文、一時ファイル、エラーなどをHTMLレポートとして可視化できます。

pg_stat_statementsがPostgreSQL内部に蓄積された統計情報を参照するのに対して、pgBadgerはPostgreSQLが出力したログを分析するツールです。必要な情報がログに出力されるよう、log_min_duration_statementやlog_line_prefixなどの設定を事前に検討しておく必要があります。 

【参考URL】
https://github.com/darold/pgbadger

SQL性能とロック監視に関連する設定パラメータ

PostgreSQLでは、SQL文やロックの監視に利用する情報を収集するための設定も重要です。 

表1： SQL性能とロック監視に関連する主な設定パラメータ

ログへ出力する情報を増やすと、性能への影響やログ容量の増加にもつながります。監視に必要な情報と、その収集コストのバランスを考えて設定します。

【参考URL】
・https://www.postgresql.jp/document/18/html/monitoring-stats.html
・https://www.postgresql.jp/document/18/html/runtime-config-logging.html

MySQLとPostgreSQLのSQL性能・ロック・レプリケーション監視の比較

ここまで紹介した監視機能を比較すると、以下のようになります。

表2：MySQLとPostgreSQLのSQL性能・ロック・レプリケーション監視の比較

SQL文の性能、ロック、レプリケーションは、いずれもOSレベルの監視だけでは原因を把握しにくい項目です。MySQLではパフォーマンス・スキーマやsysスキーマ、PostgreSQLではpg_stat_*ビューやpg_stat_statementsを中心にデータベース内部の情報を確認します。

両製品とも、現在の状態だけではなく、SQL文の累積負荷や待機の原因、複製処理がどこまで進んでいるかを確認することが重要です。

まとめ

今回は、MySQLとPostgreSQLについて、SQL文の性能、ロック、レプリケーションの監視方法を解説しました。SQL文の監視では、1回の実行時間が長いSQL文だけではなく、短時間でも大量に実行され、データベース全体で大きな負荷となっているSQL文を見つけることが重要です。

また、性能低下の原因がSQL文そのものではなく、別のトランザクションによるロック待ちである場合もあります。高可用性構成では、データベースが稼働していることだけでは十分ではありません。レプリケーションが正常に動作し、プライマリとレプリカやスタンバイの間でデータの複製が継続していることも確認する必要があります。

MySQLのパフォーマンス・スキーマやsysスキーマ、PostgreSQLのpg_stat_*ビューやpg_stat_statementsなどを利用することで、こうした問題をデータベース内部から確認できます。OSの監視情報とあわせて利用し、通常時の状態を把握しておくことが、障害や性能低下が発生した際の迅速な原因特定につながります。

次回は、長期間安定運用するための本番環境のメンテナンスを解説予定です。