Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
1
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

【PgHero】Duplicate / UNUSED な INDEXを消すとDBが壊れる可能性がある

TL;DR

UNIQUE INDEXによってDBレコードの一意性が保たれている場合、
そのINDEXを削除すると一意性の制約が失われる。
= 意図しない重複が発生し、不整合を起こす危険がある。

PgHeroは書き込み速度向上のため、利用のないインデックスや
他のインデックスによってカバーされている(Duplicate)インデックスについて
削除を促すが、仮にUNIQUE INDEXを削除すると上記の理由で不整合が起き得る。

上記を踏まえ
Mastodonの運用上、これらのPgHeroからのアドバイスは安心して無視していい。

伝えたいことは以上だが、情報共有のため
・私がDuplicate / UNUSEDなINDEXを削除するに至った経緯(ポエム)
・INDEX削除手順
・INDEX再作成手順
を記載する。

環境

Mastodonサーバーをかれこれ3年ほど運用している。
ほぼおひとり様状態だが、振り返ってみると色んな事が起きた。
Docker利用時にDBを吹き飛ばしたり、サーバーの引っ越しも2回ほどやった。
たしか、さくら:CentOS -> CloudGarage:Ubuntu -> AWS Lightsail:Debian という流れだ。
その間にMastodon自体も大きく変わった。v1 -> v2のメジャーバージョンアップも経験し、
Ostatusからの脱却、ActivityPubへの遷移などなど、振り返ると感慨深い。

Mastodon : v3.2.0
ruby : 2.6.6p146

経緯

PgHeroからのメッセージ

MastodonにはPgHeroが入っており、DBの状況をWebから確認することが出来る。
これは非常に便利で、DBのサイズや増加傾向、コネクション数などを気軽に確認できる。

PgHeroのOverviewページはDB全体の概況を知らせてくれるが、
duplicate indexesとslow queryについて黄色で警告が出ている。
DB素人の私には、スロークエリの改善は見るからに荷が重いから無視するとして、
duplicate indexesについては以下のように書いてある。

These indexes exist, but aren’t needed. Remove them with a migration for faster writes.

PgHero「このIndex、必要ないから消した方が書き込みが早くなるお!」

また、Spaceページにはこう書いてある。

16 unused index. Remove them with a migration for faster writes.

PgHero「使ってないIndex、migrationで消した方が書き込みが早くなるお!」

DB素人の私からすると、インデックスというのはクエリを高速化するために付けるもので、
それを消したところでレコードに影響は与えないと思っていた。
PgHeroがわざわざ警告するのだし、消すと書き込みが早くなる?らしいので
それじゃあ消してしまおうと考えたのである。

また、これは経験則だが、長期間運用し続けて様々な環境を経験してきた
サーバーには、ジワジワと過去の【遺産】が溜まるものだ。
かつては許された設定値が、遠い未来となった現在にひょっこり顔を出してエラーを起こしたり。
私の環境もずいぶん古いから、何かの手違いでDBに要らないIndexが張りっぱなしになっていて
こういう警告が出てるんじゃないかな?という考えもあったのである。

ちなみに補足だが、PgHeroのメッセージ自体は何の嘘も誤りもない。
インデックスが貼られているところへ書き込むと、インデックスの更新が走るので、
理屈上はその分書き込みが遅くなるらしい。
むやみやたらに不要なインデックスを貼ると、かえって遅くなるのはこのためだ。
しかし、一意性制約のために作られたインデックスは、
クエリに利用されていなくても存在意味がある…というのが本件のミソである。
また、UNUSEDについては環境の大きさによっても差異があるようだ。
この辺の理解はかなり曖昧なので間違っているかもしれないが、
いずれにせよ、Mastodonの運用上、これらのアドバイスは安心して無視していい、と言えるだろう。

INDEX削除手順

psqlで直接入ってちょちょいとINDEXをDROP!
…という思いっきりの良さは持ち合わせていなかったため、
PgHeroが勧める通りdb:migrateを使ってINDEXを落とした。
※記録目的で記載してます。実行は自己責任で。

RAILS_ENV=production bundle exec rails generate migration remove_unneeded_indexes

db/migrate/20200713024830_remove_unused_indexes.rb
class RemoveUnusedIndexes2 < ActiveRecord::Migration[5.2]
  def change
    remove_index :account_conversations, name: "index_account_conversations_on_account_id", column: :account_id
    remove_index :account_identity_proofs, name: "index_account_identity_proofs_on_account_id", column: :account_id
  end
end

コマンドを実行すると、mastodonのlive/db/migrate配下に上記のようなファイルが生成される。
def change ... end の間は空白となっているため、PgHeroの表示(And Paste)内容を張り付ける。

あとは通常のMastodonアップデート時と同様
RAILS_ENV=production bundle exec rails db:migrate
でインデックスが消える。

INDEX再作成手順

正しい状態を知る

https://github.com/tootsuite/mastodon/blob/v3.2.0/db/schema.rb
v3.2.0(執筆時最新)の正しい状態のDB構造を示しているのが上記のファイルである。
インデックス再生成では、上記ファイルの状態を目指す。

インデックス情報については、各テーブルの中でt.indexから始まる行に書かれている。

例示すると

schema.rb
  create_table "account_conversations", force: :cascade do |t|
    ...
    t.index ["account_id", "conversation_id", "participant_account_ids"], name: "index_unique_conversations", unique: true
    ...

上記の場合、
テーブル:account_conversations
インデックス名:index_unique_conversations
対象カラム:account_id, conversation_id, participant_account_ids
ユニークインデックス:true
であることが分かる。

現在の状態を知る

sudo -u postgres psql
\c <MastodonDB名>
postgres=# COPY (SELECT * FROM pg_indexes) TO '/tmp/index_list.csv' With CSV DELIMITER ',';
postgres=# quit

※DB名が分からないときはpsqlに入ってから\lのDB一覧から確認可能。

上記のコマンドで、/tmp/index_list.csvに現状のインデックス一覧が出力される。
これとschema.rbのインデックスを突き合わせることで、
あるべき姿との差異を知ることが出来る。

db:rollback

PgHeroの示す手順に沿ってインデックスを落とした場合、
rakeタスクによるロールバックが可能である。

RAILS_ENV=production bundle exec rails db:migrate:status
上記コマンドで、これまでに行ってきたdb:migrateの一覧が表示される。
※具体的にはdb/migrate配下に、この一覧に対応するファイルが存在している。

私の場合、最新の2つのmigrateがindexを落とすものだったため、
RAILS_ENV=production bundle exec rails db:rollback STEP=2
を実行した。

片方は無事ロールバックしたが、もう一方は
以下のようなエラーメッセージでロールバックに失敗した。

StandardError: An error has occurred, all later migrations canceled:
 remove_index is only reversible if given a :column option.

インデックスを落とすのは名前指定だけで十分だが、
元に戻すためにはカラム指定が必要、ということだ。

ということで、他記事を参考にしつつ、
schema.rbを元に正しいカラムをmigrate時に利用したファイルに加筆した。

db/migrate/<>_remove_unused_indexes.rb
  def change
    #remove_index :accounts, name: "index_accounts_on_url"
    #schema.rbを元にcolumnを加筆
    remove_index :accounts, name: "index_accounts_on_url", column: :url
    ...
    ...
  end

なお、schema.rbに記載がなかったインデックスについては不要と判断し、
そもそもロールバックから除外している。

migrate用ファイルの編集後、改めて以下を実行。
RAILS_ENV=production bundle exec rails db:rollback

今度はエラーなく、無事にインデックスが生成された。

直接INDEX落としちゃった場合

私は経験していないので詳細は不明だが、ここで記載しているように
psqlで直接コンソールに入り、schema.rb上でuniqueとなっているものを
CREATE UNIQUE INDEXしていけばいいようだ。

不正な重複レコードが存在するとUNIQUE INDEXは貼り直せないため、
重複を解消する必要がある。(参考)

どのように重複排除するのが正しいか断言できないが、
それぞれのデータを見比べ、利用されていそうな方を残すことになるのではなかろうか。

おわりに

「mastodon pghero index unused」のような単語で検索しても
パッとした情報が見つからなかったので、
ぼんやりと「PgHeroも言ってるし、たかがINDEX、落としちゃえ」とやった結果
それなりに面倒な目に遭遇してしまった。

本記事が私のようなふわっふわした鯖缶さんにとって
転ばぬ先の杖になったなら、それ以上に幸いなことはない。

記事内に誤り等あれば、コメントで指摘ください。

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
1
Help us understand the problem. What are the problem?