Deploy an Enterprise HTAP Server with MariaDB Enterprise ColumnStore 1.5 and MariaDB Enterprise Server 10.5 on Ubuntu 20.04 LTS¶

Optimizing Linux Kernel Parameters¶

MariaDB ColumnStore performs best when certain Linux kernel parameters are optimized.

1. Set the relevant kernel parameters in a sysctl configuration file. For proper change management, it is recommended to set them in a ColumnStore-specific configuration file.

   For example, create a /etc/sysctl.d/90-mariadb-columnstore.conf file with the following contents:

   # 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
   
   # optimize Linux to cache directories and inodes
   vm.vfs_cache_pressure = 10
   
   # minimize swapping
   vm.swappiness = 10
   

2. Set the same kernel parameters at runtime using the sysctl command:

   $ sudo sysctl --load=/etc/sysctl.d/90-mariadb-columnstore.conf
   

Linux Security Module Considerations¶

It is recommended to disable the system's Linux Security Module (LSM) on each node during installation to avoid confusion and potential problems. The specific steps to disable the security module will depend on the platform.

In the Configuring the Linux Security Module section, we will configure the security module and restart it.

Disabling the Linux Security Module with AppArmor (Debian/Ubuntu/SLES)¶

Prior to installing MariaDB Columnstore, it is necessary to disable AppArmor:

1. Disable AppArmor:

   $ sudo systemctl disable apparmor
   

2. Reboot the system.

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

Character Encoding¶

When using MariaDB ColumnStore, it is recommended to set the system's locale to UTF-8.

1. Set the system's locale to en_US.UTF-8 by executing localedef:

   $ sudo localedef -i en_US -f UTF-8 en_US.UTF-8
   

Install via APT (Debian/Ubuntu)¶

1. Retrieve your Customer Download Token at https://customers.mariadb.com/downloads/token/ and substitute for customer_download_token in the following directions.

2. Configure the APT package repository.

   To configure APT package repositories:

   $ sudo apt install wget
   
   $ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
   
   $ echo "eeebe9e08dffb8a4e820cc0f673afe437621060129169ea3db0790eb649dbe9b 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
   

3. Install Python:

   $ sudo apt install python
   

4. Install MariaDB ColumnStore and package dependencies:

   $ sudo apt install mariadb-server \
       mariadb-plugin-columnstore
   

   Note that ColumnStore currently installs a x-columnstore.cnf configuration file that sets plugin_maturity to gamma. This means that other plugins with gamma maturity can be installed as well.

5. Install some optional dependencies for ColumnStore.

   On Debian 10 and Ubuntu 20.04, install the following:

   $ sudo apt install libjemalloc2
   

   Note that jemalloc is not required, but it improves performance.

6. Configure MariaDB ColumnStore.

   Installation only loads MariaDB ColumnStore to the system. MariaDB ColumnStore may require configuration and additional post-installation steps before the database server is ready for use.

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¶

1. Determine which system variables and options you need to configure.

   Mandatory system variables and options for HTAP include:

   System Variable/Option	Description
   server_id	Sets the numeric Server ID for this MariaDB Enterprise Server. Must be unique in the deployment.
   log_bin	Set this option to enable the Binary Log.
   binlog_format	Set this to STATEMENT for HTAP.
   log_slave_updates	Set this to OFF for HTAP.
   replicate_same_server_id	Set this to ON for HTAP.
   binlog_do_db	Set this to the name of the database that you want to replicate from InnoDB to ColumnStore.
   replicate_rewrite_db	Set this to the names of the InnoDB and ColumnStore databases in the format <innodb database>-><columnstore database>.
   replicate_wild_do_table	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
   innodb_buffer_pool_size	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
   relay_log	Sets the location for the Relay Log.

2. Choose a configuration file in which to configure your system variables and options.

   It not recommended to make custom changes to one of the bundled configuration files. Instead, it is recommended to 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, then 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

3. 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]
   
   # 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%
   

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 ColumnStore to use a different user account and password.

1. Configure the Cross Engine Join credentials using the mcsSetConfig command.

   For example, to configure 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
   

2. The cross_engine@127.0.0.1 user account needs to be created on the server after it has been started. This step is described in the Create the Cross Engine Join User section.

S3 Storage Manager¶

MariaDB ColumnStore supports using S3-compatible storage. If you want to use S3-compatible storage, then you need to configure it.

1. 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
   
   [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.

2. Ensure that the local cache path has sufficient store space to store the local cache.

Start the Server¶

The server and the ColumnStore processes can be started using the systemctl command.

1. Start the MariaDB Server process and configure it to start automatically:

   $ sudo systemctl start mariadb
   $ sudo systemctl enable mariadb
   

2. Start the MariaDB ColumnStore processes and configure them to start automatically:

   $ sudo systemctl start mariadb-columnstore
   $ sudo systemctl enable mariadb-columnstore
   

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 the user account must be granted the necessary privileges to access data.

1. Connect to the server using MariaDB Client using the root@localhost user account:

   $ sudo mariadb
   

2. Create the user account with the CREATE USER statement:

   CREATE USER 'cross_engine'@'127.0.0.1'
      IDENTIFIED BY "cross_engine_passwd";
   

3. Grant the user account SELECT privileges on all databases with the GRANT statement:

   GRANT SELECT ON *.*
      TO 'cross_engine'@'127.0.0.1';
   

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.

1. Connect to the server using MariaDB Client using the root@localhost user account:

   $ sudo mariadb
   

2. Create the user account with the CREATE USER statement:

   CREATE USER 'htap_replication'@'localhost'
      IDENTIFIED BY 'passwd';
   

3. Grant the user account REPLICATION SLAVE and the BINLOG MONITOR global privileges with the GRANT statement:

   GRANT REPLICATION SLAVE, BINLOG MONITOR ON *.*
      TO 'htap_replication'@'localhost';
   

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

1. Connect to the server using MariaDB Client using the root@localhost user account:

   $ sudo mariadb
   

2. 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='';
   

3. 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;
   

4. Start replication using the START REPLICA statement:

   START REPLICA;
   

5. Confirm that replication is working properly using the SHOW REPLICA STATUS statement:

   SHOW REPLICA STATUS;
   

Using HTAP Replication¶

1. Connect to the server using MariaDB Client using the root@localhost user account:

   $ sudo mariadb
   

2. Create the databases for the InnoDB and ColumnStore tables using the CREATE DATABASE statement:

   CREATE DATABASE columnstore_db;
   
   CREATE DATABASE innodb_db;
   

3. 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;
   

4. Confirm that the tables were replicated using the SHOW TABLES statement:

   SHOW TABLES
      FROM columnstore_db;
   +--------------------------+
   | Tables_in_columnstore_db |
   +--------------------------+
   | htap_test1               |
   | htap_test2               |
   +--------------------------+
   

5. 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;
   

6. 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);
   

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

8. Create an InnoDB table that will not be replicated:

   USE innodb_db;
   CREATE TABLE transactional_test1 (id INT) ENGINE = InnoDB;
   

9. Confirm that the table was not replicated:

   SHOW TABLES
      FROM columnstore_db
      LIKE 'transactional_%';
   Empty set (0.02 sec)
   

10. Create a ColumnStore table that will not be replicated:

    USE columnstore_db;
    CREATE TABLE analytical_test1 (id INT) ENGINE = ColumnStore;
    

11. Confirm that the table was not replicated:

    SHOW TABLES
       FROM innodb_db
       LIKE 'analytical_%';
    Empty set (0.02 sec)
    

Configuring the Linux Security Module¶

If you stopped the Linux Security Module (LSM) on each node during installation, you can restart the module and configure it on each node.

The specific steps to configure the security module depend on the platform.

Configuring the Linux Security Module with AppArmor (Debian/Ubuntu/SLES)¶

After installation, AppArmor can be properly configured to handle ColumnStore.

An AppArmor profile must be created for ColumnStore. For information on how to do that, see How to create an AppArmor Profile.

Checking Server Status¶

1. 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.5-3-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¶

1. 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.5-3-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)]>
   

2. Execute the SHOW REPLICA STATUS statement.

   This statement returns a lot of columns, but the important ones are Slave_IO_Running and Slave_SQL_Running. If both of those say Yes, 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
   

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.