■pg_largeobjectが肥大化する
仕事で取り扱っている、とあるアクセスログ集計用WEBアプリ。
これはLinux上にインストールされており、ログを取り込んでDBで集計できるものです。(PostgreSQL 9.2.9を使用)
定期的なレコード削除やautovacuumが実行されているものの、ディスク使用率はじわじわと上がってきています。
このアプリは、WEB GUIで提供されているもので、DBを直接操作することは基本的に想定されていません。（サポート外）
しかし、ディスクがひっ迫しては困るので、素人ながら頑張って中身を暴いてみました。
いろいろと見てみた限り、どうもpg_largeobjectが大きすぎる印象があります。
アプリの自動月次削除でやや減るものの、最大２か月までしかデータを保管しない設計なので、それにしては大きすぎます。
恐らく、自動削除でも消えていないデータがありそうな感じ。
これはLinux上にインストールされており、ログを取り込んでDBで集計できるものです。(PostgreSQL 9.2.9を使用)
定期的なレコード削除やautovacuumが実行されているものの、ディスク使用率はじわじわと上がってきています。
このアプリは、WEB GUIで提供されているもので、DBを直接操作することは基本的に想定されていません。（サポート外）
しかし、ディスクがひっ迫しては困るので、素人ながら頑張って中身を暴いてみました。
いろいろと見てみた限り、どうもpg_largeobjectが大きすぎる印象があります。
アプリの自動月次削除でやや減るものの、最大２か月までしかデータを保管しない設計なので、それにしては大きすぎます。
恐らく、自動削除でも消えていないデータがありそうな感じ。
//【定期削除前】
SELECT schemaname, relname, TO_CHAR(n_live_tup, 'fm999,999,999,999') AS n_live_tup FROM pg_stat_all_tables WHERE relname='pg_largeobject';
schemaname | relname | n_live_tup
------------+----------------+-------------
pg_catalog | pg_largeobject | 847,890,960
//【定期削除後】
SELECT schemaname, relname, TO_CHAR(n_live_tup, 'fm999,999,999,999') AS n_live_tup FROM pg_stat_all_tables WHERE relname='pg_largeobject';
schemaname | relname | n_live_tup
------------+----------------+-------------
pg_catalog | pg_largeobject | 608,359,703
■vacuumloでクリーニング
vacuumloはPostgreSQLデータベースから"孤児になった"ラージオブジェクトをすべて削除する、単純なユーティリティです。
データベース内でoidまたはloデータ型列内にまったく現れないOIDを持つすべてのラージオブジェクト（LO）を"孤児になった"ラージオブジェクトとみなします。
データベース内でoidまたはloデータ型列内にまったく現れないOIDを持つすべてのラージオブジェクト（LO）を"孤児になった"ラージオブジェクトとみなします。
私はDBについて門外漢ですが、言っていることはなんとなくわかります。
恐らく、元となるレコードとの紐づきが失われたラージオブジェクトを一掃できる、ということだと思います。
紐づきが失われているから、元となるレコードの自動削除が実行されても、ラージオブジェクトが残存してしまっているのでしょう。
恐らく、元となるレコードとの紐づきが失われたラージオブジェクトを一掃できる、ということだと思います。
紐づきが失われているから、元となるレコードの自動削除が実行されても、ラージオブジェクトが残存してしまっているのでしょう。
このアプリのPostgreSQLにもvacuumloが同梱されていたので、早速実行してみます。
vacuumlo -U ユーザー名 DB名
ところが……。処理がちっとも完了しないのです……。
これはディスクI/O速度の問題でしょうか？
しかし、タプルの件数は減っているので、効果は出ているようです。
これはディスクI/O速度の問題でしょうか？
しかし、タプルの件数は減っているので、効果は出ているようです。
//【vacuumloを1週間ほど実行した後】
SELECT schemaname, relname, TO_CHAR(n_live_tup, 'fm999,999,999,999') AS n_live_tup FROM pg_stat_all_tables WHERE relname='pg_largeobject';
schemaname | relname | n_live_tup
------------+----------------+-------------
pg_catalog | pg_largeobject | 579,227,262
今後は、数か月程度の頻度で、バックグラウンドでvacuumloを実行してみようと思います。
追記 2024.01.09
その後、vacuumloと定期レコード削除によって、ここまで減らすことができました！
ありがとうvacuumlo！
その後、vacuumloと定期レコード削除によって、ここまで減らすことができました！
ありがとうvacuumlo！
//【vacuumlo＆定期削除後】
SELECT schemaname, relname, TO_CHAR(n_live_tup, 'fm999,999,999,999') AS n_live_tup FROM pg_stat_all_tables WHERE relname='pg_largeobject';
schemaname | relname | n_live_tup
------------+----------------+-------------
pg_catalog | pg_largeobject | 159,732,216