「MySQL」と「PostgreSQL」の安定運用に必要な「監視」の基本を理解する

はじめに

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

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

第8回の今回は、MySQLとPostgreSQLを安定して運用するための「監視」について解説します。データベースの監視では、プロセスが動いているかを確認するだけではなく、CPUやメモリ、ディスクI/OなどのOSリソース、データベース内部の接続数やメモリ、キャッシュの利用状況などを継続的に確認する必要があります。また、MySQLとPostgreSQLには、それぞれデータベース内部の状態を確認するためのさまざまな統計情報や監視機能が用意されています。

今回は監視の基本となる「死活監視」と「リソース監視」を中心に、MySQLとPostgreSQLでどのような情報を確認できるのかを紹介します。SQL文の性能、ロック競合、レプリケーションの状態といった、より詳細な監視については次回で取り上げます。

データベースでは何を監視するのか

データベースの監視では、サーバーやプロセスが動いているかだけではなく、OSやデータベース内部のリソース利用状況、さらに実行されるSQL文の性能まで確認する必要があります。

監視項目は非常に多くありますが、大きく以下の3つのレイヤーに分けて考えることができます。

1. サーバーやデータベースが正常に動作しているか
2. OSおよびデータベース内部のリソースに問題がないか
3. SQL文が期待する性能で処理されているか

表1：データベースで監視する主な項目

今回は、このうち死活監視とOSリソース、データベース内部リソースの監視を中心に扱います。SQL文の性能やロック、レプリケーションの詳細は第9回で解説します。

死活監視とOSリソースの監視

基本となるのは、データベースのプロセスが動作していることや、クライアントから接続できることを確認する死活監視です。プロセスが存在していてもデータベースへの接続やSQL文の実行ができない場合もあるため、実際に接続できることまで確認しておくことが重要です。単に接続できるかの確認だけではなく、実際にSQL文を実行してデータベースが正しく稼働しているかまで確認することが望ましいです。

また、データベースに負荷をかけないようにデータに触れないSELECT 1のようなSQL文で稼働確認をするケースもありますが、必要に応じてアプリケーションと同じ接続経路から軽量なSQL文を実行するなど、実際の利用条件に近い形で確認することも検討します。

なお、データベースはOS上で動作するため、以下のようなOSのリソースも監視対象となります。

• CPU使用率
• メモリおよびスワップの使用量
• ディスクの空き容量
• ディスクI/Oの処理量や待ち時間
• ネットワークの通信量やエラー

特にデータベースでは大量のデータをメモリやストレージとの間で処理するため、CPU使用率だけではなくメモリやディスクI/Oの状況も重要になります。

データベース内部のリソース監視

OSのリソースに余裕があっても、データベース内部で利用できるリソースが不足して性能が低下することがあります。また、OS側だけでは確認できないデータベース固有の状態もあります。

主な監視対象は以下の通りです。

• 現在の接続数やセッション数
• データベース内部で利用しているメモリ
• バッファやキャッシュの利用状況
• データファイルやログへのI/O
• 一時領域の利用状況
• バックグラウンド処理の状態

これらの基本的なリソースの監視に加え、SQL文の性能、ロック待ち、レプリケーションの状態などを確認することで、より詳細な原因調査が可能になります。

データベースを監視するためのツール

実際の監視では、1つのツールだけですべてを監視するのではなく、OSの監視ツール、RDBMSが持つ監視機能、外部の監視システムなどを組み合わせることが一般的です。

表2：データベース監視に利用する主なツール

PostgreSQLのマニュアルでも、データベース内部の統計情報だけではなくps、top、iostat、vmstatなどのOSの監視ツールを併用することが挙げられています。

MySQLにもサーバーの状態を確認するステータス変数があり、SHOW STATUSやmysqladminから参照できます。さらに詳細な情報はパフォーマンス・スキーマに収集され、sysスキーマではパフォーマンス・スキーマの情報を監視や性能分析に利用しやすい形で参照できます。

PostgreSQLではpg_stat_activityをはじめとする多数の統計情報ビューが用意されており、接続中のセッションやデータベース、テーブル、I/Oなどの状態をSQL文で確認できます。

継続的な監視では、これらの情報を外部の監視システムへ収集する構成もよく使われます。例えば、Prometheusでは監視対象からメトリクスを取得するExporterという仕組みがあり、MySQL向けのExporterなどが公開されています。収集したデータをGrafanaなどで可視化し、異常を検出した場合に通知する構成も可能です。

また、商用の監視製品やクラウド・ベースの監視サービスでは、データベース単体だけではなくOS、アプリケーション、ネットワークなどの情報をまとめて監視できるものもあります。マネージド・データベースではOSに直接アクセスできない場合もあるため、サービス側で提供されるメトリクスやログを利用することになります。

ここまで紹介した監視の観点はMySQLとPostgreSQLに共通しています。一方で、データベース内部の状態をどのテーブルや変数から取得するかは製品によって異なります。

ここからは、MySQLとPostgreSQLそれぞれについて、基本的な監視時に確認しておきたい情報を見ていきます。

MySQLにおける監視の要点

MySQLでは、サーバーの稼働状況やリソースの利用状況などを確認するためにステータス変数、パフォーマンス・スキーマ、sysスキーマといった複数の仕組みが用意されています。

ステータス変数はMySQLサーバーの状態を数値として確認するための基本的な仕組みです。SHOW STATUSで参照できるほか、パフォーマンス・スキーマやmysqladmin extended-statusからも取得できます。

パフォーマンス・スキーマではI/O、メモリなど、より詳細な情報をテーブルとして参照できます。さらにsysスキーマには、パフォーマンス・スキーマに蓄積された情報を運用や性能分析に利用しやすい形で参照するためのビューやプロシージャが用意されています。

【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/show-status.html
・https://dev.mysql.com/doc/refman/26.7/en/performance-schema.html
・https://dev.mysql.com/doc/refman/26.7/en/sys-schema.html

接続中のセッションと実行中の処理を確認する

MySQLで現在の接続や実行中の処理を確認する基本的な方法がSHOW PROCESSLISTです。MySQLサーバーに接続しているユーザーや接続元、利用しているデータベース、現在実行している処理、処理の継続時間などを確認できます。PostgreSQLのpg_stat_activityに近い役割を持つ機能です。

SHOW PROCESSLIST;

実行結果には、接続を識別するId、ユーザーを示すUser、接続元のHost、利用中のデータベースを示すdb、現在の処理を示すCommand、その状態が継続している秒数を示すTime、処理の状態を示すState、実行中のSQL文を示すInfoなどが表示されます。

SHOW FULL PROCESSLISTを使用すると、InfoにSQL文を省略せず表示できます。例えば、長時間実行されているSQL文や多数の接続が発生している場合に、どの接続がどのような処理を行っているかを確認する際に利用できます。PROCESS権限を持つユーザーは他のユーザーを含むすべてのスレッドを確認できますが、持たない場合は原則として自分自身のスレッドだけが表示されます。

同様の情報はパフォーマンス・スキーマのprocesslistテーブルやthreadsテーブルからも取得できます。またsysスキーマのprocesslistビューでは、パフォーマンス・スキーマの情報をもとにSHOW PROCESSLISTより詳細な情報を確認できます。継続的な監視やSQL文による分析では、これらのテーブルやビューを利用する方法もあります。

【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/show-processlist.html
・https://dev.mysql.com/doc/refman/26.7/en/performance-schema-processlist-table.html
・https://dev.mysql.com/doc/refman/26.7/en/sys-processlist.html

監視に利用するステータス変数とパフォーマンス・スキーマ

MySQLには非常に多くのステータス変数やパフォーマンス・スキーマのテーブルがあります。すべてを常時監視する必要はありませんが、接続、InnoDB、I/O、 メモリなど、データベースの性能や可用性に影響する項目は継続的に確認しておくことが重要です。

代表的な監視項目をまとめると、以下のようになります。

表3：MySQLで監視しておきたい主な項目

MySQL 9.7 LTSや26.7イノベーション・リリースでもThreads_connectedやThreads_runningをはじめ、InnoDBのバッファ・プール、I/O、REDOログなど多数のステータス変数が提供されています。

例えば、接続数を確認する場合は、次のように必要なステータス変数だけを参照できます。

SHOW GLOBAL STATUS
WHERE Variable_name IN
      ('Threads_connected',
       'Threads_running',
       'Connections',
       'Aborted_connects');

Threads_connectedだけを見ても、それが通常の状態なのか異常な状態なのかは判断できません。設定されている最大接続数や通常時の接続数と比較し、値の増減を確認することが重要です。また、累積されるカウンタについては、その時点の値そのものより一定期間にどの程度増加したかを見る必要があります。

InnoDBのバッファ・プール関連では、Innodb_buffer_pool_read_requestsとInnodb_buffer_pool_readsを確認すると、論理的な読み取り要求に対してストレージからの物理読み取りがどの程度発生しているかを確認できます。一方、Innodb_log_waitsが増加している場合には、ログ・バッファの不足によってフラッシュ待ちが発生していないかを調査する必要があります。

【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/server-status-variable-reference.html

パフォーマンス・スキーマを利用すると、ステータス変数よりさらに細かい単位で状態を確認できます。例えば、ファイル単位のI/Oはfile_summary_by_instance、テーブル単位のI/Oはtable_io_waits_summary_by_tableで確認できます。

メモリについてもパフォーマンス・スキーマから詳細な情報を確認できます。memory_summary_global_by_event_nameではMySQL内部の機能ごとのメモリ利用状況を確認でき、sysスキーマのmemory_global_by_current_bytesを利用すると、同じ情報をより参照しやすい形式で表示できます。ただし、パフォーマンス・スキーマのメモリ用instrumentの多くはデフォルトでは無効になっているため、詳細なメモリ監視を行う場合には事前に収集対象を確認する必要があります。

【参考URL】
・https://dev.mysql.com/doc/refman/26.7/en/monitor-mysql-memory-use.html

MySQLの監視や診断に利用できるツール

MySQLには、SQL文でパフォーマンス・スキーマなどを確認する以外にも、サーバーの状態確認や障害調査に利用できるツールがあります。

mysqladmin

mysqladminはMySQLに標準で含まれる管理用のクライアントです。監視に関連する代表的なコマンドには以下があります。

• mysqladmin ping: MySQLサーバーが応答しているか確認
• mysqladmin status: サーバーの簡易的な状態を表示
• mysqladmin extended-status: ステータス変数を表示
• mysqladmin processlist: 実行中のスレッドを表示

mysqladmin pingは簡単な死活監視に利用できます。ただし、認証に失敗してAccess deniedとなった場合でも「MySQLサーバー自体は動作している」と判断して終了ステータス0を返します。そのため、アプリケーションと同じ条件でSQL文まで実行できることを確認したい場合には、別途実際の接続テストを行う必要があります。

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

MySQL Diagnostic Monitor

MySQL 9.5で、新しい診断用クライアントとしてmysqldm (MySQL Diagnostic Monitor)が追加されました。mysqldmはあらかじめ定義されたSQL文をMySQLサーバーに対して実行し、診断に必要な情報をJSON形式で収集する、問題発生時にサーバーの診断情報をまとめて取得する用途のツールです。mysqldmは、サポートに問い合わせる際に必要な情報を集めて問い合わせに添付することを意図したツールとなり、MySQL Enterprise Editionでのみ利用可能です。

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

PostgreSQLにおける監視の要点

PostgreSQLでは、接続中のセッションやテーブルへのアクセス、I/Oなど、多くの情報をシステム・ビューから確認できます。また、PostgreSQLに付属する拡張機能や追加モジュールを利用することで、さらに詳しい情報を取得できます。

PostgreSQLの監視機能の中心となるのが、累積統計情報を収集する仕組みです。テーブルやインデックスへのアクセス、VACUUMやANALYZEの実行状況、I/Oなどが統計として収集されます。これとは別に、pg_stat_activityのように、現在のセッション状態を確認するための情報も用意されています。

【参考URL】
・https://www.postgresql.org/docs/18/monitoring-stats.html

PostgreSQLで監視する主なビュー

PostgreSQLには多数のpg_stat_*ビューがあります。基本的な監視で利用する代表的なビューをまとめると以下のようになります。

表4：PostgreSQLで監視しておきたい主なビュー

pg_stat_databaseでは、データベースごとに接続数、コミットやロールバックの回数、ブロックの読み取り数、PostgreSQLのバッファ・キャッシュから読み取れた回数などを確認できます。

pg_stat_ioではバックエンドの種類、I/O対象、I/Oのコンテキストごとにクラスタ全体のI/O統計を確認できます。なお、PostgreSQLのI/O統計だけでは、OSのページ・キャッシュから読み取ったのか、実際にストレージから読み取ったのかを区別できない場合があります。PostgreSQLのマニュアルでも、I/O性能を確認する際にはPostgreSQL内部の統計情報とOSの監視ツールを組み合わせることが推奨されています。

【参考URL】
・https://www.postgresql.org/docs/18/monitoring-stats.html

セッションの状態を監視する

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

例えば、現在接続しているうちのidleではないセッションの状態を確認するには以下のようなSQL文を利用できます。

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

長時間継続しているセッションやトランザクションは、他の処理やVACUUMなどに影響する可能性があります。ロックや待機イベントを含めた詳細な確認については次回で解説します。

VACUUMとAutovacuumを監視する

PostgreSQL固有の運用で特に重要なのがVACUUMの監視です。PostgreSQLではUPDATEやDELETEを実行しても古い行バージョンはすぐには物理的に削除されません。不要になった行バージョンを回収して領域を再利用できるようにするため、定期的にVACUUMを実行する必要があります。

通常はAutovacuumが自動的に処理します。pg_stat_user_tablesでは、以下のような値を確認できます。

• n_live_tup: 有効な行数の推定値
• n_dead_tup: 不要になった行数の推定値
• n_mod_since_analyze: 最後のANALYZE以降に変更された行数の推定値
• last_vacuum: 最後に手動VACUUMが実行された時刻
• last_autovacuum: 最後にAutovacuumが実行された時刻
• last_analyze: 最後に手動ANALYZEが実行された時刻
• last_autoanalyze: 最後にAutoanalyzeが実行された時刻
• autovacuum_count: Autovacuumが実行された回数

これらの情報から、dead tupleが多く残っているテーブルや、更新が多いにもかかわらずAutovacuumが十分に実行されていないテーブルを探すことができます。

実行中のVACUUMについてはpg_stat_progress_vacuumで進捗を確認できます。処理中のテーブル、現在のフェーズ、スキャン済みのヒープ・ブロック数などが表示されます。AutovacuumによるVACUUMも監視対象になります。

【参考URL】
・https://www.postgresql.org/docs/18/routine-vacuuming.html
・https://www.postgresql.org/docs/18/monitoring-stats.html
・https://www.postgresql.org/docs/18/progress-reporting.html

さらに注意したいのが、トランザクションID(XID)の「Wraparound」と呼ばれる現象です。PostgreSQLの通常のXIDは32ビットで、約43億トランザクションで値が周回します。

一方、XIDの新旧を判定できる範囲には制約があるため、古いXIDを持つ行をVACUUMによって適切に凍結する必要があります。この処理が長期間行われないと、最終的には新しいトランザクションIDを割り当てられなくなる可能性があります。

【参考URL】
・https://www.sraoss.co.jp/tech-blog/pgsql/transaction-id-wraparound/

データベース単位ではpg_database.datfrozenxid、テーブル単位ではpg_class.relfrozenxidから古いトランザクションIDの状態を確認できます。PostgreSQLのマニュアルでは、例えば以下のSQL文でデータベースごとの古いトランザクションIDの年齢を確認する方法が紹介されています。

SELECT datname,
       age(datfrozenxid)
  FROM pg_database
 ORDER BY age(datfrozenxid) DESC;

通常はAutovacuumによりWraparoundを防ぐ処理が実行されますが、長時間トランザクションなどがVACUUM処理を妨げることもあるため、継続的な監視が重要です。

pg_buffercacheで共有バッファの状態を確認する

pg_buffercacheは、PostgreSQLの共有バッファ・キャッシュの現在の状態を確認するための拡張機能です。どのテーブルやインデックスのブロックがバッファに存在するか、ダーティ・ページかどうか、利用回数などを確認できます。

PostgreSQL 18ではpg_buffercache_summary()も用意されており、共有バッファ全体の状態をより低いコストで集約して確認できます。詳細なpg_buffercacheビューを頻繁に読み取るのではなく、目的に応じて使い分けることができます。

【参考URL】
・https://www.postgresql.org/docs/18/pgbuffercache.html

監視に関する設定パラメータ

PostgreSQLの監視では、統計情報を見るだけではなく「どの情報を収集するか」の設定も重要です。

表5：監視に関連する主な設定パラメータ

統計情報の収集には、一定のオーバーヘッドがあります。特にI/O時間の計測などを有効にする場合には、監視による負荷も考慮する必要があります。現在の設定値はSHOWだけではなくpg_settingsビューからも確認できます。

pg_settingsでは設定値に加えて、設定元のファイルや行番号、変更後に再起動が必要かどうかを示すpending_restartなども確認できます。

【参考URL】
・https://www.postgresql.org/docs/18/monitoring-stats.html
・https://www.postgresql.org/docs/18/view-pg-settings.html
・https://www.postgresql.org/docs/18/runtime-config-logging.html

監視用ロールのpg_monitor

監視システムからPostgreSQLに接続する場合、監視のためだけにスーパーユーザー権限を与えることは避けるべきです。PostgreSQLには監視用途の定義済みロールとしてpg_monitorが用意されています。

pg_monitorはpg_read_all_settings、pg_read_all_stats、pg_stat_scan_tablesを含み、通常は制限されている各種の設定値や統計情報を監視用ユーザーから参照できるようにします。

例えば、監視用のロールに以下のように付与できます。

GRANT pg_monitor TO monitor_user;

ただし、pg_monitorからは通常ユーザーには見えない監視情報も参照できるようになるため、監視用アカウント自体のアクセス管理も必要です。

【参考URL】
・https://www.postgresql.org/docs/18/predefined-roles.html

PostgreSQLの監視に利用できるツール

PostgreSQLには、SQL文で統計情報を参照する以外にも、死活監視やGUIによる状態確認に利用できるツールがあります。

pg_isready

pg_isreadyはPostgreSQLに付属する接続状態確認用のコマンドです。下記の戻り値により状態を確認できるため、監視システムやスクリプトからの死活確認に利用できます。

• 0: 正常受付
• 1: サーバー起動中などで接続拒否
• 2: 応答なし
• 3: パラメータが無効などにより試行なし

pg_isready -h localhost -p 5432

ただし、pg_isreadyでは正しいユーザー名、パスワード、データベース名を指定しなくてもサーバーの接続状態を確認できます。そのため、アプリケーション用ユーザーで実際にログインしSQL文を実行できることまで確認したい場合には、別途接続テストを実施する必要があります。

【参考URL】
・https://www.postgresql.jp/document/18/html/app-pg-isready.html

pgAdmin

PostgreSQL向けのオープンソースGUI管理ツール「pgAdmin」にも監視機能があります。ダッシュボードではセッション、トランザクション数、タプルの更新状況、ブロックI/Oなどをグラフで確認できます。また、セッションやサーバーのログ、システム統計などもGUIから確認できます。

SQL文からpg_stat_*ビューを参照すると詳細な調査ができますが、現在の状態を視覚的に確認したい場合にはpgAdminを利用する方法もあります。

【参考URL】
・https://www.pgadmin.org/docs/pgadmin4/latest/

MySQLとPostgreSQLの基本的な監視機能の比較

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

表6：MySQLとPostgreSQLの基本的な監視機能の比較

MySQLとPostgreSQLでは監視情報を取得する仕組みは異なりますが、接続数、メモリ、キャッシュ、I/Oなど、基本的な監視対象には多くの共通点があります。

一方で、MySQLではInnoDBのバッファ・プールやREDOログ、PostgreSQLではVACUUMやトランザクションIDなど、それぞれの内部構造に応じた固有の監視項目もあります。OSのリソース監視とあわせ、データベース内部の状態を継続的に確認することが重要です。

まとめ

今回は、MySQLとPostgreSQLの監視のうち、死活監視やOSリソース、データベース内部のリソースを中心に解説しました。MySQLではステータス変数、パフォーマンス・スキーマ、sysスキーマから接続数やInnoDBのバッファ・プール、I/O、メモリなどの状態を確認できます。

PostgreSQLでもpg_stat_*ビューなどから接続やI/O、テーブルの利用状況を確認できるほか、VACUUMやトランザクションIDなどPostgreSQL固有の状態も監視する必要があります。

監視では、ある時点の値だけを見るのではなく、通常時の状態を把握し、その値がどのように変化しているかを見ることが重要です。

次回はさらに踏み込み、SQL文の処理性能、ロックによる待機、レプリケーションの状態をMySQLとPostgreSQLでどのように監視するかを解説します。