pg_dbms_stats概要
統計情報の管理を行い、間接的に実行計画を制御可能とするものです。
主な機能として、バックアップ/リストア、ロック、パージ、およびエクスポート/インポートがあります。
<目次>
1.はじめに
2.機能紹介
3.実践例
3-1．性能劣化シナリオ
3-2．テーブルロックによる回避シナリオ
3-3．リストアによる回避シナリオ
3-4．複数オブジェクトによる回避シナリオ
4.導入例
マニュアル・ダウンロード
プロジェクト公式HP
https://ja.osdn.net/projects/pgdbmsstats/
検証環境について
本稿で用いた環境情報です。
・PostgreSQL9.6.3 レプリケーション環境
・pg_dbms_stats96-1.3.7-1
1．はじめに
実行計画が突然変わってしまい、SQLのレスポンスが悪化してしまうことがあります。
このとき、対応方法として思いつく手段は複数あるかと思います。DBMS製品と機能にもよることでしょう。
本稿では、pg_dbms_statsを用いた統計情報の管理のもと、間接的に実行計画を制御する手段について迫ります。
PostgreSQLは、コストベースでSQLの実行を計画します。
計画にあたり、判断材料として有効な情報(統計)を取得することとなります。
統計情報と実行計画の関係には相関性がありますが、人為的な統計情報の管理を以て、
(間接的に)実行計画の制御を図るのがpg_dbms_statsの位置づけとなります。
▼用途概要
pg_dbms_statsは、統計情報の管理機能を拡張したものです。
統計情報の(人為的な)管理とは、即ちpg_dbms_statsの機能となりますが、
バックアップ/リストア、ロック、パージ、およびエクスポート/インポートとなります。
従って、(正しい)実績の統計情報が存在してはじめて、これの適用(リストア・ロック)を以て
管理制御するということになります。
統計情報を手動で書き換えるなど変更して、思う通りの計画を能動的に作成するといった用途は不可能で、
PostgreSQLによって生成された統計情報の存在が前提です。
例えば、何らかのリリース・初回稼働にてSQLのパフォーマンスが出ない場合、この統計情報を以てすれば
解消すると断言できる実績のある実行計画が無いため、本機能は活用できません。
同一条件の別環境が存在し、別環境での動作では問題なかったが、当環境では何故か実行計画が変わってしまい
レスポンスが悪化する・・・のとき、別環境の実行計画をエクスポート/インポートして適用すれば、
悪化状況を別環境にて再現することができます。
機能によりますが、操作可能な単位は概ね、データベース・スキーマ・テーブル・列単位です。
作業に必要なインターフェースが全て関数化されるなど整っていれば良いのですが、所々の作業で原始的な手順が必要です。
従って、ある程度の内部仕様を理解しておく必要があり、これより説明させていただきます。
2．機能紹介
はじめに、PostgreSQL本体の動作仕様として、アナライズの結果はpg_catalogスキーマ内に存在する
特定のシステムカタログテーブルに格納されます。
pg_dbms_statsを導入しますと、dbms_statsスキーマが作成され、dbms_statsスキーマ配下には(固定)テーブルが作成されます。
システムカタログに格納されている正規統計がdbms_statsスキーマ配下のテーブルにコピー転送されることとなります。
これらのテーブル定義は、システムカタログと全く同じではありませんでしたが、ほぼ類似しておりました。
「差替用」と「BK蓄積用」の2目的分が存在します。
具体的なテーブルの名称として、実行計画生成に関わるシステムカタログテーブルは
pg_stats(厳密にはビューです)ビュー・pg_classテーブルの2つです。
前者が主にカラム統計を、後者がオブジェクト統計を担うようです。
対して、「差替用」と「BK蓄積用」でそれぞれ2テーブルずつ、合計4テーブルが用意されます。
さらに、backup_historyテーブルというバックアップの履歴管理用テーブルが用意され、総計5テーブルが
dbms_statsスキーマ配下に存在する形となります。
| 目的 | 正規統計 | 差替用 | BK蓄積用 |
|---|---|---|---|
| カラム単位 | pg_stats | column_stats_locked | column_stats_backup |
| オブジェクト単位 | pg_class | relation_stats_locked | relation_stats_backup |
実行計画の作成にあたり、必要な情報が「差替用」に含まれていた場合、この情報が優先的に使用されます。
従って、正規統計はアナライズによって都度変化する可能性がありますが、(固定)テーブルに格納している分には、
意図的に書き換えや削除を行うまで情報が変化することは起こり得ません。
即ち、統計情報は固定され、最終的に、間接的な実行計画の固定が実現されることとなります。
バックアップは"現在使用されている統計情報"を「BK蓄積用」にコピーすることと等価です
(加えて、世代管理上のIDの采番など管理面と思われる動作が少々)。
従って、使用対象のうち既に「差替用」を用いているオブジェクトがあれば、このオブジェクト分に関しては
差替用の情報がバックアップされます。
ロックとは、"現在使用されている統計情報"を「差替用」に格納することです。
リストアは「BK蓄積用」から「差替用」にコピーすることです。
ここで、差替用にセットされるということは、この情報が優先的に使用されるということになり、
ロックの意味も持つということを認識しておく必要があります。
バックアップ/リストア・ロック(アンロック)・パージおよびエクスポート/インポートは関数が用意されています。
バックアップの蓄積状況、ロックの状況を示す関数は用意されていません。
つまり、前述のテーブルを管理者にて参照する必要があり、どのような動きをすればどのように格納されるか
理解していないと管理ができない状況になります(本稿の目的)。
具体的な使い方の一例を、追って実践にて紹介いたします。
3．実践例
3-1．性能劣化シナリオ
ロックやバックアップ/リストアを試みるにあたり、性能劣化のシナリオを作成します。
ここでは、適当なテーブル・インデックスの作成とデータ挿入後、データの更新が行われて断片化等が発生。
結果、インデックスが用いられなくなりフルスキャンが行われるようになってしまったストーリーを確立します。
そのうえで、当シナリオ中にロックやバックアップ/リストアの手順を挟むことで、劣化しない、
あるいは劣化解消となることを確認したいと思います。
まずはpg_dbms_stats未導入の残念なシナリオから。
▼環境作成
適当なテーブルとインデックスを作成します。
stats_test=# create table stats_test(
stats_test(# id integer,
stats_test(# memo varchar(30)
stats_test(# );
CREATE TABLE
stats_test=#
stats_test=# create index idx_id on stats_test(id);
CREATE INDEX
stats_test=#
▼状態１
適当にデータを挿入し、アナライズします。
stats_test=# insert into stats_test select generate_series(1,6000),'memo';
INSERT 0 6000
stats_test=#
stats_test=# analyze stats_test;
ANALYZE
stats_test=#
▼状況確認
現状のデータ格納状況、正規統計情報の状況、およびテーブル参照結果を確認します。
きれいにデータ格納されており、またインデックススキャンが用いられています。
stats_test=# \x
Expanded display is on.
stats_test=# select * from pg_stat_user_tables where relname = 'stats_test';
-[ RECORD 1 ]-------+------------------------------
relid | 17509
schemaname | public
relname | stats_test
seq_scan | 1
seq_tup_read | 0
idx_scan | 2
idx_tup_fetch | 6002
n_tup_ins | 6000
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 6000
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum |
last_analyze | 2017-10-14 21:27:32.605297-07
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 0
analyze_count | 1
autoanalyze_count | 0
stats_test=#
stats_test=# select * from pg_stat_user_indexes where relname = 'stats_test';
-[ RECORD 1 ]-+-----------
relid | 17509
indexrelid | 17512
schemaname | public
relname | stats_test
indexrelname | idx_id
idx_scan | 2
idx_tup_read | 6002
idx_tup_fetch | 6002
stats_test=#
stats_test=# select * from pg_stats where tablename = 'stats_test';
-[ RECORD 1 ]----------+---------
schemaname | public
tablename | stats_test
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440,1500,1560,1620,1680,1740,1800,1860,1920,1980,2040,2100,2160,2220,2280,2340,2400,2460,2520,2580,2640,2700,2760,2820,2880,2940,3000,3060,3120,3180,3240,3300,3360,3420,3480,3540,3600,3660,3720,3780,3840,3900,3960,4020,4080,4140,4200,4260,4320,4380,4440,4500,4560,4620,4680,4740,4800,4860,4920,4980,5040,5100,5160,5220,5280,5340,5400,5460,5520,5580,5640,5700,5760,5820,5880,5940,6000}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 2 ]----------+------------
schemaname | public
tablename | stats_test
attname | memo
inherited | f
null_frac | 0
avg_width | 5
n_distinct | 1
most_common_vals | {memo}
most_common_freqs | {1}
histogram_bounds |
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
stats_test=#
stats_test=# select * from pg_class where relname = 'stats_test';
-[ RECORD 1 ]-------+-----------
relname | stats_test
relnamespace | 2200
reltype | 17511
reloftype | 0
relowner | 10
relam | 0
relfilenode | 17509
reltablespace | 0
relpages | 33
reltuples | 6000
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relfrozenxid | 17362
relminmxid | 1
relacl |
reloptions |
stats_test=# \x
Expanded display is off.
stats_test=#
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.056..5.164 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 0.568 ms
Execution time: 5.870 ms
(4 rows)
stats_test=#
▼状態２
データの更新が行われ、断片化など格納効率が劣化した状況を作成します。
連番を持つレコードに対し、delete-insertを歯抜けで仕掛け、並び順までバラバラになるようにします。
pgbenchなど負荷掛けツールを用いてもよいと思います。
stats_test=# update stats_test set memo = 'memo' where id between 1 and 1000;
UPDATE 1000
stats_test=# update stats_test set memo = 'memo' where id between 2001 and 3000;
UPDATE 1000
stats_test=# update stats_test set memo = 'memo' where id between 4001 and 5000;
UPDATE 1000
stats_test=# delete from stats_test where id between 1001 and 2000;
DELETE 1000
stats_test=# delete from stats_test where id between 3001 and 4000;
DELETE 1000
stats_test=# delete from stats_test where id between 5001 and 6000;
DELETE 1000
stats_test=# insert into stats_test select generate_series(1001,2000),'memo';
INSERT 0 1000
stats_test=# insert into stats_test select generate_series(3001,4000),'memo';
INSERT 0 1000
stats_test=# insert into stats_test select generate_series(5001,6000),'memo';
INSERT 0 1000
stats_test=#
stats_test=# analyze stats_test;
ANALYZE
stats_test=#
▼状況確認
事後状況確認です。ページ数の増加、コリレーションの低下などが確認できます。
実行計画に関しては、フルスキャンになりました。
フルスキャンが悪かどうかは別として、実行計画の変化が得られるシナリオであることが確認されました。
stats_test=# \x
Expanded display is on.
stats_test=# select * from pg_stat_user_tables where relname = 'stats_test';
-[ RECORD 1 ]-------+------------------------------
relid | 17509
schemaname | public
relname | stats_test
seq_scan | 2
seq_tup_read | 6000
idx_scan | 11
idx_tup_fetch | 15005
n_tup_ins | 9000
n_tup_upd | 3000
n_tup_del | 3000
n_tup_hot_upd | 0
n_live_tup | 6000
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum | 2017-10-14 21:31:46.618678-07
last_analyze | 2017-10-14 21:31:02.426294-07
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 1
analyze_count | 2
autoanalyze_count | 0
stats_test=#
n_tup_xxxより操作跡が確認できます。
autovacuumが行われているため、n_dead_tupは0となってしまいました。元は、無効行が存在
していたことでしょう。
stats_test=# select * from pg_stat_user_indexes where relname = 'stats_test';
-[ RECORD 1 ]-+-----------
relid | 17509
indexrelid | 17512
schemaname | public
relname | stats_test
indexrelname | idx_id
idx_scan | 11
idx_tup_read | 15005
idx_tup_fetch | 15005
stats_test=#
stats_test=# select * from pg_stats where tablename = 'stats_test';
-[ RECORD 1 ]----------+--------- schemaname | public
tablename | stats_test
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440,1500,1560,1620,1680,1740,1800,1860,1920,1980,2040,2100,2160,2220,2280,2340,2400,2460,2520,2580,2640,2700,2760,2820,2880,2940,3000,3060,3120,3180,3240,3300,3360,3420,3480,3540,3600,3660,3720,3780,3840,3900,3960,4020,4080,4140,4200,4260,4320,4380,4440,4500,4560,4620,4680,4740,4800,4860,4920,4980,5040,5100,5160,5220,5280,5340,5400,5460,5520,5580,5640,5700,5760,5820,5880,5940,6000}
correlation | 0.722222
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 2 ]----------+----------
schemaname | public
tablename | stats_test
attname | memo
inherited | f
null_frac | 0
avg_width | 5
n_distinct | 1
most_common_vals | {memo}
most_common_freqs | {1}
histogram_bounds |
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
stats_test=#
カラム名"id"について、correlationが1→0.7222・・・となり、並びの優位性が劣化したことが
確認できます。
stats_test=# select * from pg_class where relname = 'stats_test';
-[ RECORD 1 ]-------+-----------
relname | stats_test
relnamespace | 2200
reltype | 17511
reloftype | 0
relowner | 10
relam | 0
relfilenode | 17509
reltablespace | 0
relpages | 65
reltuples | 6000
relallvisible | 65
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relfrozenxid | 17362
relminmxid | 1
relacl |
reloptions |
stats_test=#
ページ数が33→65となっており、無駄に膨らんでいます。単なるvacuumでは解消されません。
同じ6000レコード分の更新を行っているため、ページ数の膨らみにて約2倍で
辻褄が合う、といったところでしょうか。
stats_test=# \x
Expanded display is off.
stats_test=#
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on stats_test (cost=0.00..155.00 rows=3000 width=9) (actual time=0.334..3.133 rows=3001 loops=1)
Filter: ((id >= 1000) AND (id <= 4000))
Rows Removed by Filter: 2999
Planning time: 1.268 ms
Execution time: 3.751 ms
(5 rows)
stats_test=#
IndexScan→SeqScanとなっております。当然ながら、発行しているSQL文は先ほどと同じ。
id＝1000~4000の3000レコードという半端なレコード数の参照を以て実現できています。
これが2000や4000レコードを対象ですと、当環境ではまた異なった結果となりました。
▼解消
当問題の正解として、無効行の掃除および格納順序の整備を行うことで
再度インデックススキャンが行われるようになりました。
vacuum fullでは足りず、格納順序(コリレーション)-clusterを実施する必要がありました。
stats_test=# cluster stats_test using idx_id;
CLUSTER
stats_test=#
stats_test=# analyze stats_test;
ANALYZE
stats_test=#
▼状況確認
clusterにて掃除された結果、当初の状況に戻っています。
stats_test=# \x
Expanded display is on.
stats_test=# select * from pg_stat_user_tables where relname = 'stats_test';
-[ RECORD 1 ]-------+------------------------------
relid | 17509
schemaname | public
relname | stats_test
seq_scan | 4
seq_tup_read | 18000
idx_scan | 12
idx_tup_fetch | 21005
n_tup_ins | 9000
n_tup_upd | 3000
n_tup_del | 3000
n_tup_hot_upd | 0
n_live_tup | 6000
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum | 2017-10-14 21:31:46.618678-07
last_analyze | 2017-10-14 21:32:41.240096-07
last_autoanalyze |
vacuum_count | 0
autovacuum_count | 1
analyze_count | 3
autoanalyze_count | 0
stats_test=#
stats_test=# select * from pg_stat_user_indexes where relname = 'stats_test';
-[ RECORD 1 ]-+-----------
relid | 17509
indexrelid | 17512
schemaname | public
relname | stats_test
indexrelname | idx_id
idx_scan | 12
idx_tup_read | 21005
idx_tup_fetch | 21005
stats_test=#
stats_test=# select * from pg_stats where tablename = 'stats_test';
-[ RECORD 1 ]----------+-------
schemaname | public
tablename | stats_test
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440,1500,1560,1620,1680,1740,1800,1860,1920,1980,2040,2100,2160,2220,2280,2340,2400,2460,2520,2580,2640,2700,2760,2820,2880,2940,3000,3060,3120,3180,3240,3300,3360,3420,3480,3540,3600,3660,3720,3780,3840,3900,3960,4020,4080,4140,4200,4260,4320,4380,4440,4500,4560,4620,4680,4740,4800,4860,4920,4980,5040,5100,5160,5220,5280,5340,5400,5460,5520,5580,5640,5700,5760,5820,5880,5940,6000}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-[ RECORD 2 ]----------+---------
schemaname | public
tablename | stats_test
attname | memo
inherited | f
null_frac | 0
avg_width | 5
n_distinct | 1
most_common_vals | {memo}
most_common_freqs | {1}
histogram_bounds |
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
stats_test=#
"id"のcorrelationが1になりました。
stats_test=# select * from pg_class where relname = 'stats_test';
-[ RECORD 1 ]-------+-----------
relname | stats_test
relnamespace | 2200
reltype | 17511
reloftype | 0
relowner | 10
relam | 0
relfilenode | 17513
reltablespace | 0
relpages | 33
reltuples | 6000
relallvisible | 0
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 2
relchecks | 0
relhasoids | f
relhaspkey | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relfrozenxid | 17376
relminmxid | 1
relacl |
reloptions |
stats_test=# \x
Expanded display is off.
stats_test=#
ページ数が33に戻りました。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.190..5.193 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 1.228 ms
Execution time: 6.057 ms
(4 rows)
stats_test=#
IndexScanに戻りました。
3-2．テーブルロックによる回避シナリオ
ここでは、ロックを試してみたいと思います。劣化前にロックを行うことで、格納効率悪化シナリオ後もインデックススキャンが使用されることを確認します。
▼環境作成
▼状態１
▼状況確認
ここまで、同じシナリオです。実行結果は割愛します。インデックススキャンの状況です。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.058..15.757 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 4.124 ms
Execution time: 16.866 ms
(4 rows)
stats_test=#
◎pg_dbms_stats内部状況
ロックを行うまえにpg_dbms_statsスキーマ内部テーブルの事前状態を確認したいと思います。
当然かもしれませんが、まだ何も行っていないため全て空となっています。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
(0 rows)
stats_test=# select * from dbms_stats.relation_stats_locked;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
(0 rows)
stats_test=# select * from dbms_stats.relation_stats_backup;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
(0 rows)
stats_test=# \x
Expanded display is off.
stats_test=#
◎ロック
ロックを行います。ここでは、該当テーブルに対しテーブル単体へのロックとしてみます。
テーブルのみに実施したところ、インデックスのロックがされないことが分かり、インデックスにも実施しています。
マニュアルにはテーブル単位と書かれているのですが、オブジェクト単位という表現のほうが近いかもしれません。
stats_test=# select dbms_stats.lock_table_stats('public.stats_test');
lock_table_stats
------------------
stats_test
(1 row)
stats_test=# select dbms_stats.lock_table_stats('public.idx_id');
lock_table_stats
------------------
idx_id
(1 row)
stats_test=#
ロックに関する関数名は、lock_<操作単位>_stats で統一されているようでした。
lock_table_stats関数を用い、引数には"スキーマ名.オブジェクト名"を指定しました。
ほかに、同じ関数名で異なる引数を受け付け可能な場合もあり、使用される場合はマニュアルの参照をおすすめします。
◎pg_dbms_stats内部状況
事後確認です。ロックにつき、BK蓄積用への格納はありません。
カラム単位-column_stats_lockedには2カラム分の2レコードが、
オブジェクト単位-relation_stats_lockedには1テーブル1インデックスの２レコードが格納されました。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
-[ RECORD 1 ]----------
starelid | 17534
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | -1
stakind1 | 2
stakind2 | 3
stakind3 | 0
stakind4 | 0
stakind5 | 0
staop1 | 97
staop2 | 97
staop3 | 0
staop4 | 0
staop5 | 0
stanumbers1 |
stanumbers2 | {1}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {1,60,120,180,240,300,360,420,480,540,600,660,720,780,840,900,960,1020,1080,1140,1200,1260,1320,1380,1440,1500,1560,1620,1680,1740,1800,1860,1920,1980,2040,2100,2
160,2220,2280,2340,2400,2460,2520,2580,2640,2700,2760,2820,2880,2940,3000,3060,3120,3180,3240,3300,3360,3420,3480,3540,3600,3660,3720,3780,3840,3900,3960,4020,4080,4140,4200,42
60,4320,4380,4440,4500,4560,4620,4680,4740,4800,4860,4920,4980,5040,5100,5160,5220,5280,5340,5400,5460,5520,5580,5640,5700,5760,5820,5880,5940,6000}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
-[ RECORD 2 ]--------
starelid | 17534
staattnum | 2
stainherit | f
stanullfrac | 0
stawidth | 5
stadistinct | 1
stakind1 | 1
stakind2 | 3
stakind3 | 0
stakind4 | 0
stakind5 | 0
staop1 | 98
staop2 | 664
staop3 | 0
staop4 | 0
staop5 | 0
stanumbers1 | {1}
stanumbers2 | {1}
stanumbers3 |
stanumbers4 |
stanumbers5 |
stavalues1 | {memo}
stavalues2 |
stavalues3 |
stavalues4 |
stavalues5 |
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_locked;
-[ RECORD 1 ]----+------------------------------
relid | 17534
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 01:04:01.451301-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
relid | 17537
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_backup;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
(0 rows)
stats_test=# \x
Expanded display is off.
stats_test=#
▼状態２
劣化させます。実行内容、結果は同じです。割愛します。
▼状況確認
実行計画を確認します。フルスキャンとならず、インデックスキャンのままとなっています。
期待値通りです。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.024..2.842 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 1.580 ms
Execution time: 3.558 ms
(4 rows)
stats_test=#
◎ロック解除
さらに、ロックを解除してみましょう。
自爆的行為ではありますが、これにて正規統計が用いられることになり、フルスキャンが行われるはずです。
引き続き、テーブル単位にて、テーブル・インデックスを対象といたします。
stats_test=# select dbms_stats.unlock_table_stats('public.stats_test');
unlock_table_stats
--------------------
stats_test
(1 row)
stats_test=# select dbms_stats.unlock_table_stats('public.idx_id');
unlock_table_stats
--------------------
idx_id
(1 row)
stats_test=#
ロックに対し、ロック解除としてunlock～(unはじまり)の名称の関数を用いています。
引数の要領もロックと同様でした。
◎pg_dbms_stats内部状況
アナライズは行われており、正規統計としてはフルスキャンが導出される内容となっているため、
解除によりインデックススキャンからフルスキャンへ変化しました。
また、差替用から統計情報が削除されています。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
(0 rows)
stats_test=# select * from dbms_stats.relation_stats_locked;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
(0 rows)
stats_test=# select * from dbms_stats.relation_stats_backup;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
(0 rows)
stats_test=# \x
Expanded display is off.
stats_test=#
▼状況確認
フルスキャンが得られました。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on stats_test (cost=0.00..155.00 rows=3000 width=9) (actual time=2.685..14.832 rows=3001 loops=1)
Filter: ((id >= 1000) AND (id <= 4000))
Rows Removed by Filter: 2999
Planning time: 1.724 ms
Execution time: 16.401 ms
(5 rows)
stats_test=#
振り返りまして、期待値通りの動きかと思います。
テーブル単位で操作を試み、テーブルとそのインデックス全てに対してロックを行う必要があった点が印象的でした。
3-3．リストアによる回避シナリオ
今度は、前述のロックのタイミングでバックアップを取得しておきます。
格納効率低下後に一旦フルスキャンになるも、リストアにて復旧するシナリオです。
バックアップの取得をスキーマ単位で、リストアをテーブル単位で実施してみたいと思います。
単位の差が分かるよう、ロックのシナリオに加えて余計に1つテーブルを設けてみます。
バックアップでは2テーブル分が、リストアでは差替用に1テーブルのみの情報がセットされる様子を確認します。
▼環境作成
▼状態１
▼状況確認
ここでは、テーブルをもう1つ作成しています。とはいえ、単にcreateを余計に1本発行しているだけです。
要領は変わりません。
引き続き、初期状態はインデックススキャンです。
create table stats_test(id integer, memo varchar(30));
create index idx_id on stats_test(id);
create table stats_test2(id integer, memo varchar(30)); ・・・★new
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.036..2.255 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 5.426 ms
Execution time: 4.667 ms
(4 rows)
stats_test=#
◎pg_dbms_stats内部状況
こちらも、初期状態は引き続き空です。
◎バックアップ
スキーマ単位で指定します。第1引数にスキーマ名を、第2引数にコメントをセットします。
バックアップについては、backup_<操作単位>_statsの名称で統一されているようでした。
stats_test=# select dbms_stats.backup_schema_stats('public','backup test');
backup_schema_stats
---------------------
1
(1 row)
stats_test=#
◎pg_dbms_stats内部状況
差替用は空です。BK蓄積用について、
カラム単位用としては、2テーブル×2カラム分の4レコードが、
オブジェクト単位用としては、2テーブル＋1インデックス分の3レコードが
格納されています。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_locked;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
-[ RECORD 1 ]---------
id | 1
statypid | 23
starelid | 17538
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]---------
id | 1
statypid | 1043
starelid | 17538
staattnum | 2
<<<中略>>>
-[ RECORD 3 ]----------
id | 1
statypid | 23
starelid | 17542
staattnum | 1
<<<中略>>>
-[ RECORD 4 ]----------
id | 1
statypid | 1043
starelid | 17542
staattnum | 2
<<<中略>>>
stats_test=#
同断面(id=1)のなかで、2オブジェクト(starelid=17538、17542)でそれぞれ2カラム
(staattnum=1、2)のデータが格納されている様子が伺えます。
stats_test=# select * from dbms_stats.relation_stats_backup;
-[ RECORD 1 ]----+------------------------------
id | 1
relid | 17538
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 01:15:38.086186-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
id | 1
relid | 17541
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 3 ]----+------------------------------
id | 1
relid | 17542
relname | public.stats_test2
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze |
last_autoanalyze | 2017-10-15 01:15:50.856078-07
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
-[ RECORD 1 ]--------------------------
id | 1
time | 2017-10-15 01:32:01.783488-07
unit | s
comment | backup test
stats_test=#
stats_test=# \x
Expanded display is off.
stats_test=#
id=1の断面が入りました。入力したコメントが格納された様子も伺えます。
▼状態２
▼状況確認
シナリオに従い、フルスキャンとなります。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on stats_test (cost=0.00..155.00 rows=3000 width=9) (actual time=1.545..8.926 rows=3001 loops=1)
Filter: ((id >= 1000) AND (id <= 4000))
Rows Removed by Filter: 2999
Planning time: 3.495 ms
Execution time: 9.869 ms
(5 rows)
stats_test=#
◎リストア
テーブル単位でリストアを行います。1テーブル1インデックスの2オブジェクトが対象です。
関数名はrestore～ではじまる名称でした。
第1引数にスキーマ.テーブル名を、第2引数にバックアップのタイムスタンプを指定します。
タイムスタンプは、先ほど確認したbackup_historyテーブルのtimeカラムより値を引用しています。
stats_test=# select dbms_stats.restore_table_stats('public.stats_test','2017-10-15 01:32:01.783488-07');
restore_table_stats
---------------------
stats_test
(1 row)
stats_test=#
stats_test=# select dbms_stats.restore_table_stats('public.idx_id','2017-10-15 01:32:01.783488-07');
restore_table_stats
---------------------
idx_id
(1 row)
stats_test=#
◎pg_dbms_stats内部状況
差替用に1テーブル1インデックスの2オブジェクト分がセットされたことを確認します(BK蓄積用は何も変わりません)。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
-[ RECORD 1 ]---------
starelid | 17538
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]------------
starelid | 17538
staattnum | 2
<<<中略>>>
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_locked;
-[ RECORD 1 ]----+------------------------------
relid | 17538
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 01:15:38.086186-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
relid | 17541
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
-[ RECORD 1 ]------------
id | 1
statypid | 23
starelid | 17538
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]-----------
id | 1
statypid | 1043
starelid | 17538
staattnum | 2
<<<中略>>>
-[ RECORD 3 ]-----------
id | 1
statypid | 23
starelid | 17542
staattnum | 1
<<<中略>>>
-[ RECORD 4 ]-----------
id | 1
statypid | 1043
starelid | 17542
staattnum | 2
<<<中略>>>
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_backup;
-[ RECORD 1 ]----+------------------------------
id | 1
relid | 17538
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 01:15:38.086186-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
id | 1
relid | 17541
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 3 ]----+------------------------------
id | 1
relid | 17542
relname | public.stats_test2
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze |
last_autoanalyze | 2017-10-15 01:15:50.856078-07
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
-[ RECORD 1 ]--------------------------
id | 1
time | 2017-10-15 01:32:01.783488-07
unit | s
comment | backup test
stats_test=#
stats_test=# \x
Expanded display is off.
stats_test=#
▼状況確認
インデックスキャンが得られることを確認します。リストアにて復旧するシナリオ通りとなっております。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.469..24.648 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 27.410 ms
Execution time: 28.043 ms
(4 rows)
stats_test=#
●削除
ついでに、バックアップ、ロックの削除を行います。
該当テーブルの削除を行うと、pg_dbms_statsとして退避(差替用、BK蓄積用)された情報は自動削除されず、
現実との整合のため掃除用の関数を発行する必要があります。
stats_test=# select dbms_stats.purge_stats(1);
WARNING: no database-wide backup will remain after purge
HINT: Give true for second parameter to purge forcibly.
purge_stats
-------------
(0 rows)
stats_test=#
stats_test=# select dbms_stats.clean_up_stats();
clean_up_stats
----------------------
public.stats_test, 1
public.stats_test, 2
public.stats_test,
public.idx_id,
(4 rows)
stats_test=#
purge_statsにてバックアップの削除を、clean_up_statsにて不整合情報の掃除を試みました。
前者でエラーとなりました。
マニュアルによると、
「基本的には任意世代以前のバックアップを削除できますが、不用意にバックアップを
削除してしまうことを防ぐために、パージ後にデータベース単位のバックアップが
ひとつも残らない状況での一括削除はできません。
この制限は、ユーザが強制削除を指定することで回避可能です」
とのことで、スキーマ単位1回分が存在する現状では、強制しない限りは失敗する仕様のようです。
◎pg_dbms_stats内部状況
掃除され、差替用については空の状況に戻りました。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
(0 rows)
stats_test=# select * from dbms_stats.relation_stats_locked;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
-[ RECORD 1 ]----------
id | 1
statypid | 23
starelid | 17538
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]----------
id | 1
statypid | 1043
starelid | 17538
staattnum | 2
<<<中略>>>
-[ RECORD 3 ]---------
id | 1
statypid | 23
starelid | 17542
staattnum | 1
<<<中略>>>
-[ RECORD 4 ]------------
id | 1
statypid | 1043
starelid | 17542
staattnum | 2
<<<中略>>>
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_backup;
-[ RECORD 1 ]----+------------------------------
id | 1
relid | 17538
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 01:15:38.086186-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
id | 1
relid | 17541
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 3 ]----+------------------------------
id | 1
relid | 17542
relname | public.stats_test2
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze |
last_autoanalyze | 2017-10-15 01:15:50.856078-07
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
-[ RECORD 1 ]--------------------------
id | 1
time | 2017-10-15 01:32:01.783488-07
unit | s
comment | backup test
stats_test=#
stats_test=# \x
Expanded display is off.
stats_test=#
3-4．複数オブジェクトによるシナリオ
3-3では、バックアップをスキーマ単位で、リストアをテーブル(オブジェクト)単位で行いました。
ここでは、リストアもスキーマ単位で行ってみたいと思います。
テーブルおよびインデックスを同様のペアの形で2つ作成してみます。
▼環境作成
"テーブル名"、"テーブル名2"などとしております。インデックスの名称も、後ろに2を付けました。
create table stats_test(id integer, memo varchar(30));
create index idx_id on stats_test(id);
create table stats_test2(id integer, memo varchar(30)); ・・・★new
create index idx_id2 on stats_test2(id); ・・・★new
▼状態１
データ挿入後、2側のみ劣化させます。
stats_test=# insert into stats_test select generate_series(1,6000),'memo';
INSERT 0 6000
stats_test=#
stats_test=# insert into stats_test2 select generate_series(1,6000),'memo';
INSERT 0 6000
stats_test=#
stats_test=# update stats_test2 set memo = 'memo' where id between 1 and 1000;
UPDATE 1000
stats_test=# update stats_test2 set memo = 'memo' where id between 2001 and 3000;
UPDATE 1000
stats_test=# update stats_test2 set memo = 'memo' where id between 4001 and 5000;
delete from stats_test2 where id between 1001 and 2000;
UPDATE 1000
stats_test=# delete from stats_test2 where id between 1001 and 2000;
DELETE 1000
stats_test=# delete from stats_test2 where id between 3001 and 4000;
DELETE 1000
stats_test=# delete from stats_test2 where id between 5001 and 6000;
DELETE 1000
stats_test=# insert into stats_test2 select generate_series(1001,2000),'memo';
INSERT 0 1000
stats_test=# insert into stats_test2 select generate_series(3001,4000),'memo';
insert into stats_test2 select generate_series(5001,6000),'memo';
INSERT 0 1000
stats_test=# insert into stats_test2 select generate_series(5001,6000),'memo';
INSERT 0 1000
stats_test=#
stats_test=# analyze stats_test;
ANALYZE
stats_test=# analyze stats_test2;
ANALYZE
stats_test=#
▼状況確認
2側がフルスキャンとなります。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.073..34.555 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 0.738 ms
Execution time: 36.394 ms
(4 rows)
stats_test=# explain analyze select * from stats_test2 where id between 1000 and 4000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on stats_test2 (cost=0.00..155.00 rows=3000 width=9) (actual time=1.662..6.099 rows=3001 loops=1)
Filter: ((id >= 1000) AND (id <= 4000))
Rows Removed by Filter: 2999
Planning time: 0.984 ms
Execution time: 6.739 ms
(5 rows)
stats_test=#
◎pg_dbms_stats内部状況
これまでと同じ状況につき、結果割愛します。
◎バックアップ
スキーマ単位で行います。断面の内訳としては、１側がインデックススキャン、2側がフルスキャンです。
stats_test=# select dbms_stats.backup_schema_stats('public','backup test');
backup_schema_stats
---------------------
2
(1 row)
stats_test=#
◎pg_dbms_stats内部状況
バックアップについて、カラム分としては、引き続き4レコード追加となります。
ID=2として追加されており、前回分であるID=1を含めると合計8レコードです。
オブジェクト分については2テーブル2インデックスの4レコード追加、前回3レコードで合計7レコードです。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
(0 rows)
stats_test=# select * from dbms_stats.relation_stats_locked;
(0 rows)
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
-[ RECORD 1 ]--------------
id | 1
statypid | 23
starelid | 17538
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]------------
id | 1
statypid | 1043
starelid | 17538
staattnum | 2
<<<中略>>>
-[ RECORD 3 ]---------
id | 1
statypid | 23
starelid | 17542
staattnum | 1
<<<中略>>>
-[ RECORD 4 ]----------
id | 1
statypid | 1043
starelid | 17542
staattnum | 2
<<<中略>>>
-[ RECORD 5 ]-----------
id | 2
statypid | 23
starelid | 17553
staattnum | 1
<<<中略>>>
-[ RECORD 6 ]--------------
id | 2
statypid | 1043
starelid | 17553
staattnum | 2
<<<中略>>>
-[ RECORD 7 ]---------------
id | 2
statypid | 23
starelid | 17557
staattnum | 1
<<<中略>>>
-[ RECORD 8 ]------------
id | 2
statypid | 1043
starelid | 17557
staattnum | 2
<<<中略>>>
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_backup;
-[ RECORD 1 ]----+------------------------------
id | 1
relid | 17538
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 01:15:38.086186-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
id | 1
relid | 17541
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 3 ]----+------------------------------
id | 1
relid | 17542
relname | public.stats_test2
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze |
last_autoanalyze | 2017-10-15 01:15:50.856078-07
-[ RECORD 4 ]----+------------------------------
id | 2
relid | 17553
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 02:30:03.797787-07
last_autoanalyze |
-[ RECORD 5 ]----+------------------------------
id | 2
relid | 17556
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 6 ]----+------------------------------
id | 2
relid | 17557
relname | public.stats_test2
relpages | 65
reltuples | 6000
relallvisible | 65
curpages | 65
last_analyze | 2017-10-15 02:30:04.292939-07
last_autoanalyze | 2017-10-15 02:29:52.549805-07
-[ RECORD 7 ]----+------------------------------
id | 2
relid | 17560
relname | public.idx_id2
relpages | 49
reltuples | 6000
relallvisible | 0
curpages | 49
last_analyze |
last_autoanalyze |
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
-[ RECORD 1 ]--------------------------
id | 1
time | 2017-10-15 01:32:01.783488-07
unit | s
comment | backup test
-[ RECORD 2 ]--------------------------
id | 2
time | 2017-10-15 02:31:39.500298-07
unit | s
comment | backup test
stats_test=# \x
Expanded display is off.
stats_test=#
▼状態３
次に、1側にもデータの更新をかけ劣化させます。
▼状況確認
両者フルスキャンとなります。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on stats_test (cost=0.00..155.00 rows=3000 width=9) (actual time=2.344..44.687 rows=3001 loops=1)
Filter: ((id >= 1000) AND (id <= 4000))
Rows Removed by Filter: 2999
Planning time: 13.013 ms
Execution time: 47.359 ms
(5 rows)
stats_test=# explain analyze select * from stats_test2 where id between 1000 and 4000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on stats_test2 (cost=0.00..155.00 rows=3000 width=9) (actual time=0.512..3.738 rows=3001 loops=1)
Filter: ((id >= 1000) AND (id <= 4000))
Rows Removed by Filter: 2999
Planning time: 0.615 ms
Execution time: 4.104 ms
(5 rows)
stats_test=#
◎リストア
スキーマ単位で実施します。
stats_test=# select dbms_stats.restore_schema_stats('public','2017-10-15 02:31:39.500298-07');
restore_schema_stats
----------------------
stats_test
idx_id
stats_test2
idx_id2
(4 rows)
stats_test=#
◎pg_dbms_stats内部状況
差替用に統計情報がセットされます。2テーブル(各2カラム)、2インデックスの４オブジェクト分となります。
１側はインデックススキャンが行われることになります。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
-[ RECORD 1 ]---------
starelid | 17553
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]--------
starelid | 17553
staattnum | 2
<<<中略>>>
-[ RECORD 3 ]--------
starelid | 17557
staattnum | 1
<<<中略>>>
-[ RECORD 4 ]----------
starelid | 17557
staattnum | 2
<<<中略>>>
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_locked;
-[ RECORD 1 ]----+------------------------------
relid | 17553
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 02:30:03.797787-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
relid | 17556
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 3 ]----+------------------------------
relid | 17557
relname | public.stats_test2
relpages | 65
reltuples | 6000
relallvisible | 65
curpages | 65
last_analyze | 2017-10-15 02:30:04.292939-07
last_autoanalyze | 2017-10-15 02:29:52.549805-07
-[ RECORD 4 ]----+------------------------------
relid | 17560
relname | public.idx_id2
relpages | 49
reltuples | 6000
relallvisible | 0
curpages | 49
last_analyze |
last_autoanalyze |
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
-[ RECORD 1 ]-----------
id | 1
statypid | 23
starelid | 17538
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]--------------
id | 1
statypid | 1043
starelid | 17538
staattnum | 2
<<<中略>>>
-[ RECORD 3 ]------------
id | 1
statypid | 23
starelid | 17542
staattnum | 1
<<<中略>>>
-[ RECORD 4 ]------------
id | 1
statypid | 1043
starelid | 17542
staattnum | 2
<<<中略>>>
-[ RECORD 5 ]-------------
id | 2
statypid | 23
starelid | 17553
staattnum | 1
<<<中略>>>
-[ RECORD 6 ]----------------
id | 2
statypid | 1043
starelid | 17553
staattnum | 2
-[ RECORD 7 ]---------------
id | 2
statypid | 23
starelid | 17557
staattnum | 1
<<<中略>>>
-[ RECORD 8 ]-----------------
id | 2
statypid | 1043
starelid | 17557
staattnum | 2
<<<中略>>>
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_backup;
-[ RECORD 1 ]----+------------------------------
id | 1
relid | 17538
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 01:15:38.086186-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
id | 1
relid | 17541
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 3 ]----+------------------------------
id | 1
relid | 17542
relname | public.stats_test2
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze |
last_autoanalyze | 2017-10-15 01:15:50.856078-07
-[ RECORD 4 ]----+------------------------------
id | 2
relid | 17553
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 02:30:03.797787-07
last_autoanalyze |
-[ RECORD 5 ]----+------------------------------
id | 2
relid | 17556
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 6 ]----+------------------------------
id | 2
relid | 17557
relname | public.stats_test2
relpages | 65
reltuples | 6000
relallvisible | 65
curpages | 65
last_analyze | 2017-10-15 02:30:04.292939-07
last_autoanalyze | 2017-10-15 02:29:52.549805-07
-[ RECORD 7 ]----+------------------------------
id | 2
relid | 17560
relname | public.idx_id2
relpages | 49
reltuples | 6000
relallvisible | 0
curpages | 49
last_analyze |
last_autoanalyze |
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
-[ RECORD 1 ]--------------------------
id | 1
time | 2017-10-15 01:32:01.783488-07
unit | s
comment | backup test
-[ RECORD 2 ]--------------------------
id | 2
time | 2017-10-15 02:31:39.500298-07
unit | s
comment | backup test
stats_test=# \x
Expanded display is off.
stats_test=#
▼状態４
ここで、2側をclusterにてメンテナンスします。
stats_test=# cluster stats_test2 using idx_id2;
CLUSTER
stats_test=#
stats_test=# analyze stats_test2;
ANALYZE
stats_test=#
▼状況確認
しかし、リストアにて差替用にセットされている＝ロックされているため、
結果はバックアップ時の状態と等しくなります。
メンテしたにもかかわらず、残念ながらstats_test2はフルスキャンのままです。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.043..67.147 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 15.267 ms
Execution time: 68.075 ms
(4 rows)
stats_test=# explain analyze select * from stats_test2 where id between 1000 and 4000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on stats_test2 (cost=0.00..155.00 rows=3000 width=9) (actual time=0.886..10.258 rows=3001 loops=1)
Filter: ((id >= 1000) AND (id <= 4000))
Rows Removed by Filter: 2999
Planning time: 1.351 ms
Execution time: 10.995 ms
(5 rows)
stats_test=#
◎ロック解除
2側のロックを解除し、バックアップ内容の適用状況を放棄すれば、clusterの効果があらためて見えてきます。
テーブル単位で行います。
stats_test=# select dbms_stats.unlock_table_stats('public.stats_test2');
unlock_table_stats
--------------------
stats_test2
(1 row)
stats_test=#
stats_test=# select dbms_stats.unlock_table_stats('public.idx_id2');
unlock_table_stats
--------------------
idx_id2
(1 row)
stats_test=#
◎pg_dbms_stats内部状況
テーブル単位で実施し、差替用の内容が、1側のみ残留となっています。
stats_test=# \x
Expanded display is on.
stats_test=# select * from dbms_stats.column_stats_locked;
-[ RECORD 1 ]----------
starelid | 17553
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]---------
starelid | 17553
staattnum | 2
<<<中略>>>
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_locked;
-[ RECORD 1 ]----+------------------------------
relid | 17553
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 02:30:03.797787-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
relid | 17556
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
stats_test=#
stats_test=# select * from dbms_stats.column_stats_backup;
-[ RECORD 1 ]------------
id | 1
statypid | 23
starelid | 17538
staattnum | 1
<<<中略>>>
-[ RECORD 2 ]-----------
id | 1
statypid | 1043
starelid | 17538
staattnum | 2
<<<中略>>>
-[ RECORD 3 ]----------
id | 1
statypid | 23
starelid | 17542
staattnum | 1
<<<中略>>>
-[ RECORD 4 ]-------------
id | 1
statypid | 1043
starelid | 17542
staattnum | 2
<<<中略>>>
-[ RECORD 5 ]-------------
id | 2
statypid | 23
starelid | 17553
staattnum | 1
<<<中略>>>
-[ RECORD 6 ]------------
id | 2
statypid | 1043
starelid | 17553
staattnum | 2
<<<中略>>>
-[ RECORD 7 ]---------------
id | 2
statypid | 23
starelid | 17557
staattnum | 1
<<<中略>>>
-[ RECORD 8 ]-------------
id | 2
statypid | 1043
starelid | 17557
staattnum | 2
<<<中略>>>
stats_test=#
stats_test=# select * from dbms_stats.relation_stats_backup;
-[ RECORD 1 ]----+------------------------------
id | 1
relid | 17538
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 01:15:38.086186-07
last_autoanalyze |
-[ RECORD 2 ]----+------------------------------
id | 1
relid | 17541
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 3 ]----+------------------------------
id | 1
relid | 17542
relname | public.stats_test2
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze |
last_autoanalyze | 2017-10-15 01:15:50.856078-07
-[ RECORD 4 ]----+------------------------------
id | 2
relid | 17553
relname | public.stats_test
relpages | 33
reltuples | 6000
relallvisible | 0
curpages | 33
last_analyze | 2017-10-15 02:30:03.797787-07
last_autoanalyze |
-[ RECORD 5 ]----+------------------------------
id | 2
relid | 17556
relname | public.idx_id
relpages | 19
reltuples | 6000
relallvisible | 0
curpages | 19
last_analyze |
last_autoanalyze |
-[ RECORD 6 ]----+------------------------------
id | 2
relid | 17557
relname | public.stats_test2
relpages | 65
reltuples | 6000
relallvisible | 65
curpages | 65
last_analyze | 2017-10-15 02:30:04.292939-07
last_autoanalyze | 2017-10-15 02:29:52.549805-07
-[ RECORD 7 ]----+------------------------------
id | 2
relid | 17560
relname | public.idx_id2
relpages | 49
reltuples | 6000
relallvisible | 0
curpages | 49
last_analyze |
last_autoanalyze |
stats_test=#
stats_test=# select * from dbms_stats.backup_history;
-[ RECORD 1 ]--------------------------
id | 1
time | 2017-10-15 01:32:01.783488-07
unit | s
comment | backup test
-[ RECORD 2 ]--------------------------
id | 2
time | 2017-10-15 02:31:39.500298-07
unit | s
comment | backup test
stats_test=#
stats_test=# \x
Expanded display is off.
stats_test=#
▼状況確認
両者インデックススキャンとなりました(めでたし)。
stats_test=# explain analyze select * from stats_test where id between 1000 and 4000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id on stats_test (cost=0.28..120.28 rows=3000 width=9) (actual time=0.247..11.949 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 0.640 ms
Execution time: 12.961 ms
(4 rows)
stats_test=# explain analyze select * from stats_test2 where id between 1000 and 4000;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id2 on stats_test2 (cost=0.28..120.28 rows=3000 width=9) (actual time=0.380..6.711 rows=3001 loops=1)
Index Cond: ((id >= 1000) AND (id <= 4000))
Planning time: 1.697 ms
Execution time: 7.347 ms
(4 rows)
stats_test=#
◎pg_dbms_stats内部状況
stats_test=# select * from dbms_stats.backup_history;
id | time | unit | comment
----+-------------------------------+------+-------------
1 | 2017-10-15 01:32:01.783488-07 | s | backup test
2 | 2017-10-15 02:31:39.500298-07 | s | backup test
(2 rows)
stats_test=#
3-2ロックおよび3-3バックアップリストアの結果を踏まえれば、3-4は取り扱い単位が多少異なったものの、
特に新しいことはありません。
ここで言いたかったことは、
バックアップの取得単位が大きく、またリストアも同様の単位で実施してしまったことで、
余計なオブジェクトにまでロックがかかってしまい、メンテナンス効果が得られなくなってしまった状況の模擬です。
このシナリオを通じ、ロックやどのバックアップにどのオブジェクトがどのような状況で含まれているか、
管理者が把握管理しておく必要がありそう、ということです。
管理できていないと、不用意なロックもしくは解除につながってしまうのではないでしょうか。
4．導入例
◆インストール
(両系実施)
PostgreSQLをインストールしたOSユーザで実施します(下記は片側のみの結果を転記)。
[root@testdb01 20171015]# id
uid=0(root) gid=0(root) groups=0(root) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[root@testdb01 20171015]#
[root@testdb01 20171015]# ls -l
total 32
-rw-rw-r--. 1 root root 28996 Oct 14 20:20 pg_dbms_stats96-1.3.7-1.el7.x86_64.rpm
[root@testdb01 20171015]#
[root@testdb01 20171015]# rpm -ihv ./pg_dbms_stats96-1.3.7-1.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:pg_dbms_stats96-1.3.7-1.el7 ################################# [100%]
[root@testdb01 20171015]#
[root@testdb01 20171015]# rpm -qa | grep pg_dbms_stats
pg_dbms_stats96-1.3.7-1.el7.x86_64
[root@testdb01 20171015]#
◆データベースへの登録
(はじめに本テスト実施用にデータベース作成しました)
-bash-4.2$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
-bash-4.2$
-bash-4.2$ createdb stats_test
-bash-4.2$
-bash-4.2$ psql -d stats_test -c "\l stats_test"
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+----------+----------+---------+-------+-------------------
stats_test | postgres | UTF8 | C | C |
(1 row)
-bash-4.2$
・データベースへの登録
スーパーユーザで行います。
(#1実施)
-bash-4.2$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
-bash-4.2$
-bash-4.2$ psql -d stats_test -c "CREATE EXTENSION pg_dbms_stats"
CREATE EXTENSION
-bash-4.2$
エクステンション一覧
-bash-4.2$ psql -d stats_test -c "select * from pg_available_extensions where name = 'pg_dbms_stats'"
name | default_version | installed_version | comment
---------------+-----------------+-------------------+--------------------------------------
pg_dbms_stats | 1.3.7 | 1.3.7 | stabilize plans by fixing statistics
(1 row)
-bash-4.2$
※登録を行い、"installed_version"に"1.3.7"の値が格納されました。
◆内部状況確認
・スキーマ一覧
-bash-4.2$ psql -d stats_test -c "\dn"
List of schemas
Name | Owner
------------+----------
dbms_stats | postgres
public | postgres
(2 rows)
-bash-4.2$
・テーブル一覧
-bash-4.2$ psql -d stats_test -c "\dt dbms_stats.*"
List of relations
Schema | Name | Type | Owner
------------+-----------------------+-------+----------
dbms_stats | backup_history | table | postgres
dbms_stats | column_stats_backup | table | postgres
dbms_stats | column_stats_locked | table | postgres
dbms_stats | relation_stats_backup | table | postgres
dbms_stats | relation_stats_locked | table | postgres
(5 rows)
-bash-4.2$
◆ロード
shared_preload_libraries パラメータに'pg_dbms_stats'を追加し、DBを再起動します。
-bash-4.2$ grep shared_preload_libraries postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_statsinfo,pg_dbms_stats'
-bash-4.2$