原 GreenPlum新建实例后需要做哪些基本优化操作
👉 本文共约2833个字，系统预计阅读时间或需11分钟。
目录
OS配置
内核参数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | sed -i s/SELINUX=enforcing/SELINUX=disabled/g /etc/selinux/config setenforce 0 ll /lib64/security/pam_limits.so echo "session required /lib64/security/pam_limits.so" >> /etc/pam.d/login cat >> /etc/security/limits.conf <<"EOF" * soft nofile 655350 * hard nofile 655350 * soft nproc 655350 * hard nproc 655350 gpadmin soft priority -20 EOF sed -i 's/4096/655350/' /etc/security/limits.d/20-nproc.conf cat /etc/security/limits.d/20-nproc.conf ulimit -HSn 65535 cat >> /etc/sysctl.conf <<"EOF" fs.file-max=9000000 fs.inotify.max_user_instances = 1000000 fs.inotify.max_user_watches = 1000000 kernel.pid_max=4194304 kernel.shmmax = 4398046511104 kernel.shmmni = 4096 kernel.shmall = 4000000000 kernel.sem = 32000 1024000000 500 32000 vm.overcommit_memory=1 vm.overcommit_ratio=95 net.ipv4.ip_forward=1 vm.swappiness=20 vm.dirty_background_bytes = 0 vm.dirty_background_ratio = 5 vm.dirty_bytes = 0 vm.dirty_expire_centisecs = 600 vm.dirty_ratio = 10 vm.dirty_writeback_centisecs = 100 vm.vfs_cache_pressure = 500 vm.min_free_kbytes = 2097152 EOF sysctl -p |
进程优先级
1 | echo 'gpadmin soft priority -20' >> /etc/security/limits.conf |
配置磁盘预读
在含有数据目录的设备上，blockdev预读尺寸应该被设置为16384。
1 2 3 4 5 | /sbin/blockdev --getra /dev/sdb /sbin/blockdev --setra 16384 /dev/sdb echo '/sbin/blockdev --setra 16384 /dev/sdb' >> /etc/rc.local chmod +x /etc/rc.d/rc.local |
加入/etc/rc.local文件中。
磁盘挂载参数
1 2 3 | mount -o rw,nodev,noatime,nobarrier,inode64 /dev/sdb /data echo '/dev/vggp/lvgp /data xfs defaults,rw,nodev,noatime,nobarrier,inode64 0 0' >> /etc/fstab |
禁用透明大页
不建议对数据库工作负载使用 THP （Oracle、MySQL、PostgreSQL、MongoDB均建议关闭THP），因为THP在运行时动态分配内存，而运行时的内存分配会有延迟，对于数据库的管理来说并不友好，会导致数据库性能抖动，所以建议关闭THP。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | cat /sys/kernel/mm/transparent_hugepage/defrag cat /sys/kernel/mm/transparent_hugepage/enabled echo never > /sys/kernel/mm/transparent_hugepage/enabled echo never > /sys/kernel/mm/transparent_hugepage/defrag cat >> /etc/rc.local <<"EOF" 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 EOF chmod +x /etc/rc.d/rc.local |
参考：https://www.xmmup.com/pgshujukupeizhidaye.html
配置足够的swap
若是内存不足，发生自动切换，会报：“FTS: cannot establish libpq connection (content=0, dbid=11): could not fork new process for connection: Cannot allocate memory”或“FATAL: Out of memory. Failed on request of size 144 bytes. (context 'GPORCAmemory pool') ”或“ATAL: the database system is in recovery mode”，若没有swap内存配置，会发生OOM，特别严重时会导致segment自动故障切换。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
