PostgreSQL安裝完成後，引數調整

PostgreSQL安裝完成後，引數調整

/*CPU
檢視CPU型號*/
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c

/*檢視物理CPU個數*/
cat /proc/cpuinfo | grep "physical id" | sort -u | wc -l  

/*檢視邏輯CPU個數*/
cat /proc/cpuinfo | grep "processor" | wc -l  

/*檢視CPU核心數*/
cat /proc/cpuinfo | grep "cpu cores" | uniq  

/*檢視單個物理CPU封裝的邏輯CPU數量*/
cat /proc/cpuinfo | grep "siblings" | uniq  

/*計算是否開啟超執行緒
##邏輯CPU > 物理CPU x CPU核數 #開啟超執行緒
##邏輯CPU = 物理CPU x CPU核數 #沒有開啟超執行緒或不支援超執行緒*/

/*檢視是否超執行緒,如果cpu cores數量和siblings數量一致，則沒有啟用超執行緒，否則超執行緒被啟用。*/
cat /proc/cpuinfo | grep -e "cpu cores"  -e "siblings" | sort | uniq


/*記憶體
TOP
/*命令經常用來監控linux的系統狀況，比如cpu、記憶體的使用等。*/
/*檢視某個使用者記憶體使用情況,如:postgres*/
top -u postgres
/*
內容解釋：

　　PID：程式的ID
　　USER：程式所有者
　　PR：程式的優先順序別，越小越優先被執行
　　NInice：值
　　VIRT：程式佔用的虛擬記憶體
　　RES：程式佔用的實體記憶體
　　SHR：程式使用的共享記憶體
　　S：程式的狀態。S表示休眠，R表示正在執行，Z表示僵死狀態，N表示該程式優先值為負數
　　%CPU：程式佔用CPU的使用率
　　%MEM：程式使用的實體記憶體和總記憶體的百分比
　　TIME+：該程式啟動後佔用的總的CPU時間，即佔用CPU使用時間的累加值。
　　COMMAND：程式啟動命令名稱

常用的命令：

　　P：按%CPU使用率排行
　　T：按MITE+排行
　　M：按%MEM排行
*/

/*檢視程式相關資訊佔用的記憶體情況，(程式號可以通過ps檢視)如下所示：*/
pmap -d 14596

ps -e -o ‘pid,comm,args,pcpu,rsz,vsz,stime,user,uid‘ 
ps -e -o ‘pid,comm,args,pcpu,rsz,vsz,stime,user,uid‘ | grep postgres |  sort -nrk5
/*其中rsz為實際記憶體，上例實現按記憶體排序，由大到小*/

/*看記憶體佔用*/
free -m

/*看硬碟佔用率*/
df -h
/*檢視IO情況*/
iostat -x 1 10
/*
如果 iostat 沒有，要 yum install sysstat安裝這個包，第一眼看下圖紅色圈圈的那個如果%util接近100%,表明I/O請求太多,I/O系統已經滿負荷，磁碟可能存在瓶頸,一般%util大於70%,I/O壓力就比較大，讀取速度有較多的wait，然後再看其他的引數，
內容解釋:
rrqm/s:每秒進行merge的讀運算元目。即delta(rmerge)/s 
wrqm/s:每秒進行merge的寫運算元目。即delta(wmerge)/s 
r/s:每秒完成的讀I/O裝置次數。即delta(rio)/s 
w/s:每秒完成的寫I/0裝置次數。即delta(wio)/s 
rsec/s:每秒讀扇區數。即delta(rsect)/s 
wsec/s:每秒寫扇區數。即delta(wsect)/s 
rKB/s:每秒讀K位元組數。是rsec/s的一半，因為每扇區大小為512位元組 

wKB/s:每秒寫K位元組數。是wsec/s的一半 
avgrq-sz:平均每次裝置I/O操作的資料大小(扇區)。即delta(rsect+wsect)/delta(rio+wio) 
avgqu-sz:平均I/O佇列長度。即delta(aveq)/s/1000(因為aveq的單位為毫秒) 
await:平均每次裝置I/O操作的等待時間(毫秒)。即delta(ruse+wuse)/delta(rio+wio) 
svctm:平均每次裝置I/O操作的服務時間(毫秒)。即delta(use)/delta(rio+wio) 
%util:一秒中有百分之多少的時間用於I/O操作,或者說一秒中有多少時間I/O佇列是非空的

*/
/*找到對應程式*/
ll /proc/程式號/exe

瞭解到系統情況後便可做相關合理的調整，以達到效能優化的目的。

 

1.shared_buffers
PostgreSQL既使用自身的緩衝區，也使用核心緩衝IO。這意味著資料會在記憶體中儲存兩次，首先是存入PostgreSQL緩衝區，然後是核心緩衝區。這被稱為雙重緩衝區處理。對大多數作業系統來說，這個引數是最有效的用於調優的引數。此引數的作用是設定PostgreSQL中用於快取的專用記憶體量。
shared_buffers的預設值設定得非常低，因為某些機器和作業系統不支援使用更高的值。但在大多數現代裝置中，通常需要增大此引數的值才能獲得最佳效能。
建議的設定值為機器總記憶體大小的25％，但是也可以根據實際情況嘗試設定更低和更高的值。實際值取決於機器的具體配置和工作的資料量大小。舉個例子，如果工作資料集可以很容易地放入記憶體中，那麼可以增加shared_buffers的值來包含整個資料庫，以便整個工作資料集可以保留在快取中。
在生產環境中，將shared_buffers設定為較大的值通常可以提供非常好的效能，但應當時刻注意找到平衡點。
檢視當前shared_buffers的值：

postgres=# show shared_buffers;
 shared_buffers 
----------------
 128MB
(1 row)

2.wal_buffers

PostgreSQL將其WAL（預寫日誌）記錄寫入緩衝區，然後將這些緩衝區重新整理到磁碟。由wal_buffers定義的緩衝區的預設大小為16MB，但如果有大量併發連線的話，則設定為一個較高的值可以提供更好的效能。
檢視當前wal_buffers的值：

postgres=# show wal_buffers;
 wal_buffers 
-------------
 4MB
(1 row)

 

3.effective_cache_size

effective_cache_size提供可用於磁碟快取記憶體的記憶體量的估計值。它只是一個建議值，而不是確切分配的記憶體或快取大小。它不會實際分配記憶體，而是會告知優化器核心中可用的快取量。在一個索引的代價估計中，更高的數值會使得索引掃描更可能被使用，更低的數值會使得順序掃描更可能被使用。在設定這個引數時，還應該考慮PostgreSQL的共享緩衝區以及將被用於PostgreSQL資料檔案的核心磁碟緩衝區。預設值是4GB。
檢視當前effective_cache_size的值：

postgres=# show effective_cache_size;
 effective_cache_size 
----------------------
 4GB
(1 row)

4.work_mem

此配置用於複合排序。記憶體中的排序比溢位到磁碟的排序快得多，設定非常高的值可能會導致部署環境出現記憶體瓶頸，因為此引數是按使用者排序操作。如果有多個使用者嘗試執行排序操作，則系統將為所有使用者分配大小為work_mem *總排序運算元的空間。全域性設定此引數可能會導致記憶體使用率過高，因此強烈建議在會話級別修改此引數值。預設值為4MB。
檢視當前work_mem的值：

postgres=# show work_mem;
 work_mem 
----------
 4MB
(1 row)

 

5.maintenance_work_mem
maintenance_work_mem是用於維護任務的記憶體設定。預設值為64MB。設定較大的值對於VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等操作的效能提升效果顯著。
檢視當前maintenance_work_mem的值：

postgres=# show maintenance_work_mem;
 maintenance_work_mem 
----------------------
 64MB
(1 row)

 

6.synchronous_commit

此引數的作用為在向客戶端返回成功狀態之前，強制提交等待WAL被寫入磁碟。這是效能和可靠性之間的權衡。如果應用程式被設計為效能比可靠性更重要，那麼關閉synchronous_commit。這意味著成功狀態與保證寫入磁碟之間會存在時間差。在伺服器崩潰的情況下，即使客戶端在提交時收到成功訊息，資料也可能丟失。
檢視當前synchronous_commit的設定值：

postgres=# show synchronous_commit;
 synchronous_commit 
--------------------
 on
(1 row)

 

7.checkpoint_timeout和checkpoint_completion_target
PostgreSQL將更改寫入WAL。檢查點程式將資料重新整理到資料檔案中。發生CHECKPOINT時完成此操作。這是一項開銷很大的操作，整個過程涉及大量的磁碟讀/寫操作。使用者可以在需要時隨時發出CHECKPOINT指令，或者通過PostgreSQL的引數checkpoint_timeout和checkpoint_completion_target來自動完成。
checkpoint_timeout引數用於設定WAL檢查點之間的時間。將此設定得太低會減少崩潰恢復時間，因為更多資料會寫入磁碟，但由於每個檢查點都會佔用系統資源，因此也會損害效能。此引數只能在postgresql.conf檔案中或在伺服器命令列上設定。
checkpoint_completion_target指定檢查點完成的目標，作為檢查點之間總時間的一部分。預設值是 0.5。 這個引數只能在postgresql.conf檔案中或在伺服器命令列上設定。高頻率的檢查點可能會影響效能。
檢視當前checkpoint_timeout和checkpoint_completion_target的值：

postgres=# show checkpoint_timeout;
 checkpoint_timeout 
--------------------
 5min
(1 row)

postgres=# show checkpoint_completion_target;
 checkpoint_completion_target 
------------------------------
 0.5
(1 row)

 

8.max_connections

允許客戶端連線的最大數目

 

9.fsync

強制把資料同步更新到磁碟,如果系統的IO壓力很大，把改引數改為off

在fsync開啟的情況下，優化後效能能夠提升30%左右。因為有部分優化選項在預設的SQL測試語句中沒有體現出它的優勢，如果到實際測試中，提升應該不止30%。
測試的過程中，主要的瓶頸就在系統的IO，如果需要減少IO的負荷，最直接的方法就是把fsync關閉，但是這樣就會在掉電的情況下，可能會丟失部分資料。
 

10.commit_delay

事務提交後，日誌寫到wal log上到wal_buffer寫入到磁碟的時間間隔。需要配合commit_sibling。能夠一次寫入多個事務，減少IO，提高效能

 

11.commit_siblings

設定觸發commit_delay的併發事務數，根據併發事務多少來配置。減少IO，提高效能

注意：
並非所有引數都適用於所有應用程式型別。某些應用程式通過調整引數可以提高效能，有些則不會。必須針對應用程式及作業系統的特定需求來調整資料庫引數。

 

PostgreSQL配置引數修改

1.修改配置檔案

在配置檔案C:\PostgreSQL\data\pg96\postgresql.conf 中直接修改，修改前記得備份一下原檔案，因為你不知道意外和明天不知道哪個會先來。修改完成之後，記得重啟資料庫哦。

2.命令列的修改方式

 ALTER SYSTEM SET configuration_parameter { TO | = } { value | ‘value‘ | DEFAULT }

 

例如：我們現在要修改 maintenance_work_mem

--檢視所有資料庫引數的值
show all;


show maintenance_work_mem;
--注意這裡的設定不會改變postgresql.conf,只會改變postgresql.conf
ALTER SYSTEM SET maintenance_work_mem= 1048576;

--重啟資料庫
show maintenance_work_mem; 

--取消postgresql.auto.conf的引數設定
ALTER SYSTEM SET maintenance_work_mem= default;