greeplum的基础

greenplum系列

一： greeplum 的介绍

二：greeplum的应用场景

三: greeplum 的架构

四: greeplum 的版本演进

五：greeplum的表的分布策略

六：greeplum 的表分区介绍

七：greeplum 的与hadoop区别

八：greeplum 的部署

8.1 环境准备

环境：
   centos7.8x64 
1. master 一台
2. standby 一台
3. segment  三台
4. 扩容集群 二台
----
cat /etc/hosts 
----
192.168.3.121   test01.greenplum.uniondrug.com
192.168.3.122   test02.greenplum.uniondrug.com
192.168.3.123   test03.greenplum.uniondrug.com
192.168.3.124   test04.greenplum.uniondrug.com
192.168.3.125   test05.greenplum.uniondrug.com
192.168.3.126   test06.greenplum.uniondrug.com
192.168.3.127   test07.greenplum.uniondrug.com
----

8.2 系统初始化：

cat >> /etc/sysctl.conf  << EOF
fs.aio-max-nr = 1048576
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
kernel.sem = 204800 512000 3000 20480   
kernel.shmmax = 1073741824
kernel.shmall = 262144
kernel.shmmni = 4096
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
vm.swappiness = 10
vm.overcommit_memory = 2 
vm.overcommit_ratio = 95 
vm.zone_reclaim_mode = 0
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.dirty_background_ratio = 3 
vm.dirty_ratio = 10
#64g-
#vm.dirty_background_ratio = 3 
#vm.dirty_ratio = 10
#64g+
#vm.dirty_background_ratio = 0
#vm.dirty_ratio = 0
#vm.dirty_background_bytes = 1610612736  
#vm.dirty_bytes = 4294967296
EOF
----
sysctl -p 

---

--02.资源限制
cat >> /etc/security/limits.conf  << EOF
*                soft    nproc   unlimited 
*                 hard    nproc   unlimited
*                 soft    nofile  524288
*                 hard    nofile  524288
*           soft   stack    unlimited
*           hard   stack    unlimited
*               hard    memlock unlimited
*               soft    memlock  unlimited
EOF
rm -f /etc/security/limits.d/*

echo "session    required     pam_limits.so" >> /etc/pam.d/login
cat /etc/pam.d/login
echo "RemoveIPC=no" >> /etc/systemd/logind.conf
service systemd-logind restart

--03.安全配置
echo "SELINUX=disabled" > /etc/selinux/config
setenforce 0
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
systemctl set-default multi-user.target

XFS：
rw,noatime,inode64,allocsize=16m
/dev/sdb1 /greenplum           xfs     rw,noatime,inode64,allocsize=16m        0 0
/dev/sdb2 /usr/local              xfs     rw,noatime,inode64,allocsize=16m        0 0
echo "blockdev --setra 65536 /dev/sdb " >> /etc/rc.d/rc.local
echo "blockdev --setra 65536 /dev/sdb1 " >> /etc/rc.d/rc.local
echo "blockdev --setra 65536 /dev/sdb2 " >> /etc/rc.d/rc.local
echo deadline > /sys/block/sdb/queue/scheduler
yum install numactl
vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off transparent_hugepage=never elevator=deadline" 
grub2-mkconfig -o /etc/grub2.cfg
numastat 
numactl --show
numactl --hardware
echo "RemoveIPC=no" >> /etc/systemd/logind.conf
service systemd-logind restart

yum -y install  openssh-clients gcc gcc-c++ make automake autoconf libtool  krb5-devel perl rsync coreutils glib2 ed lrzsz sysstat e4fsprogs xfsprogs ntp readline-devel zlib zlib-devel unzip bzip2 libyaml zip libevent
shutdown -r now

/home/gpadmin/.bash_profile :
echo "source /usr/local/greenplum-db/greenplum_path.sh" >>~/.bash_profile 
echo "export MASTER_DATA_DIRECTORY=/greenplum/gpdata/master/gpseg-1" >>~/.bash_profile 
echo "export PGHOME=/usr/local/greenplum-db" >>~/.bash_profile 
echo "export PGPORT=5432" >>~/.bash_profile 
echo "export PGDATABASE=postgres" >>~/.bash_profile 
echo "export PGUSER=gpadmin" >>~/.bash_profile 
cat ~/.bash_profile   
source ~/.bash_profile  
/home/gpadmin/.bashrc :
echo "source /usr/local/greenplum-db/greenplum_path.sh" >>~/.bashrc 
echo "export MASTER_DATA_DIRECTORY=/greenplum/gpdata/master/gpseg-1" >>~/.bashrc 
echo "export PGHOME=/usr/local/greenplum-db" >>~/.bashrc 
echo "export PGPORT=5432" >>~/.bashrc 
echo "export PGDATABASE=postgres" >>~/.bashrc 
echo "export PGUSER=gpadmin" >>~/.bashrc 
cat ~/.bashrc   
source ~/.bashrc  

8.3 greeplum 的 安装区别

GPDB 4.x /5.x 
先安装master， .bin 可以安装目录
gpseginstall 
gp 集群参数校验
gpinitsystem 集群初始化
GPDB 6.x 
不在提供zip,bin的格式 只提供rpm 包
安装master； 目录 安装在 /usr/local/ 
gp6 没有gpseginstall 包了，只能打包、单独安装
gp集群参数校验
gpinitsystem 集群初始化

8.4 greeplum 安装

下载安装包（官方下载）
安装目录：/usr/local/greeplum-db
mkdir -p /greenplum/gpdata
mkdir -p /greenplum/soft
安装rpm 安装
  rpm -ivh greenplum-db-6.11.2-rhel7-x86_64.rpm

创建用户：
groupadd -g 66000 gpadmin
useradd -u 66000 -g gpadmin -m -d /home/gpadmin -s /bin/bash gpadmin
echo "gpadmin" | passwd --stdin gpadmin
su - gpadmin
exit
chown -R gpadmin:gpadmin /usr/local/greenplum*
chmod -R 775 /usr/local/greenplum*
chmod -R 775 /greenplum
chown -R gpadmin:gpadmin /greenplum

设置主机名 的配置
su - gpadmin
mkdir /usr/local/greenplum-db/config
vim /usr/local/greenplum-db/config/all_hosts.txt
---
test01.greenplum.uniondrug.com
test02.greenplum.uniondrug.com
test03.greenplum.uniondrug.com
test04.greenplum.uniondrug.com
test05.greenplum.uniondrug.com
---
vim /usr/local/greenplum-db/config/all_seg.txt
---
test03.greenplum.uniondrug.com
test04.greenplum.uniondrug.com
test05.greenplum.uniondrug.com
---

做gpadmin 的无密钥认证
./fgssh -user gpadmin -hosts "fggpmaster01 fggpmaster02 fggpdata01 fggpdata02
fggpdata03" -advanced -exverify -confirm
chmod 600 /home/gpadmin/.ssh/config
gpssh -f /usr/local/greenplum-db/config/all_hosts.txt -e 'ls -ls /greenplum'

su - gpadmin
mkdir -p /greenplum/gpdata/master （主节点与备份节点配置）
gpssh -f /usr/local/greenplum-db/config/all_hosts.txt -e 'mkdir -p /greenplum/gpdata/primary'
gpssh -f /usr/local/greenplum-db/config/all_hosts.txt -e 'mkdir -p /greenplum/gpdata/mirror'

设置：gpadmin 环境变量 
su - gpadmin
/home/gpadmin/.bash_profile :
echo "source /usr/local/greenplum-db/greenplum_path.sh" >>~/.bash_profile
echo "export MASTER_DATA_DIRECTORY=/greenplum/gpdata/master/gpseg-1" >>~/.bash_profile
echo "export PGHOME=/usr/local/greenplum-db" >>~/.bash_profile
echo "export PGPORT=5432" >>~/.bash_profile
echo "export PGDATABASE=postgres" >>~/.bash_profile
echo "export PGUSER=gpadmin" >>~/.bash_profile
cat ~/.bash_profile
source ~/.bash_profile
/home/gpadmin/.bashrc :
echo "source /usr/local/greenplum-db/greenplum_path.sh" >>~/.bashrc
echo "export MASTER_DATA_DIRECTORY=/greenplum/gpdata/master/gpseg-1" >>~/.bashrc
echo "export PGHOME=/usr/local/greenplum-db" >>~/.bashrc
echo "export PGPORT=5432" >>~/.bashrc
echo "export PGDATABASE=postgres" >>~/.bashrc
echo "export PGUSER=gpadmin" >>~/.bashrc
cat ~/.bashrc
source ~/.bashrc

同步所有节点
gpscp -f /usr/local/greenplum-db/config/all_hosts.txt /home/gpadmin/.bash_profile
gpadmin@=:/home/gpadmin/.bash_profile
gpscp -f /usr/local/greenplum-db/config/all_hosts.txt /home/gpadmin/.bashrc
gpadmin@=:/home/gpadmin/.bashrc

主机测试：
网络：
gpcheckperf -f /usr/local/greenplum-db/config/all_seg.txt -r N -d /tmp
I/O 与内存：
gpcheckperf -f /usr/local/greenplum-db/config/all_seg.txt -r ds -D -d
/greenplum/gpdata/primary -d /greenplum/gpdata/mirror
时间同步验证：
gpssh -f /usr/local/greenplum-db/config/all_hosts.txt -e 'date'

时间同步：
如果不同步，需要配置 NTP
主库：
root:
echo server 127.127.1.0 iburst >> /etc/ntp.conf
systemctl restart ntpd
systemctl enable ntpd
ntpq -p
别的库：
root:
echo server 192.168.1.11>> /etc/ntp.conf
echo restrict 192.168.1.11 nomodify notrap noquery >> /etc/ntp.conf
ntpdate -u 192.168.1.11
hwclock -w
systemctl restart ntpd
systemctl enable ntpd
ntpq -p
测试：
su - gpadmin:
gpssh -f /usr/local/greenplum-db/config/all_hosts.txt -e 'date'

九： 集群节点的安装

vim /usr/local/greenplum-db/config/gpinitsystem_config
ARRAY_NAME="greenplum"
SEG_PREFIX=gpseg
PORT_BASE=55000
declare -a DATA_DIRECTORY=(/greenplum/gpdata/primary /greenplum/gpdata/primary /greenplum/gpdata/primary)
MASTER_HOSTNAME=test01.greenplum.uniondrug.com
MASTER_DIRECTORY=/greenplum/gpdata/master
MASTER_PORT=5432
MACHINE_LIST_FILE=/usr/local/greenplum-db/config/all_seg.txt
TRUSTED_SHELL=ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UNICODE
MIRROR_PORT_BASE=56000
REPLICATION_PORT_BASE=57000
MIRROR_REPLICATION_PORT_BASE=58000
declare -a MIRROR_DATA_DIRECTORY=(/greenplum/gpdata/mirror /greenplum/gpdata/mirror /greenplum/gpdata/mirror)

初始化数据库：
gpinitsystem -c /usr/local/greenplum-db/config/gpinitsystem_config -a 
or
gpinitsystem -c /usr/local/greenplum-db/config/gpinitsystem_config -a -h
/usr/local/greenplum-db/config/all_seg.txt
完全冗余
gpinitsystem -c /usr/local/greenplum-db/config/gpinitsystem_config -a -h
/usr/local/greenplum-db/config/all_seg.txt -s fggpmaster02 -D
or
gpinitsystem -c /usr/local/greenplum-db/config/gpinitsystem_config -a -h
/usr/local/greenplum-db/config/all_seg.txt \
-s test02.greenplum.uniondrug.com -D -B 2
机器多 
gpinitsytem -c /usr/local/greenplum-db/config/gpinitsystem_config -a -h
/usr/local/greenplum-db/config/seg_hosts.txt \
-s fggpmaster02 -D -B 2 -S /greenplum/gpdata/mirror
备份方案2种
group Mirror 默认 （机器少）
主机的mirror 节点全部放在下一个主机上面
1 2 3 4 5
5 1 2 3 4
  镜像存储存放
Spread mirror 另一种方式 -S 防止一个节点挂掉，另外的一个节点集中成为瓶颈 （机器多）
一般允许挂掉一台，另一台就负载加重一倍，机器多可以允许两台挂掉
主机的 第一个mirror 在下一个主机，第二个 mirror 在次下一个主机，第三个mirror 在次次下个主机 下面
1 2 3 4 5 6 7 8
5 1 6 2 7 3 8 4
如果挂掉一台，宕机对应的mirror 机器负载家中，机器多的时候，可以挂掉一台
如果 安装失败,建议gpstop 
/bin/rm -rf /greenplum/gpdata/master/*
/bin/rm -rf /greenplum/gpdata/primary/* 
/bin/rm -rf /greenplum/gpdata/mirror/* 

修改参数
gpconfig -c shared_buffers -v 129MB -m 126MB
gpconfig -c max_connections -v 1000 -m 300
gpconfig -s shared_buffers
gpconfig -s max_connections
gpstop -u
远程登录：
psql -h 192.168.3.121 -p5432 -U gpadmin

相关使用命令
select version();

select * from gp_segment_configuration order by content asc,dbid;

关于segment 的 连接
psql -h 192.168.3.123 -p55000 -U gpadmin
----
psql: FATAL:  connections to primary segments are not allowed
DETAIL:  This database instance is running as a primary segment in a Greenplum cluster and does not permit direct connections.
HINT:  To force a connection anyway (dangerous!), use utility mode.
-----

psql -h 192.168.100.13 -p55000 -U gpadmin

data 节点连接
PGOPTIONS='-c gp_session_role=utility' psql -h127.0.0.1 -p55000
PGOPTIONS='-c gp_session_role=utility' psql -h192.168.100.13 -p55000

远程连接：

---

 psql -h192.168.100.11 -p5432 -U gpadmin
alter role gpadmin with password 'gpadmin';

cd /greenplum/gpdata/master/gpseg-1
vim pg_hba.conf 
----
到最后加上：
 host    all     all     0.0.0.0/0       md5
----
gpstop -u 

新建一个查询：
create database fgedu
create user fgedu with password 'fgedu123'
insert into itpux1 values(1,'itpux01',21);
insert into itpux1 values(2,'itpux01',22);
insert into itpux1 values(3,'itpux03',23);
insert into itpux1 values(4,'itpux04',24);
insert into itpux1 values(5,'itpux05',25);
insert into itpux1 values(6,'itpux06',26);
insert into itpux1 values(7,'itpux07',27);
insert into itpux1 values(8,'itpux05',28);
insert into itpux1 values(9,'itpux09',29);
insert into itpux1 values(10,'itpux10',30);
insert into itpux1 values(11,'itpux11',31);
insert into itpux1 values(12,'itpux12',32);

select gp_segment_id,count(*) from itpux1 group by gp_segment_id;
查看 数据分段 

create table fgedu (name varchar(50));
insert into fgedu values('风哥');
insert into fgedu values('数据库');
insert into fgedu values('培训教程');
insert into fgedu values('fgedu.net.cn');
insert into fgedu values('wx');
insert into fgedu values('itpux-com');
insert into fgedu values('wx-gzh');
insert into fgedu values('itpux_com');
insert into fgedu values('oracle');
insert into fgedu values('mysql');
insert into fgedu values('nosql');
insert into fgedu values('国产数据库');
insert into fgedu values('开源数据库');
insert into fgedu values('高端就业课程');
select * from fgedu;
select gp_segment_id,count(*) from fgedu group by gp_segment_id;

随机分布
create table itpux2(id int,name varchar(40)) distributed randomly;
insert into itpux2 values(1,'fgedu1');
insert into itpux2 values(2,'fgedu2');
insert into itpux2 select * from itpux2;
insert into itpux2 select * from itpux2;
select count(*) from itpux2;
select gp_segment_id,count(*) from itpux2 group by gp_segment_id;

复制表
create table itpux3(id int,name varchar(40)) distributed replicated;
insert into itpux3 values(1,'fgedu1');
insert into itpux3 values(2,'fgedu2');
insert into itpux3 select * from itpux3;
insert into itpux3 select * from itpux3;
select count(*) from itpux3;
PGOPTIONS='-c gp_session_role=utility' psql -h192.168.100.13 -p55002 -d fgedu -U fgedu -c 'select count(*) from itpux3';
PGOPTIONS='-c gp_session_role=utility' psql -h192.168.100.14 -p55002 -d fgedu -U fgedu -c
'select count(*) from itpux3';
PGOPTIONS='-c gp_session_role=utility' psql -h192.168.100.14 -p55001 -d fgedu -U fgedu -c
'select count(*) from itpux3';
复制表 每个节点 表的 是一样的

十： greenplum 的 监控配置

Pivotal Greenplum Command Center6.3 (gpcc 监控平台)
下载地址：
https://network.pivotal.io/products/pivotal-gpdb/

gpperfmon_install --enable --password gpmon --port 5432

主要是改了如下东西

配置文件
/greenplum/gpdata/master/gpseg-1/postgresql.conf
访问权限
/greenplum/gpdata/master/gpseg-1/pg_hba.conf

重启GP数据库
gpstop -r -a 

ps -ef |grep gp
/greenplum/gpdata/master/gpseg-1/gpperfmon/conf/
生成一个配置文件
   gpperfmon.conf
/greenplum/gpdata/master/gpseg-1/gpperfmon/logs  日志路径

psql -d gpperfmon -c 'select * from system_now';

当master 挂掉之后启用 standby 的时候涉及同步问题
cd /greenplum/gpdata/master/gpseg-1
scp pg_hba.conf gpadmin@192.168.100.12:/greenplum/gpdata/master/gpseg-1/
scp ~/.pgpass gpadmin@192.168.100.12:~/

下面安装 greenplum-cc-web 配置 使用gpadmin用户安装
 cd /greenplum/soft/
 unzip greenplum-cc-web-6.0.0-rhel7_x86_64.zip

cd greenplum-cc-web-6.0.0-rhel7_x86_64
./gpccinstall-6.0.0
会在所有的主机上面安装 
 安装目录为 /greenplum/greenplum-cc-web-6.0.0

加载gpcc_path.sh 文件
---
vim .bash_profile
source /greenplum/greenplum-cc-web-6.0.0/gpcc_path.sh
---
---
vim .bashrc 
source /greenplum/greenplum-cc-web-6.0.0/gpcc_path.sh
---
同步所有主机
scp .bashrc .bash_profile gpadmin@192.168.100.12:/home/gpadmin/
scp .bashrc .bash_profile gpadmin@192.168.100.13:/home/gpadmin/
scp .bashrc .bash_profile gpadmin@192.168.100.14:/home/gpadmin/
scp .bashrc .bash_profile gpadmin@192.168.100.15:/home/gpadmin/
source /home/gpadmin/.bash_profile
source /home/gpadmin/.bashrc

cd /greenplum/gpdata/master/gpseg-1/
vim pg-hba.conf 
增加一行授权
---
host     gpperfmon         gpmon         192.168.100.11/32    md5
---

gpcc start 

gpconfig -s gp_enable_gpperfmon
修改 
gpconfig -c gp_enable_gpperfmon -v off 

浏览器打开
http://192.168.100.11:28080/
用户名、密码  cat /home/gpadmin/.pgpass
gpmon/gpmon