DBA不可不知的操作系统内核参数
修改/etc/sysctl.conf
# vi /etc/sysctl.conf # add by digoal.zhou fs.aio-max-nr = 1048576 fs.file-max = 76724600 kernel.core_pattern= /data01/corefiles/core_%e_%u_%t_%s.%p # /data01/corefiles事先建好,权限777,如果是软链接,对应的目录修改为777 kernel.sem = 4096 2147483647 2147483646 512000 # 信号量, ipcs -l 或 -u 查看,每16个进程一组,每组信号量需要17个信号量。 kernel.shmall = 107374182 # 所有共享内存段相加大小限制(建议内存的80%) kernel.shmmax = 274877906944 # 最大单个共享内存段大小(建议为内存一半), >9.2的版本已大幅降低共享内存的使用 kernel.shmmni = 819200 # 一共能生成多少共享内存段,每个PG数据库集群至少2个共享内存段 net.core.netdev_max_backlog = 10000 net.core.rmem_default = 262144 # The default setting of the socket receive buffer in bytes. net.core.rmem_max = 4194304 # The maximum receive socket buffer size in bytes net.core.wmem_default = 262144 # The default setting (in bytes) of the socket send buffer. net.core.wmem_max = 4194304 # The maximum send socket buffer size in bytes. net.core.somaxconn = 4096 net.ipv4.tcp_max_syn_backlog = 4096 net.ipv4.tcp_keepalive_intvl = 20 net.ipv4.tcp_keepalive_probes = 3 net.ipv4.tcp_keepalive_time = 60 net.ipv4.tcp_mem = 8388608 12582912 16777216 net.ipv4.tcp_fin_timeout = 5 net.ipv4.tcp_synack_retries = 2 net.ipv4.tcp_syncookies = 1 # 开启SYN Cookies。当出现SYN等待队列溢出时,启用cookie来处理,可防范少量的SYN攻击 net.ipv4.tcp_timestamps = 1 # 减少time_wait net.ipv4.tcp_tw_recycle = 0 # 如果=1则开启TCP连接中TIME-WAIT套接字的快速回收,但是NAT环境可能导致连接失败,建议服务端关闭它 net.ipv4.tcp_tw_reuse = 1 # 开启重用。允许将TIME-WAIT套接字重新用于新的TCP连接 net.ipv4.tcp_max_tw_buckets = 262144 net.ipv4.tcp_rmem = 8192 87380 16777216 net.ipv4.tcp_wmem = 8192 65536 16777216 net.nf_conntrack_max = 1200000 net.netfilter.nf_conntrack_max = 1200000 vm.dirty_background_bytes = 409600000 # 系统脏页到达这个值,系统后台刷脏页调度进程 pdflush(或其他) 自动将(dirty_expire_centisecs/100)秒前的脏页刷到磁盘 vm.dirty_expire_centisecs = 3000 # 比这个值老的脏页,将被刷到磁盘。3000表示30秒。 vm.dirty_ratio = 95 # 如果系统进程刷脏页太慢,使得系统脏页超过内存 95 % 时,则用户进程如果有写磁盘的操作(如fsync, fdatasync等调用),则需要主动把系统脏页刷出。 # 有效防止用户进程刷脏页,在单机多实例,并且使用CGROUP限制单实例IOPS的情况下非常有效。 vm.dirty_writeback_centisecs = 100 # pdflush(或其他)后台刷脏页进程的唤醒间隔, 100表示1秒。 vm.mmap_min_addr = 65536 vm.overcommit_memory = 0 # 在分配内存时,允许少量over malloc, 如果设置为 1, 则认为总是有足够的内存,内存较少的测试环境可以使用 1 . vm.overcommit_ratio = 90 # 当overcommit_memory = 2 时,用于参与计算允许指派的内存大小。 vm.swappiness = 0 # 关闭交换分区 vm.zone_reclaim_mode = 0 # 禁用 numa, 或者在vmlinux中禁止. net.ipv4.ip_local_port_range = 40000 65535 # 本地自动分配的TCP, UDP端口号范围 fs.nr_open=20480000 # 单个进程允许打开的文件句柄上限 # 以下参数请注意 # vm.extra_free_kbytes = 4096000 # vm.min_free_kbytes = 2097152 # 如果是小内存机器,以上两个值不建议设置 # vm.nr_hugepages = 66536 # 建议shared buffer设置超过64GB时 使用大页,页大小 /proc/meminfo Hugepagesize # vm.lowmem_reserve_ratio = 1 1 1 # 对于内存大于64G时,建议设置,否则建议默认值 256 256 32
修改/etc/security/limits.conf
# nofile超过1048576的话,一定要先将sysctl的fs.nr_open设置为更大的值,并生效后才能继续设置nofile. * soft nofile 1024000 * hard nofile 1024000 * soft nproc unlimited * hard nproc unlimited * soft core unlimited * hard core unlimited * soft memlock unlimited * hard memlock unlimited
以上是postgresql数据库的Linux系统参数调优,其解释已经非常明了,如果不清楚可以网上搜索“PostgreSQL on Linux 最佳部署手册”,在这感谢该作者的贡献!!
postgresql 10的安装
yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-1.noarch.rpm yum install postgresql10 postgresql10-server postgresql10-devel -y
初始化数据库
在这里我没有用postgres用户,而是使用自己定义的用户dbuser进行数据库管理,本人觉得关于postgresql数据库的管理用户随便定义就好了,不用非要用特定的用户。另外,dbuser在不同服务器需要设置ssh免密登录,这涉及到数据库间的复制的用户权限。
#初始化数据库 $/usr/pgsql-10/bin/initdb -D /opt/db/pgdb10/ #数据库启动与停止 $/usr/pgsql-10/bin/pg_ctl -D /opt/db/pgdb10/ -l $/sas/pgdb10/logfile start $/usr/pgsql-10/bin/pg_ctl -D /opt/db/pgdb10/ -m fast stop #创建数据库 $createdb -E utf-8 -U dbuser mydb $psql -d mydb #修改dbuser的密码,后面使用md5的验证方式,不修改密码会验证不通过 mydb=#alter user dbuser with password 'db123'; #创建复制流用户 mydb=#create role repl login replication encrypted password 'db123';
修改配置文件
postgresql.conf
listen_addresses = '*' #指定监听的地址 defaults to 'localhost'; use '*' for all port = 5432 #指定数据库的端口 unix_socket_directories = '/opt/db/pgdb10/' # password_encryption = md5 #指定用户密码验证方式
pg_hba.conf
#指定数据库被访问IP地址范围,以及用户密码的验证方式 host all all 172.16.36.0/24 md5 #指定复制数据库的IP地址范围和用户,以及加密方式 host replication repl 172.16.36.0/24 md5
pgpool安裝
yum install -y http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-release-3.7-1.noarch.rpm yum install pgpool-II-pg10-debuginfo pgpool-II-pg10-devel pgpool-II-pg10-extensions pgpool-II-pg10
注意:pgpool的配置以及相关执行命令均使用root用户执行。
postgresql数据库准备
首先,将主数据库设置为流复制模式。在主备机上创建数据归档目录:
#创建归档目录,owner是dbuser别弄错了 mkdir /opt/db/archivedir
修改==主机==的postgresql.conf
listen_addresses = '*' wal_level = replica #pg10以上版本值为replica,pg10以下版本为hot_standby max_wal_senders = 2 #打开归档模式 archive_mode = on #归档数据文件到指定目录 archive_command = 'cp "%p" "/opt/db/archivedir/%f"'
准备数据库节点
进入/etc/pgpool-II/目录,首先备份pgpool.conf文件,由于我们是用流复制做数据同步,所以复制一份pgpool.conf.sample-stream文件替换pgpool.conf,操作如下:
#cd /etc/pgpool-II/ #cp pgpool.conf pgpool.conf.bak #cp pgpool.conf.sample-stream pgpool.conf
在pgpool.conf上的配置如下:
listen_addresses = '*' #修改pgpool监听IP地址 ... backend_hostname0 = '172.16.36.137' # Host name or IP address to connect to for backend 0 backend_port0 = 5432 # Port number for backend 0 backend_weight0 = 1 # Weight for backend 0 (only in load balancing mode) backend_data_directory0 = '/opt/db/pgdb10' # Data directory for backend 0 backend_flag0 = 'ALLOW_TO_FAILOVER' # Controls various backend behavior # ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER # or ALWAYS_MASTER backend_hostname1 = '172.16.36.138' backend_port1 = 5432 backend_weight1 = 1 backend_data_directory1 = '/opt/db/pgdb10' backend_flag1 = 'ALLOW_TO_FAILOVER' ... #指定日志输出路径 logdir = '/var/log/pgpool' ... sr_check_user = 'repl' sr_check_password = 'db123'
分别为 backend_hostname,backend_port,backend_weight 设置节点的主机名,端口号和负载均衡系数。在每个参数串的后面,必须通过添加从0开始(例如 0,1,2,…)的整数来指出节点编号。
backend_weight 参数都为 1 ,这意味着 SELECT 查询被平均分配到db服务器上。
Failover configuration
failover_command = '/etc/pgpool-II/failover.sh %d %P %H %R'
创建/etc/pgpool-II/failover.sh
# vi /etc/pgpool-II/failover.sh # chmod 755 /etc/pgpool-II/failover.sh
failover.sh文件内容
#! /bin/sh -x # Execute command by failover. # special values: %d = node id # %h = host name # %p = port number # %D = database cluster path # %m = new master node id # %M = old master node id # %H = new master node host name # %P = old primary node id # %R = new master database cluster path # %r = new master port number # %% = '%' character falling_node=$1 # %d old_primary=$2 # %P new_primary=$3 # %H pgdata=$4 # %R pghome=/usr/pgsql-10 log=/var/log/pgpool/failover.log date >> $log echo "failed_node_id=$falling_node new_primary=$new_primary" >> $log if [ $falling_node = $old_primary ]; then if [ $UID -eq 0 ] then su dbuser -c "ssh -T dbuser@$new_primary $pghome/bin/pg_ctl promote -D $pgdata" else ssh -T dbuser@$new_primary $pghome/bin/pg_ctl promote -D $pgdata fi exit 0; fi; exit 0;
pgpool-II在线恢复设置
pgpool.conf文件设置
recovery_user = 'dbuser' # Online recovery user recovery_password = 'db123' # Online recovery password recovery_1st_stage_command = 'recovery_1st_stage'
创建相关脚本文件
$ > /opt/db/pgdb10/recovery_1st_stage $ > /opt/db/pgdb10/pgpool_remote_start $ chmod 755 /opt/db/pgdb10/recovery_1st_stage $ chmod 755 /opt/db/pgdb10/pgpool_remote_start
注意:以上两个文件的owner是dbuser,否则在数据库复制过程中会出错。
recovery_1st_stage文件内容
#!/bin/bash -x # Recovery script for streaming replication. pgdata=$1 remote_host=$2 remote_pgdata=$3 port=$4 pghome=/usr/pgsql-10 archivedir=/opt/db/archivedir hostname=$(hostname) ssh -T dbuser@$remote_host " rm -rf $remote_pgdata $pghome/bin/pg_basebackup -h $hostname -U repl -D $remote_pgdata -c fast rm -rf $archivedir/* cd $remote_pgdata cp postgresql.conf postgresql.conf.bak sed -e 's/#*hot_standby = off/hot_standby = on/' postgresql.conf.bak > postgresql.conf rm -f postgresql.conf.bak cat > recovery.conf << EOT standby_mode = 'on' primary_conninfo = 'host="$hostname" port=$port user=repl' restore_command = 'scp $hostname:$archivedir/%f %p' EOT "
pgpool_remote_start文件内容
#! /bin/sh -x pghome=/usr/pgsql-10 remote_host=$1 remote_pgdata=$2 # Start recovery target PostgreSQL server ssh -T $remote_host $pghome/bin/pg_ctl -w -D $remote_pgdata start > /dev/null 2>&1 < /dev/null &
准备相关脚本后,我们需要在主数据库上的template1安装pgpool_recovery,操作如下:
# su - dbuser $ psql template1 -h 172.16.36.137 =# CREATE EXTENSION pgpool_recovery;
客户端权限设置
在pgpool.conf文件中修改过enable_pool_hba,默认为off。
enable_pool_hba = on
修改pool_hba.conf文件
#其余相关设置请注释,如有其他设置请自行添加 host all all 172.16.36.0/24 md5 host replication repl 172.16.36.0/24 md5
执行pg_md5 --md5auth --username=<user name> <password> 去注册用户:
$pg_md5 --md5auth --username=dbuser db123 $pg_md5 --md5auth --username=repl db123
完成后,在/etc/pgpool-II目录下会产生pool_passwd的文件。
pcp相关设置
我们使用pg_md5命令为dbuser的密码创建md5加密串,然后以“<username: encrypted password>”格式加入到pcp.conf文件最后面。
# pg_md5 -p Password: (input password) (paste the md5 encrypted password to pcp.conf) # vi /etc/pgpool-II/pcp.conf (add password entry) user name:md5 encrypted password
Pgpool-II启/停操作
在配置完成后,最好重启一下主PostgreSQL数据库。
$ systemctl start pgpool.service
$ systemctl stop pgpool.service
设置PostgreSQL standby服务器
pcp_recovery_node -h 172.16.36.137 -p 9898 -U dbuser -n 1
# psql -h 172.16.36.13 -p 9999 -U dbuser mydb postgres=> show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay ---------+----------+------+--------+-----------+---------+------------+-------------------+------------------- 0 | dbsrv1 | 5432 | up | 0.500000 | primary | 0 | true | 0 1 | dbsrv2 | 5432 | up | 0.500000 | standby | 0 | false | 0
至此,PostgreSQL 10 的使用Pgpool-II做集群的设置已经完整结束。下一期讲述使用PGPool-II自带的watchdog做PGpool-II的HA。