mariadb columnrestore预研:安装部署

mariadb columnrestore

https://mariadb.com/kb/en/mariadb-columnstore/
mariadb columnrestore是mariadb的MPP型数据库，国内可能用的人不多，资料也比较少，以下是本地安装调试记录，并没有生产实用，只是预研、了解性质。

#关闭防火墙、selinux、ssh相互免密登录
setenforce 0
sed -i '/SELINUX/s/enforcing/disabled/' /etc/selinux/config
systemctl stop firewalld 
systemctl disable firewalld 
firewall-cmd --state

#免密登录操作自行搜索

#字符集需要一致
localedef -i en_US -f UTF-8 en_US.UTF-8

#系统参数调优 
#Modify /etc/sysctl.conf for the following:
vi /etc/sysctl.conf
#increase TCP max buffer size
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

#increase Linux autotuning TCP buffer limits
#min, default, and max number of bytes to use
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

#don't cache ssthresh from previous connection
net.ipv4.tcp_no_metrics_save = 1

#recommended to increase this for 1000 BT or higher
net.core.netdev_max_backlog = 2500

#需要c++环境:gcc gcc+ gcc-c++
yum -y install epel-release boost jemalloc cmake gcc gcc+ gcc-c++

tar zxvf boost_1_55_0.tar.gz
cd boost_1_55_0
./bootstrap.sh --with-libraries=atomic,date_time,exception,filesystem,iostreams,locale,program_options,regex,signals,system,test,thread,timer,log --prefix=/usr
./b2 install
ldconfig

#安装其他依赖软件
yum -y install expect perl perl-DBI openssl zlib file sudo libaio rsync snappy net-tools numactl-libs nmap

#Data Redundancy packages
yum -y install centos-release-gluster
yum -y install glusterfs glusterfs-fuse glusterfs-server

#start / enable service: 
systemctl enable glusterd.service
systemctl start glusterd.service 

#解压缩
tar zxvf /root/mariadb-columnstore-1.1.7-1-centos7.x86_64.rpm.tar.gz
#在pm1节点安装所有rpm包
rpm -ivh /root/*.rpm

#在pm1节点快捷集群安装 需要在pm1上执行!!!采用分布式安装模式，也就是安装um1、pm2，必须在pm1节点执行!!!必须在pm1节点执行!!!必须在pm1节点执行!!!
/usr/local/mariadb/columnstore/bin/quick_installer_multi_server.sh --pm-ip-addresses=192.168.122.33,192.168.122.31 --um-ip-addresses=192.168.122.32  --dist-install

#注意查看日志，如果有ssh认证失败，可以尝试：
chown -R root:root ~/.ssh
chmod 600 ~/.ssh/authorized_keys
restorecon -r -vv ~/.ssh

#配置常用命令别名
alias mcsadmin='/usr/local/mariadb/columnstore/bin/mcsadmin'
alias cpimport='/usr/local/mariadb/columnstore/bin/cpimport'

#如果没报错，可以查询服务状态
mcsadmin getSystemStatus
mcsadmin getSystemInfo

#启停服务等其他命令可以查看帮助
mcsadmin help

#远程登录需要类似mysql一样授权
MariaDB> grant all on *.* to 'root'@'%' identified by '111111';
MariaDB> flush privileges;

#可以通过navicat等工具连接操作了
#连接um1的3306端口 192.168.122.32:3306

#普通建表
CREATE TABLE IF NOT EXISTS test.test_dim(
	id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

#mcs建表，有很多限制，具体查看官网，比如decima最大18位
CREATE TABLE `test_fact` (
  `id` int(10)   unsigned NOT NULL,
  `k` int(10)   unsigned NOT NULL DEFAULT '0',
  `c` char(120)   DEFAULT '',
  `pad` char(60)   NOT NULL DEFAULT ''
) ENGINE=Columnstore DEFAULT CHARSET=utf8;


INSERT INTO `test`.`test_dim` (`id`, `name`) VALUES ('1', '旺财');
INSERT INTO `test`.`test_dim` (`id`, `name`) VALUES ('2', '六一');
INSERT INTO `test`.`test_dim` (`id`, `name`) VALUES ('3', '豪礼');

INSERT INTO `test`.`test_fact` (`id`, `k`, `c`, `pad`) VALUES ('1', '100','
aaaaaa','aaaaa');
INSERT INTO `test`.`test_fact` (`id`, `k`, `c`, `pad`) VALUES ('2', '100','
aaaaaa','bbbbb');
INSERT INTO `test`.`test_fact` (`id`, `k`, `c`, `pad`) VALUES ('3', '100','
aaaaaa','ccccc');

#验证基本的增删改查
select * from test_dim t1 left join test_fact t2 on t1.id = t2.id
update test_fact set c='eeee' where id = '2'
DELETE from test_fact where pad = 'bbbbb' and id = '1'


cpimport test sbtest /data/sbtest.csv -E '"' -s ','

#test是数据库
#sbtest是表
#-E 字段值分隔 Enclosed by character if field values are enclosed
#-s是字段的结束符 'c' is the delimiter between column values.

性能调优，没做的很好，资料也不大全，全靠摸索试错。

vi /usr/local/mariadb/columnstore/mysql/my.cnf
max_length_for_sort_data=65535


vi /usr/local/mariadb/columnstore/etc/Columnstore.xml
TotalUmMemory 50%
MaxOutstandingRequests 60
PmMaxMemorySmallSide 4096
ConnectionsPerPrimProc 30
ProcessorThreshold 128