Greenplum 自定义参数配置 内存参数 内核参数 常用日志参数

1.物理机配置

  操作系统：CentOS 7.5

  内存：   256GB

  CPU核数： 56核

 

2.GP集群

192.168.121.244  Master,Segment；8个primary和8个mirror

192.168.121.244  Segment；8个primary和8个mirror

192.168.121.244  Segment；8个primary和8个mirror

 

3.sysctl.conf

kernel.shmmax = 137438953472

kernel.shmmni = 4096

kernel.shmall = 4294967296

kernel.sem = 500 1024000 200 4096

kernel.sysrq = 1

kernel.core_uses_pid = 1

kernel.msgmnb = 65536

kernel.msgmax = 65536

kernel.msgmni = 2048

net.ipv4.tcp_syncookies = 1

net.ipv4.ip_forward = 0

net.ipv4.conf.default.accept_source_route = 0

net.ipv4.tcp_tw_recycle = 1

net.ipv4.tcp_max_syn_backlog = 4096

net.ipv4.conf.all.arp_filter = 1

net.ipv4.ip_local_port_range = 1025 65535

net.core.netdev_max_backlog = 10000

net.core.rmem_max = 2097152

net.core.wmem_max = 2097152

vm.overcommit_memory = 2

vm.overcommit_ratio = 95

 

4.limits.conf

/etc/security/limits.conf

* soft nofile 655360

* hard nofile 655360

* soft nproc 1310720

* hard nproc 1310720

 

5.日志参数

log_statement = all

log_filename = 'gpdb_log_%a.csv'

log_truncate_on_rotation = on

log_rotation_age = 1d

log_rotation_size = 0

log_duration = on

 

6.内存参数

#主要影响vacuum,analyze,create index等操作，默认值为64MB,设置大一点可以加速上述操作

maintenance_work_mem = 2GB

#共享内存参数，可以缓存表数据、索引和查询计划，默认为125MB,设置大一点，但是需要满足公式：(num_instances_per_host * ( shared_buffers + other_seg_shmem )) + other_app_shared_mem  other_seg_shmem：segment为111MB,master为79MB

shared_buffers = 6GB

#每个查询在Segment中可用的内存，因为和查询的并发量有关，固推荐设置1GB,默认为125MB

statement_mem = 1GB

#服务器中每个实例进程可用的总内存，默认为8192，默认单位为MB,计算公式：gp_vmem =((SWAP + RAM) - (7.5GB + 0.05 * RAM))/ 1.7

gp_vmem_protect_limit = gp_vmem / acting_primary_segments

gp_vmem_protect_limit = 14336