Postgresql-11.X 性能優(yōu)化詳解

postgres 性能優(yōu)化

系統(tǒng)優(yōu)化

修改 /etc/grub.conf

關(guān)閉 numa=off ，修改磁盤IO調(diào)度方式 elevator=deadline

修改方法：

grubby --update-kernel=ALL --args="transparent_hugepage=never" --args="elevator=deadline"
驗(yàn)證：
grubby --info=ALL
返回
args="ro crashkernel=auto rhgb quiet numa=off elevator=deadline"

關(guān)閉內(nèi)存大頁(yè) hugepage

驗(yàn)證是否開(kāi)啟方法1： 
如果以下文件不存在，則是THP已經(jīng)從內(nèi)核中移除。
/sys/kernel/mm/transparent_hugepage or /sys/kernel/mm/redhat_transparent_hugepage
驗(yàn)證是否開(kāi)啟方法2： 
cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

[always] == 》表示開(kāi)啟

禁用內(nèi)存大頁(yè)

方法：

修改 /etc/rc.local

# vi /etc/rc.local #追加，禁用大頁(yè)

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
  echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
  echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

內(nèi)核優(yōu)化

vi /etc/sysctl.conf  

添加內(nèi)容如下：

vm.swappiness = 0
kernel.shmmax=135497418752
net.core.rmem_max = 4194304
net.core.wmem_max = 4194304
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.ipv4.ip_local_port_range = 9000 65535
kernel.sem = 50100 64128000 50100 1280
vm.dirty_background_bytes = 102400000
vm.dirty_ratio = 80
vm.nr_hugepages = 102352

/etc/security/limits.conf
* soft nofile 655360
* hard nofile 655360
* soft nproc 655360
* hard nproc 655360
* soft stack unlimited
* hard stack unlimited
* soft   memlock    250000000
* hard   memlock    250000000

塊設(shè)備預(yù)讀

blockdev --setra 16384 /dev/sda

設(shè)置開(kāi)機(jī)啟動(dòng)生效：

echo "blockdev --setra 16384 /dev/sda" >> /etc/rc.local 

postgres 數(shù)據(jù)層面優(yōu)化

涉及命令令

ALTER SYSTEM SET

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

Case：

alter system set max_connections='50';

SHOW configuration_parameter

Case：

show max_connections

實(shí)現(xiàn)原理：

調(diào)用該命令的用戶必須具有超級(jí)用戶權(quán)限。修改系統(tǒng)配置文件postgresql.conf，通過(guò)系統(tǒng)信號(hào)，重新加載該配置文件.

查詢postgresql配置選項(xiàng)生效級(jí)別和默認(rèn)值信息；

select name, context,setting,unit,source,sourcefile from pg_settings;

internal: 編譯期間的設(shè)置，只有重新編譯才能生效。
postmaster: 只有服務(wù)重啟才能生效。
sighup: 給服務(wù)器發(fā)送HUP信號(hào)會(huì)是服務(wù)器重新加載postgresql.conf配置，可以立即生效。
backend: 與sighup類似，但是不影響正在運(yùn)行的會(huì)話，只在新會(huì)話中生效
superuser: 使用superuser(如postgres)才能更改，不用重新加載所有配置即可生效。
user: 單個(gè)會(huì)話用戶可以在任意時(shí)間做修改，只會(huì)影響該會(huì)話。

優(yōu)化數(shù)據(jù)庫(kù)主機(jī)配置

系統(tǒng)

more /etc/redhat-release 
CentOS Linux release 7.5.1804 (Core) 

內(nèi)核

uname  -a
Linux jp33e506-3-22.ptengine.com 3.10.0-862.2.3.el7.x86_64 #1 SMP Wed May 9 18:05:47 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

內(nèi)存

Mem:         128763   

核心優(yōu)化選項(xiàng)：

postmaster: 只有服務(wù)重啟才能生效。

alter system set max_connections='50';
alter system set shared_buffers='30GB';
alter system set wal_buffers='64MB';

sighup: 給服務(wù)器發(fā)送HUP信號(hào)會(huì)是服務(wù)器重新加載postgresql.conf配置，可以立即生效。

alter system set checkpoint_completion_target='0.9';
alter system set checkpoint_timeout='15min';
alter system set fsync='off';

superuser: 使用superuser(如postgres)才能更改，不用重新加載所有配置即可生效。

alter system set commit_delay='10';

user: 單個(gè)會(huì)話用戶可以在任意時(shí)間做修改，只會(huì)影響該會(huì)話。

alter system set autovacuum_work_mem='1GB';
alter system set commit_siblings='6';
alter system set effective_cache_size='50GB';
alter system set maintenance_work_mem='1GB';
alter system set work_mem='16MB';

查看 優(yōu)化結(jié)果

show max_connections;
show shared_buffers;
show wal_buffers;
show checkpoint_completion_target;
show checkpoint_timeout;
show fsync;
show commit_delay;
show autovacuum_work_mem;
show commit_siblings;
show effective_cache_size;
show maintenance_work_mem;
show work_mem;

重新數(shù)據(jù)庫(kù)配置的方法

部分優(yōu)化項(xiàng)重啟后才能生效：

1.用超級(jí)用戶運(yùn)行
postgres=# 

SELECT pg_reload_conf();

2.用UNIX的kill手動(dòng)發(fā)起HUP信號(hào)

$kill -HUP PID

3.使用pg_ctl命令觸發(fā)SIGHUP信號(hào)

$pg_ctl reload

或者

systemctl reload  postgresql-11.service

systemctl restart  postgresql-11.service

systemctl status   postgresql-11.service

優(yōu)化后性能對(duì)比

綠線為分界線

CPU

參考文檔：

https://www.kancloud.cn/taobaomysql/monthly/140098