Wondering how to configure AWS mariadb columnstore? At Bobcares, with our Server Management Services, we can handle your server issues.

How to configure AWS mariadb columnstore?

MariaDB ColumnStore can use a Single Node or a Multi-Node system.

And it supports using both local storage and EBS storage’s.

These are the support configuration options:

1. Firstly, single-Node install with or without EBS Storages

2. Secondly, Multi-Node Install with or without EBS Storages

• Setup with User Module and Performance Module functionality on the same Instance(s)
• Setup with User Module and Performance Module functionality on different Instance(s)

Today, let us see the steps followed by support techs to configure AWS mariadb columnstore

New Installation and upgrades

The AMI is used for creating a new MariaDB ColumnStore.

It will either create the Instances and EBS storages needed during startup, or use ones that you have already created and want to utilize.

Amazon AWS-CLI Tool Set

The MariaDB ColumnStore AMI comes installed with the Amazon AWS-CLI Tool set package, version 1.11.36.

The Amazon AWS-CLI Tools provides the capability to the MariaDB ColumnStore to create EC2 Instances and EBS storages.

It allows allows for EC2 Instance/node failover, meaning if an EC2 Instance goes down or stops communicating, MariaDB ColumnStore will handle that problem keeping the system functional.

This might consist of re-attaching an EBS storage device from the problem 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.

Here is the EC2 API Tools commands that the MariaDB ColumnStore Software calls:

ec2-describe-instances
ec2-run-instances
ec2-terminate-instances
ec2-stop-instances
ec2-start-instances
ec2-associate-address
ec2-disassociate-address
ec2-create-volume
ec2-describe-volumes
ec2-detach-volume
ec2-attach-volume
ec2-delete-volume
ec2-create-tags

Features in the AMI using MariaDB ColumnStore install script (postConfigure) and mcsadmin

• Firstly, both can create the additional Instances and EBS storage’s that are used in the system. mcsadmin via the addModule/addDbroot commands.
• Secondly, both will allow user to enter EC2 Instance IDs and EBS Volume IDs, if you want to utilize AWS devices you have already created.
• Finally, you can also install via postConfigure and have the Amazon AMI tools functionality disabled.

To do this, you would enter ‘n’ at this prompt:

Do you want to have ColumnStore use the Amazon AWS CLI Tools [y,n] (y) >

Amazon AWS setup and AMI Launching

The MariaDB ColumnStore AMI Instance uses ‘iops’ EBS storage for improved performance over the standard storage type.

But it also means that based on the number of Instances/Module you plan to launch, you will need to check your account to make sure you have the permissions to create enough ‘iops’ storages.

Amazon does have a limit on this.

Recommend use Instance Type of “m4.4xlarge” or large. Need to have high performance network.

AWS Certifications Keys – 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 to either Provide an IAM role that has the Certifications Keys associated with it or you can update a file locally in the AMI Instance.

To add it in the local file, you will need to do the following once the AMI Instance is launched and you are logged in using the <USER> user.

# cd .aws
# mv credentials.template credentials
// edit the file and add in the your access and secret key that was created for your User account
   aws_access_key_id = xxxxxxxxxxxxxx
   aws_secret_access_key = xxxxxxxxxxx

These keys aren’t required for a single-node install.

AWS Management Console page

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.

NOTE: XXX.XXX.XXX.XXX is the VPC is the default subnet used in your account.

So you will need to put the VPC IP Address for your account. The IP Address can found via the VPC Dashboard

• SSH – Source = Your Public IP address
• SSH – AMI subnet XXX.XXX.XXX.XXX/16
• All ICMP
• Custom TCP rule – port range 8600-8800, subnet XXX.XXX.XXX.XXX/16
• MYSQL TCP (port 3306) – subnet XXX.XXX.XXX.XXX/16
• MYSQL TCP (port 3306) – Any other Public IP where you what to access the console 3. Select and Launch the MariaDB ColumnStore AMI:
• From AWS Console on the Instance page:
  • Press ‘Launch Instance’ button
  • Select “Community AMIs” and search for “MariaDB-ColumnStore-1.0.7” and select
  • Select Size : Recommend using Instance type of “m4.2xlarge” type to get optimal performance.
  • Press “Next: Configure Instance Details” Button
    • Select VPC/Subnet and optional provide an “IAM role”
  • Press “Next: Add Storage” button
  • Storage size = default is 100 gb, can increase if using local storage
  • Press “Next: Add Tags”
  • Value = Name of Instance, which is systemName-pm1. I.E. columnstore-1-pm1
  • Press “Next: Configure Security Group” button
  • Either select Security Group or create one with the ColumnStore setup
  • Press “Review and Launch” button
  • Select you Key Pair name and Launch Instance

Amazon AMI EC2 Instance user and password setup

The MariaDB ColumnStore AMI is setup to access and run from the <USER> account

It comes with ssh-keys setup and these used during the install process.

Access to Launched AMI Instance

Log into the instance as user <USER> or ‘mysql’. If the connection link has ‘root’ user, you will need to change that to <USER> or ‘mysql’. Here is an example:

Link from the AWS Console:

ssh -i "xxxxx.pem" root@xxxxxxxxxxx.us-west-2.compute.amazonaws.com

Change to on MariaDB ColumnStore 1.2.0 and before releases

ssh -i "xxxxx.pem" mariadb-user@xxxxxxxxxxx.us-west-2.compute.amazonaws.com

Change to on MariaDB ColumnStore 1.2.1 and later releases

ssh -i "xxxxx.pem" mysql@xxxxxxxxxxx.us-west-2.compute.amazonaws.com

MariaDB ColumnStore System installation

There are 2 methods for perform the System Configuration and Installation:

• Firstly, MariaDB ColumnStore One Step Quick Installer Script – Release 1.1.6 and later
• Secondly, MariaDB ColumnStore Post Install Script

MariaDB ColumnStore One Step Quick Installer Script, quick_installer_amazon.sh

MariaDB ColumnStore One Step Quick Installer Script, quick_installer_amazon.sh, is used to perform a simple 1 command run to perform the configuration and startup of MariaDB ColumnStore package on a Aamazon AMI system setup. Available in Release 1.1.6 and later.

The script has 4 parameters.

• –pm-count=x Number of pm instances to create
• –um-count=x Number of um instances to create, optional
• –system-name=nnnn System Name, optional

It will perform an install with these defaults:

• System-Name = columnstore-1, when not specified
• Multi-Server Install
  • with X Number of PMs when only PM IP count are provided
  • with X Number of UMs when UM IP count are provided and X Number of UMs when PM IP count are provided
• Storage – Internal
• DBRoot – 1 DBroot per 1 Performance Module
• Local Query is disabled on um/pm install
• MariaDB Replication is enabled

Running quick_installer_multi_server.sh help as non-root <USER> user

/home/<USER>/mariadb/columnstore/bin/quick_installer_amazon.sh --help
Usage ./quick_installer_amazon.sh [OPTION]

Quick Installer for an Amazon MariaDB ColumnStore Install
This requires to run on a MariaDB ColumnStore AMI

Performace Module (pm) number is required
User Module (um) number is option
When only pm counts provided, system is combined setup
When both pm/um counts provided, system is seperate setup

--pm-count=x Number of pm instances to create
--um-count=x Number of um instances to create, optional
--system-name=nnnn System Name, optional

Running quick_installer_multi_server.sh 1um/2pm setup as non-root <USER> user

$ ./mariadb/columnstore/bin/quick_installer_amazon.sh --pm-count=2 --um-count=1

NOTE: Performing a Multi-Server Seperate install with um and pm running on seperate servers

Run post-install script

The next steps are:

If installing on a pm1 node:

export COLUMNSTORE_INSTALL_DIR=/home/<USER>/mariadb/columnstore
export LD_LIBRARY_PATH=:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib
/home/<USER>/mariadb/columnstore/bin/postConfigure -i /home/<USER>/mariadb/columnstore -d

If installing on a non-pm1 using the non-distributed option:

export COLUMNSTORE_INSTALL_DIR=/home/<USER>/mariadb/columnstore
export LD_LIBRARY_PATH=:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib:/home/<USER>/mariadb/columnstore/lib:/home/<USER>/mariadb/columnstore/mysql/lib
/home/<USER>/mariadb/columnstore/bin/columnstore start

Run postConfigure script

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

IMPORTANT: This tool requires to run on the Performance Module #1

With the no-Prompting Option specify, you will require to have the following:

1. Root user ssh keys setup between all nodes in the system oruse the password commandline option.
2. Then, a Configure File to use to retrieve configure data, default to Columnstore.xml.rpmsave
or use the ‘-c’ option to point to a configuration file.

Quick Install Amazon Configuration

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

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

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

NOTE: Configured to have ColumnStore use the Amazon AWS CLI Tools

NOTE: MariaDB ColumnStore Replication Feature is enabled

Enter System Name (columnstore-1) >

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

—– 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) >

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

*** User Module #1 Configuration ***

Launched Instance for um1: i-0eac6a122c640e46c
Getting Private IP Address for Instance i-0eac6a122c640e46c, please wait…
Private IP Address of i-0eac6a122c640e46c is 172.31.33.235

—– Performance Module Configuration —–

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

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

EC2 Instance ID for pm1: i-0b309a68398cf9302
Getting Private IP Address for Instance i-0b309a68398cf9302, please wait…
Private IP Address of i-0b309a68398cf9302 is 172.31.44.176

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

*** Performance Module #2 Configuration ***

Launched Instance for pm2: i-088f06a60d267e517
Getting Private IP Address for Instance i-088f06a60d267e517, please wait…
Private IP Address of i-088f06a60d267e517 is 172.31.40.50

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

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’ >

System Installation

System Configuration is complete.

Performing System Installation.

Performing a MariaDB ColumnStore System install using a Binary package
located in the /home/<USER> directory.

—– Performing Install on ‘um1 / i-0eac6a122c640e46c’ —–

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

—– Performing Install on ‘pm2 / i-088f06a60d267e517’ —–

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 Startup =====

System Configuration is complete.
Performing System Installation.

—– Starting MariaDB ColumnStore on local server —–

MariaDB ColumnStore successfully started

MariaDB ColumnStore Database Platform Starting, please wait ……….. DONE

System Catalog Successfully Created

Run MariaDB ColumnStore Replication Setup.. DONE

MariaDB ColumnStore Install Successfully Completed, System is Active

Enter the following command to define MariaDB ColumnStore Alias Commands

. /etc/profile.d/columnstoreEnv.sh
. /etc/profile.d/columnstoreAlias.sh

Enter ‘mcsmysql’ to access the MariaDB ColumnStore SQL console
Enter ‘mcsadmin’ to access the MariaDB ColumnStore Admin console

NOTE: The MariaDB ColumnStore Alias Commands are in /etc/profile.d/columnstoreAlias.sh

MariaDB ColumnStore Post Install Script, postConfigure

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

The Instance that was launch 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.

[Searching for a solution to a different question? We are just a click away.]

Conclusion

In conclusion, our Support Engineers demonstrated how to configure AWS mariadb columnstore. Furthermore, we went through different causes and solutions for this specific error.