Deploy an Enterprise HTAP Deployment with MariaDB Enterprise ColumnStore 1.4, MariaDB Enterprise Server 10.4, and MariaDB MaxScale 2.5 on Ubuntu 20.04 LTS

These instructions detail the deployment of MariaDB Enterprise Server 10.4, MariaDB Enterprise ColumnStore 1.4, and MariaDB MaxScale 2.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 10.4

  • It is a 100% Open Source modern SQL database.

MariaDB Enterprise ColumnStore 1.4

  • It is a columnar storage engine that provides distributed, columnar storage for scalable analytical processing and smart transactions.

  • It is the analytical component of MariaDB's single stack Hybrid Transactional/Analytical Processing (HTAP) solution.

MariaDB MaxScale 2.5

  • MariaDB MaxScale is an advanced database proxy, firewall, and query router.

MariaDB Enterprise Server Components

These instructions detail the deployment of the following MariaDB Enterprise Server components:

Component

Description

ColumnStore

  • It is a columnar storage engine that provides distributed, columnar storage for scalable analytical processing and smart transactions.

  • It is the analytical component of MariaDB's single stack Hybrid Transactional/Analytical Processing (HTAP) solution.

  • It is available as a plugin in MariaDB Enterprise Server 10.4.

InnoDB

MariaDB Replication

  • It allows data to be replicated from a primary server to one or more replica servers.

  • It supports asynchronous and semi-synchronous replication.

MaxScale Components

These instructions detail the deployment of MaxScale with the following components:

Component

Description

MariaDB Monitor (mariadbmon)

Read Connection Router (readconnroute)

  • It provides simple and lightweight load balancing of connections across a set of servers.

  • It works best for use cases where each client connection needs a consistent connection to a single back-end server, and where queries can be executed on any back-end server.

  • It can be configured to route connections to any running server, any primary server, or any replica server.

  • If connections are routed to non-primary servers, the application should only use read-only queries to maintain consistency of data.

Binlog Filter (binlogfilter)

Term Definitions

Term

Definition

MaxScale instance

columnar database

  • A database where the columns of each row are stored separately.

  • Best suited for analytical and OLAP workloads.

  • Also known as a "column-oriented database".

row database

  • A database where all columns of each row are stored together.

  • Best suited for transactional and OLTP workloads.

  • Also known as a "row-oriented database".

System Preparation

MariaDB ColumnStore deployments may require some additional configurations prior to installation.

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
    

Installation

MariaDB Corporation provides a APT package repository for Ubuntu 20.04 LTS.

Install MariaDB Enterprise Server and MariaDB ColumnStore on a separate system from MariaDB MaxScale.

Within these instructions, we use these IP addresses:

IP Address

Component

192.0.2.1

MariaDB MaxScale

192.0.2.2

MariaDB Enterprise Server with MariaDB ColumnStore

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 the APT package repositories on each system:

    $ 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.4" --mariadb-maxscale-version="2.5"
    
    $ sudo apt update
    
  3. Install the MariaDB MaxScale packages on the system to host the MaxScale Instance.

    To install MariaDB MaxScale and package dependencies:

    $ sudo apt install maxscale
    
  4. Install MariaDB Enterprise Server and MariaDB ColumnStore on the system to host the Server.

    To install MariaDB ColumnStore and package dependencies:

    $ sudo apt install mariadb-server \
        mariadb-columnstore-platform mariadb-plugin-columnstore
    
  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 and MaxScale.

    Installation only loads MariaDB Enterprise Server and MaxScale to the system. MariaDB Enterprise Server and MaxScale require configuration before they are ready for use.

Configuration

HTAP deployment configuration separates into configuring MariaDB Enterprise Server to handle replication and MariaDB ColumnStore and configuring MariaDB MaxScale to route database queries to Server and route binary log events from an InnoDB table to a ColumnStore table.

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.

    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
    

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.4 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 in the /etc/columnstore/Columnstore.xml file.

    The credentials are set in the modifying the child elements of the <CrossEngineSupport> element.

    For example, to configure ColumnStore to use the cross_engine user account to connect to the server at 127.0.0.1:

    <CrossEngineSupport>
       <Host>127.0.0.1</Host>
       <Port>3306</Port>
       <User>cross_engine</User>
       <Password>cross_engine_passwd</Password>
    </CrossEngineSupport>
    

    Warning

    Editing the Columnstore.xml is dangerous and can have unexpected results. Do not edit this configuration unless you specifically need support for Cross Engine Joins.

  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.

MaxScale Configuration

MariaDB MaxScale's parameters can be configured in two ways:

  • All parameters can be set in a configuration file (such as the /etc/maxscale.cnf file). When the configuration file is updated, the MaxScale instance must be restarted to apply the changes.

  • If a parameter supports dynamic changes, then it can be set on-the-fly using the MaxCtrl utility.

Configuring MaxScale's Global Parameters

  1. Determine which global parameters you need to configure.

    Useful global parameters for MariaDB MaxScale:

    Parameter

    Description

    local_address

    Sets the local IP address or network interface to use when connecting to MariaDB Enterprise Servers.

    admin_enabled

    Sets whether the MaxScale Instance supports the REST API. Disable to block access.

    admin_host

    Sets the network interface used by the REST API.

    admin_port

    Sets the port used by the REST API.

    admin_auth

    Sets whether the REST API uses HTTP Basic Access authentication. Users can be created wih maxctrl.

  2. Set your global parameters in maxscale.cnf.

    MariaDB MaxScale's global parameters, which apply to the whole instance, are set in the [maxscale] configuration group:

    For example:

    [maxscale]
    admin_auth    = true
    

Configuring Servers in MaxScale

  1. Determine which server parameters you need to configure.

    Mandatory parameters for Server objects:

    Parameter

    Description

    type

    Set the module type to server.

    address

    Set the IP address for the MariaDB Enterprise Server.

    port

    Set the port the MariaDB Enterprise Server listens on.

    protocol

    Set the protocol to MariaDBBackend to connect the Server.

  2. Set your server parameters in maxscale.cnf.

    For each Server in your deployment, add a new uniquely-named configuration group to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like serverN or the server's host name.

    For example:

    [server1]
    type     = server
    address  = 192.0.2.2
    port     = 3306
    protocol = MariaDBBackend
    

Configuring MariaDB Monitor in MaxScale

  1. Determine which parameters for MariaDB Monitor (mariadbmon) you need to configure.

    Mandatory parameters for MariaDB Monitor (mariadbmon):

    Parameter

    Description

    type

    Set the type to monitor.

    module

    Set to mariadbmon for MariaDB Replication.

    servers

    Set to a comma-separated list of the Server object names.

    user

    Set to the user MariaDB MaxScale uses to connect to the Servers.

    password

    Set to the password MariaDB MaxScale uses to authenticate on the Servers.

  2. Set your MariaDB Monitor parameters in maxscale.cnf.

    Add a new uniquely-named configuration group for the monitor to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like repl-monitor.

    For example:

    [repl-monitor]
    type             = monitor
    module           = mariadbmon
    servers          = server1
    monitor_interval = 1000ms
    user             = repl
    password         = repl_passwd
    

Configuring the Binlog Filter in MaxScale

  1. Determine which parameters for Binlog Filter (binlogfilter) you need to configure.

    Commonly used parameters for Binlog Filter (binlogfilter):

    Parameter

    Description

    type

    Set the module type to filter to indicate filter configuration.

    module

    Use the binlogfilter module.

    match

    Regular Expression indicating the tables you want to replicate. In the example, the orders table replicates.

    rewrite_src

    Regular Expression indicating the database you want to replicate data from.

    rewrite_dest

    Replacement string for the database the filter replicates into.

  2. Set your Binlog Filter parameters in maxscale.cnf.

    Add a new uniquely-named configuration group for the filter to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like repl-filter.

    For example:

    [repl-filter]
    type         = filter
    module       = binlogfilter
    match        = /[.]orders/
    rewrite_src  = innodb
    rewrite_dest = columnstore
    

    In the example configuration, the Binary Log Filter would replicate writes to the innodb_schema.orders table into the columnstore_schema.orders table.

Configuring Read Connection Router for Replication in MaxScale

  1. Determine which parameters for Read Connection Router (readconnroute) you need to configure for the router handling replication.

    Mandatory parameters for Read Connection Router (readconnroute):

    Parameter

    Description

    type

    Set the module type as service.

    router

    Use to set the type of router you want to use for the service. For the Read Connection Router, set to readconnroute.

    servers

    Set as a comma-separated list of the Server object names.

    user

    Set the user you want the MaxScale Instance to use when connecting to Servers.

    password

    Set the password you want the MaxScale Instance to use in authentication when conecting to Servers.

    filters

    For the replication routing service, configure it to use the repl-filter as configured above.

  2. Set your Read Connection Router parameters in maxscale.cnf.

    Add a new uniquely-named configuration group for the router to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like repl-router.

    For example:

    [repl-router]
    type     = service
    router   = readconnroute
    servers  = server1
    user     = repl
    password = repl_passwd
    filters  = repl-filter
    

    In the example configuration, this router service is associated with the filter that we created in the previous section.

Configuring the Replication Router's Listener in MaxScale

  1. Determine which parameters for the listener you need to configure.

    Mandatory parameters for the listener:

    Parameter

    Description

    type

    Set the module type as a listener.

    service

    Use this parameter to connect the listener to a configured routing service.

    protocol

    Set this parameter to MariaDBClient to handle incoming client connections.

    port

    Set this parameter to specify the port you want the MaxScale Instance to listen on.

  2. Set your listener parameters in maxscale.cnf.

    Add a new uniquely-named configuration group for the listener to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like repl-router-listener.

    For example:

    [repl-router-listener]
    type     = listener
    service  = repl-router
    protocol = MariaDBClient
    port     = 3309
    

Configuring Read Connection Router for Clients in MaxScale

  1. Determine which parameters for Read Connection Router (readconnroute) you need to configure for the router handling client connections.

    Mandatory parameters for Read Connection Router (readconnroute):

    Parameter

    Description

    type

    Set the module type as service.

    router

    Use to set the type of router you want to use for the service. For the Read Connection Router, set to readconnroute.

    servers

    Set as a comma-separated list of the Server object names.

    user

    Set the user you want the MaxScale Instance to use when connecting to Servers.

    password

    Set the password you want the MaxScale Instance to use in authentication when conecting to Servers.

  2. Set your Read Connection Router parameters in maxscale.cnf.

    Add a new uniquely-named configuration group for the router to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like connection-router.

    For example:

    [connection-router]
    type     = service
    router   = readconnroute
    servers  = server1
    user     = maxscale
    password = max_passwd
    

Configuring the Client Router's Listener in MaxScale

  1. Determine which parameters for the listener you need to configure.

    Mandatory parameters for the listener:

    Parameter

    Description

    type

    Set the module type as a listener.

    service

    Use this parameter to connect the listener to a configured routing service.

    protocol

    Set this parameter to MariaDBClient to handle incoming client connections.

    port

    Set this parameter to specify the port you want the MaxScale Instance to listen on.

  2. Set your listener parameters in maxscale.cnf.

    Add a new uniquely-named configuration group for the listener to the MaxScale Instance. The name of the configuration group is arbitrary, but it cannot contain any white space. Use whatever terms you find most convenient, but it is most common to use names like connection-router-listener.

    For example:

    [connection-router-listener]
    type     = listener
    service  = connection-router
    protocol = MariaDBClient
    port     = 3306
    

Post-Installation

Installation of MariaDB ColumnStore and Enterprise Server packages provides the necessary software to run the Server as a ColumnStore Instance, but additional steps are required to configure the ColumnStore storage back-end.

Stop the Server

MariaDB ColumnStore requires that you stop MariaDB Enterprise Server before running the post-installation scripts.

  1. Stop the service:

    $ sudo systemctl stop mariadb
    
  2. Disable the service, so that it does not start up automatically:

    $ sudo systemctl disable mariadb
    

Post-Installation Script

  1. Run the columnstore-post-install script to provision the system to host the storage back-end:

    $ sudo columnstore-post-install
    

Post-Configuration Script

MariaDB ColumnStore provides a post-configuration script to configure the ColumnStore Instance.

  1. Run the postConfigure script on the Server:

    $ sudo postConfigure -qs
    
  2. When prompted, select "single" for a single-node deployment.

  3. When prompted, set the system name.

  4. When prompted, select "internal" to store data on the local file system.

  5. Once postConfigure has the information it needs, it starts MariaDB ColumnStore.

Restart the System

  1. Use mcsadmin restartSystem to restart MariaDB ColumnStore to clear the cache:

    $ sudo mcsadmin restartSystem y
    

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 Client Router User

  1. Connect to each back-end MariaDB server using MariaDB Client using the root@localhost user account:

    $ sudo mariadb
    
  2. On each back-end MariaDB server, create the user account used by the monitor and the router using the CREATE USER statement. Make sure that the user account can connect from the MaxScale instance's IP address:

    CREATE USER maxscale@192.0.2.1
       IDENTIFIED BY 'max_passwd';
    
  3. On each back-end MariaDB server, grant the required privileges to the user account used by the monitor and the router using the GRANT statement.

    For the Read Connection Router (readconnroute), the global SHOW DATABASES privilege and the SELECT privilege on several of the system's privilege tables are required:

    GRANT SHOW DATABASES ON *.*
         TO maxscale@192.0.2.1;
    GRANT SELECT ON mysql.columns_priv
         TO maxscale@192.0.2.1;
    GRANT SELECT ON mysql.db
         TO maxscale@192.0.2.1;
    GRANT SELECT ON mysql.proxies_priv
         TO maxscale@192.0.2.1;
    GRANT SELECT ON mysql.roles_mapping
         TO maxscale@192.0.2.1;
    GRANT SELECT ON mysql.tables_priv
         TO maxscale@192.0.2.1;
    GRANT SELECT ON mysql.user
         TO maxscale@192.0.2.1;
    

    For MariaDB Monitor (mariadbmon), the global REPLICATION CLIENT privilege is sufficient for the monitor's basic functionality. The global SUPER, RELOAD, PROCESS, SHOW DATABASES, and EVENT privileges are also required for the monitor's automatic failover and automatic rejoin functionality:

    GRANT SUPER, REPLICATION CLIENT,
         RELOAD, PROCESS, SHOW DATABASES,
         EVENT ON *.*
         TO maxscale@192.0.2.1;
    

Create the Replication Router User

  1. Connect to each back-end MariaDB server using MariaDB Client using the root@localhost user account:

    $ sudo mariadb
    
  2. On each back-end MariaDB server, create the user account used by the monitor and the router using the CREATE USER statement. Make sure that the user account can connect from the MaxScale instance's IP address:

    CREATE USER repl@'%'
       IDENTIFIED BY 'repl_passwd';
    
  3. On each back-end MariaDB server, grant the required privileges to the user account used by the monitor and the router using the GRANT statement.

    For the Read Connection Router (readconnroute), the global SHOW DATABASES privilege and the SELECT privilege on several of the system's privilege tables are required:

    GRANT SHOW DATABASES ON *.*
         TO repl@'%';
    GRANT SELECT ON mysql.columns_priv
         TO repl@'%';
    GRANT SELECT ON mysql.db
         TO repl@'%';
    GRANT SELECT ON mysql.proxies_priv
         TO repl@'%';
    GRANT SELECT ON mysql.roles_mapping
         TO repl@'%';
    GRANT SELECT ON mysql.tables_priv
         TO repl@'%';
    GRANT SELECT ON mysql.user
         TO repl@'%';
    

    For MariaDB Monitor (mariadbmon), the global REPLICATION SLAVE and REPLICATION CLIENT privileges are sufficient for the monitor's basic functionality:

    GRANT REPLICATION SLAVE, REPLICATION CLIENT
       ON *.*
       TO repl@'%';
    

Start the MaxScale Instance

The MaxScale processes can be started using the systemctl command.

  1. Start the service:

    $ sudo systemctl start maxscale
    
  2. Configure the service to start automatically:

    $ sudo systemctl enable maxscale
    

Replication Configuration

The deployment requires MariaDB Replication, which must be configured.

In the HTAP deployment, the MariaDB Enterprise Server treats MariaDB MaxScale as a Primary Server. The Replica Server process connects to the MaxScale Instance where it's routed back to the Server to retrieve binary log events. Those writes are then filtered, changing the database name before they replicate back to the same Server on a different database.

  1. Connect to the back-end MariaDB 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. Configure the Server to replicate from MariaDB MaxScale by using the CHANGE MASTER TO statement:

    CHANGE MASTER TO
       MASTER_USER = "repl",
       MASTER_HOST = "192.0.2.1",
       MASTER_PORT = 3309,
       MASTER_PASSWORD = "repl_passwd",
       MASTER_USE_GTID = slave_pos;
    

    Note the MASTER_HOST option is set to the IP address of the MaxScale Instance. The MASTER_PORT option is set to the replication-listener port.

  4. Start replication using the START SLAVE statement:

    START SLAVE;
    

    The Server connects to the Binary Log Router on the MaxScale Instance. Writes in InnoDB are then routed through this service back to the Server and written in the MariaDB ColumnStore table.

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

    SHOW SLAVE STATUS;
    

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.

Administration

Each system in the HTAP deployment has its own administration method.

MariaDB ColumnStore Administration

MariaDB ColumnStore includes an administrative utility called mcsadmin, which you can use to start and stop the ColumnStore processes:

Operation

Command

Start

mcsadmin startSystem

Stop

mcsadmin shutdownSystem

Restart

mcsadmin restartSystem

Status

mcsadmin getSystemStatus

MaxScale Administration

MariaDB MaxScale installations includes configuration to start, stop, restart, enable/disable on boot, and check the status of the MaxScale Instance using the operating system default process management system.

For distributions that use systemd (most supported OSes), you can manage the MaxScale process using the systemctl command:

Operation

Command

Start

sudo systemctl start maxscale

Stop

sudo systemctl stop maxscale

Restart

sudo systemctl restart maxscale

Enable during startup

sudo systemctl enable maxscale

Disable during startup

sudo systemctl disable maxscale

Status

sudo systemctl status maxscale

Testing

When you have the ColumnStore and MaxScale instances running with replication ready, you should test each feature to ensure that it is running as expected and that there weren't any issues during startup.

Checking MaxScale Status

  1. Check that MaxScale is running properly by using the MaxCtrl utility:

    $ sudo maxctrl show maxscale
    
    ┌──────────────┬──────────────────────────────────────────────────────────────────────┐
    │ Version      │                                                               │
    ├──────────────┼──────────────────────────────────────────────────────────────────────┤
    │ Commit       │ 61b8bbf7f63c38ca9c408674e66f3627a0b2192e                             │
    ├──────────────┼──────────────────────────────────────────────────────────────────────┤
    │ Started At   │ Fri, 03 Jan 2020 18:05:18 GMT                                        │
    ├──────────────┼──────────────────────────────────────────────────────────────────────┤
    │ Activated At │ Fri, 03 Jan 2020 18:05:18 GMT                                        │
    ├──────────────┼──────────────────────────────────────────────────────────────────────┤
    │ Uptime       │ 109                                                                  │
    ├──────────────┼──────────────────────────────────────────────────────────────────────┤
    │ Parameters   │ {                                                                    │
    │              │     "libdir": "/usr/lib/x86_64-linux-gnu/maxscale",                  │
    │              │     "datadir": "/var/lib/maxscale",                                  │
    │              │     "process_datadir": "/var/lib/maxscale/data3850",                 │
    │              │     "cachedir": "/var/cache/maxscale",                               │
    │              │     "configdir": "/etc",                                             │
    │              │     "config_persistdir": "/var/lib/maxscale/maxscale.cnf.d",         │
    │              │     "module_configdir": "/etc/maxscale.modules.d",                   │
    │              │     "piddir": "/var/run/maxscale",                                   │
    │              │     "logdir": "/var/log/maxscale",                                   │
    │              │     "langdir": "/var/lib/maxscale",                                  │
    │              │     "execdir": "/usr/bin",                                           │
    │              │     "connector_plugindir": "/usr/lib/x86_64-linux-gnu/mysql/plugin", │
    │              │     "threads": 1,                                                    │
    │              │     "thread_stack_size": 8388608,                                    │
    │              │     "writeq_high_water": 0,                                          │
    │              │     "writeq_low_water": 0,                                           │
    │              │     "auth_connect_timeout": 3,                                       │
    │              │     "auth_read_timeout": 1,                                          │
    │              │     "auth_write_timeout": 2,                                         │
    │              │     "skip_permission_checks": false,                                 │
    │              │     "admin_auth": true,                                              │
    │              │     "admin_enabled": true,                                           │
    │              │     "admin_log_auth_failures": true,                                 │
    │              │     "admin_host": "127.0.0.1",                                       │
    │              │     "admin_port": 8989,                                              │
    │              │     "admin_ssl_key": "",                                             │
    │              │     "admin_ssl_cert": "",                                            │
    │              │     "admin_ssl_ca_cert": "",                                         │
    │              │     "admin_pam_readwrite_service": "",                               │
    │              │     "admin_pam_readonly_service": "",                                │
    │              │     "passive": false,                                                │
    │              │     "query_classifier": "",                                          │
    │              │     "query_classifier_cache_size": 155008819,                        │
    │              │     "retain_last_statements": 0,                                     │
    │              │     "dump_last_statements": "never",                                 │
    │              │     "session_trace": 0,                                              │
    │              │     "load_persisted_configs": true,                                  │
    │              │     "max_auth_errors_until_block": 10                                │
    │              │ }                                                                    │
    └──────────────┴──────────────────────────────────────────────────────────────────────┘
    

Listing MaxScale Services

  1. List MaxScale's services by using the MaxCtrl utility to execute the list services command:

    $ sudo maxctrl list services
    
    ┌────────────────────┬───────────────┬─────────────┬───────────────────┬─────────┐
    │ Service            │ Router        │ Connections │ Total Connections │ Servers │
    ├────────────────────┼───────────────┼─────────────┼───────────────────┼─────────┤
    │ connection-router  │ readconnroute │ 0           │ 5                 │ server1 │
    ├────────────────────┼───────────────┼─────────────┼───────────────────┼─────────┤
    │ repl-router        │ readconnroute │ 1           │ 4                 │ server1 │
    └────────────────────┴───────────────┴─────────────┴───────────────────┴─────────┘
    

Checking ColumnStore Status

  1. Execute the mcsadmin getSystemStatus command:

    $ sudo mcsadmin getSystemStatus
    getsystemstatus   Sun Jan 12 21:27:02 2020
    
    System columnstore-1
    
    System and Module statuses
    
    Component     Status                       Last Status Change
    ------------  --------------------------   ------------------------
    System        ACTIVE                       Sat Jan 11 23:46:14 2020
    
    Module pm1    ACTIVE                       Sat Jan 11 23:46:12 2020
    

Connecting to the Server

Connections to the HTAP deployment should route through port 3306 on the MaxScale Instance.

  1. Connect to the server using MariaDB Client:

    $ mariadb -udb_user -p -h 192.0.2.1
    Password>
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 38
    Server version: 10.4.14-8-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)]>
    

    Remember in managing users and privileges you need to duplicate CREATE USER and GRANT statements to include the MaxScale Instance host.

Checking Replication Status

In this HTAP deployment, MariaDB Enterprise Server uses MariaDB Replication through the Binary Log Router on the MaxScale Instance to replicate writes from an InnoDB table to a MariaDB ColumnStore table.

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

    $ mariadb -udb_user -p -h 192.0.2.1
    Password>
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MariaDB connection id is 38
    Server version: 10.4.14-8-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 SLAVE 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 SLAVE STATUS;
    

Testing Replication

The HTAP deployment as configured replicates data from an innodb_schema.orders table to the columnstore_schema.orders table. To test replication, first create these tables on the Server:

  1. Create the databases with the CREATE DATABASE statement:

    CREATE DATABASE IF NOT EXISTS columnstore_schema;
    CREATE DATABASE IF NOT EXISTS innodb_schema;
    
  2. Create the tables with the CREATE TABLE statement:

    CREATE TABLE IF NOT EXISTS columnstore_schema.orders (
       id INT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(50),
       gender VARCHAR(50),
       ip_address VARCHAR(20)
    ) ENGINE=ColumnStore;
    
    CREATE TABLE IF NOT EXISTS innodb_schema.orders (
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(50),
       gender VARCHAR(50),
       ip_address VARCHAR(20)
    ) ENGINE=InnoDB;
    
  3. Insert some data into the InnoDB table with the INSERT statement:

    INSERT INTO innodb_schema.orders (first_name, last_name, email, gender, ip_address)
    VALUES
       ("Walker", "Percy", "w.percy@example.edu", "Male", "192.0.2.100"),
       ("Howard", "Lovecraft", "h.lovecraft@example.com", "Male", "192.0.2.29"),
       ("William", "Faulkner", "faulkner@example.com", "Male", "192.0.2.59"),
       ("Ernest", "Hemingway", "ernest.hemingway@example.edu", "Male", "192.0.2.15"),
       ("Scott", "Fitzgerald", "fsfitzgerald@example.edu", "Male", "192.0.2.9");
    
  4. Confirm that the data was replicated to the ColumnStore table properly with the SELECT statement:

    SELECT * FROM columnstore_schema.orders;
    
    +----+------------+------------+------------------------------+----------+---------------+
    | id | first_name | last_name  | email                        | gender   | ip_address    |
    +----+------------+------------+------------------------------+----------+---------------+
    |  1 | Walker     | Percy      | w.percy@example.edu          | Male     | 192.0.2.100   |
    |  2 | Howard     | Lovecraft  | h.lovecraft@example.com      | Male     | 192.0.2.29    |
    |  3 | William    | Faulkner   | faulkner@example.com         | Male     | 192.0.2.59    |
    |  4 | Ernest     | Hemingway  | ernest.hemingway@example.edu | Male     | 192.0.2.15    |
    |  5 | Scott      | Fitzgerald | fsfitzgerald@example.edu     | Male     | 192.0.2.9     |
    +----+------------+------------+------------------------------+----------+---------------+
    

    The replica I/O thread running on the Server read changes in its own Binary Log through the replication router on MariaDB MaxScale, it then filtered the changes to the new schema name and applied them on the MariaDB ColumnStore table.

    By issuing queries to update the table or queries requiring real-time data to the InnoDB table and issuing analytical processing queries to the MariaDB ColumnStore table, you can now perform hybrid transactional-analytical processing.