postgresql 查詢子孫資料_PostgreSQL資料庫中的記憶體架構和調優

PostgreSQL中的記憶體管理對于提高資料庫伺服器的性能非常重要。PostgreSQL的配置檔案(postgresql.conf)管理資料庫伺服器的配置。它使用參數的預設值，但是我們可以更改這些值以更好地配合工作負載和操作環境。

在本文中，我們将介紹這些記憶體相關參數。在開始介紹之前，讓我們先看看PostgreSQL中的記憶體架構。

記憶體架構

PostgreSQL中的記憶體可以分為兩類:

1. 本地記憶體區域:配置設定給每個後端程序使用。
2. 共享記憶體區域:它被PostgreSQL伺服器的所有程序使用。

本地記憶體區域

在PostgreSQL中，每個後端程序配置設定本地記憶體進行查詢處理;每個區域被劃分為大小固定或可變的子區域。

子區域如下。

Work_mem

執行器使用這個區域對進行ORDER BY和DISTINCT操作的元組進行整理。它還用于merge-join和hash-join操作的表連接配接。

Maintenance_work_mem

此參數用于某些維護操作(VACUUM，REINDEX)。

Temp_buffers

執行器使用此區域存儲臨時表。

共享記憶體區域

共享記憶體區域在PostgreSQL server啟動時配置設定。這個區域被劃分成幾個固定大小的子區域。

Shared buffer pool

PostgreSQL将表和索引中的頁面從持久存儲加載到共享緩沖池，然後直接對它們進行操作。

WAL buffer

PostgreSQL支援WAL (Write ahead log)機制，以確定在伺服器故障後不會丢失資料。WAL資料實際上是PostgreSQL中的一個事務日志，WAL buffer是将WAL資料寫入持久存儲之前的一個緩沖區域。

Commit Log

送出日志(commit log, CLOG)儲存所有事務的狀态，是并發控制機制的一部分。送出日志被配置設定給共享記憶體，并在整個事務處理過程中使用。

PostgreSQL定義了以下四種事務狀态。

1. IN_PROGRESS
2. COMMITTED
3. ABORTED
4. SUB-COMMITTED

記憶體參數調優

在PostgreSQL中，有一些重要的參數是用于記憶體管理的：

Shared_buffers

此參數指定用于共享記憶體緩沖區的記憶體容量。shared_buffers參數确定伺服器有多少記憶體用于緩存資料。shared_buffers的預設值通常為128 mb (128MB)。

這個參數的預設值非常低，因為在一些平台上，如較老的Solaris版本和SGI，想要較大的值需要進行侵入性操作，比如重新編譯核心。即使在現代Linux系統上，如果不首先調整核心設定，核心可能也不允許shared_buffers超過32MB。

在PostgreSQL 9.4和更高版本中，這種機制已經發生了變化，是以核心設定不需要在這裡進行調整。

如果資料庫伺服器上有高負載，那麼設定高值将提高性能。

如果您有一個具有1GB或更多記憶體的專用DB伺服器，shared_buffer配置參數的合理初始值是系統記憶體的25%。

shared_buffers的預設值= 128 MB。更改需要重新開機PostgreSQL服務。

下面是設定shared_buffers的一般建議。

• 在2GB記憶體以下，将shared_buffers的值設定為系統總記憶體的20%。
• 在32GB記憶體以下，将shared_buffers的值設定為系統總記憶體的25%。
• 超過32GB記憶體，将shared_buffers的值設定為8GB

Work_mem

此參數指定内部排序操作和散清單在寫入臨時磁盤檔案之前要使用的記憶體量。如果發生了很多複雜的排序，并且您有足夠的記憶體，那麼增加work_mem參數允許PostgreSQL進行更大的記憶體排序将比基于磁盤的排序更快。

注意，對于複雜查詢，許多排序或哈希操作可能并行運作。在開始将資料寫入臨時檔案之前，将允許每個操作使用與該值指定的記憶體相同的記憶體。有一種可能性是，幾個會話可以同時執行這些操作。是以，使用的總記憶體可能是work_mem參數值的許多倍。

在選擇正确的值時請記住這一點。排序操作用于ORDER BY、DISTINCT和merge連接配接。哈希表用于哈希連接配接、基于哈希的子查詢處理和基于哈希的聚合。

參數log_temp_files可用于記錄排序、散列和臨時檔案，這對于判斷排序是否溢出到磁盤而不是裝入記憶體非常有用。您可以使用EXPLAIN ANALYZE檢視查詢計劃檢查溢出到磁盤的排序。例如，在EXPLAIN ANALYZE的輸出中，如果您看到像這樣的行:“Sort Method: external merge Disk: 7528kB”，那麼一個至少8MB的work_mem将把中間資料儲存在記憶體中，并提高查詢響應時間。

work_mem的預設值是4MB。

一般建議work_mem如下設定：

• 從低值開始:32-64MB
• 然後在日志中查找temporary file”行
• 設定為最大臨時檔案的2-3倍

maintenance_work_mem

此參數指定維護操作使用的最大記憶體量(如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY)。由于資料庫會話一次隻能執行其中一個操作，而PostgreSQL安裝沒有很多操作同時運作，是以将maintenance_work_mem的值設定為明顯大于work_mem的值是安全的。

設定更大的值可以提高vacuum和恢複資料庫轉儲的性能。

有必要記住，在autovacuum運作時，可能會配置設定到autovacuum_max_workers乘以此記憶體的記憶體量，是以請注意不要将預設值設定得太高。

maintenance_work_mem的預設值是64MB。

一般建議maintenance_work_mem如下設定：

• 設定系統記憶體的10%，最多1GB
• 如果vacuum有問題，可以設定得更高

Effective_cache_size

應該将effective_cache_size設定為作業系統和資料庫本身可用于磁盤緩存的記憶體的估計數。這是關于作業系統和PostgreSQL緩沖緩存中可用記憶體的指導原則，而不是配置設定。

PostgreSQL 查詢計劃使用這個值來确定它所考慮的計劃是否适合記憶體。如果設定得太低，索引可能無法按您期望的方式執行查詢。由于大多數Unix系統在緩存時都很主動，是以專用資料庫伺服器上至少50%的可用記憶體将充滿緩存的資料。

一般建議effective_cache_size如下設定：

• 将該值設定為可用檔案系統緩存的數量
• 如果您不知道，請将該值設定為系統總記憶體的50%

effective_cache_size的預設值是4GB。

Temp_buffers

此參數設定每個資料庫會話使用的臨時緩沖區的最大值。會話本地緩沖區僅用于通路臨時表。此參數的設定可以在單個會話中更改，但隻能在會話中首次使用臨時表之前更改。

PostgreSQL資料庫利用這個記憶體區域來儲存每個會話的臨時表，當連接配接關閉時，這些臨時表将被清除。

temp_buffer的預設值為8MB。

結論

了解記憶體架構并能夠對參數進行适當的調優對于提高性能非常重要，對于高負載尤其必要。更多有關此類的性能調優技巧，請檢視 performance cheat sheet for PostgreSQL。

文章翻譯自：Architecture and Tuning of Memory in PostgreSQL Databases

https://severalnines.com/blog/architecture-and-tuning-memory-postgresql-databases?tdsourcetag=s_pcqq_aiomsg/