PostgreSQL技術大講堂 - 第32講：資料庫引數調整

PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色許可權、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

第32講：資料庫引數調整

第32講：11月04日(週六)19:30-20:30，往期文件及影片，聯絡CUUG

內容 : 資料庫常用引數調整：shared_buffers、wal_buffer、effective_cache_size、等等

shared_buffers

· PostgreSQL使用自己的緩衝區，也使用作業系統緩衝IO。這意味著資料儲存在記憶體中兩次，首先是PostgreSQL緩衝區，然後是作業系統緩衝區。

· 與其他資料庫不同，PostgreSQL不提供直接IO。這稱為雙緩衝。

· PostgreSQL緩衝區稱為shared_buffers，它是大多數作業系統最有效的可調引數。

· PostgreSQL將用shared_buffers引數快取如下資料：

表資料

索引

執行計劃

· 初始化參考值：實體記憶體1/4

wal_buffer

· PostgreSQL將其WAL(預寫日誌)記錄寫入緩衝區，然後將這些緩衝區重新整理到磁碟。

· 緩衝區的預設大小，由wal_buffers定義，但如果您有大量併發連線，則較高的值可以提供更好的效能。

· 該緩衝區的作用是臨時存放redo log，所以分配太大不會對效能有好處，一般10MB左右。

effective_cache_size

· 該effective_cache_size提供了可以用於磁碟快取儲存器的估計。

· 它只是一個指導原則，而不是確切分配的記憶體或快取大小。

· 它不分配實際記憶體，而是告訴最佳化器核心中可用的快取量。

· 如果將此值設定得太低，查詢計劃程式可以決定不使用某些索引，即使它們有用。

· 因此，設定較大的值總是有益的。

· 建議使用預設值。

work_mem

· 指定在寫入磁碟上的臨時檔案之前，ORDER BY，DISTINCT，JOIN和雜湊表的內部操作將使用的記憶體量。

· 此配置用於複雜排序，如果必須進行復雜排序，則增加work_mem的值以獲得良好結果。記憶體中的排序比溢位到磁碟的排序快得多。

· 設定非常高的值可能會導致部署環境出現記憶體瓶頸，因為此引數是按使用者排序操作。

· 如果您有許多使用者嘗試執行排序操作，系統將為所有使用者分配 work_mem * 總排序操作 。

· 全域性設定此引數可能會導致記憶體使用率過高，強烈建議在會話級別修改它。

· postgres=# SET work_mem=“2MB”; (會話級配置)

maintenance_work_mem

· maintenance_work_mem是用於維護任務的記憶體設定。預設值為64MB。本引數可以針對每個session設定。

· 設定較大的值有助於執行VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等任務。

· 由於會話中只能同時執行其中一個操作，並且通常沒有多個同時執行，因此它可能比work_mem大。

· 較大的配置可以提高VACUUM和資料庫還原的效能。

· 執行autovacuum時，或者配置autovacuum_work_mem引數來單獨管理它。

FSYNC

· 如果啟用了fsync，PostgreSQL將嘗試確保將更新寫入物理磁碟，會延長響應時間對效能有一定影響。

· 這可確保在作業系統或硬體崩潰後可以將資料庫群集恢復到一致狀態。

· 禁用fsync通常可以提高效能，但在發生電源故障或系統崩潰時可能會導致資料丟失。

· 從外部資料重新建立整個資料庫，則建議停用fsync。

synchronous_commit

· 指定在命令向客戶端返回“成功”指示之前，事務提交是否將等待WAL記錄寫入磁碟。這是效能和可靠性之間的權衡。預設設定為“on”。

· 可能的值包括：“on”，“remote_apply”，“remote_write”，“local”和“off”。

· 與fsync不同，禁用此引數不會產生任何資料庫不一致的風險：作業系統或資料庫崩潰可能導致丟失一些最近發生的可能提交的事務，但資料庫的狀態將與這些事務完全相同，未提交的將被拋棄。

· 當效能比事務永續性更重要時，停用synchronous_commit可能是一個有用的替代方法。

· 這意味著成功狀態與保證寫入磁碟之間會存在時間差。在伺服器崩潰的情況下，即使客戶端在提交時收到成功訊息，資料也可能丟失。在這種情況下，事務提交非常快，因為它不會等待重新整理WAL檔案，但可靠性受到損害。

checkpoint_timeout

· checkpoint_timeout：檢查點啟動的時間間隔

· 將此設定得太低會減少崩潰恢復時間，因為更多資料會寫入磁碟，但由於每個檢查點都會佔用寶貴的系統資源，因此也會損害效能。高頻率的檢查點可能會影響效能。例項崩潰的機率與長時間執行的效能相比，例項崩潰所佔的比重要小的多，該值設定為例項崩潰後客戶允許恢復的時間。

· 檢查點程式將資料重新整理到資料檔案中。

· 發生CHECKPOINT時完成此活動。這是一項昂貴的操作，可能會導致大量的IO。 整個過程涉及昂貴的磁碟讀/寫操作。

· checkpoint_completion_target衡量檢查點完成的時間長度。

checkpoint_completion_target

· 資料庫中一個至關重要的引數，主要與引數checkpoint_timeout(checkpoint_timeout)配合使用，值越小意味著檢查點要越快完成，要求寫得要快。

· 控制每次檢查點發生時i/o的吞吐量，值越高，則i/o佔用的資源越少，資料庫效能越好;值越低，則i/o佔用的資源越多，影響資料庫效能，但是提高檢查點完成速度。

其它常見引數

· max_connections

確定與資料庫同時連線的最大數量。因為每個客戶端都可以配置記憶體資源，因此，客戶機的最大數量表明使用的記憶體的最大數量。

· superuser_reserved_connections

在達到max_connection限制的情況下，這些連線保留給超級使用者。

· temp_buffers

設定每個會話使用的最大臨時緩衝區數。 這些是僅用於訪問臨時表的本地會話緩衝區。 會話將根據需要分配臨時緩衝區，直到temp_buffers給出的限制。

· max_wal_size

允許WAL日誌所在目錄使用的最大尺寸，預設為1GB。

該引數與wal_segment_size相關，預設是16MB，允許存放64個wal段檔案。