Connecting to MariaDB Enterprise ColumnStore

When users and applications connect to MariaDB Enterprise ColumnStore to execute queries, they typically connect through MariaDB MaxScale. MaxScale's Read/Write Split Router (readwritesplit) routes write queries to the primary node and read queries to a replica node.

This page details the benefits MaxScale provides to MariaDB Enterprise ColumnStore and describes how to test application connections and load balancing.

MaxScale as a Database Proxy

MariaDB Enterprise ColumnStore topologies rely on MariaDB MaxScale as a database proxy. MaxScale is an advanced database proxy that allows applications and clients to connect as they would to a single database while abstracting the complexity of the underlying database infrastructure.

Applications and clients should generally connect to MaxScale in all production topologies for Enterprise ColumnStore. Applications gain many benefits by connecting to MaxScale:

  • MaxScale monitors the health and performance of the nodes using MariaDB Monitor (mariadbmon).

  • MaxScale automatically load balances connections among the nodes.

  • If a node fails, MaxScale automatically detects the failure and stops using it for load balancing.

  • If a node is undergoing maintenance, MaxScale can be instructed to temporarily stop using it for load balancing.

  • If the primary node fails, MariaDB Monitor can perform auto-failover of MariaDB Replication.

  • MaxScale can perform additional audit logging.

  • MaxScale can provide additional security and traffic controls.

Client Support

All MariaDB Enterprise ColumnStore topologies are compatible with the standard MariaDB clients.

For additional information, see "MariaDB Enterprise ColumnStore Tooling: Standard MariaDB Tools".

Test Application Connections

This action is performed on a client connected to the MaxScale node.

To connect to MaxScale with MariaDB Client:

  1. Use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=col_app_user \
       --password
    

    The listener port (Configure a Listener) should be specified by the --port option. When the listener uses TCP port 3306, it is unnecessary to specify the port to the client.

    The user account (Create Application Database User) should be specified by the --user option.

    MaxScale's Read/Write Split Router (Configure Read/Write Split Router) will route the connection to one of the nodes (Configure Server Objects).

  2. Use the client connection to query the node's hostname:

    SELECT @@global.hostname;
    

    The output will show the hostname of one of the nodes.

Test Connection Load Balancing

This action is performed on a client connected to the MaxScale node.

  1. Open multiple terminals on the client.

  2. In each terminal, use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=col_app_user \
       --password
    

    The listener port (Configure a Listener) should be specified by the --port option. When the listener uses TCP port 3306, it is unnecessary to specify the port to the client.

    The user account (Create Application Database User) should be specified by the --user option.

    MaxScale's Read/Write Split Router (Configure Read/Write Split Router) will route the connection to one of the nodes (Configure Server Objects).

  3. In each terminal, use the client connection to query the node's hostname:

    SELECT @@global.hostname;
    

    The output will show the hostname of one of the nodes.

  4. Confirm that MaxScale is load balancing connections by checking that different terminals output different hostnames.