釣りキチプログラマー翔平の備忘録 
はじめに
この記事では
- なぜテーブルをメンテナンスしなければならないのか？？
- テーブルのメンテナンスで気をつけるべきことは何か？？
についてまとめました。
PostgreSQLを本番運用する際には役に立つ内容かと思います。
なぜテーブルのメンテナンスが必要なのか？？
PostgreSQLでは追記型アーキテクチャであるため、
レコードの更新・削除を行なっても、
更新前の情報が不要領域として残り、
更新後の情報が登録されます。
この不要領域を放置しておくと、
目的のデータにアクセスする際、無駄なディスクI/Oが発生します。
そのため
定期的に不要領域を再利用可能にする処理が必要になります。
この処理はバキューム(VACCUM)と呼ばれるます。
バキュームについてはこちらの記事の
自動バキュームランチャと自動バキュームワーカを
併せてご覧ください
内部で動くプロセスの役割を知る〜PostgreSQLのきほん〜
自動VACUUM設定の注意点とVACUUM FULLへの対策
PostgreSQLのデフォルト設定では1分毎に
各テーブルの挿入・更新・削除された行数が閾値を超えた場合、
自動バキュームが起動するため、
テーブルメンテナンスを意識せずに運用できるケースもあるかもしれません。
しかし、
更新が頻繁に行われるテーブルには自動バキュームも頻繁に行われ、
データベースに負荷がかかります。
(自動バキュームではページロックがかかり列統計情報の取得やディスクI/Oもある)
また、
日中帯にデータベースに負荷をかけられないようなユースケースの場合もあります。
その場合は以下のような対策が必要になるかもしれません。
- テーブル指定で自動バキューム対象から外して、夜間にバキュームを個別に行う
- テーブル指定で自動バキュームの実行閾値を調整する
ALTER TABLE `テーブル名` SET (autovacuum_enabled = off)また、
バッチ処理でLongTransactionにより長い時間commitやrollbackされないトランザクションが残り続けると、
自動バキュームが正常に動作せずVACUUM FULLが発生してしまいます。
VACUUM FULLが発生すると、
以下のような事態に陥り、実質システムが停止することになります。
- 実行中は該当テーブルが排他ロックされる(該当テーブルが参照・更新できなくなる)
- 大きいテーブルの場合数十分〜数時間かかる
- テーブルやインデックスが一時的にコピーされディスク使用量が倍になる
バッチ処理などで長期化してしまうトランザクションがある場合は
それを流しつつ、
裏でオンライン処理を流しつつ、
VACUUM FULLが発生しない確認をしておいた方が良いでしょう。
SELECT pid, query, xact_start, state FROM pg_stat_activitystateの値が「idle in transaction」でxact_startの値が他のものより大幅に古いプロセスは
Long Transactionの可能性あり！！
pg_terminate_backend関数でバックエンドプロセスを終了させられる。
今回の内容は以上です。
現場で役立つデータベースの基礎知識という
まとめ記事を作ったので是非ご覧ください。
初めてデータベースを触る方に向けて〜新人プログラマー時代の自分に伝えたいこと〜
参考文献
第15章 テーブルメンテナンス
［改訂3版］内部構造から学ぶPostgreSQL―設計・運用計画の鉄則
