PostgreSQL 的正確儲存大小估計技術
我們正在為生產使用準備一個 PostgreSQL 數據庫,我們需要估計這個數據庫的儲存大小。我們是一支在數據庫管理方面缺乏專業知識的開發人員團隊,因此我們正在研究、閱讀手冊並使用我們的一般資訊技術知識來實現這一目標。
我們有實際數據要遷移到這個數據庫,並對增長有一些粗略的估計。舉例來說,假設我們估計每年增長 50%。
關鍵是:進行良好尺寸估計的一般適當技術是什麼?
我們通過以下規則估算儲存使用量。我們需要建議的主題用粗體字標記。非常歡迎對整個過程提供回饋:
估計每個表的大小
發現每一行的實際大小。
- 對於具有固定大小的欄位(如
bigint
、char
等),我們使用文件中描述的大小- 對於具有動態大小的欄位(如
text
),我們估計了字元串長度並使用了函式select pg_column_size('expected text here'::text)
- 我們為PostgreSQL 內部使用的OID添加了 4 個字節
將每行的大小乘以估計的行數
我是否需要在這裡考慮任何成本,例如行或表元數據?
估計每個表索引的大小
- 不知道如何估計,這裡需要建議
- 估計事務日誌的大小
- 不知道如何估計,這裡需要建議
- 估計備份的大小(完整和增量)
- 不知道如何估計,這裡需要建議
- 對實際最小尺寸的所有估計值求和
- 將 1.5 倍的因子(50% 的增長)應用於 1 年後最小規模的估計值 1、2 和 4 的總和
- 對估計值 5 和 6 應用 1.2 ~ 1.4(多 20% 至 40%)的總體因子以獲得良好的安全邊際
我知道規則非常廣泛。讓我知道是否需要範例以便更好地理解。
對於第 1) 點,您需要閱讀文件的Storage Page Layout章節,並特別考慮行級別元數據的HeapTupleHeaderData Layout表。
對於使用者表,每行 4 字節的 OID 已過時。自 8.1 起,PostgreSQL 不再預設擁有它們。這現在由
default_with_oids
config 參數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 將其保存為純文字,然後嘗試推斷。