PostgreSQLの拡張機能「system_stats」のご紹介

PostgreSQLはシステムの中で稼働するソフトウェアの一つです。そのため、システム状態を監視する際には、データベースとOSの両方の状態を統合的に監視することがとても重要です。この記事では、そのような課題に対する解決策の一つとして、EDB社が提供している「system_stats」拡張機能をご紹介します。

この拡張機能を活用することで、PostgreSQLからOS情報を直接取得でき、より効率的なシステム監視が可能になります。

※system_stats はコミュニティ版の PostgreSQL でも利用可能ですが、弊社ではEDBのサブスクリプション契約でのみサポートを提供しております。本ブログもEDB Postgres Advanced Server（EPAS）を前提として寄稿しています。

EDB社が提供するPostgreSQL拡張機能「system_stats」は、OSレベルのシステム情報をPostgreSQLから直接取得できるようにする便利なツールです。この拡張機能を使用することで、以下の情報を簡単に取得できます。

system_statsの導入は以下の手順で行います。

1.パッケージのインストール

利用中の EPASバージョンに合った system_stats パッケージをインストールします。EPAS を利用している場合は既にYUMリポジトリが構成されているため、dnf（yum）コマンドでインストールを行います。

$ dnf install edb-as<バージョン>-system-stats3

2.拡張機能の作成

system_stats を使用するデータベースに接続し、CREATE EXTENSION コマンドを実行して拡張機能のインストールを完了します。

edb=# CREATE EXTENSION system_stats

system_statsには、メモリーやCPU使用率を取得するための関数が用意されています。以下はその関数の一例です。

-- OS情報の取得
SELECT * FROM pg_sys_os_info;


-- CPU情報の取得
SELECT * FROM pg_sys_cpu_info;


-- メモリー使用状況の取得
SELECT * FROM pg_sys_memory_info;


-- プロセス毎のCPU使用率、メモリー使用率を取得
SELECT * FROM pg_sys_cpu_memory_by_process;

関数の一覧は system_stats の GitHub ページに在るREADME をご参照ください。

system_statsで提供される関数とpg_stat_activityを組み合わせることで、より詳細なパフォーマンス監視が可能になります。以下はアクティブなセッションのパフォーマンスを監視するクエリ例です。

SELECT 
        ss.pid
        , name
        , cpu_usage
        , memory_usage
        , usename
        , application_name
        , backend_start
        , state, wait_event_type
        , wait_event
        , query
FROM pg_sys_cpu_memory_by_process() ss
LEFT OUTER JOIN pg_stat_activity psa　ON ss.pid = psa.pid
WHERE psa.state not like 'idle%';

上記のクエリをジョブで定期的に実行し、クエリ結果をテーブルに残しておけばパフォーマンス問題を遡って確認することができます。PostgreSQLには過去に遡ってパフォーマンス問題を調査する標準機能が無いため、問題発生後に当時のセッション状況を確認できる方法は重宝します。以下に具体的な手順とコマンドの例を示します。

定期的にパフォーマンス情報を取得するためにDBMS_JOBをセットアップします。OSSのPostgreSQLで利用する場合、定期的な情報取得の実行はpg_dbms_job拡張モジュールやcronで実装する必要があります。

--次のパラメータを設定します
edb_job_scheduler.database_list = '<パフォーマンス情報を取得するDB名>'
shared_preload_libraries = '<既存の設定値>, $libdir/edb_job_scheduler'


--設定を反映するためにDBを再起動します
$ pg_ctl restart


--dbms_job拡張機能を作成します
edb=# CREATE EXTENSION dbms_job CASCADE;

CREATE TABLE process_stats_history (
        snapshot_timestamp timestamp with time zone
        , pid  integer
        , name  text
        , running_since_second  bigint
        , cpu_usage  real
        , memory_usage  real
        , memory_bytes  bigint
        , datname  text
        , usename  text
        , application_name  text 
        , backend_start  timestamp with time zone
        , xact_start   timestamp with time zone
        , query_start   timestamp with time zone
        , state  text
        , wait_event_type  text 
        , wait_event  text
        , query  text
        , backend_type  text
);

CREATE OR REPLACE PROCEDURE get_system_stats IS
BEGIN
  INSERT INTO process_stats_history 
  SELECT 
    current_timestamp 
    , ss.pid 
    , name
    , running_since_seconds
    , cpu_usage, memory_usage
    , memory_bytes
    , datname
    , usename
    , application_name 
    , backend_start
    , xact_start
    , query_start
    , state
    , wait_event_type
    , wait_event
    , query
    , backend_type 
  FROM pg_sys_cpu_memory_by_process() ss 
  LEFT OUTER JOIN pg_stat_activity psa ON ss.pid = psa.pid 
  WHERE psa.state not like 'idle%' AND ss.pid <> pg_backend_pid()
  ORDER BY ss.pid;
END;

DECLARE 
    jobid INTEGER;
BEGIN 
    DBMS_JOB.SUBMIT(jobid,'get_system_stats;',SYSDATE,'SYSDATE + (1 / (60 * 60 * 24))');
    DBMS_OUTPUT.PUT_LINE('jobid: ' || jobid);
END;

以上でジョブが自動実行され、アクティブなセッションのパフォーマンス情報が毎秒取得されます。

pgbench で簡易なベンチマークを実行し、その時に取得された情報を確認してみます。

edb=# SELECT
edb-#     to_char(snapshot_timestamp, 'YYYY/MM/DD HH24:Mi:SS') snapshot_timestamp
edb-#     , pid
edb-#     , to_char(cpu_usage, '990.00') CPU
edb-#     , to_char(memory_usage, '990.00') MEMORY
edb-#     , to_char(query_start,'YYYY/MM/DD HH24:Mi:SS') QUERY_START
edb-#     , query
edb-# FROM process_stats_history
edb-# WHERE application_name='pgbench'
edb-# ORDER BY pid, snapshot_timestamp;
 snapshot_timestamp  |   pid   |   cpu   | memory  |     query_start     |                               query
---------------------+---------+---------+---------+---------------------+------------------------------------------------------------------------
 2025/03/10 10:31:12 | 1800561 |   20.00 |    1.33 | 2025/03/10 10:31:12 | END;
 2025/03/10 10:31:13 | 1800561 |    0.00 |    1.78 | 2025/03/10 10:31:13 | END;
 2025/03/10 10:31:14 | 1800561 |    9.52 |    2.03 | 2025/03/10 10:31:14 | END;
 2025/03/10 10:31:15 | 1800561 |   28.57 |    2.30 | 2025/03/10 10:31:15 | END;
 2025/03/10 10:31:16 | 1800561 |   20.00 |    2.47 | 2025/03/10 10:31:16 | END;
 2025/03/10 10:31:17 | 1800561 |    9.52 |    2.47 | 2025/03/10 10:31:17 | END;
 2025/03/10 10:31:18 | 1800561 |   30.00 |    2.58 | 2025/03/10 10:31:18 | END;
 2025/03/10 10:31:19 | 1800561 |   10.00 |    2.58 | 2025/03/10 10:31:19 | END;
 2025/03/10 10:31:20 | 1800561 |   30.00 |    2.58 | 2025/03/10 10:31:20 | UPDATE pgbench_tellers SET tbalance = tbalance + -4349 WHERE tid = 25;
 2025/03/10 10:31:21 | 1800561 |   28.57 |    2.66 | 2025/03/10 10:31:21 | END;
(10 行)

pgbench を実行したプロセス（PID:1800561）の1秒毎のCPU使用率やメモリー使用率、実行されていたクエリ等を確認することができます。

前項でご紹介したアクティブなセッション情報を毎秒取得する設定を EPAS17 の環境に行いました。さらに、HammerDB を使用したTPROC-Cベンチマークテストを実施し、system_stats の使用がデータベースのパフォーマンスに与える影響を検証しました。検証を行うDB環境、HammerDB実行環境はいずれもAWS EC2に次のスペックで構築しました。

EPAS実行環境：

HammerDB実行環境：

また、検証環境の構成に合わせて EPAS17 のパラメータを次のとおり変更しています。checkpoint_timeout と max_wal_size の設定を初期値より大きくしているのは、checkpoint によるスコアへの影響の揺れを排除するためです。また、SSDボリュームを利用するためランダムページアクセスのプランナコスト定数（random_page_cost）を調整しています。

スケールファクター 50 （データベースサイズ：約5GB）のデータで TPROC-C ベンチマークを行っていきます。ベンチマークの計測時間は20分、負荷を掛ける並列実行ユーザー数は ２、４、６、８、16、32、64 の 7 パターンで行います。

結果は次のグラフで示すとおりになりました。 青軸がsystem_statsを使っていない環境、 オレンジ軸がsystem_statsの関数を使ってプロセスの情報を毎秒取得している環境 の NOPM（New Orders per Minute）を表しています。

ベンチマークの結果、system_statsの使用によってある程度のパフォーマンス低下が確認されました。ただし、各環境のスコア差は 4% 以内に収束しており、パフォーマンスには大きな影響が無いことが確認できます。統合的なシステム監視の利点と比較すると、多くの運用環境において許容範囲内であると考えられます。

特に、パフォーマンス問題の調査や、性能劣化が業務に深刻な影響を与えるミッションクリティカルなシステムでは、数パーセントのオーバーヘッドと引き換えに得られる監視能力には十分な価値があると言えると思います。

ただし、システムの重要度や規模感によっては予期せぬ影響が出る可能性があります。影響の度合いを慎重に評価し、適切な措置を講じることをあわせてご検討ください。

system_statsは、PostgreSQLの運用管理において非常に有用なツールですが、パフォーマンスに影響があるため、本番環境での使用前には必ずテスト環境で影響を評価してください。監視間隔や監視データの削除もシステム規模や要件、DISKの空きに応じて適切に設定する必要があるためご注意ください。

また、system_statsはあくまでもPostgreSQLのセッションに関するパフォーマンス監視ツールの一つですので、システム全体の監視を行うにはOS側の情報を必要とします。また、総合的なパフォーマンス分析にはその他のPostgreSQLのロギング機能を組み合わせて使用する必要があります。

ただ、それらを差し引いても system_stats は PostgreSQL ユーザーにとって非常に魅力的な拡張機能であることに変わりはありません。SQLクエリ一つでOS情報を取得でき、取得した情報を蓄積することで過去に遡って負荷情報を照会できるため、DBエンジニアにとってはパフォーマンス監視が格段に簡単になります。このブログが皆様の効率的なシステム管理の実現の一助になれば幸いです。

　資料をダウンロードする