프로그램/DB

MariaDB ColumnStore 1.1.x Install Guide

milktea.kang 2018. 3. 30. 15:07

MariaDB ColumnStore 1.1.x Install Guide

시나리오

서버 3대에 설치 예정 (Centos 6.9)

Master 1대 (Parent OAM : UM + PM)

172.16.10.65 pm1

Slave 2대 (PM)

172.16.10.64 pm2

172.16.10.62 pm3

준비작업

firewall off

selinux disabled

Master에서 모든 Slave에 ssh-key를 배포하여 자동 로그인이 가능하게 한다

[root@pm1 ~]# ssh-keygen

[root@pm1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub pm2

[root@pm1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub pm3

[root@pm1 ~]# ssh-copy-id -i ~/.ssh/id_rsa.pub pm1

[root@pm1 ~]# vi ~/.ssh/authorized_keys

ssh-rsa AAAAB3NzaC1……== root@pm1

ssh-rsa AAAAB3NzaC1……== root@172.16.10.65

ssh-rsa AAAAB3NzaC1……== root@localhost

ssh-rsa AAAAB3NzaC1……== root@127.0.0.1

pm1키를 복사해서 ip,localhost,127.0.0.1을 덧붙여둔다

pm1이 자기 자신에게 리모트로 컨트롤 하는 부분이 있는데 커넥션이 안 된다고 설치 오류가 발생하기 때문에 추가로 넣어 두는 것. 정확히 셋 중에 어느 것인지는 더 파악해 봐야 하겠지만…

[root@pm1 ~]# yum groupinstall -y "Development Tools"

[root@pm1 ~]# yum install -y rsync wget

[root@pm2 ~]# yum groupinstall -y "Development Tools"

[root@pm2 ~]# yum install -y rsync wget

[root@pm3 ~]# yum groupinstall -y "Development Tools"

[root@pm3 ~]# yum install -y rsync wget

boost 버전이 1.5 이상으로 설치가 되어야 한다

파일을 받아서 압축을 풀면 *.so 파일들이 들어있으니 /usr/lib/에 복사한다

[root@pm1 ~]# wget https://downloads.mariadb.com/ColumnStore/latest/centos/x86_64/centos6_boost_1_55.tar.gz

[root@pm1 ~]# tar zxvf centos6_boost_1_55.tar.gz -C /usr/lib/

master에서 yum으로 설치 하더라도 master에서 slave에 리모트로 설치될 때 rpm 파일들이 필요하니 rpm 패키지도 받아둔다

[root@pm1 ~]# wget https://downloads.mariadb.com/ColumnStore/latest/centos/x86_64/6/mariadb-columnstore-1.1.3-1-centos6.x86_64.rpm.tar.gz

[root@pm1 ~]# tar zxvf mariadb-columnstore-1.1.3-1-centos6.x86_64.rpm.tar.gz

columnStore repository를 등록한다

[root@pm1 ~]# vi /etc/yum.repos.d/MariaDB.repo

[mariadb-columnstore]

name=MariaDB ColumnStore

baseurl=https://downloads.mariadb.com/MariaDB/mariadb-columnstore/latest/yum/centos/6/x86_64/

gpgkey=https://downloads.mariadb.com/MariaDB/mariadb-columnstore/RPM-GPG-KEY-MariaDB-ColumnStore

gpgcheck=1

[mariadb-columnstore-tools]

name=MariaDB ColumnStore Tools

baseurl=https://downloads.mariadb.com/MariaDB/mariadb-columnstore-tools/latest/yum/centos/6/x86_64/

gpgkey=https://downloads.mariadb.com/MariaDB/mariadb-columnstore/RPM-GPG-KEY-MariaDB-ColumnStore

gpgcheck=1

[mariadb-maxscale]

name=MariaDB MaxScale

baseurl=https://downloads.mariadb.com/MaxScale/latest/centos/6/x86_64

gpgkey=https://downloads.mariadb.com/MaxScale/MariaDB-MaxScale-GPG-KEY

gpgcheck=1

[root@pm1 ~]# rsync /etc/yum.repos.d/MariaDB.repo root@pm2:/etc/yum.repos.d/

[root@pm1 ~]# rsync /etc/yum.repos.d/MariaDB.repo root@pm3:/etc/yum.repos.d/

glusterfs 설치

데이터를 분산 저장 하는 모듈인데 columnstore 설치시 저장 방식에 사용하지 않을 것이면 넘어가도 된다

[root@pm1 ~]# yum install -y centos-release-gluster

[root@pm1 ~]# yum install -y glusterfs-server

[root@pm2 ~]# yum install -y centos-release-gluster

[root@pm2 ~]# yum install -y glusterfs-server

[root@pm3 ~]# yum install -y centos-release-gluster

[root@pm3 ~]# yum install -y glusterfs-server

gluster 서비스 등록 및 시작

[root@pm1 ~]# chkconfig rpcbind on

[root@pm2 ~]# chkconfig rpcbind on

[root@pm3 ~]# chkconfig rpcbind on

[root@pm1 ~]# service rpcbind start

[root@pm2 ~]# service rpcbind start

[root@pm3 ~]# service rpcbind start

[root@pm1 ~]# chkconfig glusterd on

[root@pm2 ~]# chkconfig glusterd on

[root@pm3 ~]# chkconfig glusterd on

[root@pm1 ~]# service glusterd start

[root@pm2 ~]# service glusterd start

[root@pm3 ~]# service glusterd start

[root@pm1 ~]# mkdir -p /usr/local/glusterfs

[root@pm2 ~]# mkdir -p /usr/local/glusterfs

[root@pm3 ~]# mkdir -p /usr/local/glusterfs

gluster peer 등록

[root@pm1 ~]# gluster peer probe pm2

[root@pm1 ~]# gluster peer probe pm3

gluster volume 생성

[root@pm1 ~]# gluster volume create vol_gluster replica 3 \

pm1:/usr/local/glusterfs/brick \

pm2:/usr/local/glusterfs/brick \

pm3:/usr/local/glusterfs/brick force

gluster volume start

[root@pm1 ~]# gluster volume start vol_gluster

마운트를 해야 한다는데 안 해도 돌아가니까 패스

columnStore 설치

[root@pm1 ~]# yum groupinstall -y "MariaDB ColumnStore"

[root@pm1 ~]# yum install -y mariadb-columnstore-tools

[root@pm1 ~]# /usr/local/mariadb/columnstore/bin/postConfigure

…생략

Select the type of System Server install [1=single, 2=multi] (2) > 2

…생략

Select the type of System Module Install [1=separate, 2=combined] (2) > 2

pm1 서버에 관리모듈만 설치하려면 1번

pm1 서버에 관리모듈 + slave와 같은 엔진모듈도 같이 설치하려면 2번

…생략

MariaDB ColumnStore Schema Sync feature, do you want to enable? [y,n] (y) > y

…생략

Enter System Name (columnstore-1) > [그냥 엔터]

…생략

Select the type of Data Storage [1=internal, 2=external, 3=DataRedundancy] (1) > 3

glusterfs를 구성하면 3번 옵션이 추가로 나온다

…생략

Enter number of Performance Modules [1,1024] (3) > 3

…생략

*** Parent OAM Module Performance Module #1 Configuration ***

Enter Nic Interface #1 Host Name (pm1) > pm1

Enter Nic Interface #1 IP Address of pm1 (172.16.10.65) > 172.16.10.65

Enter Nic Interface #2 Host Name (unassigned) > [그냥 엔터]

Enter the list (Nx,Ny,Nz) or range (Nx-Nz) of DBRoot IDs assigned to module 'pm1' (1) > 1

*** Performance Module #2 Configuration ***

Enter Nic Interface #1 Host Name (pm2) > pm2

Enter Nic Interface #1 IP Address of pm2 (172.16.10.64) > 172.16.10.64

Enter Nic Interface #2 Host Name (unassigned) > [그냥 엔터]

Enter the list (Nx,Ny,Nz) or range (Nx-Nz) of DBRoot IDs assigned to module 'pm2' (2) > 2

*** Performance Module #3 Configuration ***

Enter Nic Interface #1 Host Name (pm3) > pm3

Enter Nic Interface #1 IP Address of pm3 (172.16.10.62) > 172.16.10.62

Enter Nic Interface #2 Host Name (unassigned) > [그냥 엔터]

Enter the list (Nx,Ny,Nz) or range (Nx-Nz) of DBRoot IDs assigned to module 'pm3' (3) > 3

…생략

Would you like to continue with the System Installation? [y,n] (y) > y

…생략

Enter password, hit 'enter' to default to using a ssh key, or 'exit' > [그냥 엔터]

…생략

Enter Number of Copies [2-3] (2) > 2

…생략

Select the data redundancy network [1=existing, 2=dedicated] (1) > 1

…생략

Would you like to startup the MariaDB ColumnStore System? [y,n] (y) > y

operation

mysql client가 mcsmysql로 바뀌었다

[root@pm1 ~]# mcsmysql

서비스 시작 종료는 mcsadmin 에서

[root@pm1 ~]# mcsadmin startSystem

[root@pm1 ~]# mcsadmin stopSystem

[root@pm1 ~]# mcsadmin shutdownSystem

[root@pm1 ~]# mcsadmin

mcsadmin> startSystem

mcsadmin> getSystemStatus

이렇게 사용 할 수도 있다

/etc/my.cnf 파일은 /usr/local/mariadb/columnstore/mysql/my.cnf 에 있다

create table

create table (….) engine=ColumnStore

pk, index 등을 구성 할 수 없다

auto_increament는 코멘트에 설정

예) `MSG_SEQ_NO` bigint(20) unsigned NOT NULL COMMENT 'autoincrement=1'

쿼리 통계 측정 설정

[root@pm1 ~]# vi /usr/local/mariadb/columnstore/etc/Columnstore.xml

</QueryStats>

[root@pm1 ~]# vi /usr/local/mariadb/columnstore/mysql/install_calpont_mysql.sh

CREATE TABLE IF NOT EXISTS infinidb_querystats.querystats

(

queryID bigint NOT NULL AUTO_INCREMENT,

sessionID bigint DEFAULT NULL,

host varchar(50),

user varchar(50),

priority char(20),

queryType char(25),

query varchar(8000),

startTime timestamp NOT NULL,

endTime timestamp NOT NULL,

`rows` bigint,

errno int,

phyIO bigint,

cacheIO bigint,

blocksTouched bigint,

CPBlocksSkipped bigint,

msgInUM bigint,

msgOutUm bigint,

maxMemPct int,

blocksChanged bigint,

numTempFiles bigint,

tempFileSpace bigint,

PRIMARY KEY (queryID)

);

[root@pm1 ~]# mcsmysql

MariaDB [(none)]> CREATE TABLE IF NOT EXISTS infinidb_querystats.querystats

-> (

-> queryID bigint NOT NULL AUTO_INCREMENT,

-> sessionID bigint DEFAULT NULL,

-> host varchar(50),

-> user varchar(50),

-> priority char(20),

-> queryType char(25),

-> query varchar(8000),

-> startTime timestamp NOT NULL,

-> endTime timestamp NOT NULL,

-> `rows` bigint,

-> errno int,

-> phyIO bigint,

-> cacheIO bigint,

-> blocksTouched bigint,

-> CPBlocksSkipped bigint,

-> msgInUM bigint,

-> msgOutUm bigint,

-> maxMemPct int,

-> blocksChanged bigint,

-> numTempFiles bigint,

-> tempFileSpace bigint,

-> PRIMARY KEY (queryID)

-> );

Query OK, 0 rows affected (0.00 sec)

GRANT CREATE TEMPORARY TABLES ON `infinidb_vtable`.* TO 'root'@'127.0.0.1';

GRANT CREATE TEMPORARY TABLES ON `infinidb_vtable`.* TO 'root'@'%';

[root@pm1 ~]# mcsadmin stopSystem

[root@pm1 ~]# mcsadmin startSystem

통계 쿼리

SELECT

MIN(endtime-starttime)

,MAX(endtime-starttime)

,AVG(endtime-starttime)

FROM infinidb_querystats.querystats

WHERE 1=1

AND querytype='SELECT'

AND starttime >= NOW() - INTERVAL 12 HOUR;

SELECT

a.*

FROM (

SELECT

endtime-starttime execTime

,`query`

FROM infinidb_querystats.querystats

WHERE 1=1

AND sessionid = 2

AND querytype = 'SELECT'

AND starttime >= NOW()-INTERVAL 12 HOUR

ORDER BY 1 LIMIT 3

) a;

SELECT

queryid

,QUERY

,endtime-starttime

,`rows`

FROM infinidb_querystats.querystats

WHERE 1=1

AND querytype='SELECT'

AND starttime >= NOW() - INTERVAL 12 HOUR;

쿼리 분석 (explain)

explain을 사용 할 수 없고 각 노드별 분산 쿼리 결과를 분석해준다

-- 세션에 분석을 준비시킨다

SELECT calSetTrace(1);

+----------------+
| calSetTrace(1) |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

-- 내가 만든 테이블 쿼리를 실행한다

SELECT * from test.t01;

+--------------------+-------------------+
| c_name             | sum(o_totalprice) |
+--------------------+-------------------+
| Customer#000000005 |         684965.28 |
+--------------------+-------------------+
1 row in set, 1 warning (0.34 sec)

-- 세션에서 분석 결과를 가져온다

SELECT calGetTrace();

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
| calGetTrace()                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
|
Desc Mode Table           TableOID ReferencedColumns        PIO LIO PBE Elapsed Rows
BPS  PM   customer        3024     (c_custkey,c_name)       0   43  36  0.006   1
BPS  PM   orders          3038     (o_custkey,o_totalprice) 0   766 0   0.032   3
HJS  PM   orders-customer 3038     -                        -   -   -   -----   -
TAS  UM   -               -        -                        -   -   -   0.021   1
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------                                                                                                                    ----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

출력 결과는 이렇다고 한다

The columns headings in the output are as follows:

Desc – Operation being executed. Possible values:

BPS - Batch Primitive Step : scanning or projecting the column blocks.

CES - Cross Engine Step: Performing Cross engine join

DSS - Dictionary Structure Step : a dictionary scan for a particular variable length string value.

HJS - Hash Join Step : Performing a hash join between 2 tables

HVS - Having Step: Performing the having clause on the result set

SQS - Sub Query Step: Performaning a sub query

TAS - Tuple Aggregation step : the process of receiving intermediate aggregation results at the UM from the PM nodes.

TNS - Tuple Annexation Step : Query result finishing, e.g. filling in constant columns, limit, order by and final distinct cases.

TUS = Tuple Union step : Performing a SQL union of 2 sub queries.

TCS = Tuple Constant Step: Process Constant Value Columns

WFS = Window Function Step: Performing a window function.

Mode – Where the operation was performed: UM or PM

Table – Table for which columns may be scanned/projected.

TableOID – ObjectID for the table being scanned.

ReferencedOIDs – ObjectIDs for the columns required by the query.

PIO – Physical I/O (reads from storage) executed for the query.

LIO – Logical I/O executed for the query, also known as Blocks Touched.

PBE – Partition Blocks Eliminated identifies blocks eliminated by Extent Map min/max.

Elapsed – Elapsed time for a give step.

Rows – Intermediate rows returned

Note: The time recorded is the time from PrimProc and ExeMgr. Execution time from withing mysqld is not tracked here. There could be extra processing time in mysqld due to a number of factors such as ORDER BY.

저작자표시 비영리 변경금지

'프로그램 > DB' 카테고리의 다른 글

aws rds mariadb grant (0)	2021.01.12
mysql/mariadb tree query (0)	2019.10.30
mysql community server init root password (2)	2014.11.04
mysql grant schema privilege (0)	2014.10.17
centos yum install mariadb 10.0 (0)	2014.10.14

현재글MariaDB ColumnStore 1.1.x Install Guide

milktea.kang