Deploy an Enterprise HTAP Server with MariaDB Enterprise ColumnStore 5.5 and MariaDB Enterprise Server 10.5 on Ubuntu 20.04 LTS
Topics on This Page:
These instructions detail the deployment of MariaDB Enterprise ColumnStore 5.5 with MariaDB Enterprise Server 10.5 on Ubuntu 20.04 LTS in a HTAP Deployment configuration.
These instructions detail how to deploy a Hybrid Transactional/Analytical Processing (HTAP) solution, which is suited for a combined transactional and analytical workload.
MariaDB Platform Components
These instructions detail the deployment of the following MariaDB Platform components:
Component |
Description |
---|---|
|
|
|
MariaDB Enterprise Server Components
These instructions detail the deployment of the following MariaDB Enterprise Server components:
Component |
Description |
---|---|
|
|
|
|
|
Term Definitions
Term |
Definition |
---|---|
Columnar Database |
|
Row Database |
|
System Preparation
Systems hosting Enterprise ColumnStore deployments require some additional configuration prior to installation:
Optimize Linux Kernel Parameters
MariaDB Enterprise ColumnStore performs best when certain Linux kernel parameters are optimized.
Set the relevant kernel parameters in a sysctl configuration file. For proper change management, we recommend setting them in an Enterprise ColumnStore-specific configuration file.
For example, create a
/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf
file with the following contents:# minimize swapping vm.swappiness = 10 # optimize Linux to cache directories and inodes vm.vfs_cache_pressure = 10 # Increase the TCP max buffer size net.core.rmem_max = 16777216 net.core.wmem_max = 16777216 # Increase the 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 # for 1 GigE, increase this to 2500 # for 10 GigE, increase this to 30000 net.core.netdev_max_backlog = 2500
Set the same kernel parameters at runtime using the
sysctl
command:$ sudo sysctl --load=/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf
Disable the Linux Security Module
To avoid confusion and potential problems, we recommend disabling the system's Linux Security Module (LSM) during installation. The specific steps to disable the security module will depend on the platform.
In the Configure the Linux Security Module section, we will configure the security module and restart it.
Disable AppArmor (Debian/Ubuntu/SLES)
AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.
Disable AppArmor:
$ sudo systemctl disable apparmor
Reboot the system.
Confirm that no AppArmor profiles are loaded using
aa-status
:$ sudo aa-status
Example output:
apparmor module is loaded. 0 profiles are loaded. 0 profiles are in enforce mode. 0 profiles are in complain mode. 0 processes have profiles defined. 0 processes are in enforce mode. 0 processes are in complain mode. 0 processes are unconfined but have a profile defined.
Note
Remember to configure and re-enable AppArmor after the installation is complete.
Configure the Character Encoding
When using MariaDB Enterprise ColumnStore, it is recommended to set the system's locale to UTF-8.
Set the system's locale to
en_US.UTF-8
by executinglocaledef
:$ sudo localedef -i en_US -f UTF-8 en_US.UTF-8
S3-Compatible Storage
MariaDB Enterprise ColumnStore can use S3-compatible storage to store its data. However, this functionality is optional.
We would recommend S3-compatible storage for all Enterprise ColumnStore users, because it can provide many benefits:
Secure: Most S3-compatible storage is encrypted-at-rest.
Scalable: Most S3-compatible storage is highly optimized for read and write scaling.
Resilient: Most S3-compatible storage is very low maintenance and highly available, since it relies on resilient cloud infrastructure.
Economical: Most S3-compatible storage is very inexpensive.
When S3-compatible storage is used, Enterprise ColumnStore caches data locally to improve performance.
Create an S3 Bucket
If you want to use S3-compatible storage, it is important to create the S3 bucket before you start ColumnStore.
If you already have an S3 bucket, confirm that the bucket is empty.
We will configure Enterprise ColumnStore to use the S3 bucket later in the Configure the S3 Storage Manager section.
Note
The specific steps to create the S3 bucket will depend on what S3-compatible storage you are using.
Enterprise ColumnStore Installation
MariaDB Corporation provides a APT package repository for Ubuntu 20.04 LTS.
MariaDB Enterprise ColumnStore ships as a storage engine plugin for MariaDB Enterprise Server and a platform engine to handle back-end storage processes. MariaDB Enterprise Server 10.5 does not require any additional software to operate as a single-node analytics database.
Install Enterprise ColumnStore via APT (Debian/Ubuntu)
Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for
customer_download_token
in the following directions.Configure the APT package repository.
MariaDB Enterprise ColumnStore
5.5
is available on MariaDB Enterprise Server10.5
.To configure APT package repositories:
$ sudo apt install wget $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup $ echo "c78db828709d94876406a0ea346f13fbc38e73996795903f40e3c21385857dd4 mariadb_es_repo_setup" \ | sha256sum -c - $ chmod +x mariadb_es_repo_setup $ sudo ./mariadb_es_repo_setup --token="customer_download_token" --apply \ --mariadb-server-version="10.5" $ sudo apt update
Install some additional dependencies for Enterprise ColumnStore.
On Debian 10 and Ubuntu 20.04, install the following:
$ sudo apt install libjemalloc2
Install MariaDB Enterprise ColumnStore and package dependencies:
$ sudo apt install mariadb-server mariadb-backup \ libmariadb3 mariadb-client \ mariadb-plugin-columnstore
Configure MariaDB Enterprise ColumnStore.
Installation only loads MariaDB Enterprise ColumnStore to the system. MariaDB Enterprise ColumnStore requires configuration and additional post-installation steps before the database server is ready for use.
Enterprise ColumnStore Configuration
MariaDB Enterprise ColumnStore requires configuration after it is installed. The configuration file location depends on your operating system.
Enterprise Server Configuration
MariaDB Enterprise Server can be configured in the following ways:
System variables and options can be set in a configuration file (such as
/etc/my.cnf
). MariaDB Enterprise Server must be restarted to apply changes made to the configuration file.System variables and options can be set on the command-line.
If a system variable supports dynamic changes, then it can be set on-the-fly using the SET statement.
Configuration Files
MariaDB's packages include several bundled configuration files. It is also possible to create custom configuration files.
On Debian and Ubuntu, MariaDB's packages bundle the following configuration files:
/etc/mysql/my.cnf
/etc/mysql/mariadb.cnf
/etc/mysql/mariadb.conf.d/50-client.cnf
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
/etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf
/etc/mysql/mariadb.conf.d/50-server.cnf
/etc/mysql/mariadb.conf.d/60-galera.cnf
/etc/mysql/mariadb.conf.d/mariadb-enterprise.cnf
And on Debian and Ubuntu, custom configuration files from the following directories are read by default:
/etc/mysql/conf.d/
/etc/mysql/mariadb.conf.d/
Configuring MariaDB for Enterprise ColumnStore
Determine which system variables and options you need to configure.
Mandatory system variables and options for HTAP include:
System Variable/Option
Description
Set this system variable to
utf8
Set this system variable to
utf8_general_ci
Set this system variable to
ALWAYS
to always use cpimport for LOAD DATA INFILE and INSERT...SELECT statements.Sets the numeric Server ID for this MariaDB Enterprise Server. Must be unique in the deployment.
Set this option to enable the Binary Log.
Set this to
STATEMENT
for HTAP.Set this to
OFF
for HTAP.Set this to
ON
for HTAP.Set this to the name of the database that you want to replicate from InnoDB to ColumnStore.
Set this to the names of the InnoDB and ColumnStore databases in the format <innodb database>-><columnstore database>.
Set this to a pattern that matches the table names that you want to replicate from InnoDB to ColumnStore.
Useful system variables and options for MariaDB Enterprise Server include:
System Variable/Option
Description
Defines the amount of memory InnoDB reserves for the Buffer Pool. Set to no more than 16% of total memory on Servers that also run MariaDB ColumnStore.
Useful system variables and options for HTAP include:
System Variable/Option
Description
Sets the location for the Relay Log.
Choose a configuration file in which to configure your system variables and options.
We recommend not making custom changes to one of the bundled configuration files. Instead, create a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. If you want your custom configuration file to override the bundled configuration files, it is a good idea to prefix the custom configuration file's name with a string that will be sorted last, such as
z-
.On Debian and Ubuntu, a good custom configuration file would be:
/etc/mysql/mariadb.conf.d/z-custom-my.cnf
Set your system variables and options in the configuration file.
They need to be set in a group that will be read by mariadbd, such as
[mariadb]
or[server]
.For example:
[mariadb] log_error = mariadbd.err character_set_server = utf8 collation_server = utf8_general_ci columnstore_use_import_for_batchinsert = ALWAYS # Replication Configuration (HTAP Server) server_id = 1 log_bin = mariadb-bin binlog_format = STATEMENT log_slave_updates = OFF columnstore_replication_slave = ON # HTAP filtering rules # 1. The transactions are being # replicated from itself replicate_same_server_id = ON # 2. Only write queries that touch # innodb_db to the binary log binlog_do_db = innodb_db # 3. Rewrite innodb_db to columnstore_db # prior to applying transaction replicate_rewrite_db = innodb_db->columnstore_db # 4. Only replicate tables that begin with "htap" replicate_wild_do_table = columnstore_db.htap%
Configure Cross Engine Joins
When a cross engine join is executed, the ExeMgr process connects to the server using the root
user with no password by default. MariaDB Enterprise Server 10.5 will reject this login attempt by default. If you plan to use Cross Engine Joins, you need to configure Enterprise ColumnStore to use a different user account and password. These directions are for configuring the cross engine join user. Directions for creating the cross engine join user are in the Create the Cross Engine Join User section.
To configure cross engine joins, perform the following steps, use the mcsSetConfig command.
For example, to configure Enterprise ColumnStore to use the cross_engine
user account to connect to the server at 127.0.0.1
:
$ sudo mcsSetConfig CrossEngineSupport Host 127.0.0.1
$ sudo mcsSetConfig CrossEngineSupport Port 3306
$ sudo mcsSetConfig CrossEngineSupport User cross_engine
$ sudo mcsSetConfig CrossEngineSupport Password cross_engine_passwd
Note
Please choose a password that meets your organization's password policies. If your MariaDB Enterprise Server instance has a password validation plugin installed, then the password should also meet the configured requirements.
By default, MariaDB Enterprise Server installs the simple_password_check plugin. Its requirements are configured by the simple_password_check_digits, simple_password_check_letters_same_case, simple_password_check_minimal_length, and simple_password_check_other_characters system variables.
Configure the S3 Storage Manager
MariaDB Enterprise ColumnStore can use S3-compatible storage, but it is not required. S3-compatible storage must be configured before it can be used.
To configure Enterprise ColumnStore to use S3-compatible storage, edit /etc/columnstore/storagemanager.cnf
:
[ObjectStorage]
…
service = S3
…
[S3]
bucket = your_columnstore_bucket_name
endpoint = your_s3_endpoint
aws_access_key_id = your_s3_access_key_id
aws_secret_access_key = your_s3_secret_key
# iam_role_name = your_iam_role
# sts_region = your_sts_region
# sts_endpoint = your_sts_endpoint
[Cache]
cache_size = your_local_cache_size
path = your_local_cache_path
The default local cache size is 2 GB.
The default local cache path is
/var/lib/columnstore/storagemanager/cache
.Ensure that the local cache path has sufficient store space to store the local cache.
The
bucket
option must be set to the name of the bucket that you created in the Create an S3 Bucket step.To use an IAM role, you must also uncomment and set
iam_role_name
,sts_region
, andsts_endpoint
.
Start the Enterprise ColumnStore Processes
The Enterprise Server and Enterprise ColumnStore processes can be started using the systemctl
command. In case the processes were started during the installation process, use the restart command to ensure that the processes pick up the new configuration. Perform the following procedure.
Start the MariaDB Enterprise Server process and configure it to start automatically:
$ sudo systemctl restart mariadb $ sudo systemctl enable mariadb
Start the MariaDB Enterprise ColumnStore processes and configure them to start automatically:
$ sudo systemctl restart mariadb-columnstore $ sudo systemctl enable mariadb-columnstore
Create User Accounts
For HTAP deployments, a couple user accounts need to be created.
Create the Cross Engine Join User
The credentials for cross engine joins were previously configured in the Cross Engine Joins section. The user account must also be created and granted the necessary privileges to access data.
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Create the user account with the CREATE USER statement:
CREATE USER 'cross_engine'@'127.0.0.1' IDENTIFIED BY "cross_engine_passwd"; CREATE USER 'cross_engine'@'localhost' IDENTIFIED BY "cross_engine_passwd";
Note
Please choose the same user name and password that was configured in the Cross Engine Joins section.
Grant the user account
SELECT
privileges on all databases with the GRANT statement:GRANT SELECT ON *.* TO 'cross_engine'@'127.0.0.1'; GRANT SELECT ON *.* TO 'cross_engine'@'localhost';
Create the Replication User
The deployment requires MariaDB Replication, so the replication user account must be created, and the user account must be granted sufficient privileges to perform replication.
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Create the user account with the CREATE USER statement:
CREATE USER 'htap_replication'@'localhost' IDENTIFIED BY 'passwd';
Note
Please choose a password that meets your organization's password policies. If your MariaDB Enterprise Server instance has a password validation plugin installed, then the password should also meet the configured requirements.
By default, MariaDB Enterprise Server installs the simple_password_check plugin. Its requirements are configured by the simple_password_check_digits, simple_password_check_letters_same_case, simple_password_check_minimal_length, and simple_password_check_other_characters system variables.
Grant the user account
REPLICATION SLAVE
and theBINLOG MONITOR
global privileges with the GRANT statement:GRANT REPLICATION SLAVE, BINLOG MONITOR ON *.* TO 'htap_replication'@'localhost';
This user account will be used to configure replication in the Configure MariaDB Replication section.
Configure MariaDB Replication
The deployment requires MariaDB Replication, which must be configured.
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Set the GTID position by setting the gtid_slave_pos system variable.
If this is a new deployment, then it would be set to the empty string:
SET GLOBAL gtid_slave_pos='';
Use the CHANGE MASTER TO statement to configure the server to replicate from itself starting from this position:
CHANGE MASTER TO MASTER_HOST='localhost', MASTER_USER='htap_replication', MASTER_PASSWORD='passwd', MASTER_USE_GTID=slave_pos;
Start replication using the START REPLICA statement:
START REPLICA;
Confirm that replication is working properly using the SHOW REPLICA STATUS statement:
SHOW REPLICA STATUS;
Configure the Linux Security Module
If you stopped the Linux Security Module (LSM) during installation, you can restart the module and configure.
The specific steps to configure the security module depend on the platform.
Configure AppArmor (Debian/Ubuntu/SLES)
We disabled AppArmor in the Disable AppArmor section, but we have to create an AppArmor profile for Enterprise ColumnStore before re-enabling it. This will ensure that AppArmor does not interfere with Enterprise ColumnStore's functionality.
For information on how to create a profile, see How to create an AppArmor Profile on ubuntu.com.
Administration
Enterprise ColumnStore has several components. Each of those components needs to be administered.
Enterprise Server Administration
MariaDB Enterprise Server uses systemctl
to start and stop the server processes:
Operation |
Command |
Start |
|
Stop |
|
Restart |
|
Enable during startup |
|
Disable during startup |
|
Status |
|
Enterprise ColumnStore Administration
MariaDB Enterprise ColumnStore uses systemctl
to start and stop the ColumnStore processes:
Operation |
Command |
Start |
|
Stop |
|
Restart |
|
Enable during startup |
|
Disable during startup |
|
Status |
|
Testing
When you have MariaDB Enterprise ColumnStore up and running, you should test it to ensure that it is in working order and that there were not any issues during startup.
Using HTAP Replication
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Create the databases for the InnoDB and ColumnStore tables using the CREATE DATABASE statement:
CREATE DATABASE columnstore_db; CREATE DATABASE innodb_db;
Create the InnoDB versions of the HTAP tables using the CREATE TABLE statement:
USE innodb_db; CREATE TABLE htap_test1 (id INT) ENGINE = InnoDB; CREATE TABLE htap_test2 (id INT) ENGINE = InnoDB;
Confirm that the tables were replicated using the SHOW TABLES statement:
SHOW TABLES FROM columnstore_db;
+--------------------------+ | Tables_in_columnstore_db | +--------------------------+ | htap_test1 | | htap_test2 | +--------------------------+
The replication initially creates empty InnoDB tables, which need to be transformed into ColumnStore tables and which need to be populated with the initial copy of the data:
DROP TABLE IF EXISTS columnstore_db.htap_test1; CREATE TABLE columnstore_db.htap_test1 ENGINE=COLUMNSTORE SELECT * FROM innodb_db.htap_test1; DROP TABLE IF EXISTS columnstore_db.htap_test2; CREATE TABLE columnstore_db.htap_test2 ENGINE=COLUMNSTORE SELECT * FROM innodb_db.htap_test2;
Insert data into the InnoDB versions of the HTAP tables using the INSERT statement:
USE innodb_db; INSERT INTO htap_test1 VALUES (100); INSERT INTO htap_test2 VALUES (200);
Confirm that the data was replicated using the SELECT statement:
SELECT * FROM columnstore_db.htap_test1;
+------+ | id | +------+ | 100 | +------+
SELECT * FROM columnstore_db.htap_test2;
+------+ | id | +------+ | 200 | +------+
Create an InnoDB table that will not be replicated:
USE innodb_db; CREATE TABLE transactional_test1 (id INT) ENGINE = InnoDB;
Confirm that the table was not replicated:
SHOW TABLES FROM columnstore_db LIKE 'transactional_%';
Empty set (0.02 sec)
Create a ColumnStore table that will not be replicated:
USE columnstore_db; CREATE TABLE analytical_test1 (id INT) ENGINE = ColumnStore;
Confirm that the table was not replicated:
SHOW TABLES FROM innodb_db LIKE 'analytical_%';
Empty set (0.02 sec)
Checking Server Status
Connect to the HTAP server using the MariaDB Client using the
root@localhost
user account:$ sudo mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.5.8-5-MariaDB-Enterprise MariaDB Enterprise Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Checking Replication Status
Connect to the HTAP server using the MariaDB Client using the
root@localhost
user account:$ sudo mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 38 Server version: 10.5.8-5-MariaDB-Enterprise MariaDB Enterprise Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
Execute the SHOW REPLICA STATUS statement.
This statement returns a lot of columns, but the important ones are
Slave_IO_Running
andSlave_SQL_Running
. If both of those sayYes
, then replication is running correctly.SHOW REPLICA STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: htap_replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 695 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 996 Relay_Master_Log_File: mariadb-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: columnstore_db.htap% Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 695 Relay_Log_Space: 1306 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1-7 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 1 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 1
Limitations
Cross-Database Queries
This implementation relies on replicate_rewrite_db, so it does not support cross-database queries with statement-based replication.
For example, if the replicated database is selected by the USE, then the query will replicate properly:
USE innodb_db;
INSERT INTO htap_test1
VALUES (100);
SELECT * FROM columnstore_db.htap_test1;
+------+
| id |
+------+
| 100 |
+------+
However, if the replicated database is not selected, and it is instead prefixed the table name in the query, then the query will not replicate properly:
USE columnstore_db;
INSERT INTO innodb_db.htap_test1
VALUES (200);
SELECT * FROM columnstore_db.htap_test1;
+------+
| id |
+------+
| 100 |
+------+
Semi-Synchronous Replication
This implementation has not been tested with semi-synchronous replication.
Parallel Replication
This implementation has not been tested with parallel replication.
Row-Based Replication
This implementation requires the binlog_format system variable to be set to STATEMENT
. Row-based replication is not currently supported.