はじめに
サーバーリプレース作業(Windows Server 2008R2 + Oracle 11g → Windows Server 2016 + PostgreSQL 9.6) を一昨年から準備してきまして、全国25箇所を昨年の9月から順次展開中です。
展開して稼働しているところで、インデックスが壊れて更新が出来なくなる現象がありました。
幸い重要度が高くないテーブルだったためユーザー被害は少なかったのですが、重要度が高いテーブルだったら大問題になっていたかと思うとゾッとしました。
データベースを活用したアプリケーションの開発を15年近くしていますが、インデックスが壊れるというのは初めての体験でしたね。
仕様
SQLServer側でレプリケーションツールを一定間隔(10分など)で起動し、PostgreSQLのレプリケーション用テーブルを読み込みレプリケーションをしています。
PostgreSQLのレプリケーションテーブルには自動採番のID列があり、インデックスもID列のみになっています。
SQLServerでレプリケーションしたら、PostgreSQLのレプリケーション用テーブルの該当するID列のデータを削除します。
障害
レプリケーションがされなくなり、レプリケーション用テーブルの件数が溜まる一方になりました。
内容
レプリケーション用テーブルの先頭10件分がSQLServerにレプリケーションされているか確認したところ、正常にレプリケーションが出来ていました。レプリケーションツールの不具合か何かでPostgreSQL側の該当するID列のデータ削除が行われなくなったと推測しました。
データ削除が行われないと同じレプリケーションデータを繰り返す(100件単位)ことになり、SQLServer側では一意制約違反エラーが続くことになる。
原因
レプリケーション用テーブルのインデックスの一部が壊れた。
PostgreSQLログには下記が出力されていた。
[2020-08-04 15:06:10 JST] 9248[1] ERROR: could not truncate file "base/16384/114413907" to 59 blocks: Permission denied
[2020-08-04 15:06:10 JST] 9248[2] CONTEXT: automatic vacuum of table "replicate_que"
DB接続ツールにて該当するID列のデータを削除しようとDELETE句を実行したのですが削除されません。そこでSELECT句に変更してみると抽出されてきません。しかし、抽出条件を除いて全件SELECTすると該当するID列が抽出されてきます。
ID列以外の更新日時列を抽出条件に指定してSELECTしてみると抽出が出来ます。また、更新日時の1時間分くらいのID列で抽出が出来ず、それ以外の更新日時ならID列で抽出が出来ます。
対応
SQLServerにレプリケーションされているデータを確認し、レプリケーション用テーブルを更新日時を抽出条件にしてDELETE句を実行して、正常にレプリケーションがされる状態まで行います。
REINDEXコマンドを使用して、インデックスを再構築する。
REINDEXによるテーブルロック
上長からREINDEXはテーブルをロックするということで、テーブルをロックしないでインデックスの再構築を行う方法を調査しました。
※PostgreSQL 12では、REINDEXにCONCURRENTLYオプションが追加されたため、下記SQLのようにする必要はありません。
create unique index concurrently pk_replicate_que_new on replicate_que(que_id);
alter table replicate_que drop constraint pk_replicate_que;
alter index pk_replicate_que_new rename to pk_replicate_que;
alter table replicate_que add primary key using index pk_replicate_que;
ただ今回のレプリケーションテーブル(replicate_que)を100万件登録してもREINDEXによるインデックスの再構築は2秒以内でした。通常のレプリケーションテーブルの滞在するデータは多くても1万件程度なので、REINDEXによるインデックスの再構築によるテーブルロックはほとんど無視してもいい処理時間 数ミリ以下となります。
結局、テーブルロックしないインデックスの再構築の方法はやめて、REINDEXによるインデックスの再構築のままで対応することになりました。
最後に
頻繁に登録・削除が行われるため、インデックスの断片化は高くなるようなので随時監視はしている。しかし高いからといって必ずしも壊れる訳ではない。AUTOVACUUMが稼動すると断片化も下がったりもします。
※インデックスの断片化によりパフォーマンスが悪くなることはあってもインデックスが破損するという記事は見当たりませんでした。
再度発生した時にはバックアップ後にREINDEXコマンドを仕掛けるように検討しようと思います。
SELECT objectname,ROUND(CAST(app.leaf_fragmentation AS NUMERIC),2)
FROM (
SELECT *, quote_ident(schemaname) || '.' || quote_ident(indexrelname) AS objectname
FROM pg_stat_user_indexes WHERE idx_scan > 100000
) AS t, pgstatindex(objectname::regclass) AS app
WHERE app.leaf_fragmentation <> 'NaN'
ORDER BY round DESC, objectname
※pgstatindexを使用するためには、pgstattupleモジュールが必要です。
【2020/08/14追記】
1日1回の夜中2:00のバックアップ後にREINDEXコマンドを仕掛けるようにして、3ヶ月経って再度発生しました。それまで数週間〜最長2ヶ月内に1回は発生していたので、抑止効果はありました。
PostgreSQLログに壊れた場合に痕跡が残るので、これを監視してREINDEXコマンド自動的にするような仕組みを作成していく予定です。