postgres 性能优化
系统优化
修改 /etc/grub.conf
关闭 numa=off ，修改磁盘IO调度方式 elevator=deadline
修改方法：
grubby --update-kernel=ALL --args="transparent_hugepage=never" --args="elevator=deadline"
验证：
grubby --info=ALL
返回
args="ro crashkernel=auto rhgb quiet numa=off elevator=deadline"
关闭内存大页 hugepage
验证是否开启方法1：
如果以下文件不存在，则是THP已经从内核中移除。
/sys/kernel/mm/transparent_hugepage or /sys/kernel/mm/redhat_transparent_hugepage
验证是否开启方法2：
cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
[always] == 》表示开启
禁用内存大页
方法：
修改 /etc/rc.local
# vi /etc/rc.local #追加，禁用大页
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
内核优化
vi /etc/sysctl.conf
添加内容如下：
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
块设备预读
blockdev --setra 16384 /dev/sda
设置开机启动生效：
echo "blockdev --setra 16384 /dev/sda" >> /etc/rc.local
postgres 数据层面优化
涉及命令令
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
实现原理：
调用该命令的用户必须具有超级用户权限。修改系统配置文件postgresql.conf，通过系统信号，重新加载该配置文件.
查询postgresql配置选项生效级别和默认值信息；
select name, context,setting,unit,source,sourcefile from pg_settings;
internal: 编译期间的设置，只有重新编译才能生效。
postmaster: 只有服务重启才能生效。
sighup: 给服务器发送HUP信号会是服务器重新加载postgresql.conf配置，可以立即生效。
backend: 与sighup类似，但是不影响正在运行的会话，只在新会话中生效
superuser: 使用superuser(如postgres)才能更改，不用重新加载所有配置即可生效。
user: 单个会话用户可以在任意时间做修改，只会影响该会话。
优化数据库主机配置
系统
more /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
内核
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
内存
Mem: 128763
核心优化选项：
postmaster: 只有服务重启才能生效。
alter system set max_connections='50';
alter system set shared_buffers='30GB';
alter system set wal_buffers='64MB';
sighup: 给服务器发送HUP信号会是服务器重新加载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: 单个会话用户可以在任意时间做修改，只会影响该会话。
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';
查看 优化结果
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;
重新数据库配置的方法
部分优化项重启后才能生效：
1.用超级用户运行
postgres=#
SELECT pg_reload_conf();
2.用UNIX的kill手动发起HUP信号
$kill -HUP PID
3.使用pg_ctl命令触发SIGHUP信号
$pg_ctl reload
或者
systemctl reload postgresql-11.service
systemctl restart postgresql-11.service
systemctl status postgresql-11.service
优化后性能对比
绿线为分界线
CPU
参考文档：
https://www.kancloud.cn/taobaomysql/monthly/140098