The Ultimate Guide to High Availability with MariaDB

Installing and Configuring a ColumnStore System using the Amazon AMI

You are viewing an old version of this article. View the current version here.

The MariaDB ColumnStore AMI is CentOS 7 based with MariaDB ColumnStore 1.0.5-1 installed. It also has the Amazon EC2 API Tools installed, which allows MariaDB ColumnStore to create EC2 Instances and EBS storages. EC2 Instance/node failover is supported, meaning if an EC2 Instance goes down or stops communicating, MariaDB ColumnStore will handle that problem keeping the system functional. This might consist of moving an EBS storage device from the problemed EC2 Performance Module to an another Performance Module in the system. Also if a EC2 Instance was to terminate, MariaDB ColumnStore will launch another EC2 Instance in its place.

MariaDB ColumnStore can be used a Single Node or a Multi-Node system. And it supports using both local storage and EBS storage's.

You will need to have an AWS account setup to access and use the MariaDB ColumnStore AMI, plus some basic knowledge of accessing and launching AMI's and Instances.

These are the support configuration options:

1. Single-Node install with or without EBS Storages 2. Multi-Node Install with or without EBS Storages a. Setup with User Module and Performance Module functionality on the same Instance(s) b. Setup with User Module and Performance Module functionality on different Instance(s)

Features in the AMI using MariaDB ColumnStore install script (postConfigure) and mcsadmin a. Both can create the additional Instances and EBS storage's that are used in the system. mcsadmin via the addmodule/addrbroot commands. b. Both will allow user to enter EC2 Instance IDs and EBS Volume IDs, if you want to utilize AWS devices you have already created. c. You can also install via postConfigure and have the Amazon AMI tools functionality disabled, meaning you can install like like a regular hardware setup by providing host-names and IP Addresses. 1. To do this, you would utilize the postConfigure command option of -da (Disable Amazon) during the install process:

                 /home/mariadb-user/mariadb/columnstore/bin/postConfigure -i /home/mariadb-user/mariadb/columnstore -da

AWS Security - Access and Secret Keys

If you are doing a Amazon Multi-Node install and you want the ColumnStore processes to utilize the Amazon API Tool, you will need these 2 keys and have them in 2 files, which will be installed on the AMI Instance. These keys aren't required for a single-node install.

link https://aws.amazon.com/developers/access-keys/

Cut-and-Paste those 2 keys into files, example:

  • access-key.pem
  • secret-key.pem

AWS Management Console page

1. Create and download a Key-Pair, this is used to log into the AMI Instance 2. Create a VPC Security Group with the following Inbound Rules:

  • SSH - Source = Your Public IP address
  • SSH - AMI subnet 172.30.0.0/16
  • All ICMP
  • Custom TCP rule - port range 8600-8800, subnet 172.30.0.0/16
  • MYSQL TCP (port 3306) - subnet 172.30.0.0/16
  • MYSQL TCP (port 3306) - Any other Public IP where you what to access the console 3. Select and Launch the MariaDB ColumnStore AMI:
  • Recommend using a minimum Instance type of "m4.2xlarge" type to get optimal performance.
  • Number of Instances = 1
  • Storage size = default is 100 gb, can be increased if using local storage
  • Value = Name of Instance, which is systemName-pm1. I.E. columnstore-1-pm1
  • Security Group = Select the one that was setup for Columnstore
  • Select you Key Pair name and Launch Instance

Amazon AMI EC2 Instance user and password setup

The MariaDB ColumnStore AMI is setup to be accessed and run from the user 'mariadb-user'. It comes with ssh-keys setup and these used during the install process.

MariaDB ColumnStore Amazon environment installation and setup

Access to Launched AMI Instance

Log into the instance as user 'mariadb-user'. If the connection link has 'root' user, you will need to change that to 'mariadb-user'. Here is an example:

Link from the AWS Console:

ssh -i "xxxxx.pem" [email protected]

Change to:

ssh -i "xxxxx.pem" [email protected]

Follow the instructions from the README file to setup and install MariaDB Columnstore

  • This will consist of scp'ing the access file and a secret key file onto the instance, if you plan to do a multi-node install.
  • Running the post-install script, then launching the postConfigure script

Setup and Install MariaDB ColumnStore

The Instance that was launched is ColumnStore Performance Module #1. This is the primary "pm" where the install process will always start. The install and configuration script, postConfigure is run to initiate the install process.

If you are installing as a Single-Node system, you can follow install instructions on how to run postConfigure in the follow document for root user:

link https://mariadb.com/kb/en/mariadb/installing-and-configuring-mariadb-columnstore/

The following is a transcript of a typical run of the MariaDB ColumnStore configuration script. Plain-text formatting indicates output from the script and bold text indicates responses to questions. After each question there is a short discussion of what the question is asking and what some typical answers might be. You will not see these discussions the running the actual configuration script.

Common Installation Examples During postConfigure, there are 2 questions that are asked where the answer given determines the path that postConfigure takes in configuring the system. Those 2 questions are as follows:

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

and

Select the Type of Module Install being performed:
1. Separate - User and Performance functionalities on separate servers
2. Combined - User and Performance functionalities on the same server
Enter Server Type ID [1-2] (1) >

The following examples illustrates some common configurations and helps to provide answers to the above questions:

  • Single Node - User and Performance running on 1 server - single / combined
  • Mutli-Node #1 - User and Performance running on some server - multi / combined
  • Mutli-Node #2 - User and Performance running on separate servers - multi / separate

Run the post-install script first:

./mariadb/columnstore/bin/postInstall --installdir=/home/mariadb-user/mariadb/columnstore

Followed by the commands that is outputted by the post-install script

export COLUMNSTORE_INSTALL_DIR=/home/mariadb-user/mariadb/columnstore
export LD_LIBRARY_PATH=/home/mariadb-user/mariadb/columnstore/lib:/home/mariadb-user/mariadb/columnstore/mysql/lib/mysql 
/home/mariadb-user/mariadb/columnstore/bin/postConfigure -i /home/mariadb-user/mariadb/columnstore

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) > <Enter>


===== 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) > 1

Separate Server Installation will be performed.

NOTE: Local Query Feature allows the ability to query data from a single Performance
      Module. Check MariaDB Columnstore Admin Guide for additional information.

Enable Local Query feature? [y,n] (n) > <Enter>

Amazon EC2 Install, these files will need to be installed on the local instance:

 1. File containing the Amazon Access Key
 2. File containing the Amazon Secret Key

Are these files installed and ready to continue [y,n] (y) > <Enter>

Enter file name containing the Access Key (unassigned) > /home/mariadb-user/access-key.pem
Enter file name containing the Secret Key (unassigned) > /home/mariadb-user/secret-key.pem

Notes: Provide the full directory path and the 2 files that contain the Access and Secret Keys that was downloaded before the start of the install. The above file names are just examples of what you could call them.

Enter Amazon Region you are running in (us-east-1) > xxxxxx

Notes: This is the region where you initially launched the AMI from the AWS console

Enter System Name (columnstore-1) > mycs1

Notes: You should give this system a name that will appear in various Admin utilities, etc. The name can be composed of any number of printable characters and spaces.

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

----- Setup User Module MariaDB Columnstore Data Storage Mount Configuration -----

There are 2 options when configuring the storage: internal and external

  'internal' -    This is specified when a local disk is used for the Data storage.

  'external' -    This is specified when the MariaDB Columnstore Data directory is externally mounted.

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

Notes: Enter 2 if you want to create a EBS storage for the mysql schema's to be stored.

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

There are 2 options when configuring the storage: internal or external

  '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.

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

Notes: Enter 2 if you want to create a EBS storage for the MariaDB ColumnStore DBRoot Data to be stored. When using option 2, this will allow this EBS volume to be reattached to other Performance Modules during failover situations

Enter EBS Volume Type (standard, gp2, io1) : (standard) > <Enter>

Enter EBS Volume storage size in GB: [1,16384] (100) > <Enter>

===== Setup Memory Configuration =====

NOTE: Setting 'NumBlocksPct' to 70%
      Setting 'TotalUmMemory' to 50%

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

Amazon Install: For Module Configuration, you have the option to provide the
existing Instance IDs or have the Instances created.
You will be prompted during the Module Configuration setup section.

----- User Module Configuration -----

Enter number of User Modules [1,1024] (1) > <Enter>

*** User Module #1 Configuration ***

Create Instance for um1 [y,n] (y) > <Enter>

Notes: By entering 'y', Instances will automatically be created. If there are Instances already created that you want to use, then enter 'n' and you will be prompted for the Instance ID.

Launched Instance for um1: i-05ab2990
Getting Private IP Address for Instance i-05ab2990, please wait...
Private IP Address of i-05ab2990 is 172.30.0.110

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

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

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

EC2 Instance ID for pm1: i-a3a82a36
Getting Private IP Address for Instance i-a3a82a36, please wait...
Private IP Address of i-a3a82a36 is 172.30.0.107

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

*** Setup External EBS Volume for DBRoot #1 ***

*** NOTE: You have the option to provide an
          existing EBS Volume ID or have a Volume created

Create a new EBS volume for DBRoot #1 ?  [y,n] (y) > <Enter>
  Create AWS Volume for DBRoot #1
  Formatting DBRoot #1, please wait...

Notes: By entering 'y', EBS Volumnes will automatically be created. If there are EBS Volumnes already created that you want to use, then enter 'n' and you will be prompted for the Volumne ID.

*** Performance Module #2 Configuration ***

Create Instance for pm2 [y,n] (y) > <Enter>

Launched Instance for pm2: i-1ca82a89
Getting Private IP Address for Instance i-1ca82a89, please wait...
Private IP Address of i-1ca82a89 is 172.30.0.238

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

*** Setup External EBS Volume for DBRoot #2 ***

*** NOTE: You have the option to provide an
          existing EBS Volume ID or have a Volume created

Create a new EBS volume for DBRoot #2 ?  [y,n] (y) > <Enter>
  Create AWS Volume for DBRoot #2
  Formatting DBRoot #2, please wait...

===== System Installation =====

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

Notes: By entering 'y', the other Instances will now be installed and setup.

Enter the Package Type being installed to other servers [rpm,deb,binary] (binary) > 
Performing an MariaDB Columnstore System install using a Binary package located in the /home/mariadb-user 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' > <Enter>

Notes: Hit Enter since ssh-keys are setup


----- Performing Install on 'um1 / i-05ab2990' -----

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


----- Performing Install on 'pm2 / i-1ca82a89' -----

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


MariaDB Columnstore Package being installed, please wait ...  DONE

===== Checking MariaDB Columnstore System Logging Functionality =====

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

MariaDB Columnstore System Configuration and Installation is Completed

===== MariaDB Columnstore System Startup =====

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

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

----- Starting MariaDB Columnstore on 'um1' -----

MariaDB Columnstore successfully started

----- Starting MariaDB Columnstore on 'pm2' -----

MariaDB Columnstore successfully started

----- Starting MariaDB Columnstore on local server -----

MariaDB Columnstore successfully started

MariaDB Columnstore Database Platform Starting, please wait ....... DONE

System Catalog Successfully Created

MariaDB Columnstore Install Successfully Completed, System is Active

Enter the following command to define MariaDB Columnstore Alias Commands

. /home/mariadb-user/mariadb/columnstore/bin/columnstoreAlias

Enter 'mcsmysql' to access the MariaDB Columnstore SQL console
Enter 'mcsadmin' to access the MariaDB Columnstore Admin console

MariaDB Columnstore Memory Configuration

During the installation process, postConfigure will set the 2 main Memory configuration settings based on the size of memory detected on the local node.

'NumBlocksPct'   - Performance Module Data cache memory setting

TotalUmMemory  - User Module memory setting, used as temporary memory for joins

On a system that has the Performance Module and User Module functionality combined on the same server, this is the default settings:

NumBlocksPct    - 50% of total memory

TotalUmMemory - 25% of total memory, default maximum the percentage equal to 16G

On a system that has the Performance Module and User Module functionality on different servers, this is the default settings:

NumBlocksPct    - This setting is NOT configured, and the default that the applications will then use is 70%

TotalUmMemory - 50% of total memory

The user can choose to change these settings after the install is completed, if for instance they want to setup more memory for Joins to utilize. On a single server or combined UM/PM server, it is recommended to not have the combination of these 2 settings over 75% of total memory

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.