昨天分别在外網和無外網環境下安裝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的值：
| 1 2 3 4 5 6 7 8 9 10 11 | |
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，提高性能
注意：
并非所有參數都适用于所有應用程式類型。某些應用程式通過調整參數可以提高性能，有些則不會。必須針對應用程式及作業系統的特定需求來調整資料庫參數。
下面介紹幾個我認為重要的：
1、增加maintenance_work_mem參數大小
增加這個參數可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的執行效率。
2、增加checkpoint_segments參數的大小
增加這個參數可以提升大量資料導入時候的速度。
3、設定archive_mode無效
這個參數設定為無效的時候，能夠提升以下的操作的速度
・CREATE TABLE AS SELECT
・CREATE INDEX
・ALTER TABLE SET TABLESPACE
・CLUSTER等。
4、autovacuum相關參數 (autovacuum介紹文章)
autovacuum：預設為on，表示是否開起autovacuum。預設開起。特别的，當需要當機xid時，盡管此值為off，PG也會進行vacuum。
autovacuum_naptime：下一次vacuum的時間，預設1min。 這個naptime會被vacuum launcher配置設定到每個DB上。autovacuum_naptime/num of db。
log_autovacuum_min_duration：記錄autovacuum動作到日志檔案，當vacuum動作超過此值時。 “-1”表示不記錄。“0”表示每次都記錄。
autovacuum_max_workers：最大同時運作的worker數量，不包含launcher本身。
autovacuum_work_mem ：每個worker可使用的最大記憶體數。
autovacuum_vacuum_threshold ：預設50。與autovacuum_vacuum_scale_factor配合使用， autovacuum_vacuum_scale_factor預設值為20%。當update,delete的tuples數量超過autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold時，進行vacuum。如果要使vacuum工作勤奮點，則将此值改小。
autovacuum_analyze_threshold ：預設50。與autovacuum_analyze_scale_factor配合使用。
autovacuum_analyze_scale_factor ：預設10%。當update,insert,delete的tuples數量超過autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold時，進行analyze。
autovacuum_freeze_max_age：200 million。離下一次進行xid當機的最大事務數。
autovacuum_multixact_freeze_max_age：400 million。離下一次進行xid當機的最大事務數。
autovacuum_vacuum_cost_delay ：如果為-1，取vacuum_cost_delay值。
autovacuum_vacuum_cost_limit ：如果為-1，到vacuum_cost_limit的值，這個值是所有worker的累加值。
PostgreSQL配置參數修改
1.修改配置檔案
在配置檔案C:\PostgreSQL\data\pg96\postgresql.conf 中直接修改，修改前記得備份一下原檔案，因為你不知道意外和明天不知道哪個會先來。修改完成之後，記得重新開機資料庫哦。
2.指令行的修改方式
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT } --檢視所有資料庫參數的值
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; 資料庫參數優化總結
max_connections = 300 # (change requires restart)
unix_socket_directories = '.' # comma-separated list of directories
shared_buffers = 194GB # 盡量用資料庫管理記憶體，減少雙重緩存，提高使用效率
huge_pages = on # on, off, or try ，使用大頁
work_mem = 256MB # min 64kB ， 減少外部檔案排序的可能，提高效率
maintenance_work_mem = 2GB # min 1MB ， 加速建立索引
autovacuum_work_mem = 2GB # min 1MB, or -1 to use maintenance_work_mem ， 加速垃圾回收
dynamic_shared_memory_type = mmap # the default is the first option
vacuum_cost_delay = 0 # 0-100 milliseconds ， 垃圾回收不妥協，極限壓力下，減少膨脹可能性
bgwriter_delay = 10ms # 10-10000ms between rounds ， 刷shared buffer髒頁的程序排程間隔，盡量高頻排程，減少使用者程序申請不到記憶體而需要主動刷髒頁的可能（導緻RT升高）。
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round , 一次最多刷多少髒頁
bgwriter_lru_multiplier = 10.0 # 0-10.0 multipler on buffers scanned/round 一次掃描多少個塊，上次刷出髒頁數量的倍數
effective_io_concurrency = 2 # 1-1000; 0 disables prefetching ， 執行節點為bitmap heap scan時，預讀的塊數。進而
wal_level = minimal # minimal, archive, hot_standby, or logical ， 如果現實環境，建議開啟歸檔。
synchronous_commit = off # synchronization level; ， 異步送出
wal_sync_method = open_sync # the default is the first option ， 因為沒有standby，是以寫xlog選擇一個支援O_DIRECT的fsync方法。
full_page_writes = off # recover from partial page writes ， 生産中，如果有增量備份和歸檔，可以關閉，提高性能。
wal_buffers = 1GB # min 32kB, -1 sets based on shared_buffers ，wal buffer大小，如果大量寫wal buffer等待，則可以加大。
wal_writer_delay = 10ms # 1-10000 milliseconds wal buffer排程間隔，和bg writer delay類似。
commit_delay = 20 # range 0-100000, in microseconds ，分組送出的等待時間
commit_siblings = 9 # range 1-1000 , 有多少個事務同時進入送出階段時，就觸發分組送出。
checkpoint_timeout = 55min # range 30s-1h 時間控制的檢查點間隔。
max_wal_size = 320GB # 2個檢查點之間最多允許産生多少個XLOG檔案
checkpoint_completion_target = 0.99 # checkpoint target duration, 0.0 - 1.0 ，平滑排程間隔，假設上一個檢查點到現在這個檢查點之間産生了100個XLOG，則這次檢查點需要在産生100*checkpoint_completion_target個XLOG檔案的過程中完成。PG會根據這些值來排程平滑檢查點。
random_page_cost = 1.0 # same scale as above , 離散掃描的成本因子，本例使用的SSD IO能力足夠好
effective_cache_size = 240GB # 可用的OS CACHE
log_destination = 'csvlog' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on # If on, an existing log file with the
update_process_title = off
track_activities = off
autovacuum = on # Enable autovacuum subprocess? 'on'
autovacuum_max_workers = 4 # max number of autovacuum subprocesses ，允許同時有多少個垃圾回收工作程序。
autovacuum_naptime = 6s # time between autovacuum runs ， 自動垃圾回收探測程序的喚醒間隔
autovacuum_vacuum_cost_delay = 0 # default vacuum cost delay for ， 垃圾回收不妥協 ![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)