PostgreSQL Deep Dive: Rを使ってパフォーマンス統計情報を可視化する

PostgreSQL Advent Calendar 2012（全部俺）のDay 10です。

前回までに、PostgreSQLのパフォーマンスデータを取得・蓄積し、それをSQLを用いて解析する方法を紹介してきました。

しかし、増えていくデータを活用して人間が何らかのアクションを取るためには、データを何らかの形で「可視化」して、人間が容易に読解・解釈できる形に変更する必要があります。

今回は解析結果を可視化する方法について簡単に紹介します。ツールとしては、オープンソースの統計処理ソフトウェアである「R」を利用します。

■R、および関連パッケージのインストール

まず、RのサイトからRのバイナリパッケージをダウンロードします。

The R Project for Statistical Computing
http://www.r-project.org/

トップページの「Getting Started」の中の「download R」に進むとミラーの一覧が出てきますので、適当なミラーサイトを選んだら「Download R for Windows」というページから「base」を選んでバイナリパッケージをダウンロードします（Windows版のRの最新版はバージョン2.15.2のようです）。

次に、DBIおよびRPostgreSQLのパッケージをインストールします。Rのパッケージのインストールは「CRAN」という、Perlで言うところのCPANのようなサイトからネットワーク経由で直接ダウンロードしてインストールすることができます。

Rを起動してコンソールを開いたら、以下のコマンドを実行します。

> install.packages("DBI")
> install.packages("RPostgreSQL")

ミラーの一覧が出て、適当なミラーサイトを選択するとインストールが始まります。

■ブロック書き込み量を取得するビューを作る

Rの準備ができたら、次はPostgreSQL側の準備をします。

前回のエントリ「ウィンドウ関数を用いたブロック読み込みの計測」を応用して、今回は「ブロック書き込み」について、バックグラウンドライタ、チェックポイント、バックエンドプロセスによる書き込みをそれぞれ時系列で取得し、「書き込みブロック数/秒」として取得します。

まず、ウィンドウ関数を使って各スナップショット取得時の書き出しブロック数のデータを取得するためのクエリを作成し、それをビューとして作成します。

CREATE VIEW pgperf.rpt_blkwrite AS
  SELECT date_trunc('second', s.ts) AS "timestamp",
         round( (( buffers_checkpoint - lag(buffers_checkpoint) OVER (ORDER BY s.ts) )::float /
           pgperf.get_interval(lag(s.sid) OVER (ORDER BY s.ts), s.sid))::numeric, 2) AS "blks_cp/sec",
         round( (( buffers_clean - lag(buffers_clean) OVER (ORDER BY s.ts) )::float /
           pgperf.get_interval(lag(s.sid) OVER (ORDER BY s.ts), s.sid))::numeric, 2) AS "blks_bg/sec",
         round( (( buffers_backend - lag(buffers_backend) OVER (ORDER BY s.ts) )::float /
           pgperf.get_interval(lag(s.sid) OVER (ORDER BY s.ts), s.sid))::numeric, 2) AS "blks_be/sec"
    FROM pgperf.snapshot s,
         pgperf.snapshot_pg_stat_bgwriter b
   WHERE s.sid=b.sid
     AND s.ts BETWEEN date_trunc('day', now()) - '32 days'::interval
                  AND date_trunc('day', now()) + '1 day'::interval
 ORDER BY s.ts;

ここで作成したpgperf.rpt_blkwriteビューはブロック書き込みの数値を過去32日分時系列表示してくれるもので、このビューに対してSELECTを実行すると以下のような結果を取得できます。

postgres=# SELECT * FROM pgperf.rpt_blkwrite;
      timestamp      | blks_cp/sec | blks_bg/sec | blks_be/sec
---------------------+-------------+-------------+-------------
 2012-12-09 15:45:48 |             |             |
 2012-12-09 15:50:01 |        0.00 |        0.00 |        0.05
 2012-12-09 16:00:01 |        0.00 |        0.00 |        0.00
 2012-12-09 16:10:01 |        0.00 |        0.00 |        0.01
 2012-12-09 16:20:01 |        0.00 |        0.00 |        0.00
(5 rows)

postgres=#

ここまで準備ができたら、次はRからこのデータを取得して時系列でグラフ表示してみます。

■データベースへ接続してクエリを実行する

RからPostgreSQLへのクエリを実行するには、まず library() 関数を使ってRPostgreSQLパッケージを読み込みます。

> library("RPostgreSQL")
 要求されたパッケージ DBI をロード中です 
>

次に、データベースへのコネクションを作成します。

> con <- dbConnect(PostgreSQL(), host="10.0.2.12", user= "postgres", password="postgres", dbname="postgres")
> con
<PostgreSQLConnection:(9088,3)>
>

これで con オブジェクトにデータベースへのコネクションオブジェクトが作成されました。

データベースへ接続できたら、クエリを発行して結果セットを取得します。ここでは、先ほど作成したpgperf.rpt_blkwriteビューに対してSELECTを実行します。

> rs <- dbSendQuery(con, "SELECT * FROM pgperf.rpt_blkwrite")

特にエラー無くクエリを実行できたら（プロンプトが返ってきたら）、クエリの発行は成功です。

■結果を取得して時系列のグラフを描画する

クエリを実行したら結果セットからデータをフェッチします。

> out <- fetch(rs)
> out
            timestamp blks_cp/sec blks_bg/sec blks_be/sec
1 2012-12-09 15:50:01          NA          NA          NA
2 2012-12-09 16:00:01        0.00           0        0.00
3 2012-12-09 16:10:01        0.00           0        0.01
4 2012-12-09 16:20:01        0.00           0        0.00
5 2012-12-09 16:30:01        0.00           0        0.42
6 2012-12-09 16:40:01       51.36           0        0.00
(...snip...)
>

配列を表示すると、きちんとデータを取得できていることが分かります。

最後に、取り出したデータ out を使って時系列のグラフを描画します。

> ts.plot (out[2:4], gpars=list( ylab='blocks/sec', lty=c(1,1,1), col=c('green', 'red', 'blue')))

上記のts.plotを実行すると、以下のような時系列のグラフが表示されます。

ここでは、緑のラインがチェックポイントによる書き込み、赤のラインがバックグラウンドライタによる書き込みで、青のラインがバックエンドプロセスによる書き込みです。

最後に凡例を表示して完了です。

> legend('topright', c('Checkpoint','Bgwriter','Backend'), lty=c(1,1,1), col=c('green', 'red', 'blue'))

本来はX軸には時刻をプロットすべきなのですが、うまく表示する方法を見つけられず今回は間に合いませんでした。（どなたかご存じの方がいたら教えてください）。

■まとめ

今回は、前回までに取得したパフォーマンス統計情報を、統計処理ソフトウェアであるRを使って可視化する方法を紹介しました。

パフォーマンスに関するデータを取得・蓄積することももちろん重要なのですが、そのデータをきちんと読み解いてそこから「解釈」することもデータを活用する上では欠かせません。「可視化」というのは、そのための非常に重要な方法のひとつです。

Rだけでなく、データを可視化するツールにはさまざまなものがあります。ぜひ、自分に合ったツールを見つけて、取得・蓄積したパフォーマンスデータを有効に活用していただければと思います。

では、また。

■参考文献

Package 'RPostgreSQL'
http://cran.r-project.org/web/packages/RPostgreSQL/RPostgreSQL.pdf
Ｒと時系列(1)
http://mjin.doshisha.ac.jp/R/33/33.html
Plotting Time-Series Objects
http://stat.ethz.ch/R-manual/R-devel/library/stats/html/plot.ts.html

PostgreSQL Deep Dive

2012年12月10日

Rを使ってパフォーマンス統計情報を可視化する