PostgreSQL の大容量ボリュームを XNUMX ペニー節約する

によって提起された大規模なデータ ストリームの記録に関するトピックの継続 パーティション分割に関する前の記事, ここでは、できる方法を見ていきます。 保存されているファイルの「物理的」サイズを縮小します。 PostgreSQL における影響と、サーバーのパフォーマンスへの影響について説明します。

について話しましょう TOASTの設定とデータの配置。 「平均して」、これらの方法はあまり多くのリソースを節約しませんが、アプリケーション コードをまったく変更する必要はありません。

ただし、ほとんどすべてのモニタリングのストレージはその性質上、 ほとんどは追加のみ 記録されたデータに関しては。代わりにディスクに書き込むようにデータベースにどのように教えられるか疑問に思っている場合は、 200MB /秒 半分の量 - 猫の下でお願いします。

ビッグデータの小さな秘密

職務プロフィール別 当社のサービス、彼らは定期的に隠れ家から彼のところに飛んで来ます テキストパッケージ.

それ以来 VLSI コンプレックス私たちが監視しているデータベースは、複雑なデータ構造を持つマルチコンポーネント製品であり、クエリを実行します。 最大限のパフォーマンスを実現するために かなりこのようになります 複雑なアルゴリズムロジックを備えた「マルチボリューム」。そのため、リクエストの個々のインスタンスの量、またはログに記録される実行計画の量は、「平均して」非常に大きいことがわかります。

「生」データを書き込むテーブルの 1 つの構造を見てみましょう。つまり、ログ エントリの元のテキストは次のとおりです。

CREATE TABLE rawdata_orig(
  pack -- PK
    uuid NOT NULL
, recno -- PK
    smallint NOT NULL
, dt -- ключ секции
    date
, data -- самое главное
    text
, PRIMARY KEY(pack, recno)
);

典型的な標識 (もちろん、すでにセクション化されているため、これはセクション テンプレートです)。最も重要なのはテキストです。時にはかなりボリュームのあるものもあります。

PG 内の 1 つのレコードの「物理」サイズが複数のデータ ページを占めることはできませんが、「論理」サイズはまったく別の問題であることに注意してください。体積値 (varchar/text/bytea) をフィールドに書き込むには、次を使用します。 トースト技術:

PostgreSQL は固定ページ サイズ (通常は 8 KB) を使用し、タプルが複数のページにまたがることを許可しません。したがって、非常に大きなフィールド値を直接格納することはできません。この制限を克服するために、大きなフィールド値は圧縮されたり、複数の物理行に分割されたりします。これはユーザーが気付かないうちに発生し、ほとんどのサーバー コードにはほとんど影響を与えません。この方法は TOAST として知られています...

実際、「潜在的に大きな」フィールドを持つすべてのテーブルに対して、自動的に 「スライス」を使用したペアテーブルが作成されます 各「大きな」レコードは 2KB セグメントに分割されます。

TOAST(
  chunk_id
    integer
, chunk_seq
    integer
, chunk_data
    bytea
, PRIMARY KEY(chunk_id, chunk_seq)
);

つまり、「大きな」値の文字列を書かなければならない場合、 data、その後、実際の録音が行われます メインテーブルとそのPKだけでなく、TOASTとそのPKも.

TOASTの影響を軽減する

しかし、私たちの記録のほとんどはまだそれほど大きなものではありません、 8KBに収まるはずです - どうすればお金を節約できますか?...

ここで属性が役立ちます STORAGE テーブルの列:

• 拡張済み 圧縮と個別の保存の両方が可能です。これ 標準オプション ほとんどの TOAST 準拠のデータ型に対応します。まず圧縮の実行を試み、行がまだ大きすぎる場合はテーブルの外に保存します。
• メイン 圧縮は可能ですが、個別に保存することはできません。 (実際には、そのような列に対しても個別のストレージが実行されますが、 最後の手段として、ページに収まるように文字列を縮小する他に方法がない場合。)

実際、これはまさにテキストに必要なものです - できるだけ圧縮して、入りきらない場合はTOASTに入れてください。これは、1 つのコマンドで直接実行できます。

ALTER TABLE rawdata_orig ALTER COLUMN data SET STORAGE MAIN;

効果の評価方法

データの流れは日々変化するため、絶対的な数値を比較することはできませんが、相対的な数値として比較することはできません。 シェアが小さい 私たちはそれを TOAST に書き留めました - はるかに優れています。しかし、ここには危険があります。個々のレコードの「物理的」ボリュームが大きくなるほど、より多くのデータ ページをカバーする必要があるため、インデックスの「幅が広く​​」なります。

セクション 変更前:

heap  = 37GB (39%)
TOAST = 54GB (57%)
PK    =  4GB ( 4%)

セクション 変更後:

heap  = 37GB (67%)
TOAST = 16GB (29%)
PK    =  2GB ( 4%)

実際、私たちは TOAST への書き込み頻度が 2 倍減りました、ディスクだけでなく CPU もアンロードされます。

ディスクの「書き込み」だけでなく「読み取り」の処理も小さくなっていることに注意してください。テーブルにレコードを挿入するときに、インデックスを決定するために各インデックスのツリーの一部を「読み取る」必要があるためです。彼らの中での将来の立場。

PostgreSQL 11 でうまく生きられるのは誰ですか

PG11 にアップデートした後、TOAST の「チューニング」を続けることにしました。そして、このバージョンからパラメータがチューニングに使用できるようになったことに気付きました。 toast_tuple_target:

TOAST 処理コードは、テーブルに格納される行の値が TOAST_TUPLE_THRESHOLD バイト (通常は 2 KB) より大きい場合にのみ起動します。 TOAST コードは、行の値が TOAST_TUPLE_TARGET バイト (変数値、通常は 2 KB) 未満になるか、サイズを削減できなくなるまで、フィールド値を圧縮したり、テーブルの外に移動したりします。

私たちが通常持っているデータは「非常に短い」か「非常に長い」のいずれかであると判断したため、可能な最小値に制限することにしました。

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

新しい設定が再構成後のディスクの読み込みにどのような影響を与えるかを見てみましょう。

悪くない！平均 ディスクへのキューが減少しました 約 1.5 倍、ディスクは 20 パーセント「ビジー」です。しかし、これが何らかの形で CPU に影響を与えたのではないでしょうか?

少なくともそれ以上悪化することはなかった。ただし、そのようなボリュームでも平均 CPU 負荷をさらに高めることができないかどうかを判断するのは困難です。 5%.

項の位置を変更すると、合計が... 変わります。

ご存知のとおり、1 ペニーで 1 ルーブルの節約になります。当社の保管量を使えば、約 1 ルーブルの節約になります。 10TB/月 わずかな最適化でも大きな利益が得られる可能性があります。したがって、私たちはデータの物理構造に注意を払いました。 レコード内の「積み重ねられた」フィールド それぞれのテーブル。

のせいで データアライメント これは簡単です 結果のボリュームに影響します:

多くのアーキテクチャは、マシンワード境界でのデータ配置を提供します。たとえば、32 ビット x86 システムでは、整数 (整数型、4 バイト) は 4 バイトのワード境界に配置され、倍精度浮動小数点数 (倍精度浮動小数点、8 バイト) も同様です。 64 ビット システムでは、double 値は 8 バイトのワード境界に揃えられます。これも非互換性の理由の XNUMX つです。

配置により、テーブルの行のサイズはフィールドの順序によって異なります。通常、この影響はあまり顕著ではありませんが、場合によっては、サイズの大幅な増加につながる可能性があります。たとえば、char(1) フィールドと整数フィールドを混在させる場合、通常、それらの間で 3 バイトが無駄になります。

合成モデルから始めましょう。

SELECT pg_column_size(ROW(
  '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
, '2019-01-01'::date
));
-- 48 байт

SELECT pg_column_size(ROW(
  '2019-01-01'::date
, '0000-0000-0000-0000-0000-0000-0000-0000'::uuid
, 0::smallint
));
-- 46 байт

最初のケースで、いくつかの追加バイトはどこから来たのでしょうか?それは簡単です - 2 バイト境界に配置された 4 バイトの smallint 次のフィールドの前、および最後のフィールドの場合は何もなく、整列する必要はありません。

理論上はすべて問題なく、フィールドを自由に並べ替えることができます。テーブルの 10 つ（日次セクションが 15 ～ XNUMX GB を占める）の例を使用して、実際のデータでそれを確認してみましょう。

初期構造:

CREATE TABLE public.plan_20190220
(
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  dt date,
  CONSTRAINT plan_20190220_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190220_dt_check CHECK (dt = '2019-02-20'::date)
)
INHERITS (public.plan)

列の順序を変更した後のセクション - 正確に 同じフィールド、順序が違うだけ:

CREATE TABLE public.plan_20190221
(
-- Унаследована from table plan:  dt date NOT NULL,
-- Унаследована from table plan:  ts timestamp with time zone,
-- Унаследована from table plan:  pack uuid NOT NULL,
-- Унаследована from table plan:  recno smallint NOT NULL,
-- Унаследована from table plan:  host uuid,
-- Унаследована from table plan:  apn uuid,
-- Унаследована from table plan:  ptr uuid,
-- Унаследована from table plan:  bufint bigint,
-- Унаследована from table plan:  bufmem bigint,
-- Унаследована from table plan:  bufdsk bigint,
-- Унаследована from table plan:  exectime numeric(32,3),
-- Унаследована from table plan:  duration numeric(32,3),
  CONSTRAINT plan_20190221_pkey PRIMARY KEY (pack, recno),
  CONSTRAINT chck_ptr CHECK (ptr IS NOT NULL),
  CONSTRAINT plan_20190221_dt_check CHECK (dt = '2019-02-21'::date)
)
INHERITS (public.plan)

セクションの総量は「ファクト」の数によって決まり、外部プロセスのみに依存するため、ヒープのサイズを分割しましょう(pg_relation_size) その中のレコード数によって、つまり、次の結果が得られます。 実際に保存されるレコードの平均サイズ:

マイナス 6% ボリューム、 素晴らしい！

しかし、もちろん、すべてがそれほどバラ色ではありません - 結局のところ、 インデックスではフィールドの順序を変更できませんしたがって、「一般的に」(pg_total_relation_size）...

...まだここにもいます 1.5%節約コードを 1 行も変更することなく。はいはい！

フィールドを配置するための上記のオプションが最適であるという事実ではないことに注意してください。美的理由からフィールドの一部のブロックを「切り離す」ことは望まないからです。たとえば、いくつかのフィールド (pack, recno)、これはこのテーブルの PK です。

一般に、フィールドの「最小」配置を決定することは、非常に単純な「強引な」タスクです。したがって、お客様のデータからは、私たちのデータよりもさらに優れた結果を得ることができます。試してみてください。

出所： habr.com