PostgreSQL数据库优化
本文包含PostgreSQL数据库的操作系统配置、环境变量配置、数据库相关参数配置。
本文包含PostgreSQL数据库的操作系统配置、环境变量配置、数据库相关参数配置。
01 操作系统配置
操作系统的优化主要针对防火墙、内核参数、系统限制上做优化。
## 内核参数优化 vi /etc/sysctl.conf kernel.shmmax = 976257024 #1/2 of physical RAM kernel.shmall = 2097152 #physical RAM size / pagesize For most systems, this will be the value 2097152 kernel.shmmni = 819200 vm.nr_hugepages = 65536 #hugepage, when memory is bigger than 64G kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 4194304 fs.file-max = 6815744 ## 应用生效 sysctp -p
注意：上述内核参数配置规则为通用模板，优化过程中的参数的设定要以业务实际情况为准。
## 系统限制优化 vi /etc/security/limits.conf * soft nofile 1024000 #当前系统生效的打开文件的数目 * hard nofile 1024000 #系统中所能设定的打开文件的最大值 * soft nproc unlimited #当前系统生效的进程的数目 * hard nproc unlimited #系统中所能设定的进程数目的最大值 * soft core unlimited #当前系统生效的内核文件的大小 * hard core unlimited #系统中所能设定的内核文件的最大值 * soft memlock unlimited #当前系统生效的锁定内存地址空间的大小 * hard memlock unlimited #系统中所能设定的的锁定内存地址空间最大值
02 环境变量配置
建议在PostgreSQL数据库部署过程中，配置对应安装用户的环境变量。
su - postgres vi ~/.bashrc ## 添加以下： export PGUSER=postgres export PGPORT=5432 export PATH=$PATH:$HOME/bin:/pg/pg13/bin/ export PGDATA=/pg/pg13/data $ source ~/.bashrc
03 数据库配置
数据库配置主要涉及连接配置和数据库参数配置。
- 1. 连接配置（pg_hba.conf）
该文件用于控制访问安全性，管理客户端对于PostgreSQL服务器的访问权限。简单理解就是：允许哪个IP（或哪个网段）的哪个用户通过连接到哪个数据库，以及指定连接时使用的身份验证模式。
文件路径：$PGDATA/pg_hba.conf
注意：scram-sha-256是PostgreSQL10中新增的基于SASL的认证方式，也是最安全的认证方式。但这种方式不支持旧版本的客户端库。如果10以前的客户端库连接数据库，会有如下错误：
/usr/pgsql - 9.6/bin/psql - h pghostl -p 1921 - U postgres mydb psql : SCRAM authentication requires libpq version 10 or above
- 2. 数据库参数配置
文件路径：$PGDATA/postgresql.conf
数据库参数优化分为如下几块内容：
① 监听参数
## 数据库监听地址 alter system set listen_addresse = '*';
② 内存参数
## shared_buffers 默认128M，建议为主机内存的1/4。 alter system SET shared_buffers = '4096M'; ## maintenance_work_mem 默认64M，建议设置为min(8G,(主机内存/8)/max_parallel_maintenance_workers) alter system set maintenance_work_mem = '2GB'; ## wal_buffers，默认值为4M，建议值 min(wal_segment_size，shared_buffers/32) alter system set wal_buffers = '16M'; ## effective_cache_size，默认值4GB，建议设置为0.75*主机内存 alter system set effective_cache_size = '4GB';
③ 日志参数
## 数据库运行日志，默认不开启 alter system set logging_collector = 'on' ## 日志格式，默认stderr，建议csvlog alter system set log_destination = 'csvlog'; ## 慢SQL记录，单位ms alter system set log_min_duration_statement = 120000; ## 审计记录，可选参数：none\ddl\mod\all alter system set log_statement = 'ddl'; ## 死锁等待时间，默认1000，单位m alter system set log_lock_waits = on; alter system set deadlock_timeout = 1000； ## 临时文件超过某个值时会记录到日志，单位KB alter system set log_temp_files = 250000; ## 记录更详细的信息 alter system set log_error_verbosity = verbose; ## 记录检查点信息 alter system set log_checkpoints = on;
④ 强制检查点
## 单位s，不建议频繁检查点，否则xlog会产生很多的full page write alter system set checkpoint_timeout = 1800;
⑤ wal保留数量
## pg_wal日志文件段的最小大小 alter system set wal_keep_size = '1GB';
⑥ 连接数设置
## 设置最大连接数 alter system set max_connections = 1000; ## 设置super最大连接数 alter system set superuser_reserved_connections = 10;
⑦ 分组提交
## 默认分组提交是即时提交，高并发 alter system set commit_siblings = 5; alter system set commit_delay = 10;
⑧ autovacuum
## 自动清理进程，建议配置为max(min(8,cup核数/2),5) alter system set autovacuum_max_workers = 8;
⑨ 归档配置
## 启用归档配置 alter system set archive_mode = on; alter system set archive_command = 'cp %p /pg/arch/archive_wal_%f' #此处%p表示归档文件全路径，%f表示归档文件名字。
- 发表于 2025-06-17 12:59
- 阅读 ( 30 )
