The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-12-22. For the latest, interactive version please visit https://mariadb.com/kb/.

SmartRouter

SmartRouter

Overview

SmartRouter is the query router of the SmartQuery framework. Based on the type of the query, each query is routed to the server or cluster that can best handle it.

For workloads where both transactional and analytical queries are needed, SmartRouter unites the Transactional (OLTP) and Analytical (OLAP) workloads into a single entry point in MaxScale. This allows a MaxScale client to freely mix transactional and analytical queries using the same connection. This is known as Hybrid Transactional and Analytical Processing, HTAP.

Configuration

SmartRouter is configured as a service that either routes to other MaxScale routers or plain servers. Although one can configure SmartRouter to use a plain server directly, we refer to the configured "servers" as clusters.

For details about the standard service parameters, refer to the Configuration Guide.

master

One of the clusters must be designated as the master. All writes go to the master cluster, which for all practical purposes should be a master-slave ReadWriteSplit. This document does not go into details about setting up master-slave clusters, but suffice to say, that when setting up the ColumnStore servers they should be configured to be slaves of a MariaDB server running an InnoDB engine. The ReadWriteSplit documentation has more on master-slave setup.

Service as a Server

Currently the configuration for a router (service) can have a number of servers, but not other routers (services). Suppose you have a cluster of servers that are managed by ReadWriteSplit. In order for that cluster to be routed-to by SmartRouter, the service must be exposed as a server. A server section in the configuration file that exposes a service instead of defining an actual server, is known as Service as a Server.

Example

Suppose we have a service like

[RWS-Row]
type=service
router=readwritesplit

for which we have defined the listener

[RWS-Row-Listener]
type=listener
service=RWS-Row
socket=/tmp/rws-row.sock

That is, that service can be accessed using the socket /tmp/rws-row.sock.

A server section that would expose that service as a server, looks like this:

[RWS-Row-as-a-server]
type=server
socket=/tmp/rws-row.sock

Assuming we have defined RWS-Column, RWS-Column-Listener and RWS-Column-as-a-server similarly, we can define the SmartQuery service as follows:

[SmartQuery]
type = service
router = smartrouter
servers = RWS-Row-as-a-server, RWS-Column-as-a-server
master = RWS-Row-as-a-server
user = <user>
password = <password>

[SmartQuery-Listener]
type = listener
service = SmartQuery
protocol = mariadbclient
port = <port>

Note that the SmartQuery listener listens on a port, while the Service as a Server listeners listen on a Unix domain socket. The reason is that there is a significant performance benefit when SmartRouter accesses the services over a Unix domain socket compared to accessing them over a TCP/IP socket.

Note that RWS-Row-as-a-server is designated as the master.

The RWS-Row-as-a-server looks like this:

[RWS-Row-Listener]
type = listener
service = RWS-Row
protocol = mariadbclient
socket = /tmp/rws-row.sock

[RWS-Row-as-a-server]
type     = server
socket = /tmp/rws-row.sock
protocol = MariaDBBackend

A complete configuration example can be found at the end of this document.

Cluster selection - how queries are routed

SmartRouter keeps track of the performance, or the execution time, of queries to the clusters. Measurements are stored with the canonical of a query as the key. The canonical of a query is the sql with all user-defined constants replaced with question marks. When SmartRouter sees a read-query whose canonical has not been seen before, it will send the query to all clusters. The first response from a cluster will designate that cluster as the best one for that canonical. Also, when the first response is received, the other queries are cancelled. The response is sent to the client once all clusters have responded to the query or the cancel.

There is obviously overhead when a new canonical is seen. This means that queries after a MaxScale start will be slightly slower than normal. The execution time of a query depends on the database engine, and on the contents of the tables being queried. As a result, MaxScale will periodically re-measure queries.

The performance behavior of queries under dynamic conditions, and their effect on different storage engines is being studied at MariaDB. As we learn more, we will be able to better categorize queries and move that knowledge into SmartRouter.

Limitations

  • LOAD DATA LOCAL INFILE is not supported.
  • The performance data is not persisted. The measurements have to be performed anew after each startup.

Complete configuration example

[maxscale]

[row_server_1]
type = server
address = <ip>
port = <port>
protocol = MariaDBBackend

[row_server_2]
type = server
address = <ip>
port = <port>
protocol = MariaDBBackend

[Row-Monitor]
type = monitor
module = mariadbmon
servers = row_server_1, row_server_2
user = <user>
password = <password>
monitor_interval = 2000ms

[column_server_1]
type = server
address = <ip>
port = <port>
protocol = MariaDBBackend

[Column-Monitor]
type = monitor
module = csmon
servers = column_server_1
user = <user>
password = <password>
monitor_interval = 2000ms

# Row Read write split
[RWS-Row]
type = service
router = readwritesplit
servers = row_server_1, row_server_2
user = <user>
password = <password>

[RWS-Row-Listener]
type = listener
service = RWS-Row
protocol = mariadbclient
socket = /tmp/rws-row.sock

# Columnstore Read write split
[RWS-Column]
type = service
router = readwritesplit
servers = column_server_1
user = <user>
password = <password>

[RWS-Column-Listener]
type = listener
service = RWS-Column
protocol = mariadbclient
socket = /tmp/rws-col.sock

[RWS-Row-as-a-server]
type = server
socket = /tmp/rws-row.sock
protocol = MariaDBBackend

[RWS-Column-as-a-server]
type     = server
socket = /tmp/rws-col.sock
protocol = MariaDBBackend

# Smart Query router
[SmartQuery]
type = service
router = smartrouter
servers = RWS-Row-as-a-server, RWS-Column-as-a-server
master = RWS-Row-as-a-server
user = <user>
password = <password>

[SmartQuery-Listener]
type = listener
service = SmartQuery
protocol = mariadbclient
port = <port>
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.