内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch13 テーブルメンテナンス
テーブルメンテナンスが必要な状況
- PostgreSQLは追記型アーキテクチャでMVCCを実現している
- 運用を続けているうちに不要領域が蓄積していく
- VACUUMで掃除する
バキュームの内部処理
- VACUUMの役割
- 不要領域の再利用
- トランザクションID(XID)周回問題の回避
不要領域の再利用
VMとFSM
- VM: Visibility Map
- 公式
- 「可視性」
- テーブルの1ページの状態を2ビットで管理
- 下位ビット
- データが完全に可視ならば1
- そのページにはVACUUMする必要のある行なし
- 一部でも不可視なものがあれば0
- VACUUM処理の負荷軽減に用いられる
- index-onlyスキャンにも用いられる
- 14章にて
- データが完全に可視ならば1
- 上位ビット
- 完全にFREEZE処理済ならば1
- 一部でもFREESE処理されていないものがあれば0
- FREEZE処理の負荷軽減に用いられる
- VACUUM操作でビットが立つ
- 他のあらゆるデータ操作でビットが下りる
- 下位ビット
- FSM: Free Space Map
- 公式
- テーブルの空き領域の大きさを管理する補助データ
- テーブルの1ページ(8192B)の空き領域量を1バイト(0~255)で管理
トランザクションID(XID)周回問題の回避
- 公式
- PostgreSQLのMVCCにおいて、行の可視性はXIDの大小比較で行われる
- 現在実行中のトランザクションのXID
- 行ヘッダのXID(xmin)
- XIDは32ビット整数
- freezing
- 過去の行挿入に「現在および未来全てのトランザクションから可視である」という印をつける
- VACUUMで実施
- 実現方法はバージョンにより異なる
- PostgreSQL 9.4以前では、どのXIDよりも過去であることを表す特別なXID
FrozenTransactionId(2) でxminを置換していた - 9.5以降は、単にfrozenであるというフラグを立てる
- 行のxminは調査用に保持される
- PostgreSQL 9.4以前では、どのXIDよりも過去であることを表す特別なXID
- 少なくとも20億トランザクションに一度はVACUUMが必要
XIDの差分を確認する方法
- テーブルの最も古いXIDは
pg_classビューで確認できる
postgres=# SELECT * FROM pg_class WHERE relkind='r' AND relname='tbl'; -[ RECORD 1 ]-------+------ oid | 16385 relname | tbl relnamespace | 2200 reltype | 16387 reloftype | 0 relowner | 10 relam | 2 relfilenode | 16385 reltablespace | 0 relpages | 1 reltuples | 3 relallvisible | 1 reltoastrelid | 0 relhasindex | f relisshared | f relpersistence | p relkind | r relnatts | 1 relchecks | 0 relhasrules | f relhastriggers | f relhassubclass | f relrowsecurity | f relforcerowsecurity | f relispopulated | t relreplident | d relispartition | f relrewrite | 0 relfrozenxid | 488 relminmxid | 1 relacl | reloptions | relpartbound |
- これ (xid型)
relfrozenxid | 488
- これをage関数に渡すことでXIDの差分を確認できる
postgres=# SELECT relname,age(relfrozenxid) FROM pg_class WHERE relkind='r' AND relname='tbl'; relname | age ---------+----- tbl | 9 (1 row)
txid_current()関数: 現在のXIDを取得・インクリメント- シーケンスの
nextvalみたいな感じ
- シーケンスの
postgres=# SELECT txid_current();
txid_current
--------------
497
(1 row)
- xminは変わらず、現在のXIDが増加したので、ageは1増加する
postgres=# SELECT relname,age(relfrozenxid) FROM pg_class WHERE relkind='r' AND relname='tbl'; relname | age ---------+----- tbl | 10 (1 row)
自動バキュームによるメンテナンス
- PostgreSQL 8.3以降はautovacuumがある
- 公式
autovacuum_vacuum_threshold (integer) Specifies the minimum number of updated or deleted tuples needed to trigger a VACUUM in any one table. The default is 50 tuples. This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters. autovacuum_vacuum_scale_factor (floating point) Specifies a fraction of the table size to add to autovacuum_vacuum_threshold when deciding whether to trigger a VACUUM. The default is 0.2 (20% of table size). This parameter can only be set in the postgresql.conf file or on the server command line; but the setting can be overridden for individual tables by changing table storage parameters.
自動バキュームワーカが処理を始める更新・削除行数
autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * テーブルの行数)
- 設定値
postgres=# SELECT name,setting,context FROM pg_settings WHERE name LIKE 'autovacuum%';
name | setting | context
-------------------------------------+-----------+------------
autovacuum | on | sighup
autovacuum_analyze_scale_factor | 0.1 | sighup
autovacuum_analyze_threshold | 50 | sighup
autovacuum_freeze_max_age | 200000000 | postmaster
autovacuum_max_workers | 3 | postmaster
autovacuum_multixact_freeze_max_age | 400000000 | postmaster
autovacuum_naptime | 60 | sighup
autovacuum_vacuum_cost_delay | 2 | sighup
autovacuum_vacuum_cost_limit | -1 | sighup
autovacuum_vacuum_scale_factor | 0.2 | sighup
autovacuum_vacuum_threshold | 50 | sighup
autovacuum_work_mem | -1 | sighup
(12 rows)
- デフォルトで50行 + テーブルの20%
autovacuum_enabledオプションを変更することで自動バキューム対象から外すことができる
postgres=# ALTER TABLE tbl SET (autovacuum_enabled = 0);
ALTER TABLE
postgres=# \d+ tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
col | integer | | | | plain | |
Access method: heap
Options: autovacuum_enabled=0
VACUUM FULLによるメンテナンス
VACUUMによるメンテナンスが想定通りに昨日しなかった場合の対処策
VACUUMが機能しないケース
- LongTransactionが開いている間は、以降の行挿入・削除はFREEZE対象とならない
- FREEZE対象は、現在流れている最も若いXIDより前のXIDを持つ行だから
- 「現在・未来すべてのトランザクションから可視」
- FREEZE対象は、現在流れている最も若いXIDより前のXIDを持つ行だから
LongTransactionの確認方法/終了方法
SELECT pid, query, xact_start, now() - xact_start as duration, state FROM pg_stat_activity;
pid | query | xact_start | duration | state -----+-----------------------------------------------------------------------------------------+-------------------------------+-----------------+--------------------- 60 | | | | 62 | | | | 77 | SELECT pid,query,xact_start,now() - xact_start as duration,state FROM pg_stat_activity; | 2020-02-05 07:31:46.947481+00 | 00:00:00 | active 91 | begin; | 2020-02-05 07:28:02.552654+00 | 00:03:44.394827 | idle in transaction 58 | | | | 57 | | | | 59 | | | | (7 rows)
pg_stat_activityビューを用いて特定するstateがidle in transactionで、xact_startが現時点よりも大幅に古いプロセスはLog Transactionになっている可能性がある
- 当該トランザクションの
COMMIT/ABORTが困難ならば、pg_terminate_backend(<pid>)関数でプロセスを終了させる
サーバー側
postgres=# SELECT pg_terminate_backend(91); pg_terminate_backend ---------------------- t (1 row)
クライアント側
postgres=# SELECT 1;
FATAL: terminating connection due to administrator command
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
