postgresql 开启大页_Postgresql-11.X 性能优化详解

系统优化

修改 /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

优化后性能对比

绿线为分界线

CPU

参考文档：

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