35. PostgreSQL 基本調校

PostgreSQL 是一個功能非常廣泛的資料庫系統，能夠在資源非常有限的環境以及與各種其他應用程式共享的環境中高效運行。為了確保它能在許多不同的環境中正常運行，預設配置非常保守，並不十分適合高效能的生產資料庫。此外，地理空間資料庫具有不同的使用模式，而且資料往往由較少但大得多的記錄組成，這與非地理空間資料庫不同。因此，您可以看到預設配置並不完全適合我們的用途。

所有這些配置參數都可以在 postgresql.conf 資料庫設定檔中編輯。這是一個普通的文字檔，可以使用任何文字編輯器進行編輯。變更必須在伺服器重新啟動後才會生效。

本節介紹一些可以調整的配置參數，以使其更適合用於生產環境的地理空間資料庫。

注意

這些值僅為建議值；每個環境都會有所不同，需要進行測試才能確定最佳配置。但本節應該能幫助您有個好的開始。

35.1. shared_buffers

設定資料庫伺服器用於共享記憶體緩衝區的記憶體量。顧名思義，這些緩衝區會在後端處理程序之間共享。預設值通常遠遠不足以應付生產資料庫。

預設值：通常為 32MB

建議值：約佔資料庫記憶體的 75%，最多約為 2GB

35.2. effective_cache_size

除了 PostgreSQL 為 shared_buffers 保留的記憶體外，查詢規劃器還會考慮作業系統可能已將多少磁碟區塊快取為其虛擬檔案系統的一部分。對於具有大量記憶體的系統，這個值可能會很大。effective_cache_size 大約是機器上的記憶體量，減去 shared_buffers，減去 work_mem 乘以預期的連線數，減去機器上執行的任何其他處理程序所需的任何記憶體，再減去約 1GB 以滿足其他隨機作業系統需求。資料庫不會直接 使用 額外的快取，但它會計算預計作業系統已將檔案系統資料快取到這麼多記憶體中的計畫。

預設值：通常為 4GB

建議值：預期在正常作業條件下任何可用「可用」記憶體量

35.3. work_mem

定義內部排序作業、索引作業和雜湊表在資料庫切換到磁碟檔案之前可以消耗的記憶體量。此值定義每個作業的可用記憶體；複雜的查詢可能會並行運行多個排序或雜湊作業，而且每個連線的工作階段都可能會執行查詢。

因此，您必須在增加此值之前，考慮連線數和預期查詢的複雜性。增加的 好處 是，可以完成更多這些作業的處理，包括 ORDER BY 和 DISTINCT 子句、合併和雜湊聯結、基於雜湊的彙總和基於雜湊的子查詢處理，而不會產生磁碟寫入。增加的 成本 是 每個連線 都會使用的記憶體，這在生產級別的連線中可能會相當高。

預設值：1MB

建議值：32MB

35.4. maintenance_work_mem

定義用於維護作業的記憶體量，包括清理、索引和外鍵建立。由於這些作業並不十分常見，因此較高的值只會帶來偶爾的成本，並且可能會大幅加快維護活動。如下所示，也可以在執行多個 CREATE INDEX 或 VACUUM 呼叫之前，為單個工作階段增加此參數。

SET maintenance_work_mem TO '128MB';
VACUUM ANALYZE;
SET maintenance_work_mem TO '16MB';

預設值：16MB

建議值：128MB

35.5. wal_buffers

設定用於預寫式日誌 (WAL) 資料的記憶體量。預寫式日誌提供了一種高效能機制，可確保資料完整性。在每次變更命令期間，變更的效果會先寫入 WAL 檔案，然後刷新到磁碟。只有在刷新 WAL 檔案後，才會將變更寫入資料檔案本身。這樣可以最佳且非同步的方式將資料檔案寫入磁碟，同時確保在發生當機時，可以從 WAL 中復原所有資料變更。

此緩衝區的大小只需要大到足以容納單個典型交易的 WAL 資料即可。雖然預設值通常足以應付大多數資料，但地理空間資料往往會大得多。因此，建議增加此參數的大小。

預設值：64kB

建議值：1MB

35.6. checkpoint_segments

此值設定自動 WAL 檢查點之間可以填入的最大日誌檔案區段數（通常為 16MB）。WAL 檢查點是 WAL 交易序列中的一個點，保證資料檔案已使用檢查點之前的所有資訊進行更新。此時，所有髒資料頁面都會刷新到磁碟，並且會將檢查點記錄寫入日誌檔案。這允許當機復原程序找到最新的檢查點記錄，並套用所有後續的日誌區段以完成資料復原。

由於檢查點程序需要將所有髒資料頁面刷新到磁碟，因此會產生大量的 I/O 負載。上述相同的論點也適用；地理空間資料的大小足以打破非地理空間最佳化。增加此值將防止過多的檢查點，但可能會導致伺服器在當機時重新啟動速度較慢。

預設值: 3

建議值: 6

35.7. random_page_cost

這是一個無單位的數值，表示從磁碟隨機存取頁面的成本。此值相對於許多其他成本參數，包括循序頁面存取和 CPU 作業成本。雖然此值沒有神奇的解決方案，但預設值通常是保守的，適用於在旋轉媒體上運行的資料庫。SSD 的隨機存取成本應設定得更低。

可以使用 SET random_page_cost TO 2.0 命令，在每個工作階段的基礎上設定此值，這對於測試它如何影響查詢計畫非常有用。

預設值: 4.0

建議值：旋轉媒體為 2.0，SSD 為 1.0

35.8. seq_page_cost

這是控制循序頁面存取成本的參數。此值通常不需要調整，但此值與 random_page_cost 之間的差異會極大地影響查詢規劃器所做的選擇。也可以在每個工作階段的基礎上設定此值。

預設值: 1.0

建議值: 1.0

35.9. 重新載入組態

在進行這些變更後，儲存變更並重新載入組態。最簡單的方法是重新啟動 PostgreSQL 服務。

  • 在 pgAdmin 中，在伺服器 PostGIS (localhost:5432) 上按一下滑鼠右鍵，然後選取「中斷連線」。

  • 在 Windows 服務 (services.msc) 中，在 PostgreSQL 上按一下滑鼠右鍵，然後選取「重新啟動」。

  • 回到 pgAdmin 中，再次按一下伺服器，然後選取「中斷連線」。