優化分了三個部分的內容：查詢類，更新類，管理類
查詢類：work_mem參數
該參數是postgresql每個進程可以使用的工作內存空間。如果進程需要的容量超過了這個範圍，就要把處理內容輸出到臨時文件里，相當於OS的swap處理。
查詢時如果用到了排序，哈希關聯，bitmap檢索等方式的話，就要分別利用work_mem的內存空間。也就是一個SQL語句可能會同時使用多個工作內存空間，而不是共用一個。
設置這個參數的時候，就要考慮到同時連接數以及每個連接平均使用的內存空間數。總內存數減去 （同時連接數”平均使用數的總和），剩下的才是OS和postgresql可以利用的緩存。（參考值，不是絕對值） 設置的太大的話，反倒會引起OS的swap，影響性能。
正確設置work_mem的方法如下：
1) psql連接數據庫
2）database=# set client_min_messages=’LOG’;
3) database=# set log_temp_files = 0; //顯示所有使用臨時文件的日誌
4) 執行比較慢的SQL語句
5) 如果顯示了類似下列的日誌的話，就要加大work_mem的值，然後重復第五步
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp27660.1568″, size 88155700
修改這個參數不用重啓數據庫，reload一下就可以了。
最好先執行一下SQL語句，把需要的資源全部放到緩存里，然後再調試，可以排除IO的影響。
二.
查詢類：random_page_cost參數和effective_cache_size參數
索引可以增加查詢效率，而該參數的設置會影響數據庫是否積極地使用索引。
默認的索引是樹狀結構，根據索引來檢索數據的時候，是根據索引的匹配結果再到實際表中的對應位置讀取數據。從磁盤的角度來講，這種讀取數據的方式叫做亂序讀取（正式叫法不清楚…）。 相反，不利用索引，直接掃描表的方式叫做順序讀取。
亂序讀取當然比順序讀取要慢。而postgresql用random_page_cost參數來設置亂序讀取要比順序讀取慢多少（倍）。
現在我們使用的新服務器的內存比數據庫要大3倍，所以理論上所有的數據都能放入內存。這樣的話，亂序讀取和順序讀取的效率不會差多少。而默認的4倍就不合適了。這次我們把它設置為1，也就是說和順序讀取一樣的效率，讓數據庫積極的使用索引來檢索數據。
另外，effective_cache_size參數是告訴數據庫，OS的緩存大小。越大，數據庫使用索引的積極性就越高。因為數據很可能在os的緩存里，亂序讀取的效率也不差。這個值理論上等於OS可以使用的緩存大小。
驗證方法：
一個很簡單的方法就是查看是否使用了Bitmap位圖掃描規劃。
位圖掃描規劃是Bitmap Index Scan和Bitmap Heap Scan的組合。先用Bitmap Index Scan通過索引，把匹配結果放到位圖表（這個表會使用work_mem設置的大小，超過就要寫入到臨時文件，影響效率），在通過這個位圖表循序讀取數據庫表（Bitmap Heap Scan），返回最終結果。
從理論上來說，性能按照 索引掃描 > 位圖掃描 > 順序掃描 的順序由高到低排列。
如果你的服務器的內存沒這麼富裕，可以通過pg_statio_user_tables和pg_statio_user_indexes表查看每個表和索引的緩存程度。
根據表的緩存程度，調節相關的SQL語句查詢時的設置值。
三.
查詢類：shared_buffers參數
大家都很熟悉的參數吧，共享內存的大小當然是越大越好啦。先別急，再仔細想想。
讀數據時：
數據庫先在共享內存里查找，找不到的話，再檢索磁盤。檢索磁盤時先查OS的緩存，找不到的話才會實際掃描磁盤。然後把數據放到OS的緩存，再返回給數據庫，也就是放到共享內存裡面。
按照這個邏輯，同一份數據，同時存在於OS的緩存和數據庫的共享內存裡面。 如果OS的緩存太小的化，就會放生swap，把數據放到磁盤里，當然效率也就會降低了。
寫數據時：
和讀數據相反，先更新共享內存裡面的內容，攢到一定數量或者到了一定時間段，再把更新的數據反應給OS。OS也是先更新到緩存，再實際更新到磁盤里。
如果需要更新的數據超過了OS的緩存，更新處理會等OS整理緩存以得到可利用的空間。
更新處理比較多的數據庫，或者vacuum處理的時候，需要佔用大量OS緩存。一定時間內無法確保到需要的空間時，數據庫服務也有可能會停止。
綜上所述，共享內存的值與OS的緩存一致是比較安全的。
如果以檢索為主的話，共享內存大一些也沒甚麼問題吧。當然前提是數據庫基本都能放到共享內存里。
查詢類的幾個參數可以用以下的關係表示。
物理內存 ＝ work_mem ” 最大同時連接數” 平均使用work_mem數 ＋ shared_buffers ＋ effective_cache_size
以上公式只適用於數據庫專用服務器的場合。
四.
更新類：wal_sync_method參數
向磁盤強制更新 WAL 數據的方法。在linux環境里可以使用fsync，fdatasync，open_sync三種方式。那種方式最快呢？可以用postgresql代碼裡面的test_fsync這個工具來測試。8.3版本以後才有這個工具好像。。。。
$ cd (源代碼)postgresq-8.4.5/src/tools/fsync
$make
$./test_fsync
然後設置為其中最快的一種方式就行了。
例子：
open o_sync, write 8.xxx
write, fdatasync 4.xxxx
write, fsync 4.xxxx
默認的fsync方式最快。 這個結果隨著硬件和OS的不同會有很大的變化，系統有甚麼變更的時候，最好執行以下看看。
更新類：checkpoints相關參數
checkpoint就是把共有內存裡面更新過的數據寫道磁盤（磁盤緩存）裡面的處理。有兩個參數控制寫入的時機和頻率，兩個參數同時有效，滿足任何一個條件都會執行寫入處理。（應該是。。^_^;）
checkpoint_segments：定量，寫入大小。 一個是16MB，默認是3，所以就是48MB。到了這個量就會調用寫入處理。
checkpoint_timeout：定時，寫入間隔。默認是5min（五分種）。過了5分鐘就調入寫入處理。
這兩個參數很容易理解，而判斷設置的合適不合適，就要看實際的運用了。
如果日誌文件裡面頻繁出現下列的提示，就要考慮調整上述兩個參數了。
HINT: Consider increasing the configuration parameter “checkpoint_segments”.
LOG: checkpoints are occurring too frequently (25 seconds apart)
這個提示說明，共享內存裡面的數據更新的很頻繁，就算寫入磁盤之後，馬上又會被更新。這時就可以加大一下寫入間隔和寫入大小，再看看效果。但是如果在vacuum或者在定時處理的時候出現上述提示的話，基本可以不理會。
另外還有一個參數，checkpoint_completion_target，是控制寫入處理的分步程度的。
默認是0.5，也就是說，一次需要寫入的內容，實際上不是一次全部完成，而是在寫入間隔中分步完成的。
例如：
checkpoint_timeout是5分鐘，而checkpoint_completion_target是0.5的話，
那麼實際上是用了2分30秒來分步寫入變更過的數據。每次寫入不需要大量的緩存，可以加快寫入效率。
當然是越分步越好了，所以推薦設置成0.9。 當然不能超過1啦，會跟下一次的寫入發生衝突。
更新類：wal_buffers參數
WAL數據用到的內存大小。將使用OS的內存，而不是postgresql的空閒內存。更新大量數據的時候，寫入處理會使用這個空間，加快寫入速度。 更改之後需要重新啓動數據庫，所以事先設置得大一點比較方便。比如5MB
五.
管理類：maintenance_work_mem參數
這個參數表明VACUUM或者REINDEX處理可以使用的內存空間。基本上只有一個用戶在指定時間（半夜）運行此類的處理，所以可以設置的大方一點。這次服務器的內存足夠用，所以設置成最大值1GB。
管理類：autovacuum相關參數
8.4的版本優化了autovacuum的速度。把一個表裡面的頁面（page）信息放到位圖表裡管理，每次讀入需要處理的頁面，在頁面里先調整數據的配置關係後再寫入數據。
而普通的VACUUM命令和以前一樣，速度沒有進步。
默認上是推薦使用autovacuum，不過在使用pgpool之類的同步數據的時候，會有鎖表的處理，這時如果正好在autovacuum的話，就會發生延遲，影響效率。所以這次還是禁用了autovacuum，還是原來的vacuum在半夜定時處理。
另外，如果使用autovacuum的話，可以設置同時啓動幾個進程。這個參數是maintenance_work_mem，默認是3，也就是說，會有3個autovacuum隨時執行。
在導入數據的時候，autovacuum也會同時運行，服務器性能沒那麼好的時候，還是先關掉autovacuum之後在導數據比較快。
查詢類：work_mem參數
該參數是postgresql每個進程可以使用的工作內存空間。如果進程需要的容量超過了這個範圍，就要把處理內容輸出到臨時文件里，相當於OS的swap處理。
查詢時如果用到了排序，哈希關聯，bitmap檢索等方式的話，就要分別利用work_mem的內存空間。也就是一個SQL語句可能會同時使用多個工作內存空間，而不是共用一個。
設置這個參數的時候，就要考慮到同時連接數以及每個連接平均使用的內存空間數。總內存數減去 （同時連接數”平均使用數的總和），剩下的才是OS和postgresql可以利用的緩存。（參考值，不是絕對值） 設置的太大的話，反倒會引起OS的swap，影響性能。
正確設置work_mem的方法如下：
1) psql連接數據庫
2）database=# set client_min_messages=’LOG’;
3) database=# set log_temp_files = 0; //顯示所有使用臨時文件的日誌
4) 執行比較慢的SQL語句
5) 如果顯示了類似下列的日誌的話，就要加大work_mem的值，然後重復第五步
LOG: temporary file: path “base/pgsql_tmp/pgsql_tmp27660.1568″, size 88155700
修改這個參數不用重啓數據庫，reload一下就可以了。
最好先執行一下SQL語句，把需要的資源全部放到緩存里，然後再調試，可以排除IO的影響。
二.
查詢類：random_page_cost參數和effective_cache_size參數
索引可以增加查詢效率，而該參數的設置會影響數據庫是否積極地使用索引。
默認的索引是樹狀結構，根據索引來檢索數據的時候，是根據索引的匹配結果再到實際表中的對應位置讀取數據。從磁盤的角度來講，這種讀取數據的方式叫做亂序讀取（正式叫法不清楚…）。 相反，不利用索引，直接掃描表的方式叫做順序讀取。
亂序讀取當然比順序讀取要慢。而postgresql用random_page_cost參數來設置亂序讀取要比順序讀取慢多少（倍）。
現在我們使用的新服務器的內存比數據庫要大3倍，所以理論上所有的數據都能放入內存。這樣的話，亂序讀取和順序讀取的效率不會差多少。而默認的4倍就不合適了。這次我們把它設置為1，也就是說和順序讀取一樣的效率，讓數據庫積極的使用索引來檢索數據。
另外，effective_cache_size參數是告訴數據庫，OS的緩存大小。越大，數據庫使用索引的積極性就越高。因為數據很可能在os的緩存里，亂序讀取的效率也不差。這個值理論上等於OS可以使用的緩存大小。
驗證方法：
一個很簡單的方法就是查看是否使用了Bitmap位圖掃描規劃。
位圖掃描規劃是Bitmap Index Scan和Bitmap Heap Scan的組合。先用Bitmap Index Scan通過索引，把匹配結果放到位圖表（這個表會使用work_mem設置的大小，超過就要寫入到臨時文件，影響效率），在通過這個位圖表循序讀取數據庫表（Bitmap Heap Scan），返回最終結果。
從理論上來說，性能按照 索引掃描 > 位圖掃描 > 順序掃描 的順序由高到低排列。
如果你的服務器的內存沒這麼富裕，可以通過pg_statio_user_tables和pg_statio_user_indexes表查看每個表和索引的緩存程度。
根據表的緩存程度，調節相關的SQL語句查詢時的設置值。
三.
查詢類：shared_buffers參數
大家都很熟悉的參數吧，共享內存的大小當然是越大越好啦。先別急，再仔細想想。
讀數據時：
數據庫先在共享內存里查找，找不到的話，再檢索磁盤。檢索磁盤時先查OS的緩存，找不到的話才會實際掃描磁盤。然後把數據放到OS的緩存，再返回給數據庫，也就是放到共享內存裡面。
按照這個邏輯，同一份數據，同時存在於OS的緩存和數據庫的共享內存裡面。 如果OS的緩存太小的化，就會放生swap，把數據放到磁盤里，當然效率也就會降低了。
寫數據時：
和讀數據相反，先更新共享內存裡面的內容，攢到一定數量或者到了一定時間段，再把更新的數據反應給OS。OS也是先更新到緩存，再實際更新到磁盤里。
如果需要更新的數據超過了OS的緩存，更新處理會等OS整理緩存以得到可利用的空間。
更新處理比較多的數據庫，或者vacuum處理的時候，需要佔用大量OS緩存。一定時間內無法確保到需要的空間時，數據庫服務也有可能會停止。
綜上所述，共享內存的值與OS的緩存一致是比較安全的。
如果以檢索為主的話，共享內存大一些也沒甚麼問題吧。當然前提是數據庫基本都能放到共享內存里。
查詢類的幾個參數可以用以下的關係表示。
物理內存 ＝ work_mem ” 最大同時連接數” 平均使用work_mem數 ＋ shared_buffers ＋ effective_cache_size
以上公式只適用於數據庫專用服務器的場合。
四.
更新類：wal_sync_method參數
向磁盤強制更新 WAL 數據的方法。在linux環境里可以使用fsync，fdatasync，open_sync三種方式。那種方式最快呢？可以用postgresql代碼裡面的test_fsync這個工具來測試。8.3版本以後才有這個工具好像。。。。
$ cd (源代碼)postgresq-8.4.5/src/tools/fsync
$make
$./test_fsync
然後設置為其中最快的一種方式就行了。
例子：
open o_sync, write 8.xxx
write, fdatasync 4.xxxx
write, fsync 4.xxxx
默認的fsync方式最快。 這個結果隨著硬件和OS的不同會有很大的變化，系統有甚麼變更的時候，最好執行以下看看。
更新類：checkpoints相關參數
checkpoint就是把共有內存裡面更新過的數據寫道磁盤（磁盤緩存）裡面的處理。有兩個參數控制寫入的時機和頻率，兩個參數同時有效，滿足任何一個條件都會執行寫入處理。（應該是。。^_^;）
checkpoint_segments：定量，寫入大小。 一個是16MB，默認是3，所以就是48MB。到了這個量就會調用寫入處理。
checkpoint_timeout：定時，寫入間隔。默認是5min（五分種）。過了5分鐘就調入寫入處理。
這兩個參數很容易理解，而判斷設置的合適不合適，就要看實際的運用了。
如果日誌文件裡面頻繁出現下列的提示，就要考慮調整上述兩個參數了。
HINT: Consider increasing the configuration parameter “checkpoint_segments”.
LOG: checkpoints are occurring too frequently (25 seconds apart)
這個提示說明，共享內存裡面的數據更新的很頻繁，就算寫入磁盤之後，馬上又會被更新。這時就可以加大一下寫入間隔和寫入大小，再看看效果。但是如果在vacuum或者在定時處理的時候出現上述提示的話，基本可以不理會。
另外還有一個參數，checkpoint_completion_target，是控制寫入處理的分步程度的。
默認是0.5，也就是說，一次需要寫入的內容，實際上不是一次全部完成，而是在寫入間隔中分步完成的。
例如：
checkpoint_timeout是5分鐘，而checkpoint_completion_target是0.5的話，
那麼實際上是用了2分30秒來分步寫入變更過的數據。每次寫入不需要大量的緩存，可以加快寫入效率。
當然是越分步越好了，所以推薦設置成0.9。 當然不能超過1啦，會跟下一次的寫入發生衝突。
更新類：wal_buffers參數
WAL數據用到的內存大小。將使用OS的內存，而不是postgresql的空閒內存。更新大量數據的時候，寫入處理會使用這個空間，加快寫入速度。 更改之後需要重新啓動數據庫，所以事先設置得大一點比較方便。比如5MB
五.
管理類：maintenance_work_mem參數
這個參數表明VACUUM或者REINDEX處理可以使用的內存空間。基本上只有一個用戶在指定時間（半夜）運行此類的處理，所以可以設置的大方一點。這次服務器的內存足夠用，所以設置成最大值1GB。
管理類：autovacuum相關參數
8.4的版本優化了autovacuum的速度。把一個表裡面的頁面（page）信息放到位圖表裡管理，每次讀入需要處理的頁面，在頁面里先調整數據的配置關係後再寫入數據。
而普通的VACUUM命令和以前一樣，速度沒有進步。
默認上是推薦使用autovacuum，不過在使用pgpool之類的同步數據的時候，會有鎖表的處理，這時如果正好在autovacuum的話，就會發生延遲，影響效率。所以這次還是禁用了autovacuum，還是原來的vacuum在半夜定時處理。
另外，如果使用autovacuum的話，可以設置同時啓動幾個進程。這個參數是maintenance_work_mem，默認是3，也就是說，會有3個autovacuum隨時執行。
在導入數據的時候，autovacuum也會同時運行，服務器性能沒那麼好的時候，還是先關掉autovacuum之後在導數據比較快。
沒有留言:
張貼留言