greenplum部署及参数优化

greenplum部署及优化

greenplum的数据库是一个大规模并行处理(MPP)数据库服务器架构,该架构是专为管理大规模的数据仓库和商业智能设计的。

Greenplum数据存储和处理大数据量,是通过跨多个服务器和主机分配数据和负载。Greenplum数据库是基于PostgreSQL8.2单个数据库的阵列,这些数据库像单个数据库一样共同工作。master主机是Greenplum数据库系统的入口点,它是客户端连接并提交SQL语句的接口数据库实例。Master协调系统中其他数据库实例(称为Segment)的工作负载,处理数据和存储。分段通过互连,Greenplum数据库的网络层互相通信和主控

1.基础环境

centos7.4 16核32G

ip hostname 角色
192.168.0.44 k8s-master master、segment
192.168.0.43 k8s-node1 segment、standby
192.168.0.40 k8s-node2 segment

2. 服务器配置参数更改

  • 1.添加hosts
cat >> /etc/hosts << EOF
192.168.0.44 k8s-master
192.168.0.43 k8s-node1
192.168.0.40 k8s-node2 
EOF
  • 2.配置tcp参数

    修改/etc/sysctl.conf

cat >> /etc/sysctl.conf  <<EOF 
kernel.shmmni = 4096
kernel.shmall = 40000000000
kernel.shmmax = 287194767360
kernel.sem = 250 512000 100 2048
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.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.defalut.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     ###测试环境要取消这个,否则oracle启不来 ### 值为1

注意:kernel.shmmax = 5000000000 单位b(bit),值的大小为实际内存的50%。
kernel.shmmax参数调整系统内存的50%

执行 sysctl -p 使资源文件生效;

  • 3.添加limits.conf
cat >> /etc/security/limits.conf <<EOF
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072
EOF
  • 4.修改I/O调度算法
echo deadline > /sys/block/vdb/queue/scheduler
grubby --update-kernel=ALL --args="transparent_hugepage=never"
  • 5.设置gpadmin用户,密码
useradd  gpadmin -d /home/gpadmin
usermod -L gpadmin
#提权
visudo  
gpadmin    ALL=(ALL)       ALL
gpadmin    ALL=(ALL)       NOPASSWD:ALL
#创建密码
echo "gpadmin" | passwd --stdin gpadmin

注:注意设置密码为了后面gpssh-exkeys -f list 使用

3.下载&安装

  • 1.下载安装

名称 版本

操作系统 CentOS 64bit

greenplum greenplum-db-appliance-5.10.2-rhel6-x86_64.zip

文件系统 ext4

链接:

提取码: 7qzv

首先在master节点上安装,将greenplum-db-appliance-5.10.2-rhel6-x86_64.zip上传到/home/gpadmin目录下

su - gpadmin
cd /home/gpadmin
unzip greenplum-db-appliance-5.10.2-rhel6-x86_64.zip

./greenplum-db-appliance-5.10.2-rhel6-x86_64.bin
  • 2.安装目录相关文件授权
chown -R gpadmin.gpadmin /usr/local/greenplum-db*
 #需要在每个节点执行授权
chown gpadmin.gpadmin /usr/local 

以下开始,进入gpadmin用户下操作

#hostlist自定义的,一般为hostname
cat > list <<EOF
k8s-master
k8s-node1
k8s-node2
EOF
  • 3.添加环境变量
cat /usr/local/greenplum-db/greenplum_path.sh >>~/.bashrc 
cat >>  ~/.bashrc <<EOF
MASTER_DATA_DIRECTORY=/opt/data/master/gpseg-1
export MASTER_DATA_DIRECTORY
EOF
source ~/.bashrc 
  • 4.配置免密登陆
gpssh-exkeys -f list
  • 5.检查环境(可以忽略)
gpcheck -f list
  • 6.在segment节点安装greenplum-db-5.10.2-rhel7-x86_64
gpseginstall -f list
  • 7.创建数据目录
gpssh -f list -e " sudo chown gpadmin.gpadmin /opt"
gpssh -f list -e " mkdir -p /opt/data/{primary,mirror}"

# 创建master目录
mkdir -p /opt/data/master   
  • 8.创建初始化config文件
cat > gpinitsystem_config <<EOF

###需要的参数,目录可以根据需要更改##
ARRAY_NAME="Greenplum Data Platform"
SEG_PREFIX=gpseg
PORT_BASE=40000
declare -a DATA_DIRECTORY=(/opt/data/primary /opt/data/primary /opt/data/primary)
MASTER_HOSTNAME=k8s-master    #修改为主节点主机名
MASTER_DIRECTORY=/opt/data/master
MASTER_PORT=5432
TRUSTED_SHELL=ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UNICODE
declare -a MIRROR_DATA_DIRECTORY=(/opt/data/mirror /opt/data/mirror /opt/data/mirror)
EOF
  • 9.初始化数据库
gpinitsystem -a -c gpinitsystem_config -h list
重新加载配置文件
gpstop -u
#启动服务
gpstart  -a
#停止服务
gpstop -a

4. 配置修改

简单实用-添加允许连接的网段

注意:根据服务器的具体情况进行配置修改

示例:host 库名 用户名 ip/24 trust

cat /opt/data/master/gpseg-1/pg_hba.conf

host    all gpadmin 192.168.0.43/32 trust
host    all all 0.0.0.0/0   trust
local    all         gpadmin         ident
host     all         gpadmin         127.0.0.1/28    trust
host     all         gpadmin         192.168.0.44/32       trust

5. 登录测试

[root@slave3 gpadmin]# psql -d postgres
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "root", database "postgres", SSL off
[root@slave3 gpadmin]# su - gpadmin
Last login: Mon Jan 25 13:45:50 CST 2021 on pts/0
[gpadmin@slave3 ~]$  psql -d postgres
psql (8.3.23)
Type "help" for help.

postgres=# \l
                    List of databases
     Name     |  Owner  | Encoding |  Access privileges  
--------------+---------+----------+---------------------
 430000_RMDSD | gpadmin | UTF8     | 
 postgres     | gpadmin | UTF8     | 
 template0    | gpadmin | UTF8     | =c/gpadmin          
                                   : gpadmin=CTc/gpadmin
 template1    | gpadmin | UTF8     | =c/gpadmin          
                                   : gpadmin=CTc/gpadmin
(4 rows)

修改密码

alter role gpadmin with password 'gpadmin'; 

6. 授权

-- 给database授权
create user superuser xnreport password '8ql6,report';
grant all privileges on databases xn_report to xnreport;

-- 给table授权
grant all privileges on table table_name to xnreport;

7. 添加mirror

# 1、在所有需要添加mirror的主机,创建存放mirror的数据目录(如果已经创建,忽略此操作)
mkdir /opt/data/mirror

# 以下再master操作
# 2、生成配置文件
gpaddmirrors -o addmirror

# 添加mirror
gpaddmirrors -a -i addmirror -v 

8. 添加standby

-- 1、在standy节点上创建master目录
-- 2、在master上执行
gpinitstandby -a -s k8s-node1

postgres=# select * from pg_stat_replication ;
 procpid | usesysid | usename | application_name | client_addr | client_port |         backend_start         |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | syn
c_state
---------+----------+---------+------------------+-------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+----
--------
    6931 |       10 | gpadmin | walreceiver      | 192.168.0.43 |       48800 | 2018-12-24 11:51:05.376815+08 | streaming | 0/14000C18    | 0/14000C18     | 0/14000C18     | 0/14000C18      |             1 | syn
c


postgres=# select a.dbid,a.content,a.role,a.port,a.hostname,b.fsname,c.fselocation from gp_segment_configuration a,pg_filespace b,pg_filespace_entry c where a.dbid=c.fsedbid and b.oid=c.fsefsoid order by content;

留下评论