MariaDB ColumnStore is a powerful extension for MariaDB Enterprise Server and MariaDB Community Server that provides distributed, columnar storage and massively parallel processing (MPP) capabilities. ColumnStore extends MariaDB Enterprise Server with distributed storage and parallel processing, enabling scalable and high-performance analytics. It stores data in horizontal and vertical partitions, eliminating the need for indexing and providing efficient data retrieval for analytical workloads. MariaDB MaxScale is a powerful tool of MariaDB Enterprise.
MariaDB MaxScale is a database proxy and load balancer between applications and servers. It acts as an intermediary, handling tasks such as load balancing, failover, and security for database clusters. MaxScale design enhances MariaDB database deployments’ performance, scalability, and manageability. It is useful in complex database environments where there’s a need to distribute database traffic efficiently and provide high availability.
Features of MariaDB ColumnStore
- Columnar Storage: Data is stored in columns rather than rows, enabling faster analytical reporting over large volumes of data.
- Scalability: ColumnStore is designed to handle big data scaling and can process petabytes of data, providing linear scalability and exceptional performance for analytical queries.
- Compression: ColumnStore leverages compression techniques to minimize storage space and enhance query performance.
- Just-in-Time Projection: Data projection is performed dynamically at query execution time, optimizing query performance.
- Horizontal and Vertical Partitioning: Data is partitioned both horizontally and vertically, improving query performance by reducing the amount of data accessed.
How to install MariaDB MaxScale 22.08
Prerequisites:
MariaDB Corporation provides package repositories for CentOS. And for CentOS, a download token is required to access the MariaDB Enterprise Repository.
Go to https://cloud.mariadb.com/csm?id=my_customer_token to retrieve the token for your account.
Setup Repositories
On the CentOS Enterprise Server node, install the prerequisites for downloading the software from the Web:
$ sudo yum install wgetConfigure package repositories and specify MariaDB MaxScale 22.08:
$ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup$ echo "f8eb9c1b59ccfd979d27e39798d2f2a98447dd29e2149ce92bf606aab4493ad9 mariadb_es_repo_setup" \ | sha256sum -c -$ chmod +x mariadb_es_repo_setup$ sudo ./mariadb_es_repo_setup --token="CUSTOMER_DOWNLOAD_TOKEN" --apply \ --skip-server \ --skip-tools \ --mariadb-maxscale-version="22.08"Install MariaDB MaxScale
Install MariaDB MaxScale on CentOS:
$ sudo yum install maxscaleStart and configures MariaDB MaxScale 22.08
Replace the Default Configuration File
Replace the default /etc/maxscale.cnf on the MaxScale node.
[maxscale]threads = autoadmin_host = 0.0.0.0admin_secure_gui = falseRestart MaxScale
Restart the MaxScale service on the MaxScale node to ensure that MaxScale picks up the new configuration:
$ sudo systemctl restart maxscaleConfigure Server Objects
Use maxctrl create server to create a server object for each MariaDB Enterprise Server:
$ maxctrl create server node1 192.0.2.101$ maxctrl create server node2 192.0.2.102$ maxctrl create server node3 192.0.2.103Configure MariaDB Monitor
Monitors retrieve additional information from the servers. Other services use this information for filtering and routing connections. MariaDB Replication, use the MariaDB Monitor (mariadbmon).
Use maxctrl create monitor to create a MariaDB Monitor:
$ maxctrl create monitor mdb_monitor mariadbmon \ user=mxs \ password='MAXSCALE_USER_PASSWORD' \ replication_user=repl \ replication_password='REPLICATION_USER_PASSWORD' \ --servers node1 node2 node3Choose a MaxScale Router
Routers control the load between enterprise server nodes. Routers adopt a different approach for routing queries as follows.
- Router ‘Read Connection (readconnroute)’ has a configuration procedure ‘Configure Read Connection Router’. For example:
$ maxctrl create service connection_router_service readconnroute \ user=mxs \ password='MAXSCALE_USER_PASSWORD' \ router_options=slave \ --servers node1 node2 node3Configure Listener for the Read Connection Router as the example below:
$ maxctrl create listener connection_router_service connection_router_listener 3308 \ protocol=MariaDBClient- Router ‘Read/Write Split (readwritesplit)’ has a ‘Configure configuration procedure Read/Write Split’. For example:
$ maxctrl create service query_router_service readwritesplit \ user=mxs \ password='MAXSCALE_USER_PASSWORD' \ --servers node1 node2 node3Configure a Listener for the Read/Write Split Router as below example:
$ maxctrl create listener query_router_service query_router_listener 3307 \ protocol=MariaDBClientStart Services
On the MaxScale node use maxctrl start services:
$ maxctrl start servicesTest MariaDB MaxScale 22.08
Global MaxScale configuration
To check the Global MaxScale configuration use the command maxctrl show maxscale.
$ maxctrl show maxscaleCheck Server Configuration
To view the configured server objects, use the commands maxctrl list servers and maxctrl show server.
1. To get the full list of objects:
$ maxctrl list servers2. To view the configuration of each server object:
$ maxctrl show server node1How to view the monitor configuration
Use the maxctrl list monitors and maxctrl show monitor commands:
1. To get the full list of monitors:
$ maxctrl list monitors2. To view the configuration of each monitor:
$ maxctrl show monitor mdb_monitorTest application user
As applications use a dedicated user account, create a user account on the primary server. MaxScale authenticates the user connection before routing it to an Enterprise Server node. Enterprise Server authenticates the connection as originating from the IP address of the MaxScale node.
Create a user to connect from MaxScale on the primary server:
1. Connect to the primary server:
$ sudo mariadb2. Create the database user account for your MaxScale node:
CREATE USER 'app_user'@'192.0.2.10' IDENTIFIED BY 'app_user_passwd';Note: Replace 192.0.2.10 with a relevant IP address.
3. Grant the privileges required by your application to the database user account for your MaxScale node:
GRANT ALL ON test.* TO 'app_user'@'192.0.2.10';Create a user to connect from the application server to the primary server.
Create the database user account for your application server:
CREATE USER 'app_user'@'192.0.2.11' IDENTIFIED BY 'app_user_passwd';Grant the privileges required by your application to the database user account for your application server:
GRANT ALL ON test.* TO 'app_user'@'192.0.2.11';Note: Replace 192.0.2.10 with the relevant IP address.
Test Connection with Application User on the application server:
$ mariadb --host 192.0.2.10 --port 3307 --user app_user --passwordTest Connection with Read Connection Router
1. Use the maxctrl list listeners command to view the available listeners and ports:
$ maxctrl list listeners2. Open multiple terminals connected to your application server, in each use MariaDB Client to connect to the listener port for the Read Connection Router:
$ mariadb --host 192.0.2.10 --port 3308 \ --user app_user --password3. In each terminal, query the hostname and server id system variable and option to identify to which you’re connected:
SELECT @@global.hostname, @@global.server_id;Test Write Queries with Read/Write Split Router
1. Use the maxctrl list listeners command to view the available listeners and ports:
$ maxctrl list listeners2. Use MariaDB Client to connect to the listener port for the Read/Write Split Router :
$ mariadb --host 192.0.2.10 --port 3307 \ --user app_user --password3. In one terminal, create the test table:
CREATE TABLE test.load_balancing_test ( id INT PRIMARY KEY AUTO_INCREMENT, hostname VARCHAR(256), server_id INT);4. Use INSERT statement to add a row to the sample table with the values of the hostname and server_id system variable and option:
INSERT INTO test.load_balancing_test (hostname, server_id)VALUES (@@global.hostname, @@global.server_id);5. In one terminal, issue a SELECT statement to query the results:
SELECT * FROM test.load_balancing_test;Test Read Queries with Read/Write Split Router
1. Use the maxctrl list listeners command to view the available listeners and ports:
$ maxctrl list listeners2. Use MariaDB Client to connect to the listener port for the Read/Write Split Router:
$ mariadb --host 192.0.2.10 --port 3307 \ --user app_user --password3. Query the hostname and server id system variable and option to identify to which you’re connected:
SELECT @@global.hostname, @@global.server_id;4. Resend the query:
SELECT @@global.hostname, @@global.server_id;Conclusion
MariaDB ColumnStore provides a powerful extension for MariaDB Enterprise Server and Community Server, offering distributed columnar storage and massively parallel processing capabilities. It can be deployed in the cloud or on local Linux server clusters, thus providing scalability and high performance for analytical queries. As a columnar database, it stores data in columns, compresses data to optimize storage space, and leverages horizontal and vertical partitioning to improve query performance. MariaDB MaxScale is a powerful tool for managing and optimizing database deployments, especially in scenarios where you have multiple database servers and complex traffic patterns.
Xieles team of experts helps you to process your big data with MariaDB ColumnStore.
