Help us understand the problem. What is going on with this article?

【PostgreSQL】テーブルのインデックスが壊れた話

はじめに

サーバーリプレース作業(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側では一意制約違反エラーが続くことになる。

原因

レプリケーション用テーブルのインデックスの一部が壊れた。

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モジュールが必要です。

2018年度WG3活動報告書 性能トラブル調査編

yaju
静岡県島田市在住のシニアSEがコンピューター、機械学習、Unity、数学について考える
http://yaju3d.hatenablog.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした