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.

There is a MariaDB ColumnStore AMI that is based off of a CentOS 7 Amazon Instance that has the MariaDB ColumnStore 1.0.5 pre-installed. This can be used to run the MariaDB Columnstore in the Amazon EC2 Cloud Environment. It can be used to setup both a Single Node and a Multi-Node Install. 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.

The MariaDB ColumnStore AMI has installed on it the Amazon API Tool packages which allows the ColumnStore processes the ability to create EC2 instances and EBS storages. This also allows the functionality of being able to support the Instance Failure feature, meaning if a EC2 Performance Module instance goes offline, any EB2 storage on that instance would be re-attached to other instance so you would still have system functionality after a brief downtime during the outage discovery and failover action is completed. Also if an Instance Terminates, Columnstore will automatically create a new instance in its places.

These are the support configuration options: 1. Single-Node install with or with EBS Storages 2. Multi-Node Install with or with 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) c. ColumnStore install script (postConfigure) can create the additional Instances and EBS storage's that are used in the system. d. ColumnStore install script (postConfigure) supports using EC2 instances and/or EBS storage that the user has created.

The following sections document the Amazon environment setup and steps needed for the AMI installation.

AWS Security - Access and Secret Keys

If you are doing a 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:
  • We 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

4. Once the Instance has been Launch, use the 'connect' button to log into the Instance. Make sure you change the 'username' to 'mariadb', like so:

<code> ssh -i "key.pem" mariadb@xxxxx.us-west-2.compute.amazonaws.com </code>

Access to Launched AMI Instance

You will be logged into the instance as user 'mariadb'. Follow the instructions from the README file to continue to setup and install MariaDB Columnstore, which will be done as root user.

  • 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.
  • The install will be done as root user using the default root password that is noted in the /root/README file. But it is recommended after the system is installed, that you setup and use ssh-keys between all of the nodes and disable root user login access.

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/

If you are installing as a Mulit-Node system, use the following example of postConfigure This example shows setting up a system where postConfigure will create the additional Instances and EBS storage required.

[code] /usr/local/mariadb/columnstore/bin/postConfigure [/code]

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

Running postConfigure as root user:

/usr/local/mariadb/columnstore/bin/postConfigure

Running postConfigure as non-root user:

export COLUMNSTORE_INSTALL_DIR=/home/guest/mariadb/columnstore
export LD_LIBRARY_PATH=/home/guest/mariadb/columnstore/lib:/home/guest/mariadb/columnstore/mysql/lib/mysql
/home/guest/mariadb/columnstore/bin/postConfigure -i /home/guest/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) > <<creole>>**1**<</creole>>

Performing a Single Server Install. Enter System Name (columnstore-1) > mymcs1 <</code>> Notes: You should give this system a name that will appear in various Admin utilities, SNMP messages, etc. The name can be composed of any number of printable characters and spaces.

===== Setup High Availability 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 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) > <Enter>

Notes: Choosing internal and using softlinks to point to an externally mounted storage will allow you to use any format (i.e., ext2, ext3, etc.).

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

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

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

*** User Module #1 Configuration ***

Enter Nic Interface #1 Host Name (unassigned) > server-um1
Enter Nic Interface #1 IP Address of server-um1 (172.30.0.171) >
Enter Nic Interface #2 Host Name (unassigned) >


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

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

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

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

<<code>>

===== MariaDB ColumnStore SNMP-Trap Process Check =====

MariaDB ColumnStore is packaged with a SNMP-Trap Process. If the system where
MariaDB ColumnStore is being installed already has SNMP-Trap Process running, then
you have the option of disabling MariaDB ColumnStore's SNMP-Trap Process.
Additional information: Not having the MariaDB ColumnStore SNMP_trap Process will
affect the generation of MariaDB ColumnStore Alarms and associated SNMP Traps.
Please reference the MariaDB ColumnStore Installation Guide for Installs for
addition information.

MariaDB ColumnStore SNMP-Trap Process is enabled, would you like to disable it
(y,n) [n] > <Enter>

Notes: If you choose to disable (y) but still want the MariaDB ColumnStore snmp-traps to be generated and sent to a Network Management System, please see the “Error! Reference source not found.” section below.

===== Setup the Network Management System (NMS) Server Configuration =====

This would be used to receive SNMP Traps from MariaDB Columnstore.
0.0.0.0 defaults to not sending off the system

Enter IP Address(es) of NMS Server (0.0.0.0) > <Enter>

===== Performing Configuration Setup and MariaDB Columnstore Startup =====

NOTE: Setting 'NumBlocksPct' to 50%
            Setting 'TotalUmMemory' to 25% of total memory (Combined Server Install maximum value is 16G). 
             Value set to 4G

Notes: The default maximum for a single server is 16Gb.

Running the MariaDB Columnstore MySQL setup scripts

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

Starting MariaDB ColumnStore Database Platform

Starting MariaDB ColumnStore Database Platform, 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

. /usr/local/mariadb/columnstore/bin/columnstoreAlias

Enter 'mcsmysql' to access the MariaDB Columnstore MySQL 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.

The 2 settings are in the MariaDB Columnstore Configuration file, /usr/local/mariadb/columnstore/etc Columnstore.xml. These 2 settings are:

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