Vertica服务器集群和客户端部署/Vertica TPC- DS性能和并发测试/Vertica与Hive连通性测试

/*************************服务器集群部署************************/
//1. 部署准备
//cpu/mem/disk/net/filesysystem推荐设置见
https://www.vertica.com/kb/GenericHWGuide/Content/Hardware/GenericHWGuide.htm
//交换分区查看，交换分区至少2GB，交换分区设在boot盘上，别放在数据盘上

swapoff -a
dd if=/dev/zero of=/swapfile bs=2MiB count=1024
mkswap -f /swapfile
swapon /swapfile
free -m
echo "swap                    /swapfile               swap    defaults        0 0" >> /etc/fstab

echo "vm.swappiness = 1" >> /etc/sysctl.conf
sysctl -p
//防火墙
systemctl mask firewalld
systemctl disable firewalld
systemctl stop firewalld

//操作系统配置，自动和人工的配置
//自动配置的改为手动配置
echo "dbadmin -       nice    0" >> /etc/security/limits.conf
echo "vm.min_free_kbytes=7881" >> /etc/sysctl.conf
echo "dbadmin -       nofile  65536" >> /etc/security/limits.conf
#echo "fs.file-max=65536" >> /etc/sysctl.conf
echo "session required pam_limits.so" >> /etc/pam.d/su
echo "dbadmin -       as      unlimited" >> /etc/security/limits.conf
echo "dbadmin -       fsize      unlimited" >> /etc/security/limits.conf
#echo "dbadmin -       nproc     4096" >> /etc/security/limits.conf
#echo "vm.max_map_count=65536" >> /etc/sysctl.conf

#手动配置
/sbin/blockdev --setra 8192 /dev/sda
echo '/sbin/blockdev --setra 8192 /dev/sda' >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local

sed -r -i 's/SELINUX=(.*$)/SELINUX=permissive/g' /etc/selinux/config
setenforce 0

yum install chrony
systemctl status chronyd
systemctl enable chronyd
chronyc tracking

cat /sys/kernel/mm/transparent_hugepage/enabled
echo always > /sys/kernel/mm/transparent_hugepage/enabled
chmod +x /etc/rc.d/rc.local

echo deadline > /sys/block/sda/queue/scheduler
echo deadline > /sys/block/sdb/queue/scheduler
...

yum install gdb
yum install mcelog
yum install sysstat

//时区和语言设置
echo "export TZ='Asia/Shanghai'" >> /etc/profile
echo "export LANG=zh_CN.utf-8" >> /etc/profile

source /etc/profile

//包依赖
yum install openssh
yum install which
yum install dialog

//2. 用官方提供的按照包安装
//下载Red Hat Enterprise Linux 6 and 7下的版本：https://www.vertica.com/download/vertica/community-edition/
cd /opt
rz vertica-9.2.0-0.x86_64.RHEL6.rpm
//rpm中包含了部署脚本和vertica的打包程序
yum install vertica-9.2.0-0.x86_64.RHEL6.rpm

cd /opt
/opt/vertica/sbin/install_vertica --hosts mpp01,mpp02,mpp03 \
            --rpm /opt/vertica-9.2.0-0.x86_64.RHEL6.rpm \
            --dba-user dbadmin \
            --license CE \
            --accept-eula \
            --failure-threshold WARN \
            --record-config install.conf

/opt/vertica/sbin/install_vertica --config-file install.conf

//在各节点上分别创建数据目录

mkdir -p /data/vdata /data/vcatalog
chown -R dbadmin:verticadba /data/vdata
chown -R dbadmin:verticadba /data/vcatalog

su dbadmin
//图形环境配置创建数据库、配置数据库
admintools
vsql

/********************测试*****************************/

//先创建一个数据库，然后vsql才能连接，否则vsql不知道连谁。用admintools的create database功能
admintools

vetica工程师提供的测试工具：Vertica-TPC-DS-scripts.zip
cd /opt
rz Vertica-TPC-DS-scripts.zip
unzip -d TPC-DS-scripts  Vertica-TPC-DS-scripts.zip 
cd  TPC-DS-scripts
//设置环境变量, vsql的路径
export VSQL='/opt/vertica/bin/vsql -U dbadmin -w bfdadmin@cty'
vsql -U dbadmin -w bfdadmin@cty
create resource pool loading_pool

//需要是root用户，在tpcds_setup.sh中设置vsql的路径和用户名密码，需要提前建立资源池。
//修复脚本中的错误: 应该用analyze_statistics分析tpcds schema中而不是public schema中的表。
//tpcds_setup.sh用于生成测试数据，默认是100G, 测试数据和测试查询在/data/tpcdsdata目录中，用ls -alt /data/tpcdsdata查看此目录中部分隐藏文件。
vim tpcds_setup.sh
//将select analyze_statistics('public.${i}'); 改为 select analyze_statistics('tpcds.${i}');
//修改size=100为自己想测试的数据量
./tpcds_setup.sh

//tpcds-query.sh为执行查询测试的脚本, 修改tpcds-query.sh中numberOfUsers=1参数的值为numberOfUsers=100, concurrency=${CONCURRENCY}改为 concurrency=${numberOfUsers}
//grep "Time:"改为grep "start query"
tpcds_query.sh

//客户端安装///
//linux客户端下载地址：https://www.vertica.com/download/vertica/client-drivers/
//下载vertica-client-9.2.0-0.x86_64.rpm
//确定客户端安装到哪台服务器，用root用户登陆
rz vertica-client-9.2.0-0.x86_64.rpm
rpm -ivh vertica-client-9.2.0-0.x86_64.rpm
vim /etc/profile
//添加{
export PATH=/opt/vertica/bin:$PATH
export LANG=zh_CN.UTF-8
//}
source /etc/profile
参考：
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/vsql/Install/InstallingTheVsqlClient.htm

Vertica和Hive连通性测试///
hive -e "select * FROM db.tbl where l_date = '2019-04-24'" | \
dd bs=1M | pv -lpetr -s 30518825 |\
/opt/vertica/bin/vsql -h xx.xx.xx.xxx -U dbadmin -wpwd -d mytest -c \
"COPY schema.tbl FROM LOCAL STDIN DELIMITER E'\t' NULL 'NULL' DIRECT"