CentOS7 に MariaDB ColumnStore 1.1 GA を冗長構成でインストール

MariaDB の列指向データベース、MariaDB ColumnStore は 1.1 から [GlusterFS](https://www.gluster.org/) を用いた冗長構成をサポートしているようです。

# [公式ページ](https://mariadb.com/ja/downloads/columnstore)から 1.1.3 RPM tarballをダウンロード

RPMがアーカイブされたtar ballをダウンロード、展開します。まだColumnStore用のyumレポジトリはないので、yum install でのインストール，yum updateで更新はできないようです。

```
wget https://downloads.mariadb.com/ColumnStore/1.1.3/centos/x86_64/7/mariadb-columnstore-1.1.3-1-centos7.x86_64.rpm.tar.gz
tar xvzf mariadb-columnstore-1.1.3-1-centos7.x86_64.rpm.tar.gz
```

# MariaDB, 旧バージョンのColumnStoreをアンインストール

MariaDB Server、古いバージョンのColumnStoreがインストールされている場合はアンインストールします。

```
yum -y remove "mariadb-*" "MariaDB-*"
```

```
yum -y remove "mariadb-columnstore-*"
```

# MariaDB ColumnStore インストール

PM1(Performance Module 1)にのみ MariaDB ColumnStore 1.1.3 を手動インストールします。boostなどの依存RPMもインストールされます。

```
yum -y localinstall mariadb-columnstore-1.1.*.rpm
```

# GlusterFS のインストール

https://mariadb.com/kb/en/library/preparing-for-columnstore-installation-11x/
を参照、GlusterFSをインストールします。

```
yum -y install centos-release-gluster
yum -y install glusterfs glusterfs-fuse glusterfs-server
systemctl enable glusterd.service
systemctl start glusterd.service 
```

# postConfigure

インストール完了後 PM1 上で `/usr/local/mariadb/columnstore/bin/postConfigure` 設定ウィザードを実行します。
GlusterFSのvolume作成等も自動的に行われます。
[postConfigureのソース](https://github.com/mariadb-corporation/mariadb-columnstore-engine/blob/96bed8b09c267f9fa024a2ee723c6d8ac8eb15b3/oamapps/postConfigure/postConfigure.cpp)によると、gluster volume create によるボリューム作成の際、forceオプションがついているようなので、/パーティション上にdbrootが作成されます。
なお、PM1から各PMにroot SSHログインできるよう、SSH鍵などを設定する必要があります。

```
# /usr/local/mariadb/columnstore/bin/postConfigure

This is the MariaDB ColumnStore System Configuration and Installation tool.
It will Configure the MariaDB ColumnStore System and will perform a Package
Installation of all of the Servers within the System that is being configured.

IMPORTANT: This tool should only be run on the Parent OAM Module
           which is a Performance Module, preferred Module #1

Prompting instructions:

        Press 'enter' to accept a value in (), if available or
        Enter one of the options within [], if available, or
        Enter a new value


===== Setup System Server Type Configuration =====

There are 2 options when configuring the System Server Type: single and multi

  'single'  - Single-Server install is used when there will only be 1 server configured
              on the system. It can also be used for production systems, if the plan is
              to stay single-server.

  'multi'   - Multi-Server install is used when you want to configure multiple servers now or
              in the future. With Multi-Server install, you can still configure just 1 server
              now and add on addition servers/modules in the future.

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

type of System Server install は通常2(multi)を選択します。1 は主にデモ/テスト/トレーニング向けで運用開始後PM(Performance Module)を増やせません。

```
===== Setup System Module Type Configuration =====

There are 2 options when configuring the System Module Type: separate and combined

  'separate' - User and Performance functionality on separate servers.

  'combined' - User and Performance functionality on the same server

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

ここではcombindを選択しました。UM と PM が同じノードにインストールされる形になります。

```
Combined Server Installation will be performed.
The Server will be configured as a Performance Module.
All MariaDB ColumnStore Processes will run on the Performance Modules.

NOTE: The MariaDB ColumnStore Schema Sync feature will replicate all of the
      schemas and InnoDB tables across the User Module nodes. This feature can be enabled
      or disabled, for example, if you wish to configure your own replication post installation.

MariaDB ColumnStore Schema Sync feature is Enabled, do you want to leave enabled? [y,n] (y) > 
```

Schema Sync は通常有効にします。


```
NOTE: MariaDB ColumnStore Replication Feature is enabled

Enter System Name (columnstore-1) > 


===== Setup Storage Configuration =====


----- Setup Performance Module DBRoot Data Storage Mount Configuration -----

There are 3 options when configuring the storage: internal, external, or DataRedundancy

  'internal' -    This is specified when a local disk is used for the DBRoot storage.
                  High Availability Server Failover is not Supported in this mode

  'external' -    This is specified when the DBRoot directories are mounted.
                  High Availability Server Failover is Supported in this mode.

  'DataRedundancy' - This is specified when gluster is installed and you want
                  the DBRoot directories to be controlled by ColumnStore Data Redundancy.
                  High Availability Server Failover is Supported in this mode.
                  NOTE: glusterd service must be running and enabled on all PMs.

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

type of Data Storage の選択で 3=DataRedundancy を選択すると、GlusterFS を利用した冗長構成が組めます。

`/usr/local/mariadb/columnstore/bin/columnstoreAlias` にあるaliase設定を行うと、ColumnStore用クライアント`mcsmysql`と管理コマンドmcsadminがaliasとして設定されます。

```
===== Setup the Module Configuration =====


----- Performance Module Configuration -----

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

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

Enter Nic Interface #1 Host Name (cs11-pm1) > 
Enter Nic Interface #1 IP Address of cs11-pm1 (10.128.0.2) > 
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) > 

*** Performance Module #2 Configuration ***

Enter Nic Interface #1 Host Name (pm2) > 
Enter Nic Interface #1 IP Address of pm2 (10.128.0.3) > 
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) > 

*** Performance Module #3 Configuration ***

Enter Nic Interface #1 Host Name (pm3) > 
Enter Nic Interface #1 IP Address of pm3 (10.128.0.4) > 
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) > 
```

今回冗長構成にしますので、3PM構成にします。

```
===== System Installation =====

System Configuration is complete, System Installation is the next step.
Would you like to continue with the System Installation? [y,n] (y) > 
```

System Installation で y(es)を選択すると、PM1から他のPMにColumnStoreの各RPMがリモートインストールされます。

```
Performing a MariaDB ColumnStore System install using RPM packages
located in the /root directory.


Next step is to enter the password to access the other Servers.
This is either your password or you can default to using a ssh key
If using a password, the password needs to be the same on all Servers.

Enter password, hit 'enter' to default to using a ssh key, or 'exit' > 


===== Running the MariaDB ColumnStore MariaDB ColumnStore setup scripts =====

post-mysqld-install Successfully Completed
post-mysql-install Successfully Completed

----- Performing Install on 'pm2 / pm2' -----

Install log file is located here: /tmp/pm2_rpm_install.log


----- Performing Install on 'pm3 / pm3' -----

Install log file is located here: /tmp/pm3_rpm_install.log


MariaDB ColumnStore Package being installed, please wait ...  DONE
```

正常にPM2/PM3にインストールが行われた場合は上記のようなメッセージが表示されます。

```
===== Configuring MariaDB ColumnStore Data Redundancy Functionality =====

----- Setup Data Redundancy Copy Count Configuration -----

Setup the Number of Copies: This is the total number of copies of the data
in the system. At least 2, but not more than the number of PMs(3), are required.

Enter Number of Copies [2-3] (2) >  
```
Data Redundancy Copy Count をここでは 2 を選択します。

```
----- Setup Data Redundancy Network Configuration -----

  'existing'  -   This is specified when using previously configured network devices. (NIC Interface #1)
                  No additional network configuration is required with this option.

  'dedicated' -   This is specified when it is desired for Data Redundancy traffic to use
                  a separate network than one previously configured for ColumnStore.
                  You will be prompted to provide Hostname and IP information for each PM.


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

今回 NIC が一つのサーバでテストしていますので、1 を選択します。

```
----- Performing Data Redundancy Configuration -----

gluster peer probe 10.128.0.2
gluster peer probe 10.128.0.3
gluster peer probe 10.128.0.4
Gluster create and start volume dbroot1...DONE
Gluster create and start volume dbroot2...DONE
Gluster create and start volume dbroot3...DONE

----- Data Redundancy Configuration Complete -----
```

上記のように、GlusterFS の peer/volume 等の設定も自動で行われます。


GlusterFS の設定を確認するには以下のコマンドを実行します。

```
gluster peer status
gluster volume info
```



```
===== Checking MariaDB ColumnStore System Logging Functionality =====

The MariaDB ColumnStore system logging is setup and working on local server

===== MariaDB ColumnStore System Startup =====

System Installation is complete. If any part of the install failed,
the problem should be investigated and resolved before continuing.

package installed and the associated service started.

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

ここで、y を選択すると、ColumnStoreを起動します。

# mcsadmin

以下のコマンドでColumnStore管理ツール`mcsadmin`のヘルプが確認できます。mcsはMariadb ColumnStore の略です。

```
mcsadmin help
```

システムステータス取得


```
mcsadmin> getSystemStatus
getsystemstatus   Sat Sep 23 18:02:17 2017

System columnstore-1

System and Module statuses

Component     Status                       Last Status Change
------------  --------------------------   ------------------------
System        ACTIVE                       Sat Sep 23 18:02:01 2017

Module pm1    ACTIVE                       Sat Sep 23 18:01:29 2017
Module pm2    ACTIVE                       Sat Sep 23 18:01:24 2017
Module pm3    ACTIVE                       Sat Sep 23 18:01:24 2017

Active Parent OAM Performance Module is 'pm1'
Primary Front-End MariaDB ColumnStore Module is 'pm1'
MariaDB ColumnStore Replication Feature is enabled
```

プロセスステータス取得

```
mcsadmin> getProcessStatus
getprocessstatus   Sat Sep 23 18:02:59 2017

MariaDB ColumnStore Process statuses

Process             Module    Status            Last Status Change        Process ID
------------------  ------    ---------------   ------------------------  ----------
ProcessMonitor      pm1       ACTIVE            Sat Sep 23 17:59:57 2017       18033
ProcessManager      pm1       ACTIVE            Sat Sep 23 18:00:03 2017       18175
DBRMControllerNode  pm1       ACTIVE            Sat Sep 23 18:00:59 2017       19865
ServerMonitor       pm1       ACTIVE            Sat Sep 23 18:01:00 2017       19898
DBRMWorkerNode      pm1       ACTIVE            Sat Sep 23 18:01:01 2017       19967
DecomSvr            pm1       ACTIVE            Sat Sep 23 18:01:05 2017       20138
PrimProc            pm1       ACTIVE            Sat Sep 23 18:01:07 2017       20235
ExeMgr              pm1       ACTIVE            Sat Sep 23 18:01:19 2017       22003
WriteEngineServer   pm1       ACTIVE            Sat Sep 23 18:01:22 2017       22171
DDLProc             pm1       ACTIVE            Sat Sep 23 18:01:26 2017       22386
DMLProc             pm1       ACTIVE            Sat Sep 23 18:01:30 2017       22550
mysqld              pm1       ACTIVE            Sat Sep 23 18:01:36 2017       23131

ProcessMonitor      pm2       ACTIVE            Sat Sep 23 18:00:41 2017       16683
ProcessManager      pm2       HOT_STANDBY       Sat Sep 23 18:01:32 2017       18230
DBRMControllerNode  pm2       COLD_STANDBY      Sat Sep 23 18:01:15 2017
ServerMonitor       pm2       ACTIVE            Sat Sep 23 18:01:00 2017       17171
DBRMWorkerNode      pm2       ACTIVE            Sat Sep 23 18:01:03 2017       17227
DecomSvr            pm2       ACTIVE            Sat Sep 23 18:01:06 2017       17261
PrimProc            pm2       ACTIVE            Sat Sep 23 18:01:09 2017       17273
ExeMgr              pm2       ACTIVE            Sat Sep 23 18:01:19 2017       18108
WriteEngineServer   pm2       ACTIVE            Sat Sep 23 18:01:23 2017       18159
DDLProc             pm2       COLD_STANDBY      Sat Sep 23 18:01:24 2017
DMLProc             pm2       COLD_STANDBY      Sat Sep 23 18:01:24 2017
mysqld              pm2       ACTIVE            Sat Sep 23 18:01:51 2017       18550

ProcessMonitor      pm3       ACTIVE            Sat Sep 23 18:00:43 2017       14673
ProcessManager      pm3       COLD_STANDBY      Sat Sep 23 18:01:03 2017
DBRMControllerNode  pm3       COLD_STANDBY      Sat Sep 23 18:01:03 2017
ServerMonitor       pm3       ACTIVE            Sat Sep 23 18:01:07 2017       15182
DBRMWorkerNode      pm3       ACTIVE            Sat Sep 23 18:01:09 2017       15207
DecomSvr            pm3       ACTIVE            Sat Sep 23 18:01:12 2017       15220
PrimProc            pm3       ACTIVE            Sat Sep 23 18:01:15 2017       15252
ExeMgr              pm3       ACTIVE            Sat Sep 23 18:01:19 2017       15282
WriteEngineServer   pm3       ACTIVE            Sat Sep 23 18:01:23 2017       15303
DDLProc             pm3       COLD_STANDBY      Sat Sep 23 18:01:24 2017
DMLProc             pm3       COLD_STANDBY      Sat Sep 23 18:01:24 2017
mysqld              pm3       ACTIVE            Sat Sep 23 18:02:03 2017       15700
```



# ColumnStoreクライアント

```
[root@cs11-pm1 ~]# mcsmysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 10.2.10-MariaDB-log Columnstore 1.1.3-1

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+---------------------+
| Database            |
+---------------------+
| calpontsys          |
| columnstore_info    |
| infinidb_querystats |
| infinidb_vtable     |
| information_schema  |
| mysql               |
| performance_schema  |
| test                |
+---------------------+
```

# 文字コード設定

設定ファイル(my.cnf)は`/usr/local/mariadb/columnstore/mysql`にありますので、例えば文字コードをUTF8に設定する場合は以下のように設定します。

```/usr/local/mariadb/columnstore/mysql/my.cnf
[mysqld]
character-set-server=utf8
```

ColumnStoreを再起動するには、`mcsadmin`で`restartSystem`を実行します。

```
# mcsadmin restartSystem
```

# PM1 をシャットダウンしてみる

ここで、OAM(Operations, Administration and Maintenance) PMであるPM1をシャットダウンします。

```
# shutdown -h now
```

PM2で mcsadmin getSystemStatus を実行すると、以下のようなステータスとなり、

```
getsystemstatus   Sat Sep 23 18:13:48 2017

System columnstore-1

System and Module statuses

Component     Status                       Last Status Change
------------  --------------------------   ------------------------
System        ACTIVE                       Sat Sep 23 18:09:26 2017

Module pm1    AUTO_DISABLED/DEGRADED       Sat Sep 23 18:08:18 2017
Module pm2    ACTIVE                       Sat Sep 23 18:09:02 2017
Module pm3    ACTIVE                       Sat Sep 23 18:01:24 2017

Active Parent OAM Performance Module is 'pm2'
Primary Front-End MariaDB ColumnStore Module is 'pm2'
MariaDB ColumnStore Replication Feature is enabled
```

OAM PM が pm2 に引き継がれていることが確認できます。

pm2 で df コマンドを実行すると、

```
[root@cs11-pm2 ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
10.128.0.3:/dbroot2   30G  3.1G   27G  11% /usr/local/mariadb/columnstore/data2
10.128.0.3:/dbroot1   30G  3.1G   27G  11% /usr/local/mariadb/columnstore/data1
```
pm2にDBROOT1, DBROOT2がマウントされていることが確認できます。

pm3 では以下のようになります。

```
[root@cs11-pm3 ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
10.128.0.4:/dbroot3   30G  3.0G   27G  10% /usr/local/mariadb/columnstore/data3
```