PostgreSQL程式/伺服器配置

fiona8953發表於2016-07-28
SQL伺服器

postgres 20079     1  0 Jul07 ?        00:00:41 /usr/pgsql-9.2/bin/postmaster -p 60904 -D /u01/app/postgres/data/monicrm
postgres 20081 20079  0 Jul07 ?        00:00:00 postgres: logger process                                                                                                
postgres 20083 20079  0 Jul07 ?        00:00:02 postgres: checkpointer process                                                                                          
postgres 20084 20079  0 Jul07 ?        00:00:10 postgres: writer process                                                                                                
postgres 20085 20079  0 Jul07 ?        00:00:10 postgres: wal writer process                                                                                            
postgres 20086 20079  0 Jul07 ?        00:00:45 postgres: autovacuum launcher process                                                                                   
postgres 20087 20079  0 Jul07 ?        00:01:06 postgres: stats collector process                                                                                      

pg_xlog/ 子目錄裡維護著一套預寫日誌(WAL)。 這些日誌記錄著每一次對資料庫的修改細節。 這些日誌存在是為了防止崩潰： 如果系統崩潰，資料庫可以透過"重放"上次檢查點以來的日誌記錄以恢復資料庫的完整性。組合檔案系統備份與WAL檔案的備份。如果需要恢復，我們就恢復備份， 然後重放備份了的WAL檔案，把備份恢復到當前的時間。
pg_clog子目錄將會佔用更多空間， 因為它必須為所有autovacuum_freeze_max_age之後的事務儲存提交狀態。 每個事務提交狀態使用2位元組，因此如果autovacuum_freeze_max_age 設定為最大允許值為20億，pg_clog將會增加到大約500M。 如果這個尺寸比起你的資料庫來只是小菜一碟，我們推薦你將 autovacuum_freeze_max_age設為允許的最大值。否則， 如何設定將取決於你願意給pg_clog多大的空間。預設值是2億， 大約需要50MB的pg_clog儲存空間。Max XID = 2億，pg_clog=44M
pg_tblspc=108Gb

 是一個外部專案，做複雜的日誌檔案分析

SHOW將顯示當前執行時引數的數值。這些變數可以透過 SET語句、編輯postgresql.conf檔案、 PGOPTIONS環境變數(在使用基於libpq的應用程式的時候)、 啟動postgres伺服器的命令列引數來設定。
SHOW ALL;
            name         | setting |                description                                                          
-------------------------+---------+-------------------------------------------------
 allow_system_table_mods | off     | Allows modifications of the structure of ...
    .
    .
    .
 xmloption               | content | Sets whether XML data in implicit parsing ...
 zero_damaged_pages      | off     | Continues processing past damaged page headers.
(196 rows)
 data_directory (string) 宣告為資料儲存使用的目錄。這個選項只能在伺服器啟動的時候設定。 config_file (string） 宣告主伺服器配置檔案(通常叫postgresql.conf)。這個選項只能在postgres命令列上設定。 hba_file (string)宣告基於主機的認證(HBA)配置檔案(通常叫pg_hba.conf)。這個選項只能在伺服器啟動的時候設定。 ident_file (string)宣告用於使用者名稱匹配的配置檔案(通常叫pg_ident.conf)。 這個選項只能在伺服器啟動的時候設定。 external_pid_file (string)宣告可被伺服器管理程式使用的額外PID檔案。這個選項只能在伺服器啟動的時候設定。 
shared_buffers (integer)

設定資料庫伺服器將使用的共享記憶體緩衝區數量。預設通常是128兆位元組(128MB)， 但是如果你的核心設定不支援這麼大，那麼可以少些(在initdb的時候決定)。 每個緩衝區大小的典型值是128千位元組，（BLCKSZ的非預設值改變最小值） 不過，這個數值比最小值大一些通常需要更好的效能。 這個選項只能在伺服器啟動的時候設定。

如果你有1GB或更多記憶體的專用資料庫伺服器， 對於shared_buffers合理的初始值是您的系統記憶體的25%。 有一些工作負載，甚至在那裡對於shared_buffers大設定是有效的， 但因為PostgreSQL也依賴於作業系統快取， 它是不可能的，RAM到shared_buffers的多於40%的分配比更少數量的工作的更好。 對於shared_buffers的大量設定 通常要求相應增加checkpoint_segments， 為了延長寫大量新的或者需較長時間修改的資料的程式。
eg. shared_buffers = 4096M , checkpoint_segments = 32 (default 16)

work_mem (integer)

宣告內部排序操作和Hash表在開始使用臨時磁碟檔案之前使用的記憶體數目。 預設數值是1兆位元組(1MB)。請注意對於複雜的查詢， 可能會同時併發執行好幾個排序或者雜湊操作；每個都會被批准使用這個引數宣告的這麼多記憶體， 然後才會開始求助於臨時檔案。同樣，好幾個正在執行的會話可能會同時進行排序操作。 因此使用的總記憶體可能是work_mem的好幾倍。 當選擇這個值的時候，必須記住這個事實。 ORDER BY, DISTINCT和融合連線都要用到排序操作。 Hash表在雜湊連線、雜湊為基礎的聚合、雜湊為基礎的IN子查詢處理中都要用到。eg. work_mem = 8 MB 

maintenance_work_mem (integer)

宣告在維護性操作(比如VACUUM, CREATE INDEX和ALTER TABLE ADD FOREIGN KEY)中使用的最大的記憶體數。 預設是16兆位元組(16MB)。因為在一個資料庫會話裡， 任意時刻只有一個這樣的操作可以執行，並且一個資料庫安裝通常不會有太多這樣的工作併發執行， 把這個數值設定得比work_mem更大是安全的。 更大的設定可以改進清理和恢復資料庫轉儲的速度。請注意，當執行自動清理， 直至autovacuum_max_workers次分配這個記憶體， 所以要小心，不要設定預設值太高。eg. maintenance_work_mem = 16 MB

可靠性和預寫式日誌

可靠性操作的一個方面是所有已提交的資料都應該儲存在一個非易失的區域，這樣就不會因為電力失效、 作業系統崩潰、硬體失效(除了非易失區域自身失效之外)等原因導致資料丟失。向計算機的永久儲存裝置 (磁碟驅動器或其他等效的裝置)成功寫入資料通常可以滿足這個要求。實際上，即使計算機完全失效， 只要磁碟驅動器倖免於難，那麼就可以將它們移動到另外一臺硬體相容的計算機上， 而所有已經提交的事務將保持原狀。
首先，有作業系統的緩衝區記憶體，它緩衝常用的磁碟塊並將磁碟的寫入請求進行合併。
然後，在磁碟驅動器的控制器上可能還有一個緩衝；尤其在RAID控制卡上更為常見。 這些緩衝區中，有些是透過式寫入，意思是寫入動作在到達的同時寫入到磁碟上。 其它則是回寫式寫入，意思是資料將在稍後寫入驅動器，這樣的緩衝區會降低可靠性， 因為磁碟控制器上的記憶體是易失的，在發生電力失效的情況下會丟失其中的內容。 好一些的控制器卡備有電池備份單元(BBUs)， 可以在系統電力失效的情況下提供電力，在電力恢復之後，這些資料將會被寫入磁碟驅動器。最後，大多數磁碟驅動器自身也有緩衝區。有些是透過式的，有些是回寫式的。 和磁碟控制器一樣，回寫式的磁碟緩衝區也存在資料丟失的問題。 消費級別的 IDE 和 SATA 驅動器一般都有回寫式緩衝，在掉電的情況下很容易丟失資料。 很多固態磁碟(SSD)也有易失的回寫式緩衝。

預寫式日誌(WAL)是一種確保資料完整性的標準方法。 WAL的中心思想是對資料檔案的修改(它們是表和索引的載體) 必須只能發生在這些修改已經記錄到日誌之後，也就是說， 在描述這些變化的日誌記錄刷寫到永久儲存器之後。如果我們遵循這個過程， 那麼就不需要在每次事務提交的時候都把資料頁刷寫到磁碟， 因為在出現崩潰的情況下可以用日誌來恢復資料庫： 任何尚未應用於資料頁的修改都可以先從日誌記錄中重做(這叫向前滾動恢復，也叫 REDO)。
使用WAL顯著地減少了磁碟寫的次數，因為只有日誌檔案需要刷寫到磁碟以保證事務被提交， 而不是事務修改的所有資料檔案都需要刷寫到磁碟。日誌檔案是順序寫的，所以同步日誌的開銷要遠比刷寫資料頁的開銷小。 尤其對於有很多修改不同資料儲存位置的小事務的服務而言更是如此。另外，當伺服器正在處理許多小的併發事務時， 日誌檔案的一個fsync足以提交許多事務。


wal_level (enum) 最小的WAL不包含從基本的備份和WAL日誌中重建資料的足夠資訊， 所以archive或者hot_standby級別必須用來啟動 WAL歸檔()和流複製。
在hot_standby級別，相同的資訊被記錄為archive， 加上需要重建從WAL執行的事務狀態資訊。 為了在備用伺服器上啟用只讀查詢，主庫上wal_level必須設定為hot_standby， 必須啟動備庫上的。 使用hot_standby和archive級別之間的效能幾乎沒有可測量的差異 ，如果任何生產的影響是明顯的，則是值得歡迎的。


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26477398/viewspace-2122668/，如需轉載，請註明出處，否則將追究法律責任。

相關文章