This page details step 1 of the 4-step procedure "Deploy HTAP Topology".
This step prepares the system to host MariaDB Enterprise Server and MariaDB Enterprise ColumnStore 23.10.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
MariaDB Enterprise ColumnStore performs best with Linux kernel optimizations.
To optimize the kernel:
Set the relevant kernel parameters in a sysctl configuration file. To ensure proper change management, use an Enterprise ColumnStore-specific configuration file.
Create a /etc/sysctl.d/90-mariadb-enterprise-columnstore.conf file:
Use the sysctl command to set the kernel parameters at runtime
The Linux Security Modules (LSM) should be temporarily disabled on each Enterprise ColumnStore node during installation.
The LSM will be configured and re-enabled later in this deployment procedure.
The steps to disable the LSM depend on the specific LSM used by the operating system.
SELinux must be set to permissive mode before installing MariaDB Enterprise ColumnStore.
To set SELinux to permissive mode:
Set SELinux to permissive mode:
Set SELinux to permissive mode by setting SELINUX=permissive in /etc/selinux/config.
For example, the file will usually look like this after the change:
Confirm that SELinux is in permissive mode:
SELinux will be configured and re-enabled later in this deployment procedure. This configuration is not persistent. If you restart the server before configuring and re-enabling SELinux later in the deployment procedure, you must reset the enforcement to permissive mode.
AppArmor must be disabled before installing MariaDB Enterprise ColumnStore.
Disable AppArmor:
Reboot the system.
Confirm that no AppArmor profiles are loaded using aa-status:
AppArmor will be configured and re-enabled later in this deployment procedure.
When using MariaDB Enterprise ColumnStore, it is recommended to set the system's locale to UTF-8.
On RHEL 8, install additional dependencies:
Set the system's locale to en_US.UTF-8 by executing localedef:
With the HTAP topology, 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.
S3 bucket configuration will be performed later in this procedure.
Navigation in the procedure "Deploy HTAP Topology".
This page was step 1 of 4.
Next: Step 2: Install MariaDB Enterprise Server.
# minimize swapping
vm.swappiness = 1
# 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$ sudo sysctl --load=/etc/sysctl.d/90-mariadb-enterprise-columnstore.conf$ sudo setenforce permissive# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted$ sudo getenforce
Permissive$ sudo systemctl disable apparmor$ sudo aa-status
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.$ sudo yum install glibc-locale-source glibc-langpack-en$ sudo localedef -i en_US -f UTF-8 en_US.UTF-8Enterprise Server 10.5
Enterprise Server 10.6
Enterprise Server 11.4
Single-stack hybrid transactional/analytical workloads
ColumnStore for analytics with scalable S3-compatible object storage
InnoDB for transactions
Cross-engine JOINs
Enterprise Server 10.5, Enterprise ColumnStore 5, MaxScale 2.5
This procedure describes the deployment of the HTAP topology with MariaDB Enterprise Server and MariaDB Enterprise ColumnStore.
MariaDB Enterprise ColumnStore is a columnar storage engine for MariaDB Enterprise Server. This topology is best suited for Hybrid Transactional-Analytical Processing (HTAP) workloads.
This procedure has 4 steps, which are executed in sequence.
This procedure represents the basic product capability and uses 1 Enterprise ColumnStore node.
This page provides an overview of the topology, requirements, and deployment procedures.
Please read and understand this procedure before executing.
Customers can obtain support by
The following components are deployed during this procedure:
The MariaDB Enterprise ColumnStore HTAP topology is designed for hybrid transactional-analytical processing (HTAP) workloads.
The topology consists of:
One MaxScale node
One ColumnStore node running ES and Enterprise ColumnStore
The MaxScale node:
Monitors the health and availability of the ColumnStore node using the MariaDB Monitor (mariadbmon)
Accepts client and application connections
Routes queries to the ColumnStore node using the Read/Write Split Router (readwritesplit)
The ColumnStore node:
Receives queries from MaxScale
Executes queries
Uses a row-based storage engine, such as to handle transactional queries
Uses Enterprise ColumnStore as the columnar storage engine to handle analytical queries
These requirements are for the HTAP topology when deployed with MariaDB Enterprise Server 11.4 and MariaDB Enterprise ColumnStore.
In alignment to the , the HTAP topology with MariaDB Enterprise Server 11.4 and MariaDB Enterprise ColumnStore is provided for:
Debian 11 (x86_64, ARM64)
Debian 12 (x86_64, ARM64)
Red Hat Enterprise Linux 8 (x86_64, ARM64)
Red Hat Enterprise Linux 9 (x86_64, ARM64)
The HTAP topology can use S3-compatible object storage to store ColumnStore data, but it is not required.
Many S3-compatible object storage services exist. MariaDB Corporation cannot make guarantees about all S3-compatible object storage services, because different services provide different functionality.
For the preferred S3-compatible object storage providers that provide cloud and hardware solutions, see the following sections:
The use of non-cloud and non-hardware providers is at your own risk.
If you have any questions about using specific S3-compatible object storage with MariaDB Enterprise ColumnStore, .
Amazon Web Services (AWS) S3
Google Cloud Storage
Azure Storage
Alibaba Cloud Object Storage Service
Cloudian HyperStore
Dell EMC
Seagate Lyve Rack
Quantum ActiveScale
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:
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:
This implementation has not been tested with semi-synchronous replication.
This implementation has not been tested with parallel replication.
This implementation requires the system variable to be set to STATEMENT. Row-based replication is not currently supported.
MariaDB Enterprise Server packages are configured to read configuration files from different paths, depending on the operating system. Making custom changes to Enterprise Server default configuration files is not recommended because custom changes may be overwritten by other default configuration files that are loaded later.
To ensure that your custom changes will be read last, create a custom configuration file with the z- prefix in one of the include directories.
The systemctl command is used to start and stop the MariaDB Enterprise Server service.
For additional information, see "".
MariaDB Enterprise Server produces log data that can be helpful in problem diagnosis.
Log filenames and locations may be overridden in the server configuration. The default location of logs is the data directory. The data directory is specified by the datadir system variable.
The systemctl command is used to start and stop the ColumnStore service.
Navigation in the procedure "Deploy HTAP Topology".
Next: Step 1: Prepare ColumnStore Node.
This page is: Copyright © 2025 MariaDB. All rights reserved.
Uses cross-engine JOINs to join transactional and analytical tables
Replicates data between engines using MariaDB Replication
Optionally uses S3-compatible object storage for Enterprise ColumnStore data
Rocky Linux 9 (x86_64, ARM64)
Ubuntu 20.04 LTS (x86_64, ARM64)
Ubuntu 22.04 LTS (x86_64, ARM64)
Ubuntu 24.04 LTS (x86_64, ARM64)
<hostname>-bin
Enterprise Server 10.6, Enterprise ColumnStore 23.02, MaxScale 22.08
Prepare ColumnStore Node
Install MariaDB Enterprise Server
Start and Configure MariaDB Enterprise Server
Test MariaDB Enterprise Server
MariaDB Enterprise Server
Modern SQL RDBMS with high availability, pluggable storage engines, hot online backups, and audit logging.
General purpose storage engine
Support for Online Transactional Processing (OLTP) workloads
ACID-compliant
Performance
Columnar storage engine
Optimized for Online Analytical
Processing (OLAP) workloads
Scalable query execution
Configuration File
Configuration files (such as /etc/my.cnf) can be used to set system-variables and options. The server must be restarted to apply changes made to configuration files.
Command-line
The server can be started with command-line options that set system-variables and options.
SQL
Users can set system-variables that support dynamic changes on-the-fly using the SET statement.
CentOS
Red Hat Enterprise Linux (RHEL)
/etc/my.cnf.d/z-custom-mariadb.cnf
Debian
Ubuntu
/etc/my.cnf.d/z-custom-mariadb.cnf
Start
sudo systemctl start mariadb
Stop
sudo systemctl stop mariadb
Restart
sudo systemctl restart mariadb
Enable during startup
sudo systemctl enable mariadb
Disable during startup
sudo systemctl disable mariadb
Status
sudo systemctl status mariadb
<hostname>.err
server_audit.log
<hostname>-slow.log
Start
sudo systemctl start mariadb-columnstore
Stop
sudo systemctl stop mariadb-columnstore
Restart
sudo systemctl restart mariadb-columnstore
Enable during startup
sudo systemctl enable mariadb-columnstore
Disable during startup
sudo systemctl disable mariadb-columnstore
Status
sudo systemctl status mariadb-columnstore
<hostname>.log
This page details step 2 of the 4-step procedure "Deploy HTAP Topology".
This step installs MariaDB Enterprise Server MariaDB Enterprise ColumnStore 23.10, and dependencies.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
MariaDB Corporation provides package repositories for CentOS / RHEL (YUM) and Debian / Ubuntu (APT). A download token is required to access the MariaDB Enterprise Repository.
Customer Download Tokens are customer-specific and are available through the MariaDB Customer Portal.
To retrieve the token for your account:
Navigate to
Log in.
Copy the Customer Download Token.
Substitute your token for CUSTOMER_DOWNLOAD_TOKEN when configuring the package repositories.
On each Enterprise ColumnStore node, install the prerequisites for downloading the software from the Web. Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
On each Enterprise ColumnStore node, configure package repositories and specify Enterprise Server:
Checksums of the various releases of the mariadb_es_repo_setup script can be found in the section at the bottom of the page. Substitute ${checksum} in the example above with the latest checksum.
On each Enterprise ColumnStore node, install additional dependencies: Install on CentOS / RHEL (YUM)
Install of Debian 10 and Ubuntu 20.04 (APT):
Install on Debian 9 and Ubuntu 18.04 (APT):
On the Enterprise ColumnStore node, install MariaDB Enterprise Server and MariaDB Enterprise ColumnStore:
Install on CentOS / RHEL (YUM):
Install on Debian / Ubuntu (APT):
Navigation in the procedure "Deploy HTAP Topology".
This page was step 2 of 4.
Next: Step 3: Start and Configure MariaDB Enterprise Server.
USE innodb_db;
INSERT INTO htap_test1
VALUES (100);SELECT * FROM columnstore_db.htap_test1;
+------+
| id |
+------+
| 100 |
+------+USE columnstore_db;
INSERT INTO innodb_db.htap_test1
VALUES (200);SELECT * FROM columnstore_db.htap_test1;
+------+
| id |
+------+
| 100 |
+------+This page is: Copyright © 2025 MariaDB. All rights reserved.
This page is: Copyright © 2025 MariaDB. All rights reserved.
$ sudo yum install curl$ sudo apt install curl apt-transport-https$ curl -LsSO https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup$ echo "${checksum} mariadb_es_repo_setup" \
| sha256sum -c -$ chmod +x mariadb_es_repo_setup$ sudo ./mariadb_es_repo_setup --token="CUSTOMER_DOWNLOAD_TOKEN" --apply \
--skip-maxscale \
--skip-tools \
--mariadb-server-version="11.4"$ sudo yum install epel-release
$ sudo yum install jemalloc$ sudo apt install libjemalloc2$ sudo apt install libjemalloc1$ sudo yum install MariaDB-server \
MariaDB-backup \
MariaDB-shared \
MariaDB-client \
MariaDB-columnstore-engine$ sudo apt install mariadb-server \
mariadb-backup \
libmariadb3 \
mariadb-client \
mariadb-plugin-columnstoreThis page details step 3 of the 4-step procedure "Deploy HTAP Topology".
This step starts and configures MariaDB Enterprise Server and MariaDB Enterprise ColumnStore 23.10.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
The installation process might have started some of the ColumnStore services. The services should be stopped prior to making configuration changes.
On each Enterprise ColumnStore node, stop the MariaDB Enterprise Server service:
On each Enterprise ColumnStore node, stop the MariaDB Enterprise ColumnStore service:
On each Enterprise ColumnStore node, stop the CMAPI service:
On each Enterprise ColumnStore node, configure Enterprise Server.
Configure Enterprise ColumnStore S3 Storage Manager to use S3-compatible storage by editing the /etc/columnstore/storagemanager.cnf configuration file:
The S3-compatible object storage options are configured under [S3]:
The bucket option must be set to the name of the bucket that you created in "Create an S3 Bucket".
The endpoint option must be set to the endpoint for the S3-compatible object storage.
The aws_access_key_id and aws_secret_access_key options must be set to the access key ID and secret access key for the S3-compatible object storage.
To use a specific IAM role, you must uncomment and set
The local cache options are configured under [Cache]:
The cache_size option is set to 2 GB by default.
The path option is set to /var/lib/columnstore/storagemanager/cache by default.
Ensure that the specified path has sufficient storage space for the specified cache size.
Start and enable the MariaDB Enterprise Server service, so that it starts automatically upon reboot:
Start and enable the MariaDB Enterprise ColumnStore service, so that it starts automatically upon reboot:
For additional information, see "".
The HTAP topology requires several user accounts.
Enterprise ColumnStore requires a mandatory utility user account. By default, it connects to the server using the root user with no password. MariaDB Enterprise Server 10.6 will reject this login attempt by default, so you will need to configure Enterprise ColumnStore to use a different user account and password and create this user account on Enterprise Server.
On the Enterprise ColumnStore node, create the user account with the CREATE USER statement:
On the Enterprise ColumnStore node, grant the user account SELECT privileges on all databases with the GRANT statement:
Configure Enterprise ColumnStore to use the utility user:
Set the password:
For details about how to encrypt the password, see "".
Passwords should meet 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.
Enterprise HTAP uses to replicate writes between InnoDB tables and ColumnStore tables.
Create a replication user and grant it the required privileges:
Use the statement to create replication users for each replica server:
Grant the user account several global privileges with the statement.
Set the GTID position by setting the system variable. If this is a new deployment, then it would be set to the empty string:
Use the CHANGE MASTER TO statement to configure the server to replicate from itself starting from this position:
Start replication using the START REPLICA statement:
Confirm that replication is working using the SHOW REPLICA STATUS statement:
The specific steps to configure the security module depend on the operating system.
Configure SELinux for Enterprise ColumnStore:
To configure SELinux, you have to install the packages required for audit2allow. On CentOS 7 and RHEL 7, install the following:
On RHEL 8, install the following:
Allow the system to run under load for a while to generate SELinux audit events.
After the system has taken some load, generate an SELinux policy from the audit events using audit2allow:
If no audit events were found, this will print the following:
If audit events were found, the new SELinux policy can be loaded using semodule:
Set SELinux to enforcing mode by setting SELINUX=enforcing in /etc/selinux/config.
For example, the file will usually look like this after the change:
Set SELinux to enforcing mode:
For information on how to create a profile, see on ubuntu.com.
Navigation in the procedure "Deploy HTAP Topology".
This page was step 3 of 4.
Next: Step 4: Test MariaDB Enterprise Server.
Set this option to the file you want to use for the Binary Log. Setting this option enables binary logging.
Set this system variable to ON.
Set this option to the file you want to use for the Relay Logs. Setting this option enables relay logging.
Set this option to the file you want to use to index Relay Log filenames.
Sets the numeric Server ID for this MariaDB Enterprise Server. The value set on this option must be unique to each node.
iam_role_name, sts_region, and sts_endpointTo use the IAM role assigned to an EC2 instance, you must uncomment ec2_iam_mode=enabled.
Set this to the name of the database to replicate from InnoDB to ColumnStore.
Set this to STATEMENT for HTAP.
Set this system variable to utf8
Set this system variable to utf8_general_ci
columnstore_use_import_for_batchinsert
Set this system variable to ALWAYS to always use cpimport for LOAD DATA INFILE and INSERT...SELECT statements.
Set this system variable to ON.
This page is: Copyright © 2025 MariaDB. All rights reserved.
$ sudo systemctl stop mariadb$ sudo systemctl stop mariadb-columnstore$ sudo systemctl stop mariadb-columnstore-cmapi[mariadb]
log_error = mariadbd.err
character_set_server = utf8
collation_server = utf8_general_ci
# 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
# Transactions replicate from same server
replicate_same_server_id = ON
# Only write queries that touch 'innodb_db' to the binary log
binlog_do_db = innodb_db
# Rewrite innodb_db to columnstore_db prior to applying transaction
replicate_rewrite_db = innodb_db->columnstore_db
# Only replicate tables that begin with "htap"
replicate_wild_do_table = columnstore_db.htap%[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
# ec2_iam_mode = enabled
[Cache]
cache_size = your_local_cache_size
path = your_local_cache_path$ sudo systemctl start mariadb
$ sudo systemctl enable mariadb$ sudo systemctl start mariadb-columnstore
$ sudo systemctl enable mariadb-columnstoreCREATE USER 'util_user'@'127.0.0.1'
IDENTIFIED BY 'util_user_passwd';GRANT SELECT, PROCESS ON *.*
TO 'util_user'@'127.0.0.1';$ sudo mcsSetConfig CrossEngineSupport Host 127.0.0.1
$ sudo mcsSetConfig CrossEngineSupport Port 3306
$ sudo mcsSetConfig CrossEngineSupport User util_user$ sudo mcsSetConfig CrossEngineSupport Password util_user_passwdCREATE USER 'repl'@'localhost' IDENTIFIED BY 'passwd';GRANT REPLICA MONITOR,
REPLICATION REPLICA
ON *.* TO 'repl'@'localhost';SET GLOBAL gtid_slave_pos='';CHANGE MASTER TO
MASTER_HOST='localhost',
MASTER_USER='htap_replication',
MASTER_PASSWORD='passwd',
MASTER_USE_GTID=slave_pos;START REPLICA;SHOW REPLICA STATUS;$ sudo yum install policycoreutils policycoreutils-python$ sudo yum install policycoreutils python3-policycoreutils policycoreutils-python-utils$ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local$ sudo grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local
Nothing to do$ sudo semodule -i mariadb_local.pp# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=enforcing
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted$ sudo setenforce enforcing
This page details step 4 of the 4-step procedure "Deploy HTAP Topology".
This step tests Enterprise ColumnStore.
Interactive commands are detailed. Alternatively, the described operations can be performed using automation.
MariaDB Enterprise ColumnStore includes a testS3Connection command to test the S3 configuration, permissions, and connectivity.
Test the S3 configuration by executing the following:
If the testS3Connection command does not return OK, investigate the S3 configuration.
Use Systemd to test whether the MariaDB Enterprise Server service is running.
Check if the MariaDB Enterprise Server service is running by executing the following:
If the service is not running, start the service by executing the following:
Use MariaDB Client to test the local connection to the Enterprise Server node:
The sudo command is used here to connect to the Enterprise Server node using the root@localhost user account, which authenticates using the unix_socket authentication plugin. Other user accounts can be used by specifying the --user and --password command-line options.
Query the table to confirm that the ColumnStore storage engine is loaded.
Execute the following query:
The PLUGIN_STATUS column for each ColumnStore-related plugin should contain ACTIVE.
Use the statement to check the status of MariaDB Replication:
Create a test database, if it does not exist:
Create a ColumnStore table:
Add sample data into the table:
Read data from table:
Create an InnoDB table:
Add data to the table:
Perform a cross-engine join:
Connect to the server using using the root@localhost user account:
Create the databases for the InnoDB and ColumnStore tables using the CREATE DATABASE statement:
Create the InnoDB versions of the HTAP tables using the CREATE TABLE statement:
Confirm that the tables were replicated using the SHOW TABLES statement:
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:
Insert data into the InnoDB versions of the HTAP tables using the statement:
Confirm that the data was replicated using the statement:
Create an InnoDB table that will not be replicated:
Confirm that the table was not replicated:
Create a ColumnStore table that will not be replicated:
Confirm that the table was not replicated:
Navigation in the procedure "Deploy HTAP Topology". This page was step 4 of 4.
This procedure is complete.
This page is: Copyright © 2025 MariaDB. All rights reserved.
$ sudo testS3ConnectionStorageManager[26887]: Using the config file found at /etc/columnstore/storagemanager.cnf
StorageManager[26887]: S3Storage: S3 connectivity & permissions are OK
S3 Storage Manager Configuration OK$ systemctl status mariadb$ sudo systemctl start mariadb$ sudo mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 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)]>SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM information_schema.PLUGINS
WHERE PLUGIN_LIBRARY LIKE 'ha_columnstore%';
+---------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+---------------------+---------------+
| Columnstore | ACTIVE |
| COLUMNSTORE_COLUMNS | ACTIVE |
| COLUMNSTORE_TABLES | ACTIVE |
| COLUMNSTORE_FILES | ACTIVE |
| COLUMNSTORE_EXTENTS | ACTIVE |
+---------------------+---------------+SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
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: 1CREATE DATABASE IF NOT EXISTS test;CREATE TABLE IF NOT EXISTS test.contacts (
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
) ENGINE=ColumnStore;INSERT INTO test.contacts (first_name, last_name, email)
VALUES
("Kai", "Devi", "kai.devi@example.com"),
("Lee", "Wang", "lee.wang@example.com");SELECT * FROM test.contacts;
+------------+-----------+----------------------+
| first_name | last_name | email |
+------------+-----------+----------------------+
| Kai | Devi | kai.devi@example.com |
| Lee | Wang | lee.wang@example.com |
+------------+-----------+----------------------+CREATE TABLE test.addresses (
email VARCHAR(100),
street_address VARCHAR(255),
city VARCHAR(100),
state_code VARCHAR(2)
) ENGINE = InnoDB;INSERT INTO test.addresses (email, street_address, city, state_code)
VALUES
("kai.devi@example.com", "1660 Amphibious Blvd.", "Redwood City", "CA"),
("lee.wang@example.com", "32620 Little Blvd", "Redwood City", "CA");SELECT name AS "Name", addr AS "Address"
FROM (SELECT CONCAT(first_name, " ", last_name) AS name,
email FROM test.contacts) AS contacts
INNER JOIN (SELECT CONCAT(street_address, ", ", city, ", ", state_code) AS addr,
email FROM test.addresses) AS addr
WHERE contacts.email = addr.email;
+----------+-----------------------------------------+
| Name | Address |
+----------+-----------------------------------------+
| Kai Devi | 1660 Amphibious Blvd., Redwood City, CA |
| Lee Wang | 32620 Little Blvd, Redwood City, CA |
+----------+-----------------------------------------+$ sudo mariadbCREATE DATABASE columnstore_db;
CREATE DATABASE innodb_db;USE innodb_db;
CREATE TABLE htap_test1 (
id INT
) ENGINE = InnoDB;
CREATE TABLE htap_test2 (
id INT
) ENGINE = InnoDB;SHOW TABLES FROM columnstore_db;
+--------------------------+
| Tables_in_columnstore_db |
+--------------------------+
| htap_test1 |
| htap_test2 |
+--------------------------+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;USE innodb_db;
INSERT INTO htap_test1
VALUES (100);
INSERT INTO htap_test2
VALUES (200);SELECT * FROM columnstore_db.htap_test1;
+------+
| id |
+------+
| 100 |
+------+
SELECT * FROM columnstore_db.htap_test2;
+------+
| id |
+------+
| 200 |
+------+USE innodb_db;
CREATE TABLE transactional_test1 (
id INT
) ENGINE = InnoDB;SHOW TABLES FROM columnstore_db LIKE 'transactional_%';
Empty set (0.02 sec)USE columnstore_db;
CREATE TABLE analytical_test1 (
id INT
) ENGINE = ColumnStore;SHOW TABLES FROM innodb_db LIKE 'analytical_%';
Empty set (0.02 sec)