挨拶。
私の名前は Vanya です。Java 開発者です。 たまたま、私はデータベースのセットアップ、構造、パフォーマンスの最適化、週末にはちょっとした DBA の仕事など、PostgreSQL を使用することが多くあります。
最近、マイクロサービス内のいくつかのデータベースを整理し、Java ライブラリを作成しました 、これにより、この作業が簡単になり、時間を節約し、開発者が犯すよくある間違いを回避するのに役立ちます。 今日お話しするのはこの図書館です。
免責事項
私が使用している PostgreSQL のメイン バージョンは 10 です。 私が使用するすべての SQL クエリもバージョン 11 でテストされています。 サポートされる最小バージョンは 9.6 です。
背景
すべてはほぼ XNUMX 年前、私にとって奇妙な状況から始まりました。競争力のあるインデックスの突然の作成がエラーで終了したのです。 いつものように、インデックス自体は無効な状態でデータベースに残りました。 ログ分析で不足が判明 。 そして出発します...さらに深く掘り下げていくと、データベース構成に大量の問題を発見し、腕まくりをして目を輝かせながらそれらを修正し始めました。
問題 XNUMX - デフォルト構成
おそらく、コーヒーメーカーで実行できる Postgres についての比喩には、すでに誰もがかなりうんざりしていると思いますが、デフォルトの構成には実際に多くの疑問が生じます。 最低限、注意すべきことは、 Maintenance_work_mem, temp_file_limit, ステートメントタイムアウト и ロックタイムアウト.
この場合は、 Maintenance_work_mem デフォルトは 64 MB でしたが、 temp_file_limit およそ 2 GB です。大きなテーブルにインデックスを作成するには十分なメモリがありませんでした。
したがって、 pg-index-health シリーズを集めてみました 、私の意見では、データベースごとに設定する必要があるパラメーターです。
問題 XNUMX - インデックスの重複
私たちのデータベースは SSD ドライブ上に存在しており、 HA-複数のデータセンター、マスターホスト、および n-レプリカの数。 ディスクスペースは私たちにとって非常に貴重なリソースです。 パフォーマンスや CPU 消費量と同じくらい重要です。 したがって、一方では高速読み取りのためにインデックスが必要ですが、他方では、データベース内に不必要なインデックスがあることは望ましくありません。スペースを消費し、データの更新が遅くなるからです。
そして今、すべてを元に戻しました そして十分に見た上で 、私は「大規模な」粛清を組織することにしました。 開発者はデータベースのドキュメントを読むのが好きではないことが判明しました。 彼らはそれがあまり好きではありません。 このため、主キーに手動で作成されたインデックスと、一意の列に同様の「手動」インデックスが作成されるという XNUMX つの典型的なエラーが発生します。 実際のところ、それらは必要ありません。Postgres がすべてを自動的に実行します。 このようなインデックスは安全に削除でき、この目的のために診断が表示されます。 .
問題 XNUMX - インデックスの交差
ほとんどの初心者開発者は、単一の列にインデックスを作成します。 このビジネスを十分に経験した人々は、徐々にクエリを最適化し、複数の列を含むより複雑なインデックスを追加し始めます。 列のインデックスは次のように表示されます A, A + B, A + B + C 等々。 これらのインデックスの最初の XNUMX つは、XNUMX 番目のインデックスの接頭辞であるため、安全に破棄できます。 これにより、ディスク容量も大幅に節約され、これに関する診断機能も提供されます。 .
問題 XNUMX - インデックスのない外部キー
Postgres では、バッキング インデックスを指定せずに外部キー制約を作成できます。 多くの状況では、これは問題ではなく、それ自体が現れない可能性もあります...当面は...
それは私たちにとっても同じでした。ある時点で、スケジュールに従って実行され、テスト注文のデータベースをクリアするジョブが、マスター ホストによって私たちに「追加」され始めただけです。 CPU と IO が無駄になり、リクエストの速度が低下してタイムアウトになり、サービスは XNUMX になりました。 クイック分析 次のようなクエリがあることがわかりました。
delete from <table> where id in (…)この場合、当然のことながら、対象テーブルには ID によるインデックスがあり、条件に従って削除されたレコードはほとんどありませんでした。 すべてがうまくいくように思えましたが、残念ながらうまくいきませんでした。
素晴らしい人が助けに来てくれました 説明する 分析する そして、ターゲットテーブル内のレコードの削除に加えて、参照整合性チェックも行われ、関連テーブルのXNUMXつでこのチェックが失敗すると述べました。 シーケンシャルスキャン 適切なインデックスがないため。 こうして診断が誕生しました .
問題 XNUMX – インデックス内の null 値
デフォルトでは、Postgres には btree インデックスに null 値が含まれていますが、通常はそこに null 値は必要ありません。 したがって、私はこれらの null を熱心に捨てようとします (診断 )、タイプごとに NULL 許容列に部分インデックスを作成します。 where <A> is not null。 この方法で、インデックスの 1877 つのサイズを 16 MB から 16 KB に減らすことができました。 また、サービスの 4.3 つでは、インデックスから null 値が除外されたことにより、データベース サイズが合計 XNUMX% (絶対数で XNUMX GB) 減少しました。 非常に簡単な変更でディスク容量を大幅に節約できます。 🙂
問題 XNUMX – 主キーの不足
仕組みの性質上、 このような状況は可能です 大量の無効レコードが原因でテーブルのサイズが急速に増大している場合。 私はこれが私たちを脅かすことはなく、私たちの基地ではこのようなことは起こらないと素朴に信じていました。なぜなら私たちは、すごい!!!、普通の開発者だからです...私はなんて愚かで世間知らずだったのでしょうか...
ある日、100 つの素晴らしい移行により、アクティブに使用されている大規模なテーブル内のすべてのレコードが取得および更新されました。 テーブル サイズが突然 +15 GB になりました。 とても残念だったが、私たちの不幸はこれで終わりではなかった。 このテーブルの自動バキュームが XNUMX 時間後に終了した後、物理的な場所が戻らないことが明らかになりました。 サービスを停止してVACUUM FULLにすることはできないので、使用することにしました。 。 そして判明したのは、 pg_repack は主キーやその他の一意性制約なしでテーブルを処理する方法を知りません。また、私たちのテーブルには主キーがありませんでした。 こうして診断が誕生しました .
文庫版では 0.1.5 テーブルとインデックスの肥大化からデータを収集し、タイムリーに対応する機能が追加されました。
問題 XNUMX と XNUMX - 不十分なインデックスと未使用のインデックス
次の XNUMX つの診断は次のとおりです。 и – 最終形態は比較的最近になって登場しました。 重要なのは、単に取得して追加することはできないということです。
すでに書いたように、私たちは複数のレプリカを備えた構成を使用しており、ホストごとに読み取り負荷が根本的に異なります。 その結果、一部のホスト上の一部のテーブルとインデックスが実際には使用されていないことが判明し、分析するにはクラスター内のすべてのホストから統計を収集する必要があります。 これはクラスター内のすべてのホストでも必要ですが、マスター上でのみ実行することはできません。
このアプローチにより、使用されなかったインデックスを削除したり、めったに使用されないテーブルに欠落したインデックスを追加したりすることで、数十 GB を節約できました。
結論として
もちろん、ほぼすべての診断について構成できます。 。 このようにして、アプリケーションにチェックをすばやく実装して、新しいエラーの発生を防ぎ、古いエラーを徐々に修正できます。
一部の診断は、データベース移行のロールアウト直後の機能テストで実行できます。 そしてこれはおそらく、私のライブラリの最も強力な機能の XNUMX つです。 使用例は次のとおりです。 .
未使用または欠落しているインデックスや肥大化のチェックは、実際のデータベースでのみ実行するのが合理的です。 収集した値は次のように記録できます。 または監視システムに送信されます。
本当にそう願っています pg-index-health 便利で需要があるでしょう。 見つけた問題を報告したり、新しい診断を提案したりすることで、ライブラリの開発に貢献することもできます。
出所： habr.com