Jupyter+Pandasを使ったPostgreSQLパフォーマンス分析

本記事は PostgreSQL Advent Calendar 2019 の1日目の記事です。初日から遅れ気味ですすみません。。

久しぶりの記事ですが、最近はPostgreSQLをゴリゴリと触る感じでもなくなってきているため、本記事もゆるめの感じでお送りしたいと思います。

■PostgreSQLの「パフォーマンス分析」とは

PostgreSQLのパフォーマンス分析は、ざっくり言って、以下のようなステップで進められます。（PostgreSQLには限らないと思いますが）

• パフォーマンスの状況から、課題について仮説を設定する。
• パフォーマンスに関連する何の情報を収集するかを決める。
• 情報を収集する。
• 収集した情報を加工し、分析しやすい形式に整える。
• 分析し、仮説を検証、ないしは何かを発見する。
• より深堀り、確証を高めるために、再度情報集をしたり、データを加工、分析したりする。
• 何か対策を打って、その結果を再度分析して、従前と比較する。

ある程度PostgreSQLに詳しい人は、「仮説の設定」や「どこから情報を取得するか」はさくっと決められると思いますが、情報を収集したり分析に適した形に加工したりするのはそれなりの手間と時間がかかるものです。

しかも、ある程度探索的な分析を行わざるを得ないため、データやグラフの形式を最初から決めておいたとしても、パフォーマンス分析がディープになればなるほど、それが現実的に役に立つかどうかは微妙になっていきます。（なので、最終的には、あらゆるグラフを網羅的に出力するレポートを作成する羽目になります）

そのため「Excel最強説」が流れるわけですが、再現可能性や繰り返しの作業についてはやはりいろいろと難がありますので、スクリプトで処理したくなってきます。

■「Jupyter + Pandas」を使ってパフォーマンス分析

探索的かつスクリプトでのデータ処理、と言えばJupyterの出番でしょう。数値データをあれこれ捏ねたり捻ったりするのであればPandasも使うと便利そうです。

というわけで、JupyterとPandasを使ってpg_stat_statementsのデータを分析してみようと思います。

今回の目的としては、

• 複数時点のpg_stat_statementsのデータから、パフォーマンスが悪化しているクエリを抽出したい。
• それ以外にもいろいろな分析ができるようにしておきたい。

あたりを設定したいと思います。

実現するまでの全体の流れとしては、

• pg_stat_statementsのデータを時間をおいて複数回取得する。
• その時のタイムスタンプも取得する。
• 取得したデータをPandasのDataframeに読み込んで分析をする。

としたいと思います。

今回使用したJupyter Notebookは以下にあります。

• pg_stat_statements_using_pandas.ipynb

■データ収集

まず、データを収集します。

収集するのは、pg_stat_statementsビューの内容と、タイムスタンプ（CURRENT_TIMESTAMP）の情報です。

psqlコマンドを使って、

select now();
select * from pg_stat_statements;

として取得します。

取得したデータは、psqlで取得するテキスト形式のまま、以下のようにPythonの変数としてノートブック上に貼り付けます。ぺたり。

ts1 = '2019-11-30 18:41:46.290788+09'

stmt1 = """
 userid | dbid  |  queryid   |                                                                           query                                                                            | calls |     total_time      |       min_time       |       max_time       |      mean_time       |     stddev_time      |  rows  | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time
--------+-------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+---------------------+----------------------+----------------------+----------------------+----------------------+--------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
     10 | 12401 | 2111245140 | begin                                                                                                                                                      |     2 | 0.00213333060267016 | 0.000853332241068065 |   0.0012799983616021 |  0.00106666530133508 | 0.000213333060267016 |      0 |               0 |                0 |                   0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
     10 | 12401 | 3571892116 | BEGIN;                                                                                                                                                     |  8000 |    6.89236451110624 | 0.000426666120534032 |   0.0277332978347121 | 0.000861545563888342 | 0.000442352042060711 |      0 |               0 |                0 |                   0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
(snip)
     10 | 12401 |  927919161 | /*pga4dash*/                                                                                                                                              +|   228 |    54.0415308268406 |    0.139093155294095 |    0.753492368863101 |    0.237024258012459 |   0.0803793833218495 |    228 |            3034 |                1 |                   0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
        |       |            | SELECT                                                                                                                                                    +|       |                     |                      |                      |                      |                      |        |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               |
        |       |            |    (SELECT count(*) FROM pg_stat_activity WHERE datname = (SELECT datname FROM pg_database WHERE oid = ?)) AS "Total",                                    +|       |                     |                      |                      |                      |                      |        |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               |
        |       |            |    (SELECT count(*) FROM pg_stat_activity WHERE state = ? AND datname = (SELECT datname FROM pg_database WHERE oid = ?))  AS "Active",                    +|       |                     |                      |                      |                      |                      |        |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               |
        |       |            |    (SELECT count(*) FROM pg_stat_activity WHERE state = ? AND datname = (SELECT datname FROM pg_database WHERE oid = ?))  AS "Idle"                        |       |                     |                      |                      |                      |                      |        |                 |                  |                     |                     |                |                 |                    |                    |                |                   |               |
"""

何度か取得し、変数名を変えて貼り付けます。

■PandasのDataframeに変換する

次に、これらのテキストの出力をPandasのDataframeに変換します。（pgss_to_df関数の実装についてはノートブックを参照してください）

今回は、pgbenchを何度か実行し、その間に3回ほどpg_stat_statementsビューの情報を取得して使っています。

pgss1 = pgss_to_df(ts1, stmt1)
pgss2 = pgss_to_df(ts2, stmt2)
pgss3 = pgss_to_df(ts3, stmt3)

pgss = pd.concat([pgss1,pgss2,pgss3], ignore_index=True).sort_values(by=['queryid', 'datetime'])

すると以下のようなDataframeの形式になります。

■特定のクエリの平均処理時間の推移を見る

さて、Dataframeの準備ができたところで、実際にデータを見てみましょう。

まず、特定のクエリの平均処理時間の推移を見てみます。（find_by_query関数の実装についてはノートブックを参照してください）

find_by_query(pgss, 'UPDATE pgbench_tellers')

mean_timeのカラムを見ると、pgbench_tellersテーブルを更新するクエリの平均処理時間が、少しずつ長くなっていることが分かるかと思います。（13.18ミリ秒→13.30ミリ秒→13.31ミリ秒）

■平均処理時間が10%以上悪化したクエリを抽出する

次に、平均処理時間が10%以上悪化したクエリを抽出します。

まず、最初と最後のタイムスタンプのレコードを、それぞれmin/maxとして、queryidカラムとmean_timeカラムを抽出します。

# average query time at the start.
df_start = pgss[pgss.datetime == pgss['datetime'].min()].loc[:,['queryid', 'mean_time']]
# average query time at the end.
df_end = pgss[pgss.datetime == pgss['datetime'].max()].loc[:,['queryid', 'mean_time']]

次に、これらをqueryidで結合し、最後の処理時間が最初の処理時間に比べて10%以上長くなっているクエリを抽出します。

# join both using the queryid.
df = df_start.join(df_end.set_index('queryid'), on='queryid', lsuffix='_start', rsuffix='_end')

# select queries that slow down more than 10%
df = df[df.mean_time_end > df.mean_time_start * 1.1]

そして、最後にクエリ文字列と結合します。

# join query strings using the queryid
df.set_index('queryid').join(pgss.loc[:,['queryid','query']].set_index('queryid')).drop_duplicates()

これで、処理時間が10%以上伸びているクエリを抽出することができるようになりました。

■まとめ

以上、今回はJupyterとPandasを使って、PostgreSQLのパフォーマンス分析のためのデータ加工や簡単な分析を行ってみました。

今回着手するに当たって、最初はmatplotlibを使った可視化なども考えていたのですが、実際にパフォーマンス分析の実務を考えると、安直な可視化よりは基本的なデータ操作、オペレーションを関数にまとめておくことの方が、生産性の観点ではより役に立つのではないかと思うようになりました。（もちろん、トレンド分析などにおける可視化の役割は否定するものではありませんが）

実際のパフォーマンス分析の現場では、さまざまなデータの加工や集計作業が必要になります。一旦Pandasに取り込んでおけば、さまざまなデータ処理を簡単に行うことができ、いろいろ便利に使えるのではないかと思います。（Pandasを使いこなせる人であれば、という条件付きですが・・・私はPandasは初心者です・・・）

今後は、今回のような処理をうまくwrapするモジュールやクラスに整理して、Jupyterからimport一発で簡単に使えるようなものを目指してみようと思います。

では、また。

PostgreSQL Advent Calendar 2019、明日の担当は @yuina1056 さんです。お楽しみに。