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段文件。