Preparing for ColumnStore Installation
With the 1.0.4 beta version of MariaDB ColumnStore, there should be no versions of MariaDB Server or MySQL pre-installed on the OS before a MariaDB ColumnStore binary or RPM is installed on the system. If you have an installation of MariaDB server, uninstall it before proceeding.
- Package dependencies
- Choosing the type of initial download/install
- Root user installs
- Non-root user installs
Before installing MariaDB ColumnStore, there is some preparation necessary. You will need to determine the following, refer the MariaDB ColumnStore Architecture Document for additional information.
- How many User Modules (UMs) will your system need?
- How many Performance Modules (PMs) will your system need?
- How much disk space will your system need?
MariaDB ColumnStore is certified to run on:
RHEL/CentOS v6, v7
Ubuntu 16.04 LTS
but it should run on any recent Linux system.
Make sure the same OS is installed on all the servers for a multi-node system.
Make sure the locale setting on all servers are all the same.
To set locale to en_US and UTf-8, run:
# localedef -i en_US -f UTF-8 en_US.UTF-8
System administration information
Information your system administrator must provide you before you start installing MariaDB ColumnStore:
- The hostnames of each interface on each node (optional).
- The IP address of each interface on each node.
- The root/non-root password for the nodes (all nodes must have the same root/non-root password or root/non-root ssh keys must be set up between servers). MariaDB ColumnStore can be installed as root or a non-root user.
Example for 3 PM, 1UM system, these are the steps required to configure PM-1 for passwordless ssh. The equivalent steps must be repeated on every PM in the system.
[root@pm- 1 ~] $ ssh-keygen [root@pm- 1 ~] $ ssh-copy- id -i ~/.ssh/id_rsa.pub pm- 1 [root@pm- 1 ~] $ ssh-copy- id -i ~/.ssh/id_rsa.pub pm- 2 [root@pm- 1 ~] $ ssh-copy- id -i ~/.ssh/id_rsa.pub pm- 3 [root@pm- 1 ~] $ ssh-copy- id -i ~/.ssh/id_rsa.pub um- 1
MariaDB ColumnStore is quite flexible regarding networking. Some options are as follows:
- The interconnect between UMs and PMs can be one or more private VLANs. In this case MariaDB ColumnStore will automatically trunk the individual LANs together to provide greater effective bandwidth between the UMs and PMs.
- The PMs do not require a public LAN access as they only need to communicate with the UMs.
- The UMs most likely require at least one public interface to access the MySQL server front end from the site LAN. This interface can be a separate physical or logical connection from the PM interconnect.
- You can use whatever security your site requires on the public access to the MySQL server front end on the UMs. By default it is listening on port 3306.
- MariaDB ColumnStore software only requires a TCP/IP stack to be present to function. You can use any physical layer you desire.
MariaDB ColumnStore port usage
The MariaDB ColumnStore daemon utilizes port 3306.
You must reserve the following ports to run the MariaDB ColumnStore software: 8600 - 8622, 8700, and 8800
Database files (DBRoots)
As a general rule of thumb, the number of DBRoots must be equal to or greater than the maximum number of planned PMs.
Local database files
If you are using local disk to store the database files, no additional setup is required.
SAN mounted database files
If you are using a SAN to store the database files, the following must be taken into account:
- Each of these DBRoots must be a separate, mountable partition/directory
- MariaDB ColumnStore will run on most Linux filesystems, but we test most heavily with EXT2. You should have no problems with EXT3 or EXT4, but the journaling in these filesystems can be expensive for a database application. You should carefully evaluate the write characteristics of your chosen filesystem to make sure they meet your specific business needs. In any event, MariaDB ColumnStore writes relatively few, very large (64MB) files. You should consult with your Linux system administrator to see if configuring a larger bytes-per-inode setting than the default is available in your chosen filesystem.
- The fstab file must be set up (/etc/fstab). These entries would need to be added to each PM pointing to the all the dbroot(s) being used on all PMs. The 'noauto' option indicates that all dbroots will be associated to every PM but will not be automatically mounted at server startup. The associated dbroots that are assigned to each PM will be specifically mounted to that PM at InfiniDB startup.
The following example shows an /etc/fstab setup for 4 dbroots total for all PMs, but they can setup any disk type they want:
/dev/sda1 /usr/local/mariadb/columnstore/data1 ext2 noatime,nodiratime,noauto 0 0 /dev/sdd1 /usr/local/mariadb/columnstore/data2 ext2 noatime,nodiratime,noauto 0 0 /dev/sde1 /usr/local/mariadb/columnstore/data3 ext2 noatime,nodiratime,noauto 0 0 /dev/sdg1 /usr/local/mariadb/columnstore/data4 ext2 noatime,nodiratime,noauto 0 0
Performance optimization considerations
There are optimizations that should be made when using MariaDB ColumnStore listed below. As always, please consult with your network administrator for additional optimization considerations for your specific installation needs.
GbE NIC settings:
- Modify /etc/rc.d/rc.local to include the following:
/sbin/ifconfig eth0 txqueuelen 10000
- Modify /etc/sysctl.conf for the following:
# increase TCP max buffer size net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 # increase Linux autotuning TCP buffer limits # min, default, and max number of bytes to use net.ipv4.tcp_rmem = 4096 87380 16777216 net.ipv4.tcp_wmem = 4096 65536 16777216 # don't cache ssthresh from previous connection net.ipv4.tcp_no_metrics_save = 1 # recommended to increase this for 1000 BT or higher net.core.netdev_max_backlog = 2500 # for 10 GigE, use this net.core.netdev_max_backlog = 30000
- Cache memory settings:
Modify /proc/sys/vm/vfs_cache_pressure to ‘10’
You should disable any local firewalls and SELinux on mutli-node installs
You must be a Root user.
CentOS 7 and systems using systemctl
#systemctl status firewalld #systemctl stop firewalld #systemctl disable firewalld
Non CentOS 7
#service iptables save (Will save your existing IPTable Rules) #service iptables stop (It will disable firewall Temporarly)
To Disable it Permanentely:
#chkconfig iptables off
To disable SELinux,
edit file "/etc/selinux/config" and find line; SELINUX=enforcing Now replace it with, SELINUX=disabled
MariaDB ColumnStore requires that the boost package of 1.53 or newer is installed.
For Centos 7 and Ubuntu 16 and other newer OS's, you can just install the boost packages via yum or apt-get.
# yum -y install boost
# apt-get -y install libboost-all-dev
For Centos 6 OS's, you will need to install the boost source of 1.55 and build it to generate the required libraries.
NOTE: This means that the "Development Tools" group and build packages install be done prior to this, if they aren't already install
# yum -y groupinstall "Development Tools" # yum -y install cmake
Here is the procedure to download and build the boost source:
# cd /usr/ # wget http://sourceforge.net/projects/boost/files/boost/1.55.0/boost_1_55_0.tar.gz # tar zxvf boost_1_55_0.tar.gz # cd boost_1_55_0 # ./bootstrap.sh --with-libraries=atomic,date_time,exception,filesystem,iostreams,locale,program_options,regex,signals,system,test,thread,timer,log --prefix=/usr # ./b2 install
Make sure these packages are installed on the nodes where the MariaDB ColumnStore packages will be installed:
# yum -y install expect perl perl-DBI openssl zlib
# apt-get -y install expect perl openssl file sudo libdbi-perl libreadline-dev
Choosing the type of initial download/install
Root user installs
Initial download/install of MariaDB ColumnStore RPMs
- Install MariaDB ColumnStore as user root:
Note: MariaDB ColumnStore installation will install with a single MariaDB userid of root with no password. You may setup users and permissions for a MariaDB ColumnStore-Mysql account just as you would in MySQL.
Note: The packages will be installed into /usr/local. This is required for root user installs
Download the package mariadb-columnstore-release#.x86_64.tar.gz (RHEL5 64-BIT) to the server where you are installing MariaDB ColumnStore and place in the /root directory.
Unpack the tarball, which will generate multiple RPMs that will reside in the
tar -zxf mariadb-columnstore-release#.x86_64.tar
Install the RPMs. The MariaDB ColumnStore software will be installed in /usr/local/.
rpm -ivh mariadb-columnstore*release#*.rpm
Initial download/install of MariaDB ColumnStore binary package
Install MariaDB ColumnStore as user root on the server designated as PM1: Note: You may setup users and permissions for an MariaDB ColumnStore account just as you would in MariaDB.
For root user installs, MariaDB Columnstore needs to run in /usr/local. You can either install directly into /usr/local or install elsewhere and then setup a softlink to /usr/local. Here is an example of setting up a soft-link if you install the binary package in /mnt/mariadb
# ln -s /mnt/mariadb /usr/local
- Download the package into the /root/ and copy to /usr/local directory to the server where you are installing MariaDB ColumnStore.
cp /root/mariadb-columnstore-release#.x86_64.bin.tar.gz /usr/local/ mariadb-columnstore-release#.x86_64.bin.tar.gz
- Unpack the tarball, which will generate the /usr/local/ directory.
tar -zxvf mariadb-columnstore-release#.x86_64.bin.tar.gz
Run the post-install script:
Initial download/install of MariaDB ColumnStore DEB package
DEB package installs are not supported in the current version, but there is an Ubuntu 16.04 binary package that you can use to install. Just follow the binary package instructions above
Install MariaDB ColumnStore on a Debian or Ubuntu OS as user root: Note: You may setup users and permissions for an MariaDB ColumnStore account just as you would in MariaDB.
- Download the package mariadb-columnstore-release#.amd64.deb.tar.gz
(DEB 64- BIT) into the /root directory of the server where you are installing MariaDB ColumnStore.
- Unpack the tarball, which will generate DEBs.
tar -zxf mariadb-columnstore-release#.amd64.deb.tar.gz
- Install the MariaDB ColumnStore DEBs. The MariaDB ColumnStore software will be installed in /usr/
dpkg -i mariadb-columnstore*release#*.deb
Non-root user installs
MariaDB Columnstore can be installed to run as a non-root user using the binary tar file installation. These procedures will also allow you to change the installation from the default install directory into a user-specified directory. These procedures will need to be run on all the MariaDB ColumnStore Servers.
For the purpose of these instructions, the following assumptions are:
- Non-root user "guest" is used in this example
- Installation directory is /home/guest/mariadb/columnstore
- Create the non-root user (by root user)
- Update sudo configuration (by root user)
- Modify fstab if using SAN Mounted files (by root user)
- Uninstall existing MariaDB Columnstore installation if needed (by root user)
- Update permissions on certain directories that MariaDB Columnstore writes (by root user)
- Set up defaults file
- MariaDB Columnstore Installation (by non-root user)
- Enable MariaDB Columnstore to start automatically at boot time
Creation of the non-root user (by root user) Before beginning the binary tar file installation you will need your system administrator to set up accounts for you on every MariaDB Columnstore node. The account name must be the same on every node. The password used must be the same on every node. If you subsequently change the password on one node, you must change it on every node. The user-id must be the same on every node as well. In the examples below we will use the account name 'guest' and the password 'mariadb'. Additionally, every node must have a basic Linux server package setup and additionally have expect (and all its dependencies) installed.
- create new user
adduser guest -u 1000
The value for user-id must be the same for all nodes.
- Assign password to newly created user
- Log in as user guest
su - guest
- Choose an installation directory in which the non-root user has full read-write access. The
installation directory must be the same on every node. In the examples below we will use the path
On each host add the following to your startup environment (.bashrc, .profile, etc.)
export COLUMNSTORE_INSTALL_DIR=$HOME/mariadb/columnstore export PATH=$COLUMNSTORE_INSTALL_DIR/bin:$COLUMNSTORE_INSTALL_DIR/mysql/bin:/usr/sbin:$PATH export LD_LIBRARY_PATH=$COLUMNSTORE_INSTALL_DIR/lib:$COLUMNSTORE_INSTALL_DIR/mysql/lib/mysql
Note that these commands must be available to non-interactive shells. Once changes have been made, verify by running 'ssh user@host env' to ensure these values are displayed.
You must log off and log back in for these environment variables to be effective.
Update sudo configuration (by root user) The sudo configuration file on each node will need to be modified to add in the non-root user. The recommended way is to use the Unix command, visudo. The following example will add the ‘guest’ user: visudo
- Add the following line for the non-root user:
guest ALL=(ALL) NOPASSWD: ALL
- Comment out the following line, which will allow the user to login without 'tty':
Modify fstab if using SAN Mounted Database Files (by root user) If you are using a SAN to store the database files, an ‘users‘ option will need to be added to the fstab entries (by the root user). For more information, please see the “SAN Mounted Database Files” section earlier in this guide.
/dev/sda1 /home/guest/mariadb/columnstore/data1 ext2 noatime,nodiratime,noauto,users 0 0
/dev/sdd1 /home/mariadb/columnstore/data2 ext2 noatime,nodiratime,noauto,users 0 0
The disk device being used will need to have its user permissions set to the non-root user name. This is an
example command run as 'root' user setting the user ownership of dbroot /dev/sda1 to non-root user of
mke2fs dbroot (i.e., /dev/sda1) mount /dev/sda1 /tmpdir chown -R infinidb.infinidb /tmpdir umount /tmpdir
- Uninstall existing MariaDB Columnstore installation, if needed (by root user)
If MariaDB Columnstore has ever before been installed on any of the planned hosts as a root user install, you must have the system administrator verify that no remnants of that installation exist. The non-root installation will not be successful if there are MariaDB Columnstore files owned by root on any of the hosts.
- Verify the MariaDB Columnstore installation directory does not exist:
The /usr/local/mariadb/columnstore directory should not exist at all unless it is your target directory, in which case it must be completely empty and owned by the non-root user.
- Verify the /etc/fstab entries are correct for the new installation.
- Verify the /etc/default/columnstore directory does not exist.
- Verify the /var/lock/subsys/mysql-Columnstore file does not exist.
- Verify the /tmp/StopColumnstore file does not exist.
There should not be any files or directories owned by root in the /tmp directory
MariaDB Columnstore installation (by non-root user) You should be familiar with the general MariaDB Columnstore installation instructions in this guide as you will be askedthe same questions during installation.
- Log in as non-root user ( guest , in our example) Note: Ensure you are at your home directory before proceeding to the next step
- Now place the MariaDB Columnstore binary tar file in your home directory on the host you will be using as PM1. Untar the binary distribution package to the /home/guest directory: tar -xf mariadb-columnstore-release#.x86_64.bin.tar.gz
- Run post installation:
- Run the 3 command lines that were outputted by the previous post-install command, which would look like the following. See the “MariDB Columnstore Configuration” in this guide for more information:
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
a. When prompted for package type, enter 'binary' Enter the Package Type being installed to other servers [rpm,deb,binary] (rpm) > binary
b. When prompted for password, enter the non-user account password OR just hit enter if you have setup the non-root user with password-less ssh keys on all nodes (Please see the “System Administration Information” section earlier in this guide for more information on ssh keys.) Set up Defaults file (by root user) Set up the Defaults file for each MariaDB Columnstore server:
- cp /home/guest/mariadb/columnstore/bin/columnstore.def /etc/default/columnstore
(this is a rename of the file)
In this default file, change the installation directory entry ( COLUMNSTORE_INSTALL_DIR ) to /home/guest/mariadb/columnstore
Post-installation (by root user)
Optional items to assist in MariaDB Columnstore auto-start and logging:
- To configure MariaDB Columnstore to start automatically at boot time, perform the following steps in each InfiniDB server:
- Add the following to the /etc/rc.local file: su - guest –c '/home/guest/mariadb/columnstore/bin/columnstore start' Note: Make sure the above entry is added to the rc.local file that gets executed at boot time. Depending on the OS installation, rc.local could be in a different location.
- MariaDB Columnstore will setup and log using your current system logging application in the directory
/var/log/mariadb/columnstore. Perform the following if you want to setup to have the MariaDB Columnstore logs archived daily and deleted after 7 defaults (default setting):
- cp /home/guest/mariadb/columnstore/bin/columnstoreLogRotate /etc/logrotate.d/columnstore (this is a rename of the file)
The next step would be to run the install script postConfigure, check the Single Server Or Multi-Server Install guide.