PostgreSQL チューニング入門

by user

on 28 марта 2017

Category: Documents

>> Downloads: 13

views

Report

Comments

Description

Download PostgreSQL チューニング入門

Transcript

PostgreSQL チューニング入門

PostgreSQL チューニング入門
PostgreSQL チューニング入門
c 2003 by Kenji Sugita
Copyright °
1
PostgreSQL チューニング入門
チューニングとは
• パフォーマンスの維持 … 問題発生の抑制、異常の発見
– 目的 … 効率と安定性の維持
– 日常の管理
– パフォーマンスの監視
• パフォーマンスの改善 … 問題解決
– 目的 … 効率と安定性の改善及び回復
– 開発時 … 性能目標の達成
– 運用時 … 効率低下やエラーなどの障害への対応
c 2003 by Kenji Sugita
Copyright °
2
PostgreSQL チューニング入門
チューニングに必要なツール (その 1)
• PostgreSQL の contrib
– pg controldata … コントロールファイルの情報の表示。OID (Object
Identifier) と XID (Transaction Identifier) のチェック。
– oid2name … リレーションファイル名とリレーションの対応表示
– pgstattuple … テーブルの不要領域チェック
– pgbench … ベンチマーク
• PostgreSQL の機能
– EXPLAIN、EXPLAIN ANALYZE
– アクセス統計情報
サーバの稼働状況を収集し、報告する機能。逐次スキャンとインデックスス
キャンの回数。スキャン・挿入・更新・削除で処理されたタプル数。テーブル
とインデックスの読み込みブロック数、バッファヒット数。サーバが処理中の
クエリーのモニター。
– psql の \timing コマンド … クライアントから見た正確な実行時間の計測。
c 2003 by Kenji Sugita
Copyright °
3
PostgreSQL チューニング入門
チューニングに必要なツール (その 2)
• RedHat、カスタムツール
– pg filedump … RedHat。テーブルファイルやインデックスファイルのダン
プ。データベースサイズ見積りでのレコードサイズ、インデックスの空間利用
効率の確認。
– pg chkrel … カスタムツール。テーブルとインデックスの不要領域チェック、
テーブル毎のインデックスと TOAST を含むテーブルのディスク使用量を
集計。
– gennum、iterate … カスタムツール。データ生成、繰り返し。単純な大量
データを高速に作成。
c 2003 by Kenji Sugita
Copyright °
4
PostgreSQL チューニング入門
チューニングに必要なツール (その 3)
• vmstat … メモリ、スワップ、ディスク
• iostat … ディスク入出力
• mpstat … マルチ CPU 稼働状況
• free … メモリ、スワップ
• sar (System Activity Reporter)
• lsof (List Open Files) … オープンファイルの表示。
• netstat … ネットワーク
• ps、top … プロセス
• ipcs … 共有メモリ、セマフォ、メッセージキュー
c 2003 by Kenji Sugita
Copyright °
5
PostgreSQL チューニング入門
oid2name … リレーションファイル名とリレーションの対応表示
$ oid2name
All databases:
--------------------------------16976 = sugita
1
= template1
16975 = template0
$ oid2name -d sugita
All tables from database "sugita":
--------------------------------236159 = class1
$ oid2name -d sugita -x
All tables from database "sugita":
--------------------------------236159 = class1
16406 = pg_aggregate
...
1247
= pg_type
16650 = pg_type_oid_index
16651 = pg_type_typname_nsp_index
16681 = pg_user
16688 = pg_views
0
= pg_xactlock
$
c 2003 by Kenji Sugita
Copyright °
$ tree -F data
data/ … データベースクラスタ
|-- base/
|
|-- 1/ … template1
|
|-- 16975/ … template0
|
‘-- 16976/ … ユーザデータベース
|
|-- 1247 … テーブル、インデックス
...
シーケンス、TOAST
|
|-- 236159
...
|-- global/
|-- pg_clog/
|-- pg_hba.conf
|-- pg_ident.conf
|-- pg_xlog/
|-- postgresql.conf
|-- postmaster.opts
‘-- postmaster.pid
7 directories, 284 files
$
6
PostgreSQL チューニング入門
gennum、iterate … データ生成、繰り返し
=# SELECT gennum(3);
gennum
-------0
1
2
(3 rows)
=# SELECT gennum(3, 2);
gennum
-------2
3
4
(3 rows)
=# SELECT gennum(3, 2, 2);
gennum
-------2
4
6
(3 rows)
=# SELECT iterate(3);
iterate
---------
(3 rows)
=#
c 2003 by Kenji Sugita
Copyright °
=# CREATE TABLE class1(id integer, data text);
CREATE TABLE
=# INSERT INTO class1 SELECT n, rpad(’a’, n % 100, ’b’)
-#
FROM (SELECT gennum(100000) AS n) AS g;
INSERT 0 100000
=# SELECT count(*) FROM class1;
count
-------100000
(1 row)
=# SELECT * FROM class1 ORDER BY id LIMIT 10;
id |
data
----+----------0 |
=# INSERT INTO class1 SELECT (random() * 1000)
1 | a
-#
FROM (SELECT iterate(100000)) AS i;
2 | ab
INSERT 0 100000
3 | abb
=# SELECT * FROM class1 LIMIT 10;
4 | abbb
id | data
5 | abbbb
-----+-----6 | abbbbb
501 |
7 | abbbbbb
158 |
8 | abbbbbbb
566 |
9 | abbbbbbbb
252 |
(10 rows)
542 |
890 |
=#
318 |
200 |
366 |
207 |
(10 rows)
=#
7
PostgreSQL チューニング入門
lsof (List Open Files) … オープンファイルの表示。
$ lsof -h
lsof 4.60
latest revision: ftp://vic.cc.purdue.edu/pub/tools/unix/lsof/
latest FAQ: ftp://vic.cc.purdue.edu/pub/tools/unix/lsof/FAQ
latest man page: ftp://vic.cc.purdue.edu/pub/tools/unix/lsof/lsof_man
usage: [-?abChlnNoOPRstUvV] [-c c] [+|-d s] [+D D] [+|-f[cfgGn]]
[-F [f]] [-g [s]] [-i [i]] [-k k] [+|-L [l]] [-m m] [+|-M] [-o [o]] [-p s]
[+|-r [t]] [-S [t]] [-T [t]] [-u s] [+|-w] [--] [names]
Defaults in parentheses; comma-separate set (s) items; dash-separate ranges.
-?|-h list help
-a AND selections (OR)
-b avoid kernel blocks
-c c cmd c, /c/[bix]
-C no kernel name cache
+d s dir s files
-d s select by FD set
+D D dir D tree *SLOW?*
-i select IPv4 files
-l list UID numbers
-n no host names
-N select NFS files
-o list file offset
-O avoid overhead *RISKY*
-P no port names
-R list paRent PID
-s list file size
-t terse listing
-T disable TCP/TPI info
-U select Unix socket
-v list version info
-V verbose search
+|-w Warnings (+)
...
$ lsof -c post
COMMAND
PID
USER
FD
TYPE
DEVICE SIZE/OFF
NODE NAME
postgres
901 sugita cwd
VDIR
14,12
1408 1538735 /Volumes/Local (/dev/disk0s12)
postgres
901 sugita
0r VCHR
3,2
0t0 42883204 /dev/null
postgres
901 sugita
1w VREG
14,10
250937 1261306 /Volumes/Home (/dev/disk0s10)
postgres
901 sugita
2w VREG
14,10
250937 1261306 /Volumes/Home (/dev/disk0s10)
postgres
901 sugita
3u inet 0x02d3a75c
0t0
TCP *:5432 (LISTEN)
postgres
901 sugita
4u unix 0x02cffa78
0t0
->(none)
...
postgres 22999 sugita
39u VREG
14,12
16384 1526279 /Volumes/Local/opt/pgsql/7.3.1/data/global/16643
postgres 22999 sugita
40u VREG
14,12
8192 1526233 /Volumes/Local/opt/pgsql/7.3.1/data/global/1260
...
postgres 22999 sugita
70u VREG
14,12
73728 1526438 /Volumes/Local/opt/pgsql/7.3.1/data/base/16976/16408
postgres 22999 sugita
71u VREG
14,12
16384 1526450 /Volumes/Local/opt/pgsql/7.3.1/data/base/16976/16603
$
c 2003 by Kenji Sugita
Copyright °
8
PostgreSQL チューニング入門
チューニングに必要な知識
• ハードウェア (CPU 種類と数、ディスクの種類、RAID、 …)
• OS (入出力、ネットワーク、クラスター …)
• データベースシステム
– システムカタログ
– プロセス構造
– プログラム構造
• SQL
• 設計知識
• 業務知識
c 2003 by Kenji Sugita
Copyright °
9
PostgreSQL チューニング入門
日常の管理
• ログ
• バックアップ
• VACUUM、ANALYZE、インデックス再構築
• ディスク領域の監視と見積り
• その他のパフォーマンス調整
c 2003 by Kenji Sugita
Copyright °
10
PostgreSQL チューニング入門
ログ (その 1)
• 障害とアクセス状況の監視
– サーバーエラー
– クエリーエラー
– アクセスログ
– クエリーログ
• 方法
– syslog
大量のメッセージは、パフォーマンスに影響を与える。
– メッセージのリダイレクト
∗ データベースクラスタと同じディスクに保存するとパフォーマンスに影響
を与える。
∗ daemontools の multilog コマンド
http://cr.yp.to/djb.html
∗ Apache 付属の rotatelogs コマンド
c 2003 by Kenji Sugita
Copyright °
11
PostgreSQL チューニング入門
ログ (その 2)
multilog - daemontools のログプログラム
multilog script
スクリプト
t
-pattern
+pattern
s#
n#
!processor
dir
行の先頭に TAI64N 形式のタイムスタンプを付ける。tai64nlocal で可読形式に変換
pattern に一致する行を非選択。
pattern に一致する行を選択。
最大ファイルサイズ 4096∼16777215。デフォルト 99999。
最大ログファイル数。デフォルト 10。
フィルタプロッセッサを指定する。
ログの指定。
rotatelogs - Apache ログファイルのローテーションを行う
rotatelogs logfile rotationtime [offset]
標準入力からログメッセージを読み込み、ファイルに保存する。ファイルは、指定した
間隔で、ローテーションされる。
オプション
logfile
ログファイルのパス名。パス名には、strftime の % フォーマット指定を含め
ることができる。% 指定がない場合には、ファイル作成時刻から .nnnnnnnnnn
形式のサフィックスが求められ付加される。
rotationtime
ローテーション間隔を秒数で指定する。
offset
UTC との差を分数で指定する
c 2003 by Kenji Sugita
Copyright °
12
PostgreSQL チューニング入門
ログ (その 2)
• 関連 GUC
– silent mode
– syslog (0、0 … 端末、1 … 端末と syslog、2 … syslog)
– syslog facility (’LOCAL0’)
– syslog ident (’postgres’)
– log connections
– log duration
– log min error statement
– log pid
– log statement
– log timestamp
c 2003 by Kenji Sugita
Copyright °
13
PostgreSQL チューニング入門
VACUUM、ANALYZE インデックスの再構築
• VACUUM
不要領域の回収。
• ANALYZE
統計情報の収集。
• インデックスの再構築。
REINDEX、DROP INDEX&CREATE INDEX。
c 2003 by Kenji Sugita
Copyright °
14
PostgreSQL チューニング入門
VACUUM
• コンカレントバキューム
運用を妨げずに並行して実行可能
• 空き領域の登録
1 日に 1 回は実行したい。
• XID ラップアラウンドの回避
パラメータを指定しない VACUUM の実行。
• 指標
pgstattuple、pg chkrel。
c 2003 by Kenji Sugita
Copyright °
15
PostgreSQL チューニング入門
ANALYZE
• 統計情報の収集
プランナが統計情報とアクセスメソッドの評価関数を用いて、アクセスパスを評
価し、コストベースによる最適な実行プランを選択する。
• 実行タイミング
– データを大量に追加、更新、削除したときに実行する。
– 定期的に実行する。
c 2003 by Kenji Sugita
Copyright °
16
PostgreSQL チューニング入門
インデックスの再構築
• DROP INDEX & CREATE INDEX
データ量の多いバッチ更新処理がある場合。
• REINDEX
– 運用中の実行が可能。
対象テーブルはロックされる。実行時間を調整。
– システムカタログのインデックス再構築。
c 2003 by Kenji Sugita
Copyright °
17
PostgreSQL チューニング入門
ディスク領域の監視と見積り
• ディスク領域の監視
– 容量
– ボトルネック
• ディスク領域の見積り
単純で正確な方法はまだない。
c 2003 by Kenji Sugita
Copyright °
18
PostgreSQL チューニング入門
テーブル領域と不要領域の計測 (その 1)
整数フィールド 1 つ、主キー。10 万件の内 9 万件を削除。
削除後 ANALYZE
=# SELECT * FROM pgstattuple(’class1’);
-[ RECORD 1 ]------+-------table_len
| 3612672
tuple_count
| 10000
tuple_len
| 320000
tuple_percent
| 8.86
dead_tuple_count
| 90000
dead_tuple_len
| 2880000
dead_tuple_percent | 79.72
free_space
| 3848
free_percent
| 0.11
=#
VACUUM ANALYZE 後
=# SELECT * FROM pgstattuple(’class1’);
-[ RECORD 1 ]------+------table_len
| 368640
… テーブルの物理的な大きさ
tuple_count
| 10000
… タプル数
tuple_len
| 320000
… タプル長の合計
tuple_percent
| 86.81
… (tuple_len / table_len) x 100
dead_tuple_count
| 0
… デッドタプル数
dead_tuple_len
| 0
… デッドタプルタプル長の合計
dead_tuple_percent | 0
… (dead_tuple_len / table_len) x 100
free_space
| 2882260
… フリースペース
free_percent
| 79.78
… (free_space / table_len) x 100
=#
c 2003 by Kenji Sugita
Copyright °
19
PostgreSQL チューニング入門
テーブル領域と不要領域の計測 (その 2)
$ /usr/local/pgsql/tools/scripts/pg_chkrel
Date
: Thu May 1 15:25:06 JST 2003
Host
: papi
User
: sugita
Database Host
: default (local)
Database Name
: sugita (16976)
Database User
: sugita (1)
Database Port
: default
Database Directory : /opt/pgsql/7.3.1/data
Version
: PostgreSQL 7.3.1 on powerpc-apple-darwin6.4, compiled by GCC gcc (GCC) 3.1 20020420 (prerelease)
Page Size (KB)
: 8 (default)
Relation Name
r pg_catalog.pg_aggregate
i pg_catalog.pg_aggregate_fnoid_index
...
削除後 ANALYZE
r public.class1
i public.class1_pkey
VACUUM ANALYZE 後
r public.class1
i public.class1_pkey
REINDEX 後
r public.class1
i public.class1_pkey
VACUUM FULL 後
r public.class1
i public.class1_pkey
REINDEX 後
r public.class1
i public.class1_pkey
...
c 2003 by Kenji Sugita
Copyright °
File Name Tuples
16406
16600
Pages
File Size Size/Tuples Tuple Size
8
0.133
0.027
16
0.133
-.---
60
60
1 (
2 (
3)
2)
1247353
1247355
3065
1000
441 (
1 (
442)
1)
3528
2448
1.151
2.440
0.004
-.---
1247353
1247355
10000
10000
441 (
306 (
747)
306)
3528
2448
0.353
0.244
0.004
-.---
1247353
1347357
10000
10000
441 (
24 (
465)
24)
3528
192
0.353
0.018
0.004
-.---
1247353
1347357
10000
10000
45 (
47 (
92)
47)
360
376
0.036
0.037
0.004
-.---
1247353
1347358
10000
10000
45 (
24 (
69)
24)
360
192
0.036
0.018
0.004
-.---
20
PostgreSQL チューニング入門
その他のパフォーマンス調整 (その 1)
• フリースペースマップ
– max fsm pages … 必要ディスク容量 (バイト) / 8192
– max fsm relations
• ファイルディスクリプタキャッシュ
– max files per process
• WAL
– wal sync method … pg bench を用いる
– commit delay
– commit siblings
• コネクション数
– max connections
– superuser reserved connections
c 2003 by Kenji Sugita
Copyright °
21
PostgreSQL チューニング入門
wal sync method パラメータ (Solaris 2.6)
c 2003 by Kenji Sugita
Copyright °
22
PostgreSQL チューニング入門
その他のパフォーマンス調整 (その 2)
• 共有バッファ
– shared buffers
• ワークメモリ
– sort mem
– vacuum mem
c 2003 by Kenji Sugita
Copyright °
23
PostgreSQL チューニング入門
その他のパフォーマンス調整 (GEQO その 1)
• Pool Size
個体数 (解のとりうる空間中の解の候補の数)。pool は個体の入れものを指す。
解の多様性に関わる。大きな値にすると多くのパターンについて調べることがで
きるので、よい解が発見される可能性が高くなるが、計算量も増加する。
多様性は数だけでなく解の候補の初期状態の多様性にも依存する。PostgreSQL
では、解の候補の初期状態を一様乱数によって決めている。
• Generations
世代数 (計算回数)。
解の収束の遅い問題では大きく設定するが、速く収束する問題では大きくすると
無駄な計算を繰り返す。
PostgreSQL では解の収束条件がなく、常に Generations の回数の計算を行う。
c 2003 by Kenji Sugita
Copyright °
24
PostgreSQL チューニング入門
その他のパフォーマンス調整 (GEQO その 2)
• Selection Bias
通常の GA では、子の選択をするが PostgreSQL では親の選択を行っている。そ
の親の選択時のバイアス。変域は [1.5, 2.0] で、大きい程評価の高い親が選択さ
れる確率が大きくなる。50% 確率は 1.5 で上位 38.2%、2.0 で上位 29.3%。バイ
アスカーブは次の関数が用いられている。
p=b−
p
b2 − 4(b − 1)r
p: 値域 [0, 1.0] のバイアスのかかった乱数
b: 変域 [1.5, 2.0] のバイアス係数
r: 定義域 [0, 1.0] の一様乱数
c 2003 by Kenji Sugita
Copyright °
25
PostgreSQL チューニング入門
その他のパフォーマンス調整 (GEQO その 3)
• GEQO のアルゴリズム
解の候補を用意し、コスト (評価値) の順に並べる。
while (Generations 回繰り返す) {
・親を選択 (解の候補を 2 つ選択)
・ERX を行う。
・子のコストを求める。
・pool に、子を入れ、最低コストの解を取り出す。
}
ERX (Edge Recombination Crossover) … 辺再組合せ交叉、親を辺分解し、親の
共通辺を残すように再組合せした交叉。他の交叉のコードも書かれてるがデフォ
ルトではコンパイルされない。
c 2003 by Kenji Sugita
Copyright °
26
PostgreSQL チューニング入門
その他のパフォーマンス調整 (GEQO その 4)
• 関連 GUC
– geqo
– geqo effort
– geqo generations
– geqo pool size
– geqo random seed
– geqo selection bias
– geqo threshold
c 2003 by Kenji Sugita
Copyright °
27
PostgreSQL チューニング入門
パフォーマンスの監視 (その 1)
• データ量の変化を監視
– 最善は、全数試験
– 予想と合っているか
– 予想するにはどうするか
– 予想と違う原因
∗ 想定外のデータ
∗ 見積り方法が確立していない
• データの質がパフォーマンスを変える
– 予想と違う性質になる可能性がある
– 予想と違って来ていることを見逃さないようにする
– 変わってしまったならば、チューニングが必要
• データ量と質の変化で、データベースの設計ミスがデータの増加で露見
c 2003 by Kenji Sugita
Copyright °
28
PostgreSQL チューニング入門
パフォーマンスの監視 (その 2)
• EXPLAIN による監視
定期的に、システムで使用される典型的なクエリーの EXPLAIN の結果を監視し、
チェックする。
– インデックスが使われなくなっていないか
– 変化していたならば、それは妥当かどうか
– 件数の多いソートが頻発していないか
• 必要に応じて、実行時間の計測
\timing コマンド
• アクセス統計情報による監視
• 統計情報の妥当性の監視
c 2003 by Kenji Sugita
Copyright °
29
PostgreSQL チューニング入門
アクセス統計情報 (その 1)
• バックエンドアクティビティ
• アクセス統計情報
– シーケンシャルスキャン数
– インデックススキャン数
∗ インデックスが使用されているか
∗ 未使用インデックスの特定 … ディスクの無駄、更新時の負荷増加
– 挿入・更新・削除タプル数
• I/O 統計情報
– 読み込みディスクブロック数
– 読み込みディスクブロックのキャッシュヒット数
c 2003 by Kenji Sugita
Copyright °
30
PostgreSQL チューニング入門
アクセス統計情報 (その 2)
• 関連 GUC
– stats block level
– stats command string
– stats reset on server start
– stats row level
– stats start collector
c 2003 by Kenji Sugita
Copyright °
31
PostgreSQL チューニング入門
アクセス統計情報 (その 3)
=# CREATE TABLE class1 (id integer);
CREATE TABLE
=# INSERT INTO class1 SELECT gennum(100000);
INSERT 0 100000
=# SELECT * FROM pg_stat_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]-+------relid
| 436186
schemaname
| sugita
relname
| class1
seq_scan
| 0
seq_tup_read | 0
idx_scan
|
idx_tup_fetch |
n_tup_ins
| 100000
n_tup_upd
| 0
n_tup_del
| 0
=# SELECT count(*) FROM class1;
-[ RECORD 1 ]count | 100000
=# SELECT * FROM pg_stat_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]-+------relid
| 436186
schemaname
| sugita
relname
| class1
seq_scan
| 2
seq_tup_read | 200000
idx_scan
| 0
idx_tup_fetch | 0
n_tup_ins
| 100000
n_tup_upd
| 0
n_tup_del
| 0
=# CREATE INDEX class1_id_index ON class1(id);
CREATE INDEX
=# SELECT * FROM pg_stat_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]-+------relid
| 436186
schemaname
| sugita
relname
| class1
seq_scan
| 1
seq_tup_read | 100000
idx_scan
| 0
idx_tup_fetch | 0
n_tup_ins
| 100000
n_tup_upd
| 0
n_tup_del
| 0
c 2003 by Kenji Sugita
Copyright °
32
PostgreSQL チューニング入門
アクセス統計情報 (その 4)
=# SELECT * FROM class1 ORDER BY id LIMIT 1;
-[ RECORD 1 ]
id | 0
=# SELECT * FROM pg_stat_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]-+------relid
| 436186
schemaname
| sugita
relname
| class1
seq_scan
| 2
seq_tup_read | 200000
idx_scan
| 1
idx_tup_fetch | 2
n_tup_ins
| 100000
n_tup_upd
| 0
n_tup_del
| 0
=# SELECT * FROM class1 ORDER BY id DESC LIMIT 1;
-[ RECORD 1 ]
id | 99999
=# SELECT * FROM pg_stat_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]-+------relid
| 436186
schemaname
| sugita
relname
| class1
seq_scan
| 2
seq_tup_read | 200000
idx_scan
| 2
idx_tup_fetch | 4
n_tup_ins
| 100000
n_tup_upd
| 0
n_tup_del
| 0
c 2003 by Kenji Sugita
Copyright °
33
PostgreSQL チューニング入門
アクセス統計情報 (その 5)
=# CREATE TABLE class1 (id integer);
CREATE TABLE
=# INSERT INTO class1 SELECT gennum(1000000);
INSERT 0 1000000
=# SELECT * FROM pg_statio_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]---+-------relid
| 5347401
schemaname
| public
relname
| class1
heap_blks_read | 4406
heap_blks_hit
| 999999
idx_blks_read
|
idx_blks_hit
|
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit
|
=# CREATE INDEX class1_id_index ON class1(id);
CREATE INDEX
=# SELECT * FROM pg_statio_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]---+-------relid
| 5347401
schemaname
| public
relname
| class1
heap_blks_read | 8812
heap_blks_hit
| 999999
idx_blks_read
| 2194
idx_blks_hit
| 0
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit
|
c 2003 by Kenji Sugita
Copyright °
=# SELECT count(*) FROM class1;
-[ RECORD 1 ]-count | 1000000
=# SELECT * FROM pg_statio_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]---+-------relid
| 5347401
schemaname
| public
relname
| class1
heap_blks_read | 13218
heap_blks_hit
| 999999
idx_blks_read
| 2194
idx_blks_hit
| 0
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit
|
=# SELECT * FROM class1 ORDER BY id DESC LIMIT 1;
-[ RECORD 1 ]
id | 999999
-[ RECORD 1 ]---+-------relid
| 5347401
schemaname
| public
relname
| class1
heap_blks_read | 13218
heap_blks_hit
| 1000001
idx_blks_read
| 2198
idx_blks_hit
| 0
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit
|
34
PostgreSQL チューニング入門
アクセス統計情報 (その 6)
=# SELECT * FROM class1 ORDER BY ID DESC LIMIT 1;
-[ RECORD 1 ]
id | 999999
=# SELECT * FROM pg_statio_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]---+-------relid
| 5347401
schemaname
| public
relname
| class1
heap_blks_read | 13218
heap_blks_hit
| 1000003
idx_blks_read
| 2198
idx_blks_hit
| 4
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit
|
=# SELECT * FROM class1 ORDER BY id LIMIT 1;
-[ RECORD 1 ]
id | 0
=# SELECT * FROM pg_statio_all_tables WHERE relname = ’class1’;
-[ RECORD 1 ]---+-------relid
| 5347401
schemaname
| public
relname
| class1
heap_blks_read | 13219
heap_blks_hit
| 1000004
idx_blks_read
| 2200
idx_blks_hit
| 6
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit
|
=#
c 2003 by Kenji Sugita
Copyright °
35
PostgreSQL チューニング入門
パフォーマンスチューニング
• チューニング対象
– OS
– データベースシステム
ディスク配置、GUC、インデックス
– クエリー (一番問題が多い) … EXPLAIN による実行計画分析
– 処理フロー
– データベース設計
c 2003 by Kenji Sugita
Copyright °
36
PostgreSQL チューニング入門
OS チューニング
• 共有メモリ量
• オープンファイル数
• ファイルシステム
– 種類
– 最終アクセスタイム
– ジャーナル設定
– write cache
c 2003 by Kenji Sugita
Copyright °
37
PostgreSQL チューニング入門
ディスク分割
• OS、PostgreSQL のバイナリ、アプリケーション。
• データベースクラスタの分割
– ディスクを分離して、一般的には、どれだけ効果があるかは明確でない。シス
テム毎に試さないと効果は判断できない。
– PostgreSQL は、効果的なディスク分割ができない。tablespaces が必要。
data/ … データベースクラスタ ○
|-- base/
|
|-- 1/
… template1
|
|-- 16975/
… template0
|
‘-- 16976/
… ユーザデータベース
|
|-- 1247 … テーブル、インデックス、シーケンス、TOAST
|
|-- 1249
...
|
|-- 16680
|
|-- PG_VERSION
|
|-- pg_internal.init
|
‘-- pgsql_tmp/ … ソートとハッシュのワークディレクトリ ○
|-- global/ … グローバルテーブル、pg_control、pgstat.stat ○
|-- pg_clog/ … コミットログ ○
‘-- pg_xlog/ … WAL ○
c 2003 by Kenji Sugita
Copyright °
38
PostgreSQL チューニング入門
EXPLAIN (その 1)
• EXPLAIN
– 実行計画
– 最初のタプルが返る迄の予測コスト
– 全タプルが返る迄の予測コスト
– 結果の予測行数
– 結果の予測タプルサイズ
• EXPLAIN ANALYZE
– 実行計画
– 実測値 … クエリーの実行時間とは異なる
– 最初のタプルが返る迄の時間
– 全タプルが返る迄の時間
– 結果の行数
– クエリーオペレータの繰り返し回数
c 2003 by Kenji Sugita
Copyright °
39
PostgreSQL チューニング入門
EXPLAIN (その 2)
SELECT t1.typname AS castsource,
t2.typname AS casttarget,
p.proname AS proname,
p.provolatile AS provolatile,
c.castcontext AS castcontext
FROM pg_type t1, pg_type t2, pg_cast c, pg_proc p
WHERE c.castsource = t1.oid
AND
c.casttarget = t2.oid
AND
c.castfunc = p.oid
AND
t1.typname = ’bit’
ORDER BY 1, 2
;
EXPLAIN:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------Sort (cost=21.77..21.78 rows=1 width=218)
Sort Key: t1.typname, t2.typname
-> Nested Loop (cost=5.50..21.76 rows=1 width=218)
-> Nested Loop (cost=5.50..15.73 rows=1 width=149)
-> Hash Join (cost=5.50..10.12 rows=1 width=81)
Hash Cond: ("outer".castsource = "inner".oid)
-> Seq Scan on pg_cast c (cost=0.00..3.74 rows=174 width=13)
-> Hash (cost=5.49..5.49 rows=1 width=68)
-> Index Scan using pg_type_typname_nsp_index on pg_type t1 (cost=0.00..5.49 rows=1 width=68)
Index Cond: (typname = ’bit’::name)
-> Index Scan using pg_type_oid_index on pg_type t2 (cost=0.00..5.57 rows=1 width=68)
Index Cond: ("outer".casttarget = t2.oid)
-> Index Scan using pg_proc_oid_index on pg_proc p (cost=0.00..5.98 rows=1 width=69)
Index Cond: ("outer".castfunc = p.oid)
c 2003 by Kenji Sugita
Copyright °
40
PostgreSQL チューニング入門
EXPLAIN (その 3)
EXPLAIN ANALYZE:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------Sort (cost=21.77..21.78 rows=1 width=218) (actual time=54.44..54.45 rows=2 loops=1)
Sort Key: t1.typname, t2.typname
-> Nested Loop (cost=5.50..21.76 rows=1 width=218) (actual time=41.26..47.14 rows=2 loops=1)
-> Nested Loop (cost=5.50..15.73 rows=1 width=149) (actual time=27.16..28.48 rows=3 loops=1)
-> Hash Join (cost=5.50..10.12 rows=1 width=81) (actual time=27.07..28.21 rows=3 loops=1)
Hash Cond: ("outer".castsource = "inner".oid)
-> Seq Scan on pg_cast c (cost=0.00..3.74 rows=174 width=13) (actual time=6.97..9.11 rows=174 loops=1)
-> Hash (cost=5.49..5.49 rows=1 width=68) (actual time=17.89..17.89 rows=0 loops=1)
-> Index Scan using pg_type_typname_nsp_index on pg_type t1 (cost=0.00..5.49 rows=1 width=68)
(actual time=17.78..17.80 rows=1 loops=1)
Index Cond: (typname = ’bit’::name)
-> Index Scan using pg_type_oid_index on pg_type t2 (cost=0.00..5.57 rows=1 width=68) (actual time=0.04..0.05 rows=1 loops=3)
Index Cond: ("outer".casttarget = t2.oid)
-> Index Scan using pg_proc_oid_index on pg_proc p (cost=0.00..5.98 rows=1 width=69) (actual time=6.18..6.19 rows=1 loops=3)
Index Cond: ("outer".castfunc = p.oid)
Total runtime: 55.81 msec
Time: 88.98 ms
castsource | casttarget | proname | provolatile | castcontext
------------+------------+---------+-------------+------------bit
| int4
| int4
| i
| e
bit
| int8
| int8
| i
| e
Time: 41.05 ms
c 2003 by Kenji Sugita
Copyright °
41
PostgreSQL チューニング入門
クエリーオペレータ (その 1)
Seq Scan
Index Scan
Tid Scan
Nested Loop
Merge Join
Hash Join、Hash
c 2003 by Kenji Sugita
Copyright °
Sort
Group
Unique
Aggregate
Limit
Append
Materialize
Result
Subplan
Subquery Scan
Function Scan
Setop
Intersect
Intersect All
Except
Except All
42
PostgreSQL チューニング入門
クエリーオペレータ (その 2)
=# \d pg_proc
Table "pg_catalog.pg_proc"
Column
|
Type
| Modifiers
--------------+-----------+----------proname
| name
| not null
pronamespace | oid
| not null
proowner
| integer
| not null
prolang
| oid
| not null
proisagg
| boolean
| not null
prosecdef
| boolean
| not null
proisstrict | boolean
| not null
proretset
| boolean
| not null
provolatile | "char"
| not null
pronargs
| smallint | not null
prorettype
| oid
| not null
proargtypes | oidvector | not null
prosrc
| text
|
probin
| bytea
|
proacl
| aclitem[] |
Indexes: pg_proc_oid_index unique btree
pg_proc_proname_args_nsp_index
関数名
ネームスペース名
関数のオーナのデータベースユーザ id
記述言語、または、呼び出しインターフェースの oid
true ならば、集約関数
true ならば、関数のオーナ権限で実行
true ならば、引数が NULL の場合に NULL を返す
true ならば、集合を返す
IMMUTABLE | STABLE | VOLATILE
引数の数
戻り値のデータ型の oid
引数のデータ型の oid の配列
関数ハンドラが使用する関数呼び出し方法
関数呼び出し方法の付加情報
関数アクセス権
(oid),
unique btree (proname, pronargs, proargtypes, pronamespace)
=#
c 2003 by Kenji Sugita
Copyright °
43
PostgreSQL チューニング入門
クエリーオペレータ (その 3)
-- Seq Scan … シーケンシャルスキャン
SELECT * from pg_proc;
QUERY PLAN
------------------------------------------------------------Seq Scan on pg_proc (cost=0.00..73.07 rows=1507 width=311)
-- Index Scan … インデックススキャン
SELECT * from pg_proc WHERE proname = ’max’;
QUERY PLAN
-------------------------------------------------------------------------------------------------Index Scan using pg_proc_proname_args_nsp_index on pg_proc (cost=0.00..59.35 rows=16 width=311)
Index Cond: (proname = ’max’::name)
-- Tid Scan … ページ番号とページ内インデックスによるスキャン
SELECT * from pg_proc WHERE ctid = ’(7,24)’;
QUERY PLAN
--------------------------------------------------------Tid Scan on pg_proc (cost=0.00..4.01 rows=1 width=311)
Filter: (ctid = ’(7,24)’::tid)
c 2003 by Kenji Sugita
Copyright °
44
PostgreSQL チューニング入門
クエリーオペレータ (その 4)
-- Nested Loop … 入れ子反復結合
DROP TABLE class1;
CREATE TABLE class1 (id integer);
DROP TABLE class2;
CREATE TABLE class2 (id integer);
INSERT
CREATE
INSERT
CREATE
INTO class1 SELECT gennum(1000);
INDEX class1_id_index ON class1(id);
INTO class2 SELECT random() * 10000 FROM (SELECT iterate(10000)) AS i;
INDEX class2_id_index ON class2(id);
VACUUM ANALYZE;
SELECT c1.id AS ‘‘c1.id’’, c2.id AS ‘‘c2.id’’
FROM class1 c1, class2 c2
WHERE c1.id < 5 AND c2.id < 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------Nested Loop (cost=0.00..34.50 rows=24 width=8) (actual time=1.32..8.02 rows=55 loops=1)
-> Index Scan using class2_id_index on class2 c2 (cost=0.00..19.88 rows=5 width=4) (actual time=0.46..1.62 rows=11 loops=1)
Index Cond: (id < 10)
-> Index Scan using class1_id_index on class1 c1 (cost=0.00..3.07 rows=5 width=4) (actual time=0.10..0.15 rows=5 loops=11)
Index Cond: (id < 5)
c 2003 by Kenji Sugita
Copyright °
45
PostgreSQL チューニング入門
クエリーオペレータ (その 5)
-- Merge Join … マージソート結合
DROP TABLE class1;
CREATE TABLE class1 (id integer);
CREATE INDEX class1_id_index ON class1(id);
INSERT INTO class1 SELECT gennum(1000);
DROP TABLE class2;
CREATE TABLE class2 (id integer);
CREATE INDEX class2_id_index ON class2(id);
INSERT INTO class2 SELECT gennum(100);
VACUUM analyze;
SELECT c1.id AS ‘‘c1.id’’, c2.id AS ‘‘c2.id’’ FROM class1 c1, class2 c2
WHERE c1.id = c2.id;
QUERY PLAN
------------------------------------------------------------------------------------------Merge Join (cost=0.00..7.95 rows=100 width=8)
Merge Cond: (‘‘outer’’.id = ‘‘inner’’.id)
-> Index Scan using class1_id_index on class1 c1 (cost=0.00..21.00 rows=1000 width=4)
-> Index Scan using class2_id_index on class2 c2 (cost=0.00..4.10 rows=100 width=4)
c 2003 by Kenji Sugita
Copyright °
46
PostgreSQL チューニング入門
クエリーオペレータ (その 6)
-- Hash Join、Hash … ハッシュ結合
DROP TABLE class1;
CREATE TABLE class1 (id integer);
DROP TABLE class2;
CREATE TABLE class2 (id integer);
CREATE INDEX class2_id_index ON class2(id);
INSERT INTO class1 SELECT gennum(1000);
INSERT INTO class2 SELECT gennum(100) FROM (SELECT iterate(10)) AS i;
VACUUM ANALYZE;
SELECT * FROM class1 c1, class2 c2
WHERE c1.id = c2.id;
QUERY PLAN
------------------------------------------------------------------------Hash Join (cost=17.50..50.00 rows=1000 width=8)
Hash Cond: (‘‘outer’’.id = ‘‘inner’’.id)
-> Seq Scan on class2 c2 (cost=0.00..15.00 rows=1000 width=4)
-> Hash (cost=15.00..15.00 rows=1000 width=4)
-> Seq Scan on class1 c1 (cost=0.00..15.00 rows=1000 width=4)
-- Sort … ソート
SELECT * FROM pg_proc ORDER BY proname;
QUERY PLAN
------------------------------------------------------------------Sort (cost=152.62..156.39 rows=1507 width=311)
Sort Key: proname
-> Seq Scan on pg_proc (cost=0.00..73.07 rows=1507 width=311)
c 2003 by Kenji Sugita
Copyright °
47
PostgreSQL チューニング入門
クエリーオペレータ (その 7)
-- Group, Aggregate
-- PostgreSQL 7.3
SELECT proname, count(*) AS duplicates FROM pg_proc GROUP BY proname HAVING count(*) > 1;
QUERY PLAN
-----------------------------------------------------------------------------Aggregate (cost=152.62..167.69 rows=151 width=64)
Filter: (count(*) > 1)
-> Group (cost=152.62..160.16 rows=1507 width=64)
-> Sort (cost=152.62..156.39 rows=1507 width=64)
Sort Key: proname
-> Seq Scan on pg_proc (cost=0.00..73.07 rows=1507 width=64)
-- PostgreSQL 7.4devel
SELECT proname, count(*) AS duplicates FROM pg_proc GROUP BY proname HAVING count(*) > 1;
QUERY PLAN
-----------------------------------------------------------------HashAggregate (cost=85.39..94.47 rows=1210 width=64)
Filter: (count(*) > 1)
-> Seq Scan on pg_proc (cost=0.00..74.08 rows=1508 width=64)
-- Unique
SELECT DISTINCT proname FROM pg_proc;
QUERY PLAN
-----------------------------------------------------------------------Unique (cost=152.62..160.16 rows=151 width=64)
-> Sort (cost=152.62..156.39 rows=1507 width=64)
Sort Key: proname
-> Seq Scan on pg_proc (cost=0.00..73.07 rows=1507 width=64)
c 2003 by Kenji Sugita
Copyright °
48
PostgreSQL チューニング入門
クエリーオペレータ (その 8)
-- Limit
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------Limit (cost=8.55..8.58 rows=10 width=68)
-> Sort (cost=8.55..8.86 rows=124 width=68)
Sort Key: relpages
-> Seq Scan on pg_class (cost=0.00..4.24 rows=124 width=68)
c 2003 by Kenji Sugita
Copyright °
49
PostgreSQL チューニング入門
クエリーオペレータ (その 9)
-- Append, Subquery Scan
SELECT proname, oid FROM pg_proc WHERE proname = ’max’
UNION
SELECT proname, oid FROM pg_proc WHERE proname = ’min’;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------Unique (cost=119.50..119.74 rows=3 width=68)
-> Sort (cost=119.50..119.58 rows=32 width=68)
Sort Key: proname, oid
-> Append (cost=0.00..118.70 rows=32 width=68)
-> Subquery Scan "*SELECT* 1" (cost=0.00..59.35 rows=16 width=68)
-> Index Scan using pg_proc_proname_args_nsp_index on pg_proc (cost=0.00..59.35 rows=16 width=68)
Index Cond: (proname = ’max’::name)
-> Subquery Scan "*SELECT* 2" (cost=0.00..59.35 rows=16 width=68)
-> Index Scan using pg_proc_proname_args_nsp_index on pg_proc (cost=0.00..59.35 rows=16 width=68)
Index Cond: (proname = ’min’::name)
-- Materialize, Subplan
SELECT proname, oid FROM pg_proc
WHERE proname IN (SELECT proname FROM pg_proc WHERE proname ~ ’^macaddr’);
QUERY PLAN
------------------------------------------------------------------------------------------------------------Seq Scan on pg_proc (cost=0.00..9078.84 rows=754 width=68)
Filter: (subplan)
SubPlan
-> Materialize (cost=5.97..5.97 rows=1 width=64)
-> Index Scan using pg_proc_proname_args_nsp_index on pg_proc (cost=0.00..5.97 rows=1 width=64)
Index Cond: ((proname >= ’macaddr’::name) AND (proname < ’macadds’::name))
Filter: (proname ~ ’^macaddr’::text)
c 2003 by Kenji Sugita
Copyright °
50
PostgreSQL チューニング入門
クエリーオペレータ (その 10)
-- Result
SELECT pg_backend_pid();
QUERY PLAN
-----------------------------------------Result (cost=0.00..0.01 rows=1 width=0)
=#
-- Function Scan
SELECT *
FROM pg_lock_status()
AS (relation oid, "database" oid, "transaction" xid, pid integer, "mode" text, granted boolean);
QUERY PLAN
-----------------------------------------------------------------------Function Scan on pg_lock_status (cost=0.00..12.50 rows=1000 width=49)
c 2003 by Kenji Sugita
Copyright °
51
PostgreSQL チューニング入門
クエリーオペレータ (その 11)
SELECT gennum(2);
gennum
-------0
1
SELECT gennum(2, 1);
gennum
-------1
2
-- Intersect, Intersect All, Except, Except All
(SELECT gennum(2) UNION ALL SELECT gennum(2)) INTERSECT (SELECT gennum(2, 1) UNION ALL SELECT gennum(2, 1));
QUERY PLAN
-------------------------------------------------------------------------------------------SetOp Intersect (cost=0.08..0.10 rows=1 width=0)
-> Sort (cost=0.08..0.09 rows=4 width=0)
Sort Key: gennum
-> Append (cost=0.00..0.04 rows=4 width=0)
-> Result (cost=0.00..0.02 rows=2 width=0)
-> Append (cost=0.00..0.02 rows=2 width=0)
-> Subquery Scan "*SELECT* 1" (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.02 rows=2 width=0)
-> Append (cost=0.00..0.02 rows=2 width=0)
-> Subquery Scan "*SELECT* 3" (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan "*SELECT* 4" (cost=0.00..0.01 rows=1 width=0)
-> Result (cost=0.00..0.01 rows=1 width=0)
c 2003 by Kenji Sugita
Copyright °
52
PostgreSQL チューニング入門
プランナの制御
• PostgreSQL では、プランナに詳細な指示をすることはできない
• 統計情報を正確にするのが基本
• GUC によるプランナの制御
enable_hashjoin
enable_indexscan
enable_mergejoin
enable_nestloop
enable_seqscan
enable_sort
enable_tidscan
• JOIN で、明示的な結合順序を指定する
c 2003 by Kenji Sugita
Copyright °
53
PostgreSQL チューニング入門
EXPLAIN によるチューニング (その 1)
CREATE
CREATE
CREATE
CREATE
TABLE
INDEX
TABLE
INDEX
master (id text);
master_id_index ON master(id);
slave (id text);
slave_id_index ON slave(id);
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
INTO
master
master
master
master
master
master
master
master
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
slave
slave
slave
slave
slave
slave
slave
SELECT
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
SELECT
SELECT
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
n::text FROM (SELECT gennum(0, 10000) AS n) AS g;
(’10000’);
(’10000’);
(’10000’);
(’10000’);
(’10000’);
(’10000’);
n::text FROM (SELECT gennum(10000, 10000) AS n) AS g;
gennum(1000);
(’10000’);
(’10000’);
(’10000’);
(’11000’);
(’11000’);
(’11000’);
VACUUM ANALYZE;
c 2003 by Kenji Sugita
Copyright °
54
PostgreSQL チューニング入門
EXPLAIN によるチューニング (その 2)
SELECT * FROM master WHERE id IN (SELECT DISTINCT id FROM slave WHERE id = ’10000’);
id
------10000
10000
10000
10000
10000
10000
10000
(7 rows)
Time: 32.05 ms
EXPLAIN ANALYZE
SELECT * FROM MASTER WHERE id IN (SELECT DISTINCT id FROM slave WHERE id = ’10000’);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------Seq Scan on master (cost=0.00..61107.88 rows=5003 width=9) (actual time=0.12..50.12 rows=7 loops=1)
Filter: (subplan)
SubPlan
-> Materialize (cost=6.09..6.09 rows=1 width=6) (actual time=0.00..0.00 rows=1 loops=10006)
-> Unique (cost=0.00..6.09 rows=1 width=6) (actual time=0.06..0.08 rows=1 loops=1)
-> Index Scan using slave_id_index on slave (cost=0.00..6.08 rows=3 width=6) (actual time=0.06..0.07 rows=3 loops=1)
Index Cond: (id = ’10000’::text)
Total runtime: 50.56 msec
c 2003 by Kenji Sugita
Copyright °
55
PostgreSQL チューニング入門
EXPLAIN によるチューニング (その 3)
SELECT master.id FROM master, (SELECT DISTINCT id FROM slave WHERE id = ’10000’) AS s
WHERE s.id = master.id;
id
------10000
10000
10000
10000
10000
10000
10000
(7 rows)
Time: 2.44 ms
EXPLAIN ANALYZE
SELECT master.id FROM master, (SELECT DISTINCT id FROM slave WHERE id = ’10000’) AS s
WHERE s.id = master.id;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------Nested Loop (cost=0.00..9.12 rows=1 width=41) (actual time=0.09..0.16 rows=7 loops=1)
-> Subquery Scan s (cost=0.00..6.09 rows=1 width=6) (actual time=0.06..0.07 rows=1 loops=1)
-> Unique (cost=0.00..6.09 rows=1 width=6) (actual time=0.05..0.07 rows=1 loops=1)
-> Index Scan using slave_id_index on slave (cost=0.00..6.08 rows=3 width=6) (actual time=0.05..0.06 rows=3 loops=1)
Index Cond: (id = ’10000’::text)
-> Index Scan using master_id_index on master (cost=0.00..3.01 rows=1 width=9) (actual time=0.02..0.06 rows=7 loops=1)
Index Cond: (‘‘outer’’.id = master.id)
Total runtime: 0.33 msec
c 2003 by Kenji Sugita
Copyright °
56
PostgreSQL チューニング入門
レコードサイズの大きい場合のチューニング (その 1)
1. 2400 回の更新で、実行時間が 10 倍
2. カラム数が数百
3. pg filedump で、テーブルを見ると 1 ブロックに 1 レコードで、半分は空きス
ペース
4. アクセス統計情報で、読み込みブロック数が多く、ヒット率が低い。
5. VACUUM と REINDEX で元の速度
6. shared buffers を増やすことで実行時間が 7 倍
7. ブロッキングが効かないためインデックスの不要エントリによる極端な速度低下
が発生
暫定的な対処 … VACUUM 回数と shared buffers を増やす。
8. ブロックサイズを 32k に変更
9. 1 ブロックに 7 レコードとなる
10. 実行時間が 2 倍
11. shared buffers を増やし、10% の時間増に改善
c 2003 by Kenji Sugita
Copyright °
57
PostgreSQL チューニング入門
レコードサイズの大きい場合のチューニング (その 2)
$ pg_filedump 2347381
*****************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: 2347381
* Options used: None
*
* Dump created on: Fri May 2 09:55:08 2003
*****************************************************************
Block
2 ******************************************************
<Header> ----Block Offset: 0x00004000
Offsets: Lower
24 (0x0018)
Block Size: 8192
Upper
3972 (0x0f84)
LSN: logid
0 recoff 0x2480c1a4
Special 8192 (0x2000)
Items:
1
Free Space: 3948
Length (including item array): 28
<Data> -----Item
1 -- Length: 4218
Offset: 3972 (0x0f84)
Flags: USED
Block
0 ******************************************************
<Header> ----Block Offset: 0x00000000
Offsets: Lower
24 (0x0018)
Block Size: 8192
Upper
3964 (0x0f7c)
LSN: logid
0 recoff 0x24809fe8
Special 8192 (0x2000)
Items:
1
Free Space: 3940
Length (including item array): 28
Block
3 ******************************************************
<Header> ----Block Offset: 0x00006000
Offsets: Lower
24 (0x0018)
Block Size: 8192
Upper
3972 (0x0f84)
LSN: logid
0 recoff 0x2480d260
Special 8192 (0x2000)
Items:
1
Free Space: 3948
Length (including item array): 28
<Data> -----Item
1 -- Length: 4226
<Data> -----Item
1 -- Length: 4218
Offset: 3964 (0x0f7c)
Flags: USED
Offset: 3972 (0x0f84)
Flags: USED
Block
1 ******************************************************
<Header> ----Block Offset: 0x00002000
Offsets: Lower
24 (0x0018)
Block Size: 8192
Upper
3964 (0x0f7c)
LSN: logid
0 recoff 0x2480b0d4
Special 8192 (0x2000)
Items:
1
Free Space: 3940
Length (including item array): 28
<Data> -----Item
1 -- Length: 4226
Offset: 3964 (0x0f7c)
c 2003 by Kenji Sugita
Copyright °
Flags: USED
58
PostgreSQL チューニング入門
レコードサイズの大きい場合のチューニング (その 3)
=# select * from pg_statio_user_tables;
-[ RECORD 1 ]---+--------------relid
| 16558
relname
| largerecord
heap_blks_read | 12699
heap_blks_hit
| 2213
idx_blks_read
| 545
idx_blks_hit
| 181
toast_blks_read | 1
toast_blks_hit | 1
tidx_blks_read | 3
tidx_blks_hit
| 2
=#
=# select * from pg_statio_user_tables;
-[ RECORD 1 ]---+--------------relid
| 16555
relname
| largerecord
heap_blks_read | 756
heap_blks_hit
| 7988
idx_blks_read
| 404
idx_blks_hit
| 76
toast_blks_read |
toast_blks_hit |
tidx_blks_read |
tidx_blks_hit
|
=#
c 2003 by Kenji Sugita
Copyright °
59