Xieles Support

Installation and configuration of MariaDB MaxScale 22.08 on Centos 7

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:

Copy to Clipboard

Configure package repositories and specify MariaDB MaxScale 22.08:

Copy to Clipboard

Install MariaDB MaxScale

Install MariaDB MaxScale on CentOS:

Copy to Clipboard

Start and configures MariaDB MaxScale 22.08

Replace the Default Configuration File

Replace the default /etc/maxscale.cnf on the MaxScale node.

Copy to Clipboard

Restart MaxScale

Restart the MaxScale service on the MaxScale node to ensure that MaxScale picks up the new configuration:

Copy to Clipboard

Configure Server Objects

Use maxctrl create server to create a server object for each MariaDB Enterprise Server:

Copy to Clipboard

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:

Copy to Clipboard

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:
Copy to Clipboard

Configure Listener for the Read Connection Router as the example below:

Copy to Clipboard
  • Router ‘Read/Write Split (readwritesplit)’ has a ‘Configure configuration procedure Read/Write Split’. For example:
Copy to Clipboard

Configure a Listener for the Read/Write Split Router as below example:

Copy to Clipboard

Start Services

On the MaxScale node use maxctrl start services:

Copy to Clipboard

Test MariaDB MaxScale 22.08

Global MaxScale configuration

To check the Global MaxScale configuration use the command maxctrl show maxscale.

Copy to Clipboard

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:

Copy to Clipboard

2. To view the configuration of each server object:

Copy to Clipboard

How to view the monitor configuration

Use the maxctrl list monitors and maxctrl show monitor commands:

1. To get the full list of monitors:

Copy to Clipboard

2. To view the configuration of each monitor:

Copy to Clipboard

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:

Copy to Clipboard

2. Create the database user account for your MaxScale node:

Copy to Clipboard

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:

Copy to Clipboard

Create a user to connect from the application server to the primary server.

Create the database user account for your application server:

Copy to Clipboard

Grant the privileges required by your application to the database user account for your application server:

Copy to Clipboard

Note: Replace 192.0.2.10 with the relevant IP address.

Test Connection with Application User on the application server:

Copy to Clipboard

Test Connection with Read Connection Router

1. Use the maxctrl list listeners command to view the available listeners and ports:

Copy to Clipboard

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:

Copy to Clipboard

3. In each terminal, query the hostname and server id system variable and option to identify to which you’re connected:

Copy to Clipboard

Test Write Queries with Read/Write Split Router

1. Use the maxctrl list listeners command to view the available listeners and ports:

Copy to Clipboard

2. Use MariaDB Client to connect to the listener port for the Read/Write Split Router :

Copy to Clipboard

3. In one terminal, create the test table:

Copy to Clipboard

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:

Copy to Clipboard

5. In one terminal, issue a SELECT statement to query the results:

Copy to Clipboard

Test Read Queries with Read/Write Split Router

1. Use the maxctrl list listeners command to view the available listeners and ports:

Copy to Clipboard

2. Use MariaDB Client to connect to the listener port for the Read/Write Split Router:

Copy to Clipboard

3. Query the hostname and server id system variable and option to identify to which you’re connected:

Copy to Clipboard

4. Resend the query:

Copy to Clipboard

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.

Exit mobile version