PostgreSQL安定運用のコツ2009 @hbstudy#5

2009

PostgreSQL安定運用のコツ
I/Oを制する者はRDBMSを制す

アップタイム・テクノロジーズ
永安悟史

Copyright 2009 Uptime Technologies LLC, All rights reserved.

アジェンダ
• パフォーマンスと初期設定
• データベースの監視
• 性能劣化とメンテナンス
• パフォーマンスチューニング（GUC編）
• パフォーマンスチューニング（SQL編）
• バックアップ＆リカバリ

本日の資料は http://slideshare.net/uptimejp にアップしてあります。


（１）パフォーマンスと初期設定


PostgreSQLプロセス構造
• PostgreSQL 8.4の場合
[snaga@devpg01 ~]$ ps -aef | cat | grep ^snaga
snaga 5282 1 0 20:15 tty1 00:00:00 /usr/local/pgsql/bin/postgres -
D ./pgdata
snaga 5283 5282 0 20:15 ? 00:00:00 postgres: logger process
snaga 5285 5282 0 20:15 ? 00:00:05 postgres: writer process
snaga 5286 5282 0 20:15 ? 00:00:01 postgres: wal writer process
snaga 5287 5282 0 20:15 ? 00:00:00 postgres: autovacuum launcher process
snaga 5288 5282 0 20:15 ? 00:00:00 postgres: archiver process archiving
000000010000000000000059
snaga 5289 5282 0 20:15 ? 00:00:00 postgres: stats collector process
snaga 28467 6806 1 20:26 pts/0 00:00:06 pgbench -i -s 100 pgbench2
snaga 28468 5282 11 20:26 ? 00:00:57 postgres: snaga pgbench2 [local] COPY
snaga 28728 6806 0 20:27 pts/0 00:00:01 pgbench -c 8 -t 1000 -s 10 pgbench
snaga 28756 5282 0 20:27 ? 00:00:00 postgres: snaga pgbench [local] COMMIT
snaga 28757 5282 0 20:27 ? 00:00:00 postgres: snaga pgbench [local] INSERT
snaga 28758 5282 0 20:27 ? 00:00:00 postgres: snaga pgbench [local] UPDATE
snaga 28763 5282 0 20:27 ? 00:00:00 postgres: snaga pgbench [local] UPDATE
waiting
snaga 31381 5288 0 20:34 ? 00:00:00 /bin/cp
pg_xlog/000000010000000000000059
/home/snaga/pgdata/pg_xlogarch/000000010000000000000059
[snaga@devpg01 ~]$


PostgreSQLデータディレクトリ構造
• PostgreSQL 8.4の場合
[snaga@devpg01 ~]$ ls -l pgdata
total 148
drwx------ 8 snaga snaga 4096 Nov 5 20:26 base
drwx------ 2 snaga snaga 4096 Nov 5 20:26 global
drwx------ 2 snaga snaga 4096 Nov 5 20:01 pg_clog
-rw------- 1 snaga snaga 3652 Nov 5 20:01 pg_hba.conf
-rw------- 1 snaga snaga 1631 Nov 5 20:01 pg_ident.conf
drwx------ 2 snaga snaga 4096 Nov 5 20:15 pg_log
drwx------ 4 snaga snaga 4096 Nov 5 20:01 pg_multixact
drwx------ 2 snaga snaga 4096 Nov 5 20:33 pg_stat_tmp
drwx------ 2 snaga snaga 4096 Nov 5 20:01 pg_subtrans
drwx------ 2 snaga snaga 4096 Nov 5 20:01 pg_tblspc
drwx------ 2 snaga snaga 4096 Nov 5 20:01 pg_twophase
-rw------- 1 snaga snaga 4 Nov 5 20:01 PG_VERSION
drwx------ 3 snaga snaga 4096 Nov 5 20:33 pg_xlog
lrwxrwxrwx 1 snaga snaga 23 Nov 5 20:08 pg_xlogarch -> /home/snaga/pg_xlogarch
-rw------- 1 snaga snaga 16807 Nov 5 20:13 postgresql.conf
-rw------- 1 snaga snaga 46 Nov 5 20:15 postmaster.opts
-rw------- 1 snaga snaga 46 Nov 5 20:15 postmaster.pid
[snaga@devpg01 ~]$


PostgreSQLの構成
• さまざまなプロセス・メモリ領域・ファイルによって構成され
る

writer
postmaster logger wal writer autovacuum
（バックグラウンド
（リスナプロセス）（サーバログ）（WALライタ）（自動vacuum）
ライタ）
プロセス群
archiver stat collector postgres
（WALアーカイバ）（統計情報収集）（サーバプロセス）

shared_buffers wal_buffers freespacemap トランザクション
メモリ群（共有バッファ）（WALバッファ）（空き領域情報）制御情報

ファイル群テーブルインデックストランザクションアーカイブ
設定ファイル
ファイルファイルログファイルログファイル


PostgreSQLのアーキテクチャ
• 共有バッファを中心として、複数のプロセス間で連携しな
がら処理を行うマルチプロセス構造

postmaster
（リスナプロセス）

（

shared_buffers
postgres 共
クライアント有
（サーバプロセス）バ
ッ
フ
ァ
）
writer
ライタ）

トランザクション
ログファイル
テーブル
ファイル

インデックス
ファイル


PostgreSQLのファイル種別
• テーブルファイル
– レコードの実データを保存
– タプル（行）単位で、テーブルファイルに追記

• インデックスファイル
– インデックスのキーとレコードへのポインタを保持
– ツリー構造を持つ（ルート、インターナル、リーフ）

• トランザクションログ
– テーブルやインデックスの更新情報を追記
– リカバリの際に使用される


テーブルファイル
• 8kB単位のブロック単位で管理
• ブロックの中にレコード（タプル）を配置
– 基本的に追記のみ
– 削除したら削除マーク（VACUUMで回収）
– 更新時は「削除＋追記」

レコード１
レコード２ブロック１（８ｋB）
レコード３

レコード４
レコード５ブロック２（８ｋB）

ブロック３（８ｋB）


インデックスファイル
• ツリー構造を持つ
– 各ノードが、8kBのブロックに相当
– ルートノードから辿っていく
– リーフノードは、テーブルファイルの実タプルへのポインタを持つ

インデックスファイルテーブルファイル

レコード１
レコード２
レコード３

レコード４
レコード５

1～5 6～10 11～17 18～25


トランザクションログ
• 更新情報を記録していく（追記）
– 1セグメント16MB。使い終わると次のファイルへ
– リカバリの際に読み込まれる
– pg_xlog/ 以下に配置
– 「Write Ahead Log（WAL）」とも呼ばれる

Aテーブルのレコード1をmに変更
Bテーブルのレコード6をnに変更
Aテーブルのレコード4をxに変更
Aテーブルのレコード1をyに変更
Bテーブルのレコード2をzに変更

ファイルの先頭から
順番に更新情報が
追記されていく


RDBMSで発生するファイルI/O
• さまざまなパターンのファイルI/Oが発生する
– 例えば、プライマリキーで検索してレコードを更新する場合

ひとつの物理ディスク
テーブルファイル ②読む

④書く
ディスク
ヘッド
①読む

③書く
ログファイル


アクセスパターン
• シーケンシャルアクセス
– 全レコード、または多くのレコードを処理する必要がある場合（集約処
理、LIKE文の中間一致など）
• ランダムアクセス
– 主にインデックスを用いたアクセス

シーケンシャルランダム
アクセスアクセス

ファイルの先頭から
順番に読み込んでいく必要なレコードだけ
ピンポイントで読み込む

テーブルファイルテーブルファイル


共有バッファとは
• ディスク上のブロックをキャッシュするメモリ領域
– ディスクI/Oを抑えるための機構

postmaster
（リスナプロセス）

（

shared_buffers
postgres 共
クライアント有
（サーバプロセス）バ
ッ
フ
ァ
）
writer
ライタ）

ログファイル
テーブル
ファイル

インデックス
ファイル


共有バッファ
• ディスク上のブロックをキャッシュするメモリ領域
– 読み書きともにキャッシュすることで高速化を図る
• すべてのバックエンドプロセスで共有
• 8kB単位で管理
– postgresql.conf の shared_buffers でサイズ指定

postgres

postgres

postgres 共有バッファ

バックエンド

ディスク

レコード量とデータサイズ
• Pgbenchのaccountsテーブルのサイズ
– 10万レコードの場合、15MB
– 100万件レコードの場合、148MB

pgbench=# SELECT count(*) FROM
accounts;
count
10万レコードのcount --------
100000
(1 row)

Time: 107.818 ms

pgbench10=# SELECT count(*) FROM accounts;
count
---------
1000000
100万レコードのcount (1 row)

Time: 1009.377 ms


初期設定
• 必ず変更すべき項目
– shared_buffers
– checkpoint_segments
– wal_buffers

• 変更を推奨する項目
– max_connections
– log_line_prefix
– stats_start_collector, stats_block_level, stats_row_level
• PostgreSQL 8.1 / 8.2 の場合
– track_activities, track_counts
• PostgreSQL 8.3 / 8.4 の場合


（２）データベースの監視


なぜ「監視」が重要なのか？
• PDCA（Plan-Do-Check-Action）を回すため
– データベースがきちんとサービスを提供しているか？
– 性能レベルが落ちていないか？

• 監視は「Action」につなげるための「Check」
– チューニングを行う
– ハードウェアの増強を行う
– メンテナンスを行う

• 「何のために、何を監視するのか」
– あらかじめ決めておくことが重要


監視すべき項目とその方法
• データベースサイズ、テーブルサイズ
– データベースサイズ
• pg_database_size()関数
– テーブルサイズ
• pg_relation_size()関数、pg_total_relation_size()関数

• トランザクション量（論理I/O）
– コミット数、ロールバック数（データベース単位）
• pg_stat_databaseシステムビュー
– INSERT/UPDATE/DELETE数（テーブル単位）
• pg_stat_user_tablesシステムビュー

• ディスクI/O量（物理I/O）
– ブロック読み込み、キャッシュ読み込み（データベース単位）
• pg_stat_databaseシステムビュー
– ブロック読み込み、キャッシュ読み込み（テーブル単位）
• pg_statio_user_tablesシステムビュー


データベースサイズ、テーブルサイズの取得
• データベース、テーブルサイズ取得用関数
– pg_database_size()
– pg_relation_size()
– pg_total_relation_size()

• 使い方
– SELECT pg_database_size('データベース名')
– SELECT pg_relation_size('テーブル名')


データベースサイズ、テーブルサイズの取得（例）
dbt1=# SELECT pg_database_size('dbt1');
pg_database_size
------------------
5616124552

dbt1=# SELECT pg_relation_size('orders');
pg_relation_size
------------------
407257088

dbt1=# SELECT pg_total_relation_size('orders');
pg_total_relation_size
------------------------
504676352


トランザクション量の取得
• アクセス統計情報（システムビュー）
– pg_stat_database
– pg_stat_user_tables

• 使い方
– SELECT * FROM pg_stat_database
– SELECT * FROM pg_stat_user_tables


監視結果の可視化
• サンプルWebアプリを数日間実行し、その間のトランザク
ション数およびデータベースサイズを計測

データベースサイズとトランザクション数

740 1200
720

トランザクション数（TPM）
1000
700
DBサイズ（MB）

800
680
660 600
640
400
620
200
600
580 0
2006/5/4 2006/5/5 2006/5/6 2006/5/7


有効にすべきGUC項目
• PostgrSQL 8.1/8.2の場合
– stats_start_collector 統計情報の収集を行う
– stats_command_string SQLコマンドの統計を取得する
– stats_block_level ブロック単位の統計を取得する
– stats_row_level 行単位の統計を取得する

• PostgreSQL 8.3/8.4の場合
– track_activities SQLコマンドの統計を取得する
– track_counts ブロック単位、行単位の統計を取得する


（３）性能劣化とメンテナンス


性能劣化の原因
• データ量の増大
– 実データの増大
• データが蓄積されていくことによって、実際のデータ量が増大。
– 不要領域の増大
• データ量は増えていないが、追加・削除・更新を繰り返すことによって、
ディスクの利用効率が悪くなり、余計なI/Oが発生。

• 問い合わせ処理の増大
– 接続数の増大
– 処理処理の増大


不要領域のできる仕組み
• あるトランザクションによってレコードが（論理的に）削除され
ると、「削除フラグ」が設定される
– 物理的にはディスク上に残る

• これは、複数のトランザクションからのレコードの可視性を制
御するためである。
– Multi-Version Concurrency Control （MVCC）

• よって、削除して不要になった領域は、事後的に「再利用可
能領域」として回収する必要がある。
– これが「VACUUM処理」


追記型アーキテクチャ
レコード1 「レコード5」を追加レコード1
レコードレコード2 レコード2
追加処理レコード3 レコード3
レコード4 レコード4
（INSERT）レコード5
ファイル中に4件のレコードが
順番に並んでいるレコード5がファイル末尾に追加され、
ファイルサイズが増える

「レコード2」を削除
レコードレコード2 （レコード2）
削除処理レコード3 レコード3
（DELETE）
ファイル中に4件のレコードがレコード2に削除マークが付けられる
順番に並んでいる

「レコード2」を
レコード1 「レコード2’」として更新レコード1
レコードレコード2 （レコード2）
更新処理レコード3 レコード3
（UPDATE）
レコード2’
ファイル中に4件のレコードがレコード2に削除マークが付けられ、
順番に並んでいるレコード2’が新たに追加、ファイルサイズ増加

データファイル不要領域率の確認
• テーブルの不要領域の確認
– pgstattuple関数を使う
• インデックスの不要領域の確認
– pgstatindex関数を使う

• 入手先
– pgstattuple関数
• contribのpgstattupleモジュール
– pgstatindex関数
• バージョン8.1の場合：以下から取得
http://www.pgperf.com/tools/pgstatindex
• バージョン8.2以降の場合：pgstattupleモジュールに同梱


不要領域率とパフォーマンス
• 不要領域が増えるとパフォーマンスが落ちる

pgbenchスコアと不要領域率
(accountsテーブル)

350 25

300
20
トランザクション数/秒

250

不要領域率(%)
200 15
150 不要領域率
10
100
5
50

0 0
目

目

目

目

目

目

目

目

目

目
1回

2回

3回

4回

5回

6回

7回

8回

9回

回
10

pgbench実行回数 ※PostgreSQL 8.1での検証


VACUUMとは
• テーブルの不要領域を回収し、「未使用領域」として記録す
る。
– 次の更新（追記）の時から、未使用領域を利用できるようになる。

• VACUUMコマンド
– VACUUM ＜テーブル名＞
– VACUUM


VACUUM処理
VACUUM前 VACUUM後
（レコード2）
VACUUM処理
空き領域
VACUUM レコード3 レコード3
処理レコード4 レコード4
レコード2’ レコード2’
レコード2に削除マークがレコード2の領域が「空き領域」として
付いている再利用可能になる。

追記前追記後
レコード1 レコード5を追記レコード1
空き領域レコード5
してあるとレコード4 レコード4
「空き領域」があるファイルサイズを変えずに追記できる

レコード5を追記
(レコード2） (レコード2)
してないとレコード4 レコード4
レコード2の領域が埋まったままレコード5
ファイルサイズが増加

REINDEXとは
• インデックスを再作成する
– 不要領域のないインデックスが作られる

• REINDEXコマンド
– REINDEX TABLE ＜テーブル名＞
– REINDEX DATABASE ＜データベース名＞


メンテナンスの効果
• FULL VACUUMとREINDEXを実行

accountsテーブルサイズとpgbenchスコア

350 180
160
300
140

テーブルサイズ(MB)
250
120
200 100 トランザクション数/秒
150 80 テーブルサイズ(MB)

60
100
40
50
20
0 0
目

目

目

目

目

目

目

目

目

目

目
1回

2回

3回

4回

5回

6回

7回

8回

9回

回

回
10

11

pgbench実行回数


タプルの更新とインデックスの更新
8.3以前
インデックス付きカラム

ヒープタプルレコード1 レコード1
インデックスのない
（テーブル）レコード2 カラムを更新すると・・・ (レコード2)
レコード2’

エントリ1 エントリ1 インデックスサイズも
インデックス
エントリ2 (エントリ2) 増える
エントリ2’

8.3以降インデックス付きカラム

ヒープタプルレコード1 レコード1
（テーブル）インデックスのない
レコード2 カラムを更新すると・・・
(レコード2)
レコード2’

エントリ1 エントリ1 インデックスサイズは
インデックス
エントリ2 エントリ2 増えない

インデックスの張られていないカラムを更新すると、
ヒープのみの（インデックスエントリが無い）カラムができる。

これが、HOT（Heap Only Tuple）

その他の情報
• VACUUMの自動化「autovacuum」
– 負荷状況を見ながら、VACUUMを自動実行する機能

• VACUUMの負荷分散「vacuum delay」
– Vacuumを「ゆっくり」実行する機能


Q&A


今回の話、（おおむね）入ってます！
• 連載「PostgreSQL安定運用のコツ」
WEB+DB PRESS vol.32～37

＋


Thank you.

質問またはコメントなどがある方は、

snaga@uptime.jp

または

twitter.com/uptimejp

まで、お気軽にご連絡ください。


PostgreSQL安定運用のコツ2009 @hbstudy#5

More Related Content

PostgreSQL安定運用のコツ2009 @hbstudy#5