Step 4: Test MariaDB Enterprise Server
This page is part of MariaDB's Documentation.
The parent of this page is: Deploy HTAP Topology with MariaDB Enterprise Server 10.5
Topics on this page:
Overview
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.
Test S3 Connection
MariaDB Enterprise ColumnStore 5 includes a testS3Connection
command to test the S3 configuration, permissions, and connectivity.
Test the S3 configuration by executing the following:
$ sudo testS3Connection
StorageManager[26887]: Using the config file found at /etc/columnstore/storagemanager.cnf
StorageManager[26887]: S3Storage: S3 connectivity & permissions are OK
S3 Storage Manager Configuration OK
If the testS3Connection
command does not return OK
, investigate the S3 configuration.
Test Enterprise Server Service
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:
$ systemctl status mariadb
If the service is not running, start the service by executing the following:
$ sudo systemctl start mariadb
Test Local Client Connections
Use MariaDB Client to test the local connection to the Enterprise Server node:
$ sudo mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.5.26-20-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)]>
The sudo
command is used here to connect to the Enterprise Server node using the root@localhost
user account, which authenticates using the unix_
Test ColumnStore Storage Engine Plugin
Query the information_
Execute the following query:
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 |
+---------------------+---------------+
The PLUGIN_STATUS
column for each ColumnStore-related plugin should contain ACTIVE
.
Test Replication Status
Use the SHOW REPLICA STATUS statement to check the status of MariaDB Replication:
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: 1
Test ColumnStore Table Creation
Create a test database, if it does not exist:
CREATE DATABASE IF NOT EXISTS test;
Create a ColumnStore table:
CREATE TABLE IF NOT EXISTS test.contacts ( first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) ) ENGINE=ColumnStore;
Add sample data into the table:
INSERT INTO test.contacts (first_name, last_name, email) VALUES ("Kai", "Devi", "kai.devi@example.com"), ("Lee", "Wang", "lee.wang@example.com");
Read data from table:
SELECT * FROM test.contacts;
+------------+-----------+----------------------+ | first_name | last_name | email | +------------+-----------+----------------------+ | Kai | Devi | kai.devi@example.com | | Lee | Wang | lee.wang@example.com | +------------+-----------+----------------------+
Test Cross Engine Join
Create an InnoDB table:
CREATE TABLE test.addresses ( email VARCHAR(100), street_address VARCHAR(255), city VARCHAR(100), state_code VARCHAR(2) ) ENGINE = InnoDB;
Add data to the table:
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");
Perform a cross-engine join:
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 | +----------+-----------------------------------------+
Test HTAP Replication
Connect to the server using MariaDB Client using the
root@localhost
user account:$ sudo mariadb
Create the databases for the InnoDB and ColumnStore tables using the CREATE DATABASE statement:
CREATE DATABASE columnstore_db; CREATE DATABASE innodb_db;
Create the InnoDB versions of the HTAP tables using the CREATE TABLE statement:
USE innodb_db; CREATE TABLE htap_test1 ( id INT ) ENGINE = InnoDB; CREATE TABLE htap_test2 ( id INT ) ENGINE = InnoDB;
Confirm that the tables were replicated using the SHOW TABLES statement:
SHOW TABLES FROM columnstore_db;
+--------------------------+ | Tables_in_columnstore_db | +--------------------------+ | htap_test1 | | htap_test2 | +--------------------------+
The replication initially creates empty InnoDB tables, which need to be transformed into ColumnStore tables and which need to be populated with the initial copy of the data:
DROP TABLE IF EXISTS columnstore_db.htap_test1; CREATE TABLE columnstore_db.htap_test1 ENGINE=COLUMNSTORE SELECT * FROM innodb_db.htap_test1; DROP TABLE IF EXISTS columnstore_db.htap_test2; CREATE TABLE columnstore_db.htap_test2 ENGINE=COLUMNSTORE SELECT * FROM innodb_db.htap_test2;
Insert data into the InnoDB versions of the HTAP tables using the INSERT statement:
USE innodb_db; INSERT INTO htap_test1 VALUES (100); INSERT INTO htap_test2 VALUES (200);
Confirm that the data was replicated using the SELECT statement:
SELECT * FROM columnstore_db.htap_test1;
+------+ | id | +------+ | 100 | +------+
SELECT * FROM columnstore_db.htap_test2;
+------+ | id | +------+ | 200 | +------+
Create an InnoDB table that will not be replicated:
USE innodb_db; CREATE TABLE transactional_test1 ( id INT ) ENGINE = InnoDB;
Confirm that the table was not replicated:
SHOW TABLES FROM columnstore_db LIKE 'transactional_%';
Empty set (0.02 sec)
Create a ColumnStore table that will not be replicated:
USE columnstore_db; CREATE TABLE analytical_test1 ( id INT ) ENGINE = ColumnStore;
Confirm that the table was not replicated:
SHOW TABLES FROM innodb_db LIKE 'analytical_%';
Empty set (0.02 sec)
Next Step
Navigation in the procedure "Deploy HTAP Topology":
This page was step 4 of 4.
This procedure is complete.