下記で作成したデータベースで検証するんご
PostgreSQL その44 性能評価とチューニング検証用データベース作成 その3
【課題】
・データとは、テーブル、インデックス、WALファイル、WALアーカイブファイル、その他
・ファイルシステム上のデータサイズ
※追記型データベースのPostgreSQLでは更新や削除後もファイル上に不要領域が残る。
※auto vacuum後のファイルシステム上のサイズ変化
※full vacuum後のファイルシステム上のサイズ変化
・pg_dumpallファイルのサイズとファイルシステム上のサイズの比較
・pgカタログやインフォメーションビューなどによる実データ領域のサイズ、不要領域のサイズの評価→PostgreSQL その49 監視項目 その1
・FILLFACTORとか使って下記の計算式で概算を求めてみる（PostgreSQL その46 構成）
総ページ数の概算値 = RN / (( 8192 * FF ) -24 ) / TS )
RN :想定レコード数 [件数]
FF :FILLFACTOR [%]
TS :行の想定平均サイズ [バイト] （行ヘッダや可変長列ヘッダサイズを含む）
■お題：
ファイルシステム上のテーブルサイズを調べる
・初期状態
せ70> du -h /var/lib/pgdata/base
7.6M /var/lib/pgdata/base/1
7.6M /var/lib/pgdata/base/13857
7.9M /var/lib/pgdata/base/13858
2.7G /var/lib/pgdata/base/16393
0 /var/lib/pgdata/base/pgsql_tmp
2.7G /var/lib/pgdata/base
testdb=> select datid,datname from pg_stat_database where datid=16393 or datid=13857 or datid=13858 or datid=1;
datid | datname
-------+-----------
13858 | postgres
1 | template1
13857 | template0
16393 | testdb
(4 rows)
・1千万レコードをもったtable0piをdropしてみる
せ70> time PGPASSWORD=punisuke psql -h 127.0.0.1 -U punisuke testdb -c "drop table table0pi"
DROP TABLE
real 0m0.360s ←0.36秒でdrop完了
user 0m0.002s
sys 0m0.006s
せ70> du -h /var/lib/pgdata/base
7.6M /var/lib/pgdata/base/1
7.6M /var/lib/pgdata/base/13857
7.9M /var/lib/pgdata/base/13858
1.9G /var/lib/pgdata/base/16393 ←800MBも減ったんご
0 /var/lib/pgdata/base/pgsql_tmp
2.0G /var/lib/pgdata/base
■お題：
大量updateを行う前後で下記の①～④を比較
①pg_database_size、
②pg_relation_size、pg_indexes_size
③ファイルシステム上のファイルサイズ（テーブル、インデックス）
④pg_dump
※全レコードの3分の1くらいupdateする
[大量update前]
①testdbデータベースのサイズ確認
testdb=# select * from pg_database_size('testdb');
-[ RECORD 1 ]----+-----------
pg_database_size | 2021283303 ←2021283303バイト (約1.88GB)
②テーブルとインデックスのサイズ確認
testdb=# select * from pg_relation_size('punisuke.table0');
-[ RECORD 1 ]----+----------
pg_relation_size | 781090816 ←テーブルサイズは約745MB
testdb=# select * from pg_relation_size('punisuke.table0i');
-[ RECORD 1 ]----+----------
pg_relation_size | 781090816 ←テーブルサイズは約745MB
testdb=# select * from pg_indexes_size('punisuke.table0i');
-[ RECORD 1 ]---+----------
pg_indexes_size | 449323008 ←インデックスサイズは約429MB
・現在testdbデータベース内のサイズの大きいテーブルやインデックスはすべてpunisukeスキーマに属しているので、punisukeスキーマのテーブルとインデックスサイズの一覧を確認
（参照：http://blog.y-yuki.net/entry/2017/07/22/000000）
testdb=# select objectname,to_char(pg_relation_size(objectname::regclass),'999,999,999,999') as bytes from(select tablename as objectname from pg_tables where schemaname='punisuke' union select indexname as objectname from pg_indexes where schemaname='punisuke') as objectlist order by bytes desc;
objectname | bytes
-----------------+------------------
table0i | 781,090,816 ←table0iテーブルは約745MB
table0 | 781,090,816 ←table0テーブルは約745MB
table0i_comment_idx | 224,665,600 ←table0i_comment_idxインデックスは約214MB
table0i_uriage_no_idx | 224,657,408 ←table0i_uriage_no_idxインデックスは約214MB
kokyaku1_pkey | 16,384
uriage2_pkey | 16,384
uriage1_pkey | 16,384
tb3_id_idx | 16,384
shohin1_pkey | 16,384
shohin2_pkey | 16,384
kokyaku2_pkey | 16,384
item | 8,192
tb2 | 8,192
punisuke_tb1 | 8,192
tab1 | 8,192
member | 8,192
uriage1 | 8,192
uriage2 | 8,192
tb3 | 8,192
tb1 | 8,192
shohin1 | 8,192
shohin2 | 8,192
meisai2 | 8,192
kokyaku2 | 8,192
kokyaku1 | 8,192
punisuke_tb2 | 0
(26 rows)
③ファイルシステム上のファイルサイズ（テーブル、インデックス）
せ70> du /var/lib/pgdata/base
7756 /var/lib/pgdata/base/1
7756 /var/lib/pgdata/base/13857
7988 /var/lib/pgdata/base/13858
1973936 /var/lib/pgdata/base/16393 ←1973936KB (約1.88GB)
0 /var/lib/pgdata/base/pgsql_tmp
1997436 /var/lib/pgdata/base
testdb=# select relname,relfilenode,relpages,reltuples from pg_class where relname='table0';
relname | relfilenode | relpages | reltuples
-------+---------+--------+------------
table0 | 16958 | 41166 | 4.3271e+06 ←table0のOID番号は16958
(1 row)
testdb=# select relname,relfilenode,relpages,reltuples from pg_class where relname='table0i';
relname | relfilenode | relpages | reltuples
-------+---------+--------+------------
table0i | 16961 | 95348 | 1.0001e+07 ←table0iのOID番号は16961
(1 row)
testdb=# select relname,relfilenode,relpages,reltuples from pg_class where relname='table0i_uriage_no_idx';
relname | relfilenode | relpages | reltuples
----------+---------+--------+-------------
table0i_uriage_no_idx | 16963 | 27424 | 7.74362e+06 ←table0i_uriage_no_idxのOID番号は16963
(1 row)
testdb=# select relname,relfilenode,relpages,reltuples from pg_class where relname='table0i_comment_idx';
relname | relfilenode | relpages | reltuples
----------+---------+--------+-------------
table0i_comment_idx | 16966 | 27425 | 1.0001e+07 ←table0i_comment_idxのOID番号は16966
(1 row)
せ70> ls -lh /var/lib/pgdata/base/16393/*16958*
-rw-------. 1 postgres postgres 745M 6月 17 04:19 /var/lib/pgdata/base/16393/16958
-rw-------. 1 postgres postgres 208K 6月 10 14:10 /var/lib/pgdata/base/16393/16958_fsm
せ70> ls -lh /var/lib/pgdata/base/16393/*16961*
-rw-------. 1 postgres postgres 745M 6月 17 04:19 /var/lib/pgdata/base/16393/16961
-rw-------. 1 postgres postgres 208K 6月 10 14:27 /var/lib/pgdata/base/16393/16961_fsm
せ70> ls -lh /var/lib/pgdata/base/16393/*16963*
-rw-------. 1 postgres postgres 215M 6月 17 04:19 /var/lib/pgdata/base/16393/16963
せ70> ls -lh /var/lib/pgdata/base/16393/*16966*
-rw-------. 1 postgres postgres 215M 6月 17 04:19 /var/lib/pgdata/base/16393/16966
④testdbデータベースのpg_dumpする
せ70> time su postgres -c "/opt/PostgreSQL/10/bin/pg_dump testdb -f /tmp/testdb.dump"
Password:
real 1m23.573s ←dumpに要した時間は約1分24秒
user 0m1.037s
sys 0m5.601s
せ70> ls -l /tmp/testdb.dump
-rw-r--r--. 1 postgres postgres 1175146397 6月 17 16:38 /tmp/testdb.dump ←dumpデータのサイズは約1.12GB
[大量update実施]
table0iのcomment列の約4割（400万行分）の値を'1000a1'にupdateする。
[実施前]
・table0iのレコード総数
testdb=# select count(*) from table0i;
-[ RECORD 1 ]---
count | 10001002
・table0iのcomment列の値が'1000a1'の個数
せ70> time PGPASSWORD=punisuke psql -h 127.0.0.1 -U punisuke testdb -c "select count(*) from table0i where uriage_no>=1 and uriage_no<4000001 and comment='1000a1';"
count
-------
21 ←大量更新実施前は21個
(1 行)
real 0m0.072s ←インデックスアクセスで検索所要時間は0.07秒
user 0m0.004s
sys 0m0.001s
[update実施]
せ70> time PGPASSWORD=punisuke psql -h 127.0.0.1 -U punisuke testdb -c "update table0i set comment='1000a1' where uriage_no>=1 and uriage_no<4000001;"
・・・しばらく時間がかかるので、1分後くらいに別トランザクションで下記を実行
testdb=# select * from table0i where uriage_no=1;
uriage_no | uriage_date | kokyaku_name | shohin_name | price | number | comment
-----------+---------------------+--------------+-------------+-------+--------+---------
1 | 1991-01-23 00:00:00 | 顧客10 | 商品9 | 900 | 41 | 28929a0 ←まだ更新前の値
(1 row)
→400万箇所updateのトランザクションがまだコミットされてないので別トランザクションでselectしたら、まだ更新される前の値が読まれたのであらためてダーティーリードが起こらないことをが再確認できる。
・update終了
せ70> time PGPASSWORD=punisuke psql -h 127.0.0.1 -U punisuke testdb -c "update table0i set comment='1000a1' where uriage_no>=1 and uriage_no<4000001;" ←発行済み
UPDATE 4000000
real 3m9.813s ←400万行のupdateの所要時間は約3分9秒
user 0m0.006s
sys 0m0.025s
・別トランザクションからupdateがコミット済みかどうか確認
testdb=# select * from table0i where uriage_no=1;
uriage_no | uriage_date | kokyaku_name | shohin_name | price | number | comment
-----------+---------------------+--------------+-------------+-------+--------+---------
1 | 1991-01-23 00:00:00 | 顧客10 | 商品9 | 900 | 41 | 1000a1
(1 row)
testdb=# select * from table0i where uriage_no=1000;
uriage_no | uriage_date | kokyaku_name | shohin_name | price | number | comment
-----------+---------------------+--------------+-------------+-------+--------+---------
1000 | 1991-01-23 00:00:00 | 顧客8 | 商品4 | 400 | 30 | 1000a1
(1 row)
→ちゃんとコミットされてますね
・大量アップデートされたことの確認
せ70> time PGPASSWORD=punisuke psql -h 127.0.0.1 -U punisuke testdb -c "select count(*) from table0i where uriage_no>=1 and uriage_no<4000001 and comment='1000a1';"
count
---------
4000000 ←400万行全部commit列の値が'1000a1'に更新された
(1 行)
real 0m25.208s ←400万行更新前は21個しかヒットしなくて所要時間はたった0.072秒だったのに約25秒（2回目の実行）になった
user 0m0.001s
sys 0m0.005s
→検索される結果が21個の場合と400万個の場合で実行計画（access plan）がindex accessからsequential accessに変わったからだと思われる
・table0iのレコード総数
testdb=# select count(*) from table0i;
-[ RECORD 1 ]---
count | 10001002
→当然レコード総数は変わってませんね
[大量update後]
①testdbデータベースのサイズ確認
testdb=# select * from pg_database_size('testdb');
-[ RECORD 1 ]----+-----------
pg_database_size | 2627802599 ←約2.51GB →大量updateで約500MB増えた！
②テーブルとインデックスのサイズ確認
testdb=# select * from pg_relation_size('punisuke.table0i');
-[ RECORD 1 ]----+-----------
pg_relation_size | 1093435392 ←テーブルサイズは約1.02GB →大量updateで約250MB増えた！
testdb=# select * from pg_indexes_size('punisuke.table0i');
-[ RECORD 1 ]---+----------
pg_indexes_size | 743383040 ←インデックスサイズは約709MB →大量updateで約280MB増えた！
testdb=# select objectname,to_char(pg_relation_size(objectname::regclass),'999,999,999,999') as bytes from(select tablename as objectname from pg_tables where schemaname='punisuke' union select indexname as objectname from pg_indexes where schemaname='punisuke') as objectlist order by bytes desc;
objectname | bytes
-----------------+------------------
table0i | 1,093,435,392 ←table0iテーブルは約1.02GB
table0 | 781,090,816 ←table0テーブルは約781MB
table0i_uriage_no_idx | 404,660,224 ←table0i_comment_idxインデックスは約386MB
table0i_comment_idx | 338,722,816 ←able0i_uriage_no_idxインデックスは約323MB
kokyaku1_pkey | 16,384
uriage2_pkey | 16,384
uriage1_pkey | 16,384
tb3_id_idx | 16,384
・・・後略・・・
③ファイルシステム上のファイルサイズ（テーブル、インデックス）
せ70> du /var/lib/pgdata/base
7756 /var/lib/pgdata/base/1
7756 /var/lib/pgdata/base/13857
7988 /var/lib/pgdata/base/13858
2566240 /var/lib/pgdata/base/16393 ←2566240KB (約2.45GB)
0 /var/lib/pgdata/base/pgsql_tmp
2589740 /var/lib/pgdata/base
testdb=# select relname,relfilenode,relpages,reltuples from pg_class where relname='table0i';
relname | relfilenode | relpages | reltuples
---------+--------+----------+-------------
table0i | 16961 | 133476 | 1.00105e+07 ←table0iのOID番号は当然変わらず
(1 row)
testdb=# select relname,relfilenode,relpages,reltuples from pg_class where relname='table0i_uriage_no_idx';
relname | relfilenode | relpages | reltuples
-----------------------+-------------+----------+------------
table0i_uriage_no_idx | 16963 | 49397 | 1.0001e+07 ←table0i_uriage_no_idxのOID番号は当然変わらず
(1 row)
testdb=# select relname,relfilenode,relpages,reltuples from pg_class where relname='table0i_comment_idx';
relname | relfilenode | relpages | reltuples
---------------------+-------------+----------+------------
table0i_comment_idx | 16966 | 41348 | 1.0001e+07 ←table0i_comment_idxのOID番号は当然変わらず
(1 row)
せ70> ls -lh /var/lib/pgdata/base/16393/*16961*
-rw-------. 1 postgres postgres 1.0G 6月 17 17:30 /var/lib/pgdata/base/16393/16961 ←約250MB増えた
-rw-------. 1 postgres postgres 19M 6月 17 17:24 /var/lib/pgdata/base/16393/16961.1 ←新しく表れた
-rw-------. 1 postgres postgres 280K 6月 17 17:32 /var/lib/pgdata/base/16393/16961_fsm ←約72KB増えた
-rw-------. 1 postgres postgres 40K 6月 17 17:32 /var/lib/pgdata/base/16393/16961_vm ←新しく表れた
せ70> ls -lh /var/lib/pgdata/base/16393/*16963*
-rw-------. 1 postgres postgres 386M 6月 17 17:26 /var/lib/pgdata/base/16393/16963 ←約170MB増えた
せ70> ls -lh /var/lib/pgdata/base/16393/*16966*
-rw-------. 1 postgres postgres 324M 6月 17 17:28 /var/lib/pgdata/base/16393/16966 ←約110MB増えた
④testdbデータベースのpg_dumpする
せ70> time su postgres -c "/opt/PostgreSQL/10/bin/pg_dump testdb -f /tmp/testdb.dump2"
Password:
real 1m27.193s ←行全体の数が増えてないので大量更新前の1m23.573sとほとんど変わらず
user 0m2.373s
sys 0m2.919s
せ70> ls -l /tmp/testdb.dump2
-rw-r--r--. 1 postgres postgres 1172503997 6月 17 18:26 /tmp/testdb.dump2 ←dumpデータのサイズは約1.1GBのまま
→大量更新前は1175146397バイトだったので約2.5MB減少したが、comment列の値を'1000a1'に更新することにより10000a1とかは1文字分（1バイト分）減ってるのでおそらくその差の分だけサイズが減少したと思われる
・auto vacuumは行われたのか確認 （400万行update前の結果はPostgreSQL その48 監視項目 その1参照）
testdb=# select * from pg_stat_user_tables where relname = 'table0i';
-[ RECORD 1 ]-------+------------------------------
relid | 16935
schemaname | punisuke
relname | table0i
seq_scan | 14
seq_tup_read | 60006008
idx_scan | 30
idx_tup_fetch | 12000278
n_tup_ins | 3
n_tup_upd | 4000001 ←有効行の推定値 1回から400万1回に増えた
n_tup_del | 1 ←不要行の推定値 auto vacuumが実施された後不要行は0になる。この不要行というのは「削除フラグが立ってる行」の意味
n_tup_hot_upd | 0
n_live_tup | 10010468
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum |
last_autovacuum | 2018-06-17 17:30:33.068482+09 ←400万行updateの際、auto vacuumも行われた
last_analyze |
last_autoanalyze | 2018-06-17 17:31:12.035565+09 ←400万行updateの際、auto analizeも行われた
vacuum_count | 0
autovacuum_count | 1 ←auto vacuumは1回だけ行われた
analyze_count | 0
autoanalyze_count | 1 ←auto analizeは1回だけ行われた
・念のためもう一回テーブルとインデックスサイズを確認。
testdb=# select objectname,to_char(pg_relation_size(objectname::regclass),'999,999,999,999') as bytes from(select tablename as objectname from pg_tables where schemaname='punisuke' union select indexname as objectname from pg_indexes where schemaname='punisuke') as objectlist order by bytes desc;
objectname | bytes
-----------------------+------------------
table0i | 1,093,435,392
table0 | 781,090,816
table0i_uriage_no_idx | 404,660,224
table0i_comment_idx | 338,722,816
kokyaku1_pkey | 16,384
uriage2_pkey | 16,384
uriage1_pkey | 16,384
・・・後略・・・
→大量update後にauto vacuumは一回行われたけどテーブルとインデックスサイズの肥大は解消されていない
更新されたレコードが参照されなくなった時点でauto_vacuumが行われるとのことだが、auto_vacuumでは不要行（削除フラグが立ってる行）の削除フラグを消して再利用行に組み替える処理。手動vacuumも同じ。削除フラグは消されて再利用可能となったけど、「依然ディスク上を占有している領域」（オイラの勝手なことばでこの領域を「ディスク残留領域」と呼ぶことにするんご）の削除までは行わないためディスク残留領域を削除して、ディスク空き容量を回復するためにはFull VACUUMが必要（PostgreSQL その46 構成）
念のため、Postgresqlサービスをリスタートしてpg_stat_user_tablesを実施したが、大量更新以後auto_vacuumは実施されていなかった。
Postgresqlサービスをリスタート後、テーブルとインデックスサイズを確認したところ、サイズの増減はなかった。
→auto_vacuumと手動vacuumではディスク残留領域の削除はされない。不要領域を示す削除フラグを消す処理（不要領域削除）のみ行われる
→auto_vacuumはシステムリスタートとは関係ない （当然だよねwww）
