Greenplum 源码安装

数据库规划
1  master - standby
5  segment(s) - segment mirror(s)

硬件规划
6台主机
master节点配置(cpu 8核, mem 16G, network 1GB, disk 1*250G)
segments配置

建议规划

所有节点执行
# yum -y install rsync coreutils glib2 lrzsz sysstat e4fsprogs xfsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl perl-devel perl-ExtUtils* OpenIPMI-tools openldap openldap-devel logrotate python-py gcc-c++ libevent-devel apr-devel libcurl-devel bzip2-devel libyaml-devel

# vi /etc/sysctl.conf
kernel.shmmax = 68719476736
kernel.shmmni = 4096
kernel.shmall = 4000000000
kernel.sem = 50100 64128000 50100 1280
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
fs.file-max = 7672460
net.ipv4.netfilter.ip_conntrack_max = 655360
fs.aio-max-nr = 1048576
net.ipv4.tcp_keepalive_time = 72 
net.ipv4.tcp_keepalive_probes = 9 
net.ipv4.tcp_keepalive_intvl = 7

# sysctl -p

# vi /etc/security/limits.conf
* soft nofile 131072  
* hard nofile 131072  
* soft nproc 131072 
* hard nproc 131072
* soft    memlock unlimited
* hard    memlock unlimited

# rm -f /etc/security/limits.d/90-nproc.conf

把所有主机的IP和主机名列到/etc/hosts中。使用真实的主机名。
# vi /etc/hosts
127.0.0.1  localhost
xxx.xxx.193.96  digoal193096.zmf
xxx.xxx.199.92  digoal199092.zmf
xxx.xxx.200.164  digoal200164.zmf
xxx.xxx.204.16  digoal204016.zmf
xxx.xxx.204.63  digoal204063.zmf
xxx.xxx.209.198  digoal209198.zmf

# 文件系统mount option
如果是ext4
ext4 mount option
noatime,nodiratime,nobarrier,discard,nodelalloc,data=writeback

如果是xfs
rw,noatime,inode64,allocsize=16m

如果是ZFS
set zfs:zfs_arc_max=0x600000000 

设置块设备预读大小
# /sbin/blockdev --setra 16384 /dev/xvda1

创建一个管理greenplum 的用户，这里使用digoal

创建一个目录，放gp软件, 给greenplum管理用户写权限，也可以直接使用用户的HOME目录，例如/home/digoal/greenplum-db-4.3.6.1

所有 segment 节点  {
创建一个目录，放数据库, 给greenplum管理用户写权限
# mkdir -p /data01/gpdata
# chown -R digoal /data01/gpdata
# chmod -R 700 /data01/gpdata
}

master节点执行 {
# mkdir -p /data01/gpdata/master_pgdata
# chown -R digoal /data01/gpdata/master_pgdata
# chmod 700 /data01/gpdata/master_pgdata
}

主节点执行

非源码安装：
{  ----------------------------------------------

下载greenplum-db-4.3.6.1-build-2-RHEL5-x86_64.zip
unzip greenplum-db-4.3.6.1-build-2-RHEL5-x86_64.zip


使用普通用户安装
$ ./greenplum-db-4.3.6.1-build-2-RHEL5-x86_64.bin
安装到 /home/digoal/greenplum-db-4.3.6.1

$ cd /home/digoal/greenplum-db/bin
$ ./pg_config 
BINDIR = /home/digoal/greenplum-db-4.3.6.1/bin
DOCDIR = /home/digoal/greenplum-db-4.3.6.1/doc/postgresql
HTMLDIR = /home/digoal/greenplum-db-4.3.6.1/doc/postgresql
INCLUDEDIR = /home/digoal/greenplum-db-4.3.6.1/include
PKGINCLUDEDIR = /home/digoal/greenplum-db-4.3.6.1/include/postgresql
INCLUDEDIR-SERVER = /home/digoal/greenplum-db-4.3.6.1/include/postgresql/server
LIBDIR = /home/digoal/greenplum-db-4.3.6.1/lib
PKGLIBDIR = /home/digoal/greenplum-db-4.3.6.1/lib/postgresql
LOCALEDIR = /home/digoal/greenplum-db-4.3.6.1/share/locale
MANDIR = /home/digoal/greenplum-db-4.3.6.1/man
SHAREDIR = /home/digoal/greenplum-db-4.3.6.1/share/postgresql
SYSCONFDIR = /home/digoal/greenplum-db-4.3.6.1/etc/postgresql
PGXS = /home/digoal/greenplum-db-4.3.6.1/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CC = gcc -m64
CPPFLAGS = -D_GNU_SOURCE -I/home/digoal/greenplum-db-4.3.6.1/include
CFLAGS = -O3 -funroll-loops -fargument-noalias-global -fno-omit-frame-pointer -g -finline-limit=1800 -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -Werror -I/home/digoal/greenplum-db-4.3.6.1/include
CFLAGS_SL = -fpic
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/data/pulse2-agent/agents/agent1/work/GPDB-4_3_x-rcbuilds/rhel5_x86_64/4.3.6.1-build-2_output/greenplum-db-4.3.6.1-build-2/lib',--enable-new-dtags -L/home/digoal/greenplum-db-4.3.6.1/lib
LDFLAGS_SL = 
LIBS = -lpgport -lxml2 -lpam -lrt -lgssapi_krb5 -lcrypt -ldl -lm -L/home/digoal/greenplum-db-4.3.6.1/lib
VERSION = PostgreSQL 8.2.15


$cd /home/digoal/greenplum-db

$cat greenplum_path.sh 
GPHOME=/home/digoal/greenplum-db-4.3.6.1

# Replace with symlink path if it is present and correct
if [ -h ${GPHOME}/../greenplum-db ]; then
    GPHOME_BY_SYMLINK=`(cd ${GPHOME}/../greenplum-db/ && pwd -P)`
    if [ x"${GPHOME_BY_SYMLINK}" = x"${GPHOME}" ]; then
        GPHOME=`(cd ${GPHOME}/../greenplum-db/ && pwd -L)`/.
    fi
    unset GPHOME_BY_SYMLINK
fi
PATH=$GPHOME/bin:$GPHOME/ext/python/bin:$PATH
LD_LIBRARY_PATH=$GPHOME/lib:$GPHOME/ext/python/lib:$LD_LIBRARY_PATH
PYTHONPATH=$GPHOME/lib/python
PYTHONHOME=$GPHOME/ext/python
OPENSSL_CONF=$GPHOME/etc/openssl.cnf
export GPHOME
export PATH
export LD_LIBRARY_PATH
export PYTHONPATH
export PYTHONHOME
export OPENSSL_CONF

添加到用户的环境变量
$cat greenplum_path.sh >> ~/.bash_profile

$. ~/.bash_profile

}  ----------------------------------------------

源码安装
{  ----------------------------------------------

$ git clone https://github.com/greenplum-db/gpdb.git
$ cd gpdb
$ ./configure --prefix=/home/digoal/gpdb
$ make
$ make install
$ ln -s /home/digoal/gpdb /home/digoal/greenplum-db

$ vi ~/env_gp.sh
GPHOME=/home/digoal/greenplum-db

# Replace with symlink path if it is present and correct
if [ -h ${GPHOME}/../greenplum-db ]; then
    GPHOME_BY_SYMLINK=`(cd ${GPHOME}/../greenplum-db/ && pwd -P)`
    if [ x"${GPHOME_BY_SYMLINK}" = x"${GPHOME}" ]; then
        GPHOME=`(cd ${GPHOME}/../greenplum-db/ && pwd -L)`/.
    fi
    unset GPHOME_BY_SYMLINK
fi
PATH=$GPHOME/bin:$GPHOME/ext/python/bin:$PATH
LD_LIBRARY_PATH=$GPHOME/lib:$GPHOME/ext/python/lib:$LD_LIBRARY_PATH
PYTHONPATH=$GPHOME/lib/python
#PYTHONHOME=$GPHOME/ext/python
OPENSSL_CONF=$GPHOME/etc/openssl.cnf

export GPHOME
export PATH
export LD_LIBRARY_PATH
export PYTHONPATH
#export PYTHONHOME
export OPENSSL_CONF
export MASTER_DATA_DIRECTORY=/data01/digoal/gpdata/gpseg-1
export PGHOST=127.0.0.1
export PGPORT=1921
export PGUSER=digoal
export PGDATABASE=postgres

}  ----------------------------------------------

# easy_install pip
# pip install paramiko
# pip install psutil
# pip install lockfile

使用gpseginstall将GP软件安装到所有节点

创建主机文件，包括所有节点以及主节点本身
$ vi host
digoal193096.zmf
digoal199092.zmf
digoal200164.zmf
digoal204016.zmf
digoal204063.zmf
digoal209198.zmf

交换KEY，master使用gp管理用户(digoal)访问所有的segment不需要输入密码，master pub拷贝到所有的segment authorized_keys
$ gpssh-exkeys -f ./host


安装软件到segment hosts
$gpseginstall -f ./host -u digoal


初始化数据库

配置文件

$ cp /home/digoal/greenplum-db/docs/cli_help/gpconfigs/gpinitsystem_config ~/
$ chmod 644 ~/gpinitsystem_config
$ vi ~/gpinitsystem_config

# FILE NAME: gpinitsystem_config

# Configuration file needed by the gpinitsystem

################################################
#### REQUIRED PARAMETERS
################################################

#### Name of this Greenplum system enclosed in quotes.
ARRAY_NAME="digoal greenplum dw"

#### Naming convention for utility-generated data directories.
SEG_PREFIX=gpseg

#### Base number by which primary segment port numbers 
#### are calculated.
PORT_BASE=40000

#### File system location(s) where primary segment data directories 
#### will be created. The number of locations in the list dictate
#### the number of primary segments that will get created per
#### physical host (if multiple addresses for a host are listed in 
#### the hostfile, the number of segments will be spread evenly across
#### the specified interface addresses).
#### 每台主机安装几个segment，就需要几个目录
declare -a DATA_DIRECTORY=(/data01/gpdata /data01/gpdata /data01/gpdata /data01/gpdata)

#### OS-configured hostname or IP address of the master host.
MASTER_HOSTNAME=digoal193096.zmf

#### File system location where the master data directory 
#### will be created.
MASTER_DIRECTORY=/data01/gpdata/master_pgdata

#### Port number for the master instance. 
MASTER_PORT=1921

#### Shell utility used to connect to remote hosts.
TRUSTED_SHELL=ssh

#### Maximum log file segments between automatic WAL checkpoints.
CHECK_POINT_SEGMENTS=64

#### Default server-side character set encoding.
ENCODING=UTF-8

################################################
#### OPTIONAL MIRROR PARAMETERS
################################################

#### Base number by which mirror segment port numbers 
#### are calculated.
#MIRROR_PORT_BASE=50000

#### Base number by which primary file replication port 
#### numbers are calculated.
#REPLICATION_PORT_BASE=41000

#### Base number by which mirror file replication port 
#### numbers are calculated. 
#MIRROR_REPLICATION_PORT_BASE=51000

#### File system location(s) where mirror segment data directories 
#### will be created. The number of mirror locations must equal the
#### number of primary locations as specified in the 
#### DATA_DIRECTORY parameter.
#declare -a MIRROR_DATA_DIRECTORY=(/data01/gpdata_mirror /data01/gpdata_mirror /data01/gpdata_mirror /data01/gpdata_mirror)


################################################
#### OTHER OPTIONAL PARAMETERS
################################################

#### Create a database of this name after initialization.
DATABASE_NAME=digoal

#### Specify the location of the host address file here instead of
#### with the the -h option of gpinitsystem.
MACHINE_LIST_FILE=/home/digoal/host

编辑主机文件，不要包含master, standby，除非master,standby节点也需要当segment node使用.
$vi /home/digoal/host
digoal193096.zmf
digoal199092.zmf
digoal200164.zmf
digoal204016.zmf
digoal204063.zmf
digoal209198.zmf

初始化数据库
$gpinitsystem -c ./gpinitsystem_config --locale=C --max_connections=48 --shared_buffers=1GB --su_password=digoal


加入主备节点环境变量
$vi ~/env_gp.sh
export MASTER_DATA_DIRECTORY=/data01/gpdata/master_pgdata/gpseg-1
export PGHOST=127.0.0.1
export PGPORT=1921
export PGUSER=digoal
export PGDATABASE=digoal

$. ~/env_gp.sh

$psql -h 127.0.0.1 -p 1921 -U digoal digoal
psql (8.2.15)
Type "help" for help.
digoal=# select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.2.15 (Greenplum Database 4.3.6.1 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct  1 2015 15:14:22
(1 row)

digoal=#   create table test(id int primary key, info text, crt_time timestamp) with (OIDS=false, FILLFACTOR=95) DISTRIBUTED BY (id);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
插入4000万测试数据
digoal=# \timing
Timing is on.

digoal=# do language plpgsql 
$$

digoal$# declare
digoal$# begin
digoal$#   for i in 1..10 loop
digoal$#     execute 'insert into test select generate_series('||(i-1)*4000000+1||','||i*4000000||'),md5(random()::text),clock_timestamp()';
digoal$#   end loop;
digoal$# end;
digoal$# 
$$
;
gp目前不支持do语法，使用函数。

digoal=# create or replace function f_test() returns void as 
$$

digoal$# declare
digoal$# begin
digoal$#   for i in 1..10 loop
digoal$#     execute 'insert into test select generate_series('||(i-1)*4000000+1||','||i*4000000||'),md5(random()::text),clock_timestamp()';
digoal$#   end loop;
digoal$# end;
digoal$# 
$$
 language plpgsql;
CREATE FUNCTION
digoal=# select f_test();
 f_test 
--------
 
(1 row)

Time: 179127.416 ms
digoal=# select count(*) from test;
  count   
----------
 40000000
(1 row)
Time: 1119.652 ms

使用9.5 的pgbench测试greenplum oltp性能
vi test.sql
\setrandom id 1 40000000
update test set info=info where id=:id;

$/home/digoal/pgsql9.5/bin/pgbench -M prepared -n -r -P 1 -f ./test.sql -c 1 -j 1 -T 1000 -h 127.0.0.1 -p 1921 -U digoal digoal
progress: 1.0 s, 25.0 tps, lat 38.682 ms stddev 42.391
progress: 2.0 s, 31.0 tps, lat 32.264 ms stddev 26.702
progress: 3.0 s, 34.0 tps, lat 29.486 ms stddev 7.113
progress: 4.0 s, 36.0 tps, lat 27.873 ms stddev 5.031
progress: 5.0 s, 30.0 tps, lat 32.921 ms stddev 9.209
progress: 6.0 s, 36.0 tps, lat 28.026 ms stddev 5.132
progress: 7.0 s, 29.0 tps, lat 34.364 ms stddev 30.162
progress: 8.0 s, 38.0 tps, lat 26.911 ms stddev 4.510
progress: 9.0 s, 35.0 tps, lat 28.445 ms stddev 15.298


$/home/digoal/pgsql9.5/bin/pgbench -M prepared -n -r -P 1 -f ./test.sql -c 16 -j 16 -T 1000 -h 127.0.0.1 -p 1921 -U digoal digoal
progress: 1.0 s, 20.0 tps, lat 558.569 ms stddev 159.308
progress: 2.0 s, 35.0 tps, lat 467.861 ms stddev 28.536
progress: 3.0 s, 38.0 tps, lat 422.836 ms stddev 29.095
progress: 4.0 s, 27.0 tps, lat 550.747 ms stddev 103.408
progress: 5.0 s, 31.0 tps, lat 577.198 ms stddev 144.169
progress: 6.0 s, 36.0 tps, lat 437.262 ms stddev 13.159
progress: 7.0 s, 35.0 tps, lat 456.761 ms stddev 28.191
progress: 8.0 s, 33.0 tps, lat 461.790 ms stddev 43.644
progress: 9.0 s, 36.0 tps, lat 470.014 ms stddev 29.238
progress: 10.0 s, 42.0 tps, lat 391.530 ms stddev 15.373

vi test.sql
\setrandom id 1 40000000
select * from test where id=:id;
性能差不多。

修改配置的方法举例
$gpconfig -c optimizer -v on
$gpstop -u