Day 1のエントリにも少し出てきましたが、PostgreSQLを始めとするRDBMSは、共有バッファ（データベースバッファと呼ばれることもある）にデータを一時的に保持して、ディスクI/Oを削減することでパフォーマンスの向上を実現しています。
普段はなかなか窺い知ることのできない共有バッファの内容ですが、今回はそのPostgreSQLの共有バッファの中身を見てみることにします。
■PostgreSQLの共有バッファ
PostgreSQLの共有バッファの簡単な解説については、改めて以下のスライドを見ていただければと思います。
PostgreSQLのテーブルやインデックスファイルは、8kBのブロック単位で構成されており、SQLの処理の時に実際に必要とされるブロックだけを読み込むことによって、共有バッファ上でデータの読み書きを可能にしているのです。
■pg_buffercacheモジュール
PostgreSQLにはpg_buffercacheというcontribモジュールがあり、このモジュールをインストールすることによって、共有バッファに載ってる各ブロックが、どのデータベースの、どのテーブルの、何番目のブロックであり、その状態がどのようになっているのか、といった情報を取得することができます。
F.24. pg_buffercache
http://www.postgresql.jp/document/9.0/html/pgbuffercache.html
contribモジュールのインストール方法の詳細は、Day2のエントリを参照してください。ここでは、バージョン9.1上でEXTENSIONとして導入します。
postgres=# CREATE EXTENSION pg_buffercache; CREATE EXTENSION postgres=#
■共有バッファの中身を見てみる
pg_buffercaheモジュールをインストールすると、「pg_buffercache」というビューを使うことができるようになります。
まずは、そのpg_buffercacheビューの中身を見てみましょう。pg_buffercacheビューでは、各レコードが1つのバッファページの状態を示しています。この例では、共有バッファが4,096ページ（8kB×4,096ページ＝32MB）あることが分かります。
postgres=# SELECT count(*) FROM pg_buffercache;
count
-------
4096
(1 row)
postgres=# SELECT * FROM pg_buffercache LIMIT 5;
bufferid | relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | isdirty | usagecount
----------+-------------+---------------+-------------+---------------+----------------+---------+------------
1 | 54381 | 1664 | 0 | 0 | 0 | f | 4
2 | 54399 | 1664 | 0 | 0 | 0 | f | 4
3 | 53935 | 1663 | 12699 | 0 | 1740 | f | 3
4 | 53930 | 1663 | 12699 | 0 | 15493 | f | 1
5 | 76868 | 1663 | 12699 | 0 | 16330 | t | 3
(5 rows)
postgres=#
各カラムの見方は以下の通りです。- bufferidカラムはバッファ内のページ番号（連番）
- relfilenodeカラムは各テーブルやインデックスの該当するファイル名
- reltablespaceカラムはテーブルスペースのOID（内部的な管理番号）
- reldatabaseカラムは該当するデータベースのOID
- relforknumberカラムはフォーク番号：テーブルやインデックスのデータファイル本体なら0、FreeSpaceMap（空き領域マップ）ファイルなら1、VisibilityMap（可視性マップ）ファイルなら2
- relblocknumberカラムは該当ファイルの中でのブロック番号
- isdirtyカラムはページが更新されているかどうか（ダーティかどうか）
- usagecountカラムは当該ページが参照された回数：最小0、上限5
なお、PostgreSQLでは共有バッファの管理に "clock sweep" というアルゴリズムを採用しています。
共有バッファ内のページはリング状に管理されており、ページが参照されるたびに usagecount が増加していきます（但し、5が上限値）。逆に、ディスクから新しくブロックを読み込む時には、バッファの状態をスキャンして usagecount が0のページを入れ替え（eviction）の対象とします。
■ダーティページの数を数えてみる
前述したように、ダーティページは後刻ディスクに書き戻す必要のあるページであり、ダーティページが増加すると結果としてディスクの書き込みが多くなりますので、パフォーマンスに影響を与えることになります。
該当するページが更新済みかどうかは isdirty というカラムが true になっているかどうかで判断することができますので、isdirtyカラムでGROUP BYすればダーティページとそうでないページの集計を取得することができます。
postgres=# SELECT isdirty,count(*) FROM pg_buffercache GROUP BY isdirty; isdirty | count ---------+------- f | 3894 t | 202 (2 rows) postgres=#ここでは、更新済みのページは202ページ、更新されていないページが3,894ページあることが分かります。
■テーブルごとのバッファページ数を集計、ランキング
次に、データベース、テーブルごとのバッファページ数を集計して、ランキングを作成してみます。
データベース名は、pg_buffercacheビューのreldatabaseカラムとpg_databaseシステムテーブルのoidカラムをJOINすることによって、pg_databaseシステムテーブルのdatnameカラムから取得します。
テーブル名は、pg_buffercacheビューのrelfilenodeカラムとpg_classシステムテーブルのrelfilenodeカラムをJOINすることによって、pg_classシステムテーブルのrelnameカラムから取得します。
なお、接続しているデータベース以外のユーザデータベースについてはテーブル名を正しく取得できないため（pg_classシステムテーブルが異なっているためです）、接続しているデータベースおよびreldatabaseが0のデータベース（システムで共有されているデータベース）のみを対象とします。
まとめると以下のようなSQLになります。
SELECT d.datname,c.relname,count(*)
FROM pg_buffercache b
LEFT OUTER JOIN
( SELECT oid,* FROM pg_database WHERE oid=0 OR datname=current_database() ) AS d
ON b.reldatabase=d.oid
LEFT OUTER JOIN
pg_class c
ON b.relfilenode=c.relfilenode
GROUP BY d.datname,c.relname
ORDER BY d.datname,c.relname;
上記のSQLを実行すると、以下のような結果を取得することができます。
datname | relname | count
----------+-----------------------------------------+-------
postgres | pgbench_accounts_pkey | 2745
postgres | pgbench_accounts | 866
postgres | | 107
postgres | pg_statistic | 16
| | 15
postgres | pg_operator | 13
postgres | pg_rewrite | 7
(...snip...)
postgres | pg_aggregate | 1
postgres | pg_am | 1
postgres | pg_namespace | 1
postgres | pgbench_tellers | 1
(59 rows)
このようにして、共有バッファの中の状態を集計することができました。
■まとめ
データベースは共有バッファを中心として処理が行われています。
普段はここまで細かく共有バッファの中身を見ることは無いと思いますが、共有バッファの動作を理解して、管理・チューニングをすることはデータベースの運用管理上、非常に重要になります。
機会があれば、こういったツールもうまく使ってPostgreSQLを使いこなしてみてください。
では、また。
0 件のコメント:
コメントを投稿