- 1秒あたり5'000-10'000のオーダーのトランザクション数
- 約100GB（急成長しています）
- ほぼ同じ数の読み取り/書き込み操作
- 主に小さなトランザクション
歴史的な理由により、彼らが言うように、基地全体は、これまでシャーディングなしで1つの基地であり、多くの問題を解決することができました。 より深刻なのは、PostgreSQLの機能に正確に合ったソリューションを見つけることです。 わずかな血液で実装できるソリューション。
私たちが直面するいくつかの問題と適用される解決策について説明します。 誰かがそれを有用だと思うなら、神のために。 誰かがそれを修正したら、使用されているアプローチの脆弱性を発見し、データベースの効率を上げるだけです。 説明は、データベーススキーマを変更するだけで、アプリケーションロジックを妨げることなく適用できます。
PostgreSQL 9.2についてです（9.3はまだテストされていません）。 そして、はい、構成の最適な構成に関する管理上のヒントはありません-それらを正しくシャープにする方法を知ってうれしいです。
「適切なインデックスを作成する」などのキャプテンの推奨事項もありません。 これらのナノテクノロジーは表面にあると思います。
大きなテーブルとインデックスのサイズ
データベースの書き込みが集中的になればなるほど、サンプルの平均速度が低下し、データベースが「バカ」になり始めるのは周知の事実です。 これは、データベースのトランザクションの性質に直接関連しています。 文字列（タプル）はバージョン管理されているため、特定の文字列の値を変更するときに、この値を以前の値の代わりに取得して書き込むことはできません。 代わりに、PostgreSQLは行を複製し、その新しい修正バージョンを個別に書き込みます。 つまり、大まかに言うと、1GBのテーブルを使用して、1回の操作で任意のフィールドにUPDATEを実行すると、テーブルのサイズが「突然」2GBに増加します。 インデックスでもほぼ同じことが起こります。 データベースのサイズを大きくすると、クエリの実行速度にすぐに影響します。
もちろん、AUTOVACUUMは定期的に（設定されている場合）動作しますが、必要に応じて手でVACUUMを起動することもできます。
問題を軽減するために何ができますか？
- フィルファクタを使用します。 このパラメーターは、ディスク上のテーブルのページ（データブロック）の充足度と、変更されたバージョンのタプルをそこに書き込むためにブロックに必要な空きスペースの量を決定します。 私たちの経験（主に1つのレコードで更新されますが、多くの場合）は、顕著なプラスの効果を得るには、90％の値で十分であることを示しています（デフォルトでは100％です、つまり、テーブルは可能な限り密集しています）。 これは、テーブルが少なくとも約10％大きくなることがわかっていることを意味しますが、行を更新するときに、新しいバージョンを場所だけでなく、それが配置されたブロックに書き込むことができ、より高速でコンパクトになります。 ドキュメントでは、レコードが決して変更されない（または非常にまれな）テーブルの場合、フィルファクターを変更しても意味がないと述べています。 ところで、インデックスの場合、FILL FACTORはデフォルトですでに90です。
- 部分インデックスを使用します。 多くの場合、ブール値フィールドの特定の値または特定の列挙値を持つレコードにのみ関心があることがわかります。 たとえば、バックグラウンドタスクを実行するためにデータベースにタスクのストレージを編成しました（ただし、これは良い考えではありませんが、他の理由で）。ほとんどの場合、そこから新しいステータスのタスクを選択します。 では、実際に無視できる数のレコードから選択が行われている場合、テーブル全体にインデックスを付けるのはなぜですか（明らかに、すでに完了しているよりもはるかに少ない新しいタスクがあるでしょう）。 インデックスに制限を設定すると、そのサイズは数倍になりますが、桁違いではありません。
- 機能インデックスを使用します。 機能インデックスは、一般的にテーブルに列を作成することを回避できる強力なツールです。 たとえば、日付と時刻を含むタイムスタンプタイプのテーブルのフィールドがあります。 また、サンプルを作成する必要があります。たとえば、1時間ごとのデータサンプリングを使用します。 タイムスタンプ列で直接検索できます（そしてインデックスを付けます）。 トリガーまたは手動で時間値を個別に保存できる別の列を作成できます。 また、クロックの値のみを含む機能インデックスを作成し、既存のタイムスタンプ列に基づいて構築できます。 その結果、追加の列は作成されず、効果的なインデックスがあります。
遅いサンプル
- 前の2つのポイントは、部分インデックスと機能インデックスの使用という関連性を失わない。
- クラスタリング（ CLUSTER ）。 オブジェクトにアクセスするための一般的なオプションを知っている場合に役立つトピック。 指定されたインデックスでテーブルをクラスタリングするプロセスで、PostgreSQLはインデックスに従って、ハードドライブ上のテーブルデータを整然と分散します。 たとえば、家に関連付けられたアパートのリストを含むテーブルがあります。 「家ID」フィールドでインデックスを作成し、それをクラスター化できます。アパートのレコードは家ごとに物理的にグループ化されるため、「家番号777のすべてのアパートを私に返す」ように要求すると、データベースはテーブル全体でアパートを収集する必要がなくなります。 それらはコンパクトに保存されます。 確かに、クラスタリングは定期的に繰り返される必要があります。 AUTOVACUUMプロセスでは、生成されません。
トランザクションカウンターを超えました
MVCCメカニズムの操作は、トランザクションカウンターが存在しないと不可能でした。 このカウンタがまだ32ビットである理由は謎ですが、私たちが持っているのは、カウンタの末尾の20億トランザクションごとにリセットされることになっています。 取り返しのつかない事態が発生しないようにするには、この時点でデータベース内のすべての行のトランザクション番号をリセットする必要があります。 より正確には、特定の予約済みFrozenXIDがこれらの行に割り当てられます。 通常、AUTOVACUUMおよび手動VACUUMはFrozenXIDを設定せず、トランザクションカウンターが特定の構成値に達すると、通常のデータベース操作に対して、自動バキュームは「ラップアラウンドを防ぐため」という素晴らしいコメントで開始します。 大きなテーブル（数十GB）の場合、このプロセスには時間がかかり、その間、読み取りにも書き込みにもテーブルにアクセスできなくなります。
（私にとって）アプリケーションのロジックとアーキテクチャに影響を与えずにここで何もすることはできません。 トランザクションの数を減らしてトランザクションを拡大する（他の問題になる可能性があり、一般に効果がない）か、データベースを断片に分割する必要がありますが、これも問題を解決する運用方法につながりません。
長い取引
これはかなり明白ですが、念のために言及しておきます。
これは、長いトランザクション自体に関するものではなく、バグのためにアプリケーションがトランザクションを閉じずにさらに処理を続けた状況（たとえば、数日間ハングする可能性があるバックグラウンドタスクハンドラー）に関するものです。 アクティブなPostgreSQLプロセスのリストでは、このような接続は「idle in transaction」ステータスでハングします。 定期的にVACUUMを実行すると、削除または編集が実行されたトランザクションの数に応じて、古いバージョンの文字列（編集または削除後に残る）によって占有されていたスペースが解放されます。 大まかに言って、トランザクション1および2によって削除された行があり、現在開いているトランザクションの最小数が3である場合、これら2つの行は「実際に」削除できます。 現在開いているトランザクションの範囲内にはありません。 中断されたトランザクションの場合、VACUUMはこのトランザクションの開始後に編集された単一の行を削除できません。その結果、データベースは数時間で大きく成長する可能性があります。
唯一のアドバイスがあります-現在のプロセスを監視し、「トランザクションのアイドル」で悪役を捕まえることです。
長いブロッキングバックアップ
レプリケーションは、その魅力をすべて備えており、たとえば、パッチが曲がっているなど、ベースが破られた状況からは救いません。 彼らはそこにオブジェクトをリンクするためのパッチを作成し、すべての良いものが複製されました-そしてこんにちは。 したがって、何と言ってもバックアップが必要です。 大きなベースは約1時間バックアップされます。 ほぼすべての期間、プロジェクトは非アクティブです。 ここで、率直に言って、私は何もアドバイスすることはできません。 それどころか、この状況に誰がどのように対処しているかを聞いてうれしいです。 唯一の考えは、データの一貫性を維持する方法を考えて、断片化して断片化してバックアップすることです。
バージョン9.3では、プレゼンテーションで顕著な作業が行われました。これにより、データベースでの作業を最適化することもできますが、これは別のトピックです。