主持人 > 博客 > 管理 > 在 PostgreSQL 中為大容量資料節省一分錢

在 PostgreSQL 中為大容量資料節省一分錢

繼續記錄大數據流的話題 上一篇關於分區的文章，在此我們將探討您可以使用的方法 減少儲存的“物理”大小 PostgreSQL 中的內容及其對伺服器效能的影響。

我們將討論 TOAST 設定和數據對齊。 “平均而言”，這些方法不會節省太多資源，而且根本不需要修改應用程式程式碼。

在 PostgreSQL 中為大容量資料節省一分錢
然而，我們的經驗在這方面非常富有成效，因為幾乎所有監控的儲存本質上都是 主要是僅附加 就記錄的數據而言。如果您想知道如何教導資料庫寫入磁碟 200MB /秒 一半 - 請在貓下。

大數據的小秘密

按職位簡介 我們的服務，他們定期從巢穴飛向他 文字包.

並且因為 超大規模積體電路綜合體我們監控的資料庫是一個多組件產品，資料結構複雜，然後查詢 以獲得最佳性能 結果就像這樣 演算法邏輯複雜的“多卷”。因此，我們收到的日誌中請求的每個單獨實例或產生的執行計劃的數量「平均」相當大。

讓我們來看看我們寫入「原始」資料的表之一的結構 - 也就是說，這裡是日誌條目的原始文字：

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

一個典型的標誌（當然，已經分區，所以這是一個分區模板），其中最重要的是文字。有時相當龐大。

回想一下，PG 中一筆記錄的「實體」大小不能佔用超過一頁數據，但「邏輯」大小則完全不同。若要將體積值 (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裡。這可以透過一個命令直接即時完成：

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 中為大容量資料節省一分錢
在 PostgreSQL 中為大容量資料節省一分錢
我要指出的是，我們在“讀取”磁碟方面也變得更小，而不僅僅是“寫入” - 因為當將記錄插入到表中時，我們還必須“讀取”每個索引的樹的一部分以確定其未來在他們中的地位。

誰能在 PostgreSQL 11 上過得很好

更新到PG11後，我們決定繼續「調優」TOAST，發現從這個版本開始參數 toast_tuple_target:

只有當要儲存在表中的行值大於 TOAST_TUPLE_THRESHOLD 位元組（通常為 2 KB）時，才會觸發 TOAST 處理程式碼。 TOAST程式碼會將欄位值壓縮和/或移出表，直到行值變得小於TOAST_TUPLE_TARGET位元組（變數值，通常也是2 KB）或大小無法減少。

我們認為通常擁有的數據要么“非常短”，要么“非常長”，因此我們決定將自己限制在盡可能小的值：

ALTER TABLE rawplan_orig SET (toast_tuple_target = 128);

讓我們看看重新配置後新設定如何影響磁碟載入：

在 PostgreSQL 中為大容量資料節省一分錢
不錯！平均的 磁碟隊列已減少 約 1.5 倍，磁碟「繁忙」率為 20%！但這也許會以某種方式影響CPU？

在 PostgreSQL 中為大容量資料節省一分錢
至少情況沒有變得更糟。不過，很難判斷即使這樣的容量是否仍無法提高平均 CPU 負載 5%.

透過更改項目的位置，總和...會改變！

如您所知，一分錢可以節省一盧布，就我們的存儲量而言，大約是 10TB/月 即使是一點點優化也能帶來不錯的利潤。因此，我們關注資料的物理結構──到底如何 記錄內的「堆疊」字段 每張桌子。

因為因為 數據對齊 這很簡單 影響最終的體積:

許多架構提供機器字邊界上的資料對齊。例如，在 32 位元 x86 系統上，整數（整數類型，4 位元組）將在 4 位元組字邊界上對齊，雙精確度浮點數（雙精確度浮點數，8 位元組）也是如此。而在 64 位元系統上，雙精確度值將與 8 位元組字邊界對齊。這是不相容的另一個原因。

由於對齊，表行的大小取決於欄位的順序。通常這種影響不是很明顯，但在某些情況下可能會導致尺寸顯著增加。例如，如果混合使用 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 位元組smallint 在 4 位元組邊界上對齊 在下一個欄位之前，當它是最後一個欄位時，什麼都沒有，不需要對齊。

理論上，一切都很好，您可以根據需要重新排列字段。讓我們以其中一張表格為例來檢查實際數據，該表的每日部分佔用 10-15GB。

初始結構：

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）透過其中的記錄數 - 也就是說，我們得到 實際儲存記錄的平均大小:

在 PostgreSQL 中為大容量資料節省一分錢
體積負6%， 偉大的！

但當然，一切並不那麼美好——畢竟， 在索引中我們無法更改欄位的順序，因此「一般來說」（pg_total_relation_size）...

在 PostgreSQL 中為大容量資料節省一分錢
……也還在這裡 節省 1.5%無需更改一行程式碼。是的是的！

在 PostgreSQL 中為大容量資料節省一分錢

我注意到上述安排欄位的選項並不是最優化的。因為您不想出於美觀原因“撕裂”某些字段 - 例如，幾個字段 (pack, recno)，這是該表的 PK。

一般來說，確定欄位的「最小」排列是一項相當簡單的「蠻力」任務。因此，您可以從您的數據中獲得比我們更好的結果 - 試試吧！

來源： www.habr.com

添加評論