Postgresql

PostgreSQL 的正確儲存大小估計技術

  • September 5, 2017

我們正在為生產使用準備一個 PostgreSQL 數據庫,我們需要估計這個數據庫的儲存大小。我們是一支在數據庫管理方面缺乏專業知識的開發人員團隊,因此我們正在研究、閱讀手冊並使用我們的一般資訊技術知識來實現這一目標。

我們有實際數據要遷移到這個數據庫,並對增長有一些粗略的估計。舉例來說,假設我們估計每年增長 50%。

關鍵是:進行良好尺寸估計的一般適當技術是什麼?

我們通過以下規則估算儲存使用量。我們需要建議的主題用粗體字標記。非常歡迎對整個過程提供回饋:

  1. 估計每個表的大小

  2. 發現每一行的實際大小。

    • 對於具有固定大小的欄位(如bigintchar等),我們使用文件中描述的大小
    • 對於具有動態大小的欄位(如text),我們估計了字元串長度並使用了函式select pg_column_size('expected text here'::text)
    • 我們為PostgreSQL 內部使用的OID添加了 4 個字節
  3. 將每行的大小乘以估計的行數

  4. 我是否需要在這裡考慮任何成本,例如行或表元數據?

  5. 估計每個表索引的大小

  • 不知道如何估計,這裡需要建議
  1. 估計事務日誌的大小
  • 不知道如何估計,這裡需要建議
  1. 估計備份的大小(完整和增量)
  • 不知道如何估計,這裡需要建議
  1. 對實際最小尺寸的所有估計值求和
  2. 將 1.5 倍的因子(50% 的增長)應用於 1 年後最小規模的估計值 1、2 和 4 的總和
  3. 對估計值 5 和 6 應用 1.2 ~ 1.4(多 20% 至 40%)的總體因子以獲得良好的安全邊際

我知道規則非常廣泛。讓我知道是否需要範例以便更好地理解。

對於第 1) 點,您需要閱讀文件的Storage Page Layout章節,並特別考慮行級別元數據的HeapTupleHeaderData Layout表。

對於使用者表,每行 4 字節的 OID 已過時。自 8.1 起,PostgreSQL 不再預設擁有它們。這現在由default_with_oidsconfig 參數WITH(OIDS)CREATE TABLE.

當數據的重要部分是活動的(經常更新)時,估計磁碟大小會更難,因為更新相當於插入新版本,然後刪除舊版本,然後希望盡可能重用未使用的空間。這裡還有用於表的fillfactor儲存選項(請參閱CREATE TABLE中的儲存參數部分)

指數層面也存在膨脹。對於寫入繁重的表,索引比其最佳大小大幾倍的情況並不少見。

您可能需要了解一些有關VACUUM的知識,以了解您在特定使用中可能受到表和索引膨脹的影響。

事務日誌的大小完全取決於對數據庫的寫入量。

進行良好大小估計的一般正確技術是模擬模式並用假數據填充表。generate_series 函式對此很有用。只是為了說明錯誤是如何填充手動執行過程的,對於(1)你開始充實你每行缺少大約 24 個字節的標題資訊,正如你在(3)中所懷疑的那樣,並且沒有正常表上的 OID 不再存在。

估計索引大小更加困難。它們相對於數據的壓縮程度取決於該數據有多少變化。由於 B 樹索引的工作方式,它們甚至不會線性增長。隨著時間的推移,在 UPDATE 和 DELETE 的工作量很大的情況下,您將獲得比在主要是 INSERT 上更大的索引。除非您模擬工作負載並讓它咀嚼一些測試數據一段時間,否則很難做到這一點。

主數據庫目錄中的事務日誌大小受 checkpoint_segments 參數的限制。該文件提供了一些用於估計大小的公式,更糟糕的情況是它的大小約為 16MB * 3 * checkpoint_segment。一旦它增長到那個大小並開始自我修剪,它就會被修復,並且與更大數據庫中的其他所有內容相比通常是微不足道的。

如果您對數據庫進行二進製備份,它將與數據庫大小相同。增量數據通常儲存為一系列 WAL 文件,這些是要考慮調整大小的重要事務日誌內容。WAL 格式過於復雜,無法手動調整大小,並且在很大程度上取決於 checkpoint_segments 等。同樣,估計它的唯一好方法是執行模擬並查看在該執行期間吐出多少 WAL 文件。

也可以進行基於文本的邏輯備份。這些將有多大取決於您使用的數據類型。文本格式會更大,但數字格式比字元串格式增長得更多,這是最簡單的例子。同樣,最簡單的估計方法是模擬一些數據,使用 pg_dump 將其保存為純文字,然後嘗試推斷。

引用自:https://dba.stackexchange.com/questions/25617