« pgAdminIIIでクエリ結果のデリミタを変更 | トップページ | FUNCTION(PL/pgSQL)定義にあるCOSTの意味 »

2015.02.12

pg_dataパンク時の対応

PostgreSQLは他のRDBMSと比較したときにMVCCを採用しているがために、データベースが肥大化しやすい傾向があることは否めない。
ただし適切なタイミングでAUTO VACUUMが稼動していれば、爆発的な肥大化というのは基本的には起きないんじゃないかな、とは思うがそれでも肥大化してにっちもさっちもいかなくなる、ということは発生しうる。
"No space left on device"エラーが出力されると最悪だ。

基本的にはデータはpg_dataディレクトリ配下に保存されていることが多いだろう。
まずはこのディレクトリを含むデバイスの空き領域を確認する。
Linux/UNIX系であればdfコマンドを使って確認することが多いだろう。

しかしこのエラーが出たということは、pg_dataがあるディレクトリのマウントされている領域は空きがない=パンクしている、ということなので、対策としては
 1.ディスク領域を拡張する
 2.使用しているディスク領域を解放する
のどちらかのアプローチとなる。

1.については物理サーバであれば空きパーティションがあればそこをうまく使うしかないだろう。空きパーティションがなければ物理追加やNASへのマウントを検討することになろう。
ただDBMSの保存領域としてはNASは適切ではないので、pg_dataと同じパーティションを使っている他のデータを別領域に追い出す、といったアプローチもありである。

2.については実は悩ましい。
PostgreSQLにおいてDB肥大した場合は、テーブル本体やインデックスの肥大化が要因と考えられる。どちらにしてもVACUUM FULL/REINDEXの使用を検討する必要がある。
しかしここでいきなり"VACUUM FULL dbname"とかしてはならない。
VACUUM FULLはその仕様上、VACUUM元になるデータを一度コピーしてから最適化を行なうため、VACUUM元と同じサイズの空き領域がないと動かないのである。
今回はデータ領域が不足しているわけだから、そこにVACUUM FULLをいきなりかけても状況は何も変わらない。
また、DELETE文を使ってデータ削減を行なおうとしてもこれもMVCCの特性上ムダである。DELETEしてもPostgreSQLが確保している領域は減らない。

2-1.ちょっとずつVACUUM FULLしていく
ディスクの空き領域が本当に0%であればダメだが、多少の空きがあるようであれば、その空き領域に収まる対象テーブル・インデックスを探して、それを地道にVACUUM FULLしていく方法がある。空き領域2MBを発見したら、2MB未満のテーブルに対してVACUUM FULLしていくことでちょっとずつ空き領域が回復していくはずである。
しかしこれはこれで気が遠くなる作業となりうる。

2-2.緊急性のないテーブルをtruncateする
もう一つの方法としては、truncate処理を行なうことである。
TruncateはDeleteと異なり、次の特徴がある。

TRUNCATEはテーブル群から全ての行を素早く削除します。各テーブルに対して条件指定のないDELETEコマンドの実行と同じ効果を持ちますが、実際にテーブルを走査しない分、このコマンドの方が高速です。さらに、その後にVACUUM操作を行うことなく、このコマンドはディスク領域を即座に回収します。このコマンドは、大きなテーブルを対象とする場合に最も有用です。
ref)PostgreSQL 9.3.2文書:TRUNCATE

実稼動しているDB上から、一つのテーブルデータを全削除するのは度胸がいるが、pg_dataとは別の領域にファイル保管をすることが可能であれば、
2-2-1.履歴データのようなテーブルをCOPYで保存して
2-2-2.当該履歴データをTRUNCATEして
2-2-3.影響の大きいテーブルをVACUUM FULLなどして
2-2-4.TRUNCATEしたファイルをCOPYで戻す
というような作業で復帰することもできる。

とはいえ、本番稼働しているDBであるならばキャパシティ監視を怠らず、実際にパンクする前にメンテナンスを実施することが望ましいのは言うまでもないことである。

|

« pgAdminIIIでクエリ結果のデリミタを変更 | トップページ | FUNCTION(PL/pgSQL)定義にあるCOSTの意味 »

コメント

コメントを書く



(ウェブ上には掲載しません)




トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/2022/61108759

この記事へのトラックバック一覧です: pg_dataパンク時の対応:

« pgAdminIIIでクエリ結果のデリミタを変更 | トップページ | FUNCTION(PL/pgSQL)定義にあるCOSTの意味 »