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:
Configure package repositories and specify MariaDB MaxScale 22.08:
Install MariaDB MaxScale
Install MariaDB MaxScale on CentOS:
Start and configures MariaDB MaxScale 22.08
Replace the Default Configuration File
Replace the default /etc/maxscale.cnf on the MaxScale node.
Restart MaxScale
Restart the MaxScale service on the MaxScale node to ensure that MaxScale picks up the new configuration:
Configure Server Objects
Use maxctrl create server to create a server object for each MariaDB Enterprise Server:
Configure 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:
Choose 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:
Configure Listener for the Read Connection Router as the example below:
- Router ‘Read/Write Split (readwritesplit)’ has a ‘Configure configuration procedure Read/Write Split’. For example:
Configure a Listener for the Read/Write Split Router as below example:
Start Services
On the MaxScale node use maxctrl start services:
Test MariaDB MaxScale 22.08
Global MaxScale configuration
To check the Global MaxScale configuration use the command maxctrl show maxscale.
Check 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:
2. To view the configuration of each server object:
How to view the monitor configuration
Use the maxctrl list monitors and maxctrl show monitor commands:
1. To get the full list of monitors:
2. To view the configuration of each monitor:
Test 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:
2. Create the database user account for your MaxScale node:
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:
Create a user to connect from the application server to the primary server.
Create the database user account for your application server:
Grant the privileges required by your application to the database user account for your application server:
Note: Replace 192.0.2.10 with the relevant IP address.
Test Connection with Application User on the application server:
Test Connection with Read Connection Router
1. Use the maxctrl list listeners command to view the available listeners and ports:
2. Open multiple terminals connected to your application server, in each use MariaDB Client to connect to the listener port for the Read Connection Router:
3. In each terminal, query the hostname and server id system variable and option to identify to which you’re connected:
Test Write Queries with Read/Write Split Router
1. Use the maxctrl list listeners command to view the available listeners and ports:
2. Use MariaDB Client to connect to the listener port for the Read/Write Split Router :
3. In one terminal, create the test table:
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:
5. In one terminal, issue a SELECT statement to query the results:
Test Read Queries with Read/Write Split Router
1. Use the maxctrl list listeners command to view the available listeners and ports:
2. Use MariaDB Client to connect to the listener port for the Read/Write Split Router:
3. Query the hostname and server id system variable and option to identify to which you’re connected:
4. Resend the query:
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.