【PG效能】Postgresql效能相關（作業系統及資料庫簡單說明）

--pg 效能調整相關
--記憶體
buffer cache 直接對磁碟進行操作的資料會快取到buffer cache
page cache   檔案系統中的資料則交給page cache進行快取
--cache不自動回收，資料庫需要記憶體時，cache可以很快被回收，如果沒用到交換分割槽，可說明記憶體夠用
-釋放快取
sync
echo 1 > /proc/sys/vm/drop_caches
--vmstat  虛擬記憶體、程式、cpu等整體情況
-r 當前佇列中有幾個程式在等待
-b  當前有多少個程式進入不可中斷式睡眠狀態
-swpd  已使用的交換分割槽的大小
-free  空閒記憶體大小
-buff 已使用的buffer大小
--cache 已使用的page cache大小
-si/so 從磁碟交換到swap分割槽和從swap分割槽交換的磁碟大小
-bi/bo 從磁碟讀取和寫入到磁碟的大小，單位blocks/s
-in 每秒被中斷的程式數
-cs 每秒多少個cpu程式在進進出出
--iostat  監控磁碟輸入輸出
--mpstat cpu詳細效能資訊
--sar  預設儲存28天，目錄 /var/log/sa   修改 /etc/sysconfig/sysstat
--檢視某一時間段
sar -q -f /var/log/sa/sa15 -s 22:00:00 -e 23:00:00
sar -q  #彙總cpu狀況
sar -b  #彙總io狀況
--linux I/O 排程
##檢視當前支援的排程演算法
dmesg | grep -i scheduler
--cfg 絕對公平排程演算法 ，預設
--noop 電梯排程演算法  適合ssd
--deadline  絕對保障演算法 
--檢視當前磁碟sda的io排程演算法
cat /sys/block/sda/queue/scheduler
--臨時修改
echo noop > /sys/block/sda/queue/scheduler
##磁碟預讀扇區
/sbin/blockdev --getra /dev/sda  #預設256，可設定16384或更大
/sbin/blockdev --setra 16384 /dev/sda
#或
echo 16384 /sys/block/sda/queue/read_ahead_kb
##禁用swap
swapoff -a 
##啟用
swapon -a
--透明大頁，要關閉
cat /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/enabled
##numa guanbi 
numactl -hardware
--or
numastat
#可編輯 /etc/grub.conf 行末加numa=off禁用
##資料庫方面
--統計資訊
pg_stat_database
--快取命中率，如果低於1，可嘗試調整shared_buffers
select blks_hit::float/(blks_read + blks_hit) as cache_hit_ratio from pg_stat_database where datname=current_database();
--事務提交率,低於1，檢查是否死鎖或其他超時太多
select xact_commit::float/(xact_commit +xact_rollback) as successful_xact_ratio from pg_stat_database where datname=current_database();
--最佳化後建議執行以下語句，方面對比最佳化前後資料
pg_stat_reset()
--表級統計資訊
pg_stat_user_tables
--索引使用率
select sum(idx_scan)/(sum(idx_scan) + sum(seq_scan)) as idx_scan_ratio from pg_stat_all_tables where schemaname='your_schema';
select relname,idx_scan::float/(idx_scan+seq_scan+1) as idx_scan_ratio from stat_all_tables where schemaname='your schema' order by idx_scan_ratio asc;
--語句級統計資訊 透過pg_stat_statements ,postgres 日誌、auto_explain 來獲取
pg_stat_statements
--開啟
shared_preload_libraries='pg_stat_statements'
pg_stat_statements.track=all 
create extension pg_stat_statements;
--查詢平均執行時間最長的3條查詢
select calls,total_time/calls as avg_time,left(query,80) from pg_stat_statements order by 2 desc limit 3;
--檢視執行計劃 analyze可以得到真正執行計劃
explain analyze select * from tb1;
--除了analyze選項，可以使用其他
explain (analyze on ,timing on,verbose on,buffers on) select * from tb1;
--session 級別 log_xxx_stat判斷問題,使用的系統資源等，
set client_min_messages=log;
set log_parser_stats=on;
set log_planner_stats=on;
--or
set client_min_messages=log;
set log_parser_stats=off;
set log_planner_stats=off;
set log_statement_stats=on;
--重建索引
create unique index concurrently on mytb1 using btree(id);
--id 欄位有兩索引
select schemaname,relname,indexrelanme,pg_relation_size(indexrelid) as index_size,idx_scan,idx_tup_read,idx_tup_fetch from pg_stat_user_indexes where indexrelname in (select indexname from pg_indexes where schemaname='public' and tablename='mytb1');
--開啟事務刪除主機索引，同時將第二索引更新為主鍵約束
begin;
alter table mytb1 drop constraint mytb1_pkey;
alter table mytb1 add constraint mytb1_id_idx primary key using index mytb1_id_idx;
end;
--對於大規模，可透過pg_repack工具進行定時索引重建