You are here
Sharding with MariaDB MaxScale
Tue, 2024-03-19 17:02 — Shinguz
New command
Table of contents
- Overview
- Preparation of the shards (MariaDB database instances)
- Create test data
- Creating roles and users
- MaxScale Monitor User
- MaxScale Admin User
- Create application role and accounts
- Proxy protocol
- MaxScale SchemaRouter configuration
- Starting and stopping the MaxScale Load Balancer
- Application tests
- Simple application tests
- New command show shards
- More general test
- Less simple tests
- More complex application tests
- Cross-shard tests
- Operation of a MaxScale sharding system
- Do-on-all-shards
- Invalidating the database map cache
- How to change SchemaRouter variables dynamically?
- Adding and removing a tenant
- Moving a tenant
- Adding or removing a shard
- Customising the configuration files
- Maintenance work on the shard
- Observation / monitoring of a MariaDB MaxScale sharding system
- Literature / Sources
Overview
This feature should more or less work with MariaDB MaxScale 6.x.y, 22.08.x, 23.02.x, 23.08.x and 24.02.x. We have tested it with the latest MaxScale version 23.08.05, as we encountered problems with an older version (MXS-5026).
shell> maxscale --version MaxScale 23.08.5
We used MariaDB 10.11 as the database backend (shards).
Less than approx. 2% of all MariaDB installations known to us are what we technically understand by multi-tenant systems (each customer in its own database (also called a schema)).
This MariaDB MaxScale feature is therefore used relatively rarely and there is an increased risk of encountering bugs that no-one has come across before!
This feature is called SchemaRouter at MariadDB MaxScale and is still declared as beta quality (MXS-5025):
maxctrl> show module schemarouter ┌─────────────┬────────────────────────────────────────────────┐ │ Module │ schemarouter │ ├─────────────┼────────────────────────────────────────────────┤ │ Type │ Router │ ├─────────────┼────────────────────────────────────────────────┤ │ Version │ V1.0.0 │ ├─────────────┼────────────────────────────────────────────────┤ │ Maturity │ Beta │ ├─────────────┼────────────────────────────────────────────────┤ │ Description │ A database sharding router for simple sharding │ ├─────────────┼────────────────────────────────────────────────┤ │ ...
The target topology should look like this: Each customer (client, tenant) is located in its own database (= schema). The databases are distributed across several MariaDB instances (shards). So that the application can access the database transparently, a pair of MaxScale load balancers is connected in front of it, which knows where the customer is located and forwards the traffic to the shard accordingly. To ensure that the MaxScale load balancers are designed for high availability, a virtual IP (VIP) is connected upstream, e.g. using Keepalived. If this is still too simple for you, you can design each individual shard as a master/slave or Galera cluster construct...
Preparation of the shards (MariaDB database instances)
The first problem we had with this PoC was with the test database. By deleting the test database on all shards, the problem disappeared. Alternatively, you can run mariadb-secure-installation, which you should do on production systems anyway, or you can use the MaxScale configuration parameters: ignore_tables or ignore_tables_regex to allow the same tables in different shards (MXS-5027).
See also: MaxScale Router Parameters.
Create test data
So that we have something to play with, we have created test data:
-- On shard 1: 2 customers SQL> CREATE DATABASE customer_0010; SQL> CREATE TABLE customer_0010.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0010.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0010.address VALUES (1, 'Customer 10 GmbH'); SQL> INSERT INTO customer_0010.sales VALUES (1, 'Apples', 5, 1.2, 6), (2, 'Pears', 2, 0.9, 1.8), (3, 'Bread', 1, 2.5, 2.5); SQL> CREATE DATABASE customer_0011; SQL> CREATE TABLE customer_0011.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0011.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0011.address VALUES (1, 'Customer 11 SE'); SQL> INSERT INTO customer_0011.sales VALUES (1, 'Oranges', 2, 1.7, 3.4), (2, 'Salad', 5, 1.2, 6); -- On shard 2: 3 customers SQL> CREATE DATABASE customer_0020; SQL> CREATE TABLE customer_0020.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0020.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0020.address VALUES (1, 'Customer 20 AG'); SQL> INSERT INTO customer_0020.sales VALUES (1, 'Oranges', 2, 1.7, 3.4), (2, 'Salad', 5, 1.2, 6); SQL> CREATE DATABASE customer_0021; SQL> CREATE TABLE customer_0021.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0021.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0021.address VALUES (1, 'Customer 21 GmbH'); SQL> INSERT INTO customer_0021.sales VALUES (1, 'Oranges', 2, 1.7, 3.4), (2, 'Salad', 5, 1.2, 6); SQL> CREATE DATABASE customer_0022; SQL> CREATE TABLE customer_0022.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0022.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0022.address VALUES (1, 'Customer 22 Gebr.'); SQL> INSERT INTO customer_0022.sales VALUES (1, 'Oranges', 2, 1.7, 3.4), (2, 'Salad', 5, 1.2, 6); -- On shard 3: 1 customer SQL> CREATE DATABASE customer_0030; SQL> CREATE TABLE customer_0030.address (id INT UNSIGNED, name VARCHAR(255)); SQL> CREATE TABLE customer_0030.sales (id INT UNSIGNED, product VARCHAR(255), sales TINYINT, amount DECIMAL(6, 2), total_amount DECIMAL(6, 2)); SQL> INSERT INTO customer_0030.address VALUES (1, 'Customer 30 GmbH'); SQL> INSERT INTO customer_0030.sales VALUES (1, 'Pickles', 2, 2.2, 4.4), (2, 'Salad', 1, 3.1, 3.1), (3, 'Pudding', 5, 2.2, 11.0), (4, 'Asparagus', 12, .3, 3.6);
Create roles and users
Since in a sharded system, in contrast to a Galera cluster for example, the individual database instances do not know anything about each other and do not communicate with each other, we have to create the roles and users or accounts individually on EACH shard.
MariaDB MaxScale needs a user for the SchemaRouter service and the monitor (on each shard).
As the name suggests, the monitor user is responsible for monitoring and the SchemaRouter service user is responsible for collecting the user account information from the sharding backends and forwarding the queries to the correct shard.
Since a redundant system typically works with at least two MaxScale routers and we wanted to prevent the privileges of the accounts from diverging, we work with roles for both the MaxScale users and the application users.
MaxScale Monitor User
SQL> CREATE ROLE maxscale_monitor_role; SQL> GRANT SELECT ON mysql.user TO 'maxscale_monitor_role'; SQL> GRANT REPLICATION CLIENT ON *.* TO 'maxscale_monitor_role'; SQL> GRANT SLAVE MONITOR ON *.* TO 'maxscale_monitor_role'; SQL> GRANT FILE ON *.* TO 'maxscale_monitor_role'; SQL> GRANT CONNECTION ADMIN ON *.* TO 'maxscale_monitor_role'; SQL> SHOW GRANTS FOR maxscale_monitor_role; +-----------------------------------------------------------------------------------------------+ | Grants for maxscale_monitor_role | +-----------------------------------------------------------------------------------------------+ | GRANT FILE, BINLOG MONITOR, CONNECTION ADMIN, SLAVE MONITOR ON *.* TO `maxscale_monitor_role` | | GRANT SELECT ON `mysql`.`user` TO `maxscale_monitor_role` | +-----------------------------------------------------------------------------------------------+ SQL> CREATE USER maxscale_monitor@'10.139.158.210' IDENTIFIED BY 'secret'; SQL> CREATE USER maxscale_monitor@'10.139.158.211' IDENTIFIED BY 'secret'; SQL> GRANT maxscale_monitor_role TO maxscale_monitor@'10.139.158.210'; SQL> GRANT maxscale_monitor_role TO maxscale_monitor@'10.139.158.211'; SQL> SET DEFAULT ROLE maxscale_monitor_role FOR maxscale_monitor@'10.139.158.210'; SQL> SET DEFAULT ROLE maxscale_monitor_role FOR maxscale_monitor@'10.139.158.211'; SQL> SELECT user, host, is_role, default_role FROM mysql.user WHERE user LIKE 'maxscale_monitor%'; +-----------------------+----------------+---------+-----------------------+ | User | Host | is_role | default_role | +-----------------------+----------------+---------+-----------------------+ | maxscale_monitor_role | | Y | | | maxscale_monitor | 10.139.158.210 | N | maxscale_monitor_role | | maxscale_monitor | 10.139.158.211 | N | maxscale_monitor_role | +-----------------------+----------------+---------+-----------------------+ SQL> SHOW GRANTS FOR maxscale_monitor@'10.139.158.211'; +------------------------------------------------------------------------------------------------------------------------------+ | Grants for maxscale_monitor@10.139.158.211 | +------------------------------------------------------------------------------------------------------------------------------+ | GRANT `maxscale_monitor_role` TO `maxscale_monitor`@`10.139.158.211` | | GRANT USAGE ON *.* TO `maxscale_monitor`@`10.139.158.211` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `maxscale_monitor_role` FOR `maxscale_monitor`@`10.139.158.211` | +------------------------------------------------------------------------------------------------------------------------------+
MaxScale Admin User
SQL> CREATE ROLE maxscale_admin_role; SQL> GRANT SHOW DATABASES ON *.* TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.user TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.db TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.tables_priv TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.columns_priv TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.proxies_priv TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.roles_mapping TO 'maxscale_admin_role'; SQL> GRANT SELECT ON mysql.procs_priv TO 'maxscale_admin_role'; SQL> SHOW GRANTS FOR maxscale_admin_role; +------------------------------------------------------------------+ | Grants for maxscale_admin_role | +------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`user` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`roles_mapping` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`tables_priv` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`procs_priv` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`db` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`columns_priv` TO `maxscale_admin_role` | | GRANT SELECT ON `mysql`.`proxies_priv` TO `maxscale_admin_role` | +------------------------------------------------------------------+ SQL> CREATE USER maxscale_admin@'10.139.158.210' IDENTIFIED BY 'secret'; SQL> CREATE USER maxscale_admin@'10.139.158.211' IDENTIFIED BY 'secret'; SQL> GRANT maxscale_admin_role TO maxscale_admin@'10.139.158.210'; SQL> GRANT maxscale_admin_role TO maxscale_admin@'10.139.158.211'; SQL> SET DEFAULT ROLE maxscale_admin_role FOR maxscale_admin@'10.139.158.210'; SQL> SET DEFAULT ROLE maxscale_admin_role FOR maxscale_admin@'10.139.158.211'; SQL> SELECT user, host, is_role, default_role FROM mysql.user WHERE user LIKE 'maxscale_admin%'; +---------------------+----------------+---------+---------------------+ | User | Host | is_role | default_role | +---------------------+----------------+---------+---------------------+ | maxscale_admin_role | | Y | | | maxscale_admin | 10.139.158.210 | N | maxscale_admin_role | | maxscale_admin | 10.139.158.211 | N | maxscale_admin_role | +---------------------+----------------+---------+---------------------+ SQL> SHOW GRANTS FOR maxscale_admin@'10.139.158.211'; +----------------------------------------------------------------------------------------------------------------------------+ | Grants for maxscale_admin@10.139.158.211 | +----------------------------------------------------------------------------------------------------------------------------+ | GRANT `maxscale_admin_role` TO `maxscale_admin`@`10.139.158.211` | | GRANT USAGE ON *.* TO `maxscale_admin`@`10.139.158.211` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `maxscale_admin_role` FOR `maxscale_admin`@`10.139.158.211` | +----------------------------------------------------------------------------------------------------------------------------+
See also: SchemaRouter Configuration
Create application role and accounts
The application also requires a user, which we create here as on every shard as follows:
SQL> CREATE ROLE app_role; SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO 'app_role'; SQL> GRANT SHOW DATABASES ON *.* TO 'app_role'; SQL> GRANT CREATE, DROP, ALTER ON *.* TO 'app_role'; -- For creating new tenant databases SQL> SHOW GRANTS FOR app_role; +----------------------------------------------------------------------+ | Grants for app_role | +----------------------------------------------------------------------+ | GRANT SHOW DATABASES ON *.* TO `app_role` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `customer_%`.* TO `app_role` | +----------------------------------------------------------------------+ SQL> CREATE USER app@'10.139.158.%' IDENTIFIED BY 'secret'; SQL> GRANT app_role TO app@'10.139.158.%'; SQL> SET DEFAULT ROLE app_role FOR app@'10.139.158.%'; SQL> SELECT user, host, is_role, default_role FROM mysql.user WHERE user LIKE 'app%'; +----------+--------------+---------+--------------+ | User | Host | is_role | default_role | +----------+--------------+---------+--------------+ | app_role | | Y | | | app | 10.139.158.% | N | app_role | +----------+--------------+---------+--------------+ SQL> SHOW GRANTS FOR app@'10.139.158.%'; +---------------------------------------------------------------------------------------------------------------+ | Grants for app@10.139.158.% | +---------------------------------------------------------------------------------------------------------------+ | GRANT `app_role` TO `app`@`10.139.158.%` | | GRANT USAGE ON *.* TO `app`@`10.139.158.%` IDENTIFIED BY PASSWORD '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7' | | SET DEFAULT ROLE `app_role` FOR `app`@`10.139.158.%` | +---------------------------------------------------------------------------------------------------------------+
Proxy protocol
Load balancers and proxies have the property that they exchange the IP addresses of the clients with their own IP addresses. On the one hand, this means that you can no longer see where the client originally came from in the database and, on the other hand, you can no longer assign access authorisations to users and IPs, as the IP of the load balancer is always checked.
These two problems can be solved using the proxy protocol.
To do this, both the database and the load balancer, in this case MaxScale, must have the proxy protocol activated.
On the database side, the proxy protocol is activated as follows:
# # my.cnf # [mariadbd] proxy_protocol_networks = ::1, 10.139.158.0/24, localhost
and on the MaxScale side with:
# # /etc/maxscale.cnf # [shard] type = server proxy_protocol = true
You can check the two settings with:
SQL> SHOW GLOBAL VARIABLES LIKE 'proxy%'; +-------------------------+---------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------+ | proxy_protocol_networks | ::1, 10.139.158.0/24, localhost | +-------------------------+---------------------------------+ shell> maxctrl show server shard1 | grep proxy │ │ "proxy_protocol": true, │
Sources:
- The PROXY protocol, Versions 1 & 2
- MariaDB MaxScale Configuration Guide - Proxy Protocol
- MariaDB Knowledge Base: Proxy Protocol Support
MaxScale SchemaRouter configuration
Next, we prepare the MaxScale configuration for sharding. The file recommended by MariaDB is /etc/maxscale.cnf. Whether it makes more sense to create a separate configuration file under /etc/maxscale.cnf.d/ or even to configure the entire MaxScale dynamically (/var/lib/maxscale/maxscale.cnf.d/*.cnf) remains to be seen in the long term. See also warnings below. The configuration file for this sharding PoC looks like this:
# # /etc/maxscale.cnf # [maxscale] threads = auto admin_gui = false [shard1] type = server address = 10.139.158.1 port = 3363 proxy_protocol = true [shard2] type=server address=10.139.158.1 port=3364 proxy_protocol = true [shard3] type = server address = 10.139.158.1 port = 3365 proxy_protocol = true [sharding monitor] type = monitor module = galeramon servers = shard1,shard2,shard3 user = maxscale_monitor password = secret monitor_interval = 1s [Sharded-Service-Listener] type = listener service = Sharded-Service protocol = MariaDBClient port = 3306 [Sharded-Service] type = service router = schemarouter servers = shard1,shard2,shard3 user = maxscale_admin password = secret auth_all_servers = true
Note: Recommendation of the MaxScale developer: "One workaround might be to actually use galeramon to monitor the nodes instead of mariadbmon."
Starting and stopping the MaxScale Load Balancer
MaxScale is started and stopped as usual via SystemD:
shell> systemctl restart maxscale
shell> systemctl status maxscale
● maxscale.service - MariaDB MaxScale Database Proxy
Loaded: loaded (/lib/systemd/system/maxscale.service; enabled; vendor preset: enabled)
Drop-In: /run/systemd/system/service.d
└─zzz-lxc-service.conf
Active: active (running) since Tue 2024-02-27 09:52:57 UTC; 39s ago
Process: 187 ExecStart=/usr/bin/maxscale (code=exited, status=0/SUCCESS)
Main PID: 188 (maxscale)
Tasks: 10 (limit: 18663)
Memory: 4.6M
CPU: 150ms
CGroup: /system.slice/maxscale.service
└─188 /usr/bin/maxscale
systemd[1]: Starting MariaDB MaxScale Database Proxy...
maxscale[188]: Module 'galeramon' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libgaleramon.so'.
maxscale[188]: Module 'schemarouter' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libschemarouter.so'.
maxscale[188]: Using up to 2.3GiB of memory for query classifier cache
systemd[1]: Started MariaDB MaxScale Database Proxy.
If there were errors or warnings, you can see them in the MaxScale error log:
shell> grep -v notice /var/log/maxscale/maxscale.log 2024-02-13 16:47:22 MariaDB MaxScale is shut down. ---------------------------------------------------- MariaDB MaxScale /var/log/maxscale/maxscale.log Tue Feb 13 16:47:22 2024 ---------------------------------------------------------------------------- 2024-02-27 09:52:56 warning: Discarding journal file '/var/lib/maxscale/Sharding-Monitor_journal.json'. File is for module 'mariadbmon'. Current module is 'galeramon'. 2024-02-27 09:52:56 warning: [galeramon] Invalid 'wsrep_local_index' on server 'shard1': 18446744073709551615
Application tests
Simple application tests
shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --execute='show databases' +--------------------+ | Database | +--------------------+ | customer_0010 | | customer_0011 | | customer_0020 | | customer_0021 | | customer_0022 | | customer_0030 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+
New command show shards
shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0030 --execute='show shards' | grep customer_00.* | sort | column -t customer_0010.address shard1 customer_0010.sales shard1 customer_0010. shard1 customer_0011.address shard1 customer_0011.sales shard1 customer_0011. shard1 customer_0020.address shard2 customer_0020.sales shard2 customer_0020. shard2 customer_0021.address shard2 customer_0021.sales shard2 customer_0021. shard2 customer_0022.address shard2 customer_0022.sales shard2 customer_0022. shard2 customer_0030.address shard3 customer_0030.sales shard3 customer_0030. shard3
New databases are not displayed immediately, but only when the cached data has been updated (refresh_interval (300s / 5 min)).
See also: Custom SQL commands
More general test
As a reminder:
| Shard | Port | Customer | State |
|---|---|---|---|
| #1 | 3363 | customer_001<n> | Running |
| #2 | 3364 | customer_002<n> | Running |
| #3 | 3365 | customer_003<n> | Running |
| #4 | 3366 | customer_004<n> | Running |
shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3363 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --database=customer_0010 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3363 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --database=customer_0020 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3364 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 --execute='use customer_0020; SELECT @@port' +--------+ | @@port | +--------+ | 3364 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0010 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3363 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0020 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3364 | +--------+ shell> mariadb --user=app --password=secret --host=10.139.158.211 --port=3306 customer_0030 --execute='SELECT @@port' +--------+ | @@port | +--------+ | 3365 | +--------+
Less simple (backup) test
shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction customer_0010 > /tmp/customer_0010.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction customer_0020 > /tmp/customer_0020.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction customer_0030 > /tmp/customer_0030.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction --databases customer_0011 > /tmp/customer_0011.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction --databases customer_0021 > /tmp/customer_0021.sql shell> echo $? 0 shell> mariadb-dump --user=app --password=secret --host=10.139.158.211 --port=3306 --single-transaction --databases customer_0030 > /tmp/customer_0030.sql shell> echo $? 0 shell> ll /tmp/customer_00*sql -rw-rw-r-- 1 oli oli 2738 Mar 18 12:07 /tmp/customer_0010.sql -rw-rw-r-- 1 oli oli 2904 Mar 18 12:08 /tmp/customer_0011.sql -rw-rw-r-- 1 oli oli 2712 Mar 18 12:08 /tmp/customer_0020.sql -rw-rw-r-- 1 oli oli 2906 Mar 18 12:08 /tmp/customer_0021.sql -rw-rw-r-- 1 oli oli 2964 Mar 18 12:08 /tmp/customer_0030.sql shell> tail -n 1 /tmp/customer_*.sql ==> /tmp/customer_0010.sql <== -- Dump completed on 2024-02-13 14:39:21 ==> /tmp/customer_0011.sql <== -- Dump completed on 2024-02-13 14:39:35 ==> /tmp/customer_0020.sql <== -- Dump completed on 2024-02-13 14:40:15 ==> /tmp/customer_0021.sql <== -- Dump completed on 2024-02-13 14:40:42 ==> /tmp/customer_0030.sql <== -- Dump completed on 2024-02-13 14:40:52 shell> cat /tmp/customer_00*sql | grep -A1 -i insert INSERT INTO `address` VALUES (1,'Customer 10 GmbH'); -- INSERT INTO `sales` VALUES (1,'Apples',5,1.20,6.00), -- INSERT INTO `address` VALUES (1,'Customer 11 SE'); -- INSERT INTO `sales` VALUES (1,'Oranges',2,1.70,3.40), -- INSERT INTO `address` VALUES (1,'Customer 20 AG'); -- INSERT INTO `sales` VALUES (1,'Oranges',2,1.70,3.40), -- INSERT INTO `address` VALUES (1,'Customer 21 GmbH'); -- INSERT INTO `sales` VALUES (1,'Oranges',2,1.70,3.40), -- INSERT INTO `address` VALUES (1,'Customer 30 GmbH'); -- INSERT INTO `sales` VALUES (1,'Pickles',2,2.20,4.40),
In MaxScale 23.08.4 there was a pretty bad bug: A return value of 0 but no data in the backup!!! See also the tickets: MXS-4966: mariadb-dump gets an error dumping schemas and MXS-4947: Tables in information_schema are treated as a normal tables. Symptoms of the bug look like this:
Error: Couldn't read status information for table address () Error: Couldn't read status information for table sales ()
We therefore strongly recommend upgrading to MaxScale 23.08.5!
More complex application tests
We have created a somewhat more complex test (./sharding_test.php) that processes the following queries:
SET NAMES utf8mb4 SHOW DATABASES use customer_START TRANSACTION; SELECT MIN(id) AS first, MAX(id) AS last FROM `sales` INSERT INTO sales (id, product, sales, amount, total_amount) VALUES (%d, '%s', %f, %f, %f) INSERT INTO sales (id, product, sales, sales, amount, total_amount) VALUES (%d, '%s', %f, %f, %f) UPDATE sales SET product = 'Prepare to delete' WHERE id = %d DELETE FROM sales WHERE id = %d COMMIT
This test ran flawlessly. The corresponding control query:
SQL> SELECT * FROM customer_0021.sales WHERE id >= (SELECT MAX(id) - 10 FROM customer_0021.sales);
Various load scenarios can also be tested with db_bench or the Acronis perfkit. For more information, see here.
Cross-shard tests
In any case, you might come up with the idea of running cross-shard queries. This will NOT work, which should not really be surprising, firstly because it is not easy to implement and secondly because it is described here:
"Note: As the sharding solution in MaxScale is relatively simple, cross-database queries between two or more shards are not supported."
Source: Simple Sharding with Two Servers
and
"USE db1 is routed to the server with db1. If the database is divided to multiple servers, only one server will get the command."
Source: SchemaRouter.
Here is a test with UNION:
SQL> use customer_0030 Database changed SQL> SELECT * FROM customer_0020.sales UNION SELECT * FROM customer_0030.sales; ERROR 1146 (42S02): Table 'customer_0020.sales' doesn't exist
And here is the proof to the contrary:
SQL> use customer_0020 Database changed SQL> SELECT * FROM customer_0020.sales UNION SELECT * FROM customer_0030.sales; ERROR 1146 (42S02): Table 'customer_0030.sales' doesn't exist
And here is the test with JOIN:
SQL> use customer_0020 SQL> SELECT * FROM customer_0020.sales a JOIN customer_0030.sales b ON a.id = b.id WHERE a.sales > 1 ; ERROR 1146 (42S02): Table 'customer_0030.sales' doesn't exist SQL> use customer_0030 SQL> SELECT * FROM customer_0020.sales a JOIN customer_0030.sales b ON a.id = b.id WHERE a.sales > 1 ; ERROR 1146 (42S02): Table 'customer_0020.sales' doesn't exist
Operation of a MaxScale sharding system
In this chapter we discuss some points that can be useful for the operation of a MariaDB MaxScale sharding system.
Do-on-all-shards
Since it can always happen that O/S or database operations have to be executed on all shards, it would certainly make sense to create a script that executes the same command on all shards in turn:
shell> ./do-on-all-shards.sh --sql='SHOW DATABASES'
A script of this type should greatly reduce the error rate during operation. Operations such as the re-sharding of a tenant, as described below, are also sensibly scripted and executed centrally (MXS-5029).
Invalidating the database map cache
The invalidate command can be used to invalidate the database map cache of the MariaDB MaxScale SchemaRouter. This allows us to quickly update the cache after adding or removing tenants.
shell> maxctrl call command schemarouter invalidate Sharded-Service OK
In contrast to the invalidate command, which updates the entries after the next refresh_interval, the clear command deletes the entries and a remap is executed immediately.
If you want to invalidate the database map cache remotely with a REST API call, you can do this as follows:
shell> curl -i -X POST -u api_admin:secret http://10.139.158.211:8989/v1/maxscale/modules/schemarouter/clear?Sharded-Service HTTP/1.1 204 No Content Connection: close Date: Mon, 18 Mar 24 11:49:58 GMT X-Frame-Options: Deny X-XSS-Protection: 1 Referrer-Policy: same-origin Cache-Control: no-cache
Sources:
How to change SchemaRouter variables dynamically?
The refresh_interval specifies the lifetime of the entries in the SchemaRouter Database Map Cache. The default value is 300 s (5 min). Refresh Interval is therefore, in my opinion, an unfortunate term as it does not define the interval between two mappings but the lifetime of the cache entries (livetime?, timeout?). As soon as the entry has been deleted, a new refresh of the "database map" is triggered on each shard. The command currently looks like this:
SELECT LOWER(t.table_schema), LOWER(t.table_name) FROM information_schema.tables t UNION ALL SELECT LOWER(s.schema_name), '' FROM information_schema.schemata s
It looks like a simple connect is enough to trigger the refresh of the database map.
The current value for refresh_interval can be queried as follows:
shell> maxctrl show service Sharded-Service | grep refresh_interval | awk -F'│' '{ print $3 }'
"refresh_interval": "300000ms",
The following command helps to change the value dynamically:
shell> MAXCTRL_WARNINGS=0 maxctrl alter service Sharded-Service refresh_interval=10s OK
The value should not be set too small, as all other connections are stopped during the mapping process.
Sources:
Adding and removing a tenant
Adding a new tenant to a shard is not a major problem:
SQL> CREATE DATABASE customer_0029; SQL> use customer_0029 SQL> CREATE TABLE address LIKE customer_template.address; SQL> CREATE TABLE sales LIKE customer_template.sales; shell> maxctrl call command schemarouter invalidate Sharded-Service OK
Removing a tenant from a shard, on the other hand, is somewhat more complicated and must be done in consultation with the application:
SQL> DROP DATABASE customer_0011; shell> ./sharding_test.php .....ERROR: Table 'customer_0011.sales' doesn't exist...ERROR: Unknown database 'customer_0011'.ERROR: Unknown database 'customer_0011'......ERROR: Unknown database 'customer_0011'... shell> maxctrl call command schemarouter clear Sharded-Service OK
At least I have not come up with a cleverer variant yet. See also Moving a tenant below.
Moving a tenant
The combination of adding and removing would then be moving a tenant from one shard to another shard, also known as re-sharding. This also requires a concerted action to be planned together with the application.
If this is not possible, at least the time that the application receives errors can be reduced... The following procedure can be used to move a tenant from shard 2 to shard 3:
SQL> use customer_0020; LOCK TABLES address READ, sales READ; -- On Shard 2, application will be blocked at best! shell> mariadb-dump --user=app --password=secret --host=10.139.158.1 --port=3364 --single-transaction --skip-add-locks --databases customer_0020 | mariadb --user=app --password=secret --host=10.139.158.1 --port=3365 # Copy tenant 20 from shard 2 to shard 3 SQL> DROP DATABASE customer_0020; -- Deleting tenant 20 does not work! ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction SQL> UNLOCK TABLES; DROP DATABASE customer_0020; # How to delete tenant 20. shell> maxctrl call command schemarouter clear Sharded-Service # Update MaxScale Database Map. Do it quickly!!!
Until the database map is refreshed, the following errors may occur:
error : (47621) [schemarouter] (Sharded-Service); 'customer_0020.' found on servers 'shard2','shard3' for user 'app'@'10.139.158.1'. error : (47621) [schemarouter] (Sharded-Service); 'customer_0020.address' found on servers 'shard2','shard3' for user 'app'@'10.139.158.1'. error : (47621) [schemarouter] (Sharded-Service); 'customer_0020.sales' found on servers 'shard2','shard3' for user 'app'@'10.139.158.1'. error : (47621) [schemarouter] (Sharded-Service); Duplicate tables found, closing session.
And on the application side too:
ERROR: Error: duplicate tables found on two different shards
Adding or removing a shard
Moving a tenant from one shard to another shard is a small re-sharding operation. It becomes somewhat more complex if you want to add new shards or remove old shards. Subsequently (after the addition or before the removal), a large re-sharding would then take place. The first step is to add a shard to the cluster:
shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬───────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 0 │ Running │ 0-3363-26014 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 0 │ Running │ 0-3364-240612 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 0 │ Running │ 0-3365-289873 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴───────────────┴──────────────────┘
The prepared shard is made known to MaxScale:
shell> maxctrl create server shard4 10.139.158.1 3366 OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 1 │ Running │ 0-3363-23676 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-52321 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-39751 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 0 │ Down │ │ │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘
The new shard is then linked to the MaxScale Monitor and the service:
shell> MAXCTRL_WARNINGS=0 maxctrl link monitor Sharding monitor shard4 OK shell> MAXCTRL_WARNINGS=0 maxctrl link service Sharded service shard4 OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 1 │ Running │ 0-3363-24961 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-56215 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-45177 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-32 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘
Whether this second step is also absolutely necessary was not investigated.
You can follow the entire process in the MariaDB MaxScale error log:
warning: Discarding journal file '/var/lib/maxscale/Sharding-Monitor_journal.json'. Servers described in the journal are different from the ones configured on the current monitor. warning: Saving runtime modifications to 'Sharding-Monitor' in '/var/lib/maxscale/maxscale.cnf.d/Sharding-Monitor.cnf'. The modified values will override the values found in the static configuration files. notice : shard4 sent version string '10.11.7-MariaDB-log'. Detected type: MariaDB, version: 10.11.7. notice : Server 'shard4' charset: latin1_swedish_ci notice : Server changed state: shard4[10.139.158.1:3366]: server_up. [Down] -> [Running] warning: Saving runtime modifications to 'Sharded-Service' in '/var/lib/maxscale/maxscale.cnf.d/Sharded-Service.cnf'. The modified values will override the values found in the static configuration files. notice : Added 'shard4' to 'Sharded-Service'
What we must not forget here is to also equip the new shard with the proxy protocol:
shell> maxctrl show server shard4 | grep proxy │ │ "proxy_protocol": false, │ MAXCTRL_WARNINGS=0 maxctrl alter server shard4 proxy_protocol=true OK
And now new tenants can be added to the new shard or old tenants can be moved to the new shard... In our setup, we want to move all tenants from shard 1 to shard 4 and also create a new tenant customer_0040 on shard 4. The individual steps required for this are listed above.
Once shard 1 has been emptied, it can be dismantled:
shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 1 │ Running │ 0-3363-25916 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-62887 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-54035 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-2247 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘
A shard is deleted with the destroy server command. Before this works, however, a shard must be removed from the monitor and the service:
shell> MAXCTRL_WARNINGS=0 maxctrl unlink service Sharded service shard1 OK shell> MAXCTRL_WARNINGS=0 maxctrl unlink monitor Sharding monitor shard1 OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard1 │ 10.139.158.1 │ 3363 │ 0 │ Running │ │ │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-64394 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-56072 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-3267 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘
Once the shard has been removed from the monitor and the service, it can then be deleted:
shell> maxctrl destroy server shard1
Warning: Object 'shard1' is defined in a static configuration file and cannot be permanently deleted. If MaxScale is restarted, the object will appear again.
To hide these warnings, run:
export MAXCTRL_WARNINGS=0
OK
shell> maxctrl list servers
┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐
│ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │
├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤
│ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-65018 │ Sharding-Monitor │
├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤
│ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-56886 │ Sharding-Monitor │
├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤
│ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-3648 │ Sharding-Monitor │
└────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘
And you can follow the changes in the MaxScale Error Log:
notice : Removed 'shard1' from 'Sharded-Service' warning: Discarding journal file '/var/lib/maxscale/Sharding-Monitor_journal.json'. Servers described in the journal are different from the ones configured on the current monitor. notice : Destroyed server 'shard1' at 10.139.158.1:3363
Important: I was informed that with destroy server --force the unlink service and unlink monitor commands are automatically executed by MaxScale.
Source:
Customising the configuration files
During the shard operations described above we received some warnings:
Warning: Object 'shard1' is defined in a static configuration file and cannot be permanently deleted. If MaxScale is restarted, the object will appear again.
and
Warning: Saving runtime modifications to 'Sharding-Monitor' in '/var/lib/maxscale/maxscale.cnf.d/Sharding-Monitor.cnf'. The modified values will override the values found in the static configuration files.
The corresponding configuration files are automatically created by MaxScale when dynamic system changes are made:
shell> ll /var/lib/maxscale/maxscale.cnf.d/ /etc/maxscale.cnf -rw-r--r-- 1 root root 612 Feb 13 14:23 /etc/maxscale.cnf /var/lib/maxscale/maxscale.cnf.d/: total 12 -rw------- 1 maxscale maxscale 187 Feb 13 16:08 Sharding-Monitor.cnf -rw------- 1 maxscale maxscale 150 Feb 13 16:07 Sharded-Service.cnf -rw------- 1 maxscale maxscale 52 Feb 13 15:46 shard4.cnf cat /var/lib/maxscale/maxscale.cnf.d/* [Sharded-Service] debug=true refresh_interval=10000ms auth_all_servers=true log_debug=true password=secret router=schemarouter type=service user=maxscale_admin targets=shard2,shard3,shard4 [sharding monitor] module=galeramon monitor_interval=1000ms password=secret servers=shard2,shard3,shard4 type=monitor user=maxscale_monitor [shard4] address=10.139.158.1 port=3366 type=server
The configuration files still need to be improved accordingly. You should generally consider whether you should not configure everything dynamically via commands in a highly dynamic system...
Maintenance work on the shard
If a shard is to be taken offline for maintenance work, here in the example shard2, this can be done as follows:
shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬──────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 1 │ Running │ 0-3364-69817 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 1 │ Running │ 0-3365-63166 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼──────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 1 │ Running │ 0-3366-6902 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴──────────────┴──────────────────┘ shell> maxctrl set server shard2 drain OK shell> maxctrl set server shard2 maintenance OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬──────────────────────┬───────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 0 │ Maintenance, Running │ 0-3364-240612 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 0 │ Running │ 0-3365-289873 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼──────────────────────┼───────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 0 │ Running │ 0-3366-119848 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴──────────────────────┴───────────────┴──────────────────┘
At this point, maintenance work can be carried out on the machine or the database...
Afterwards, BOTH statuses must be cleared again if both have been set (MXS-5028):
shell> maxctrl clear server shard2 maintenance OK shell> maxctrl clear server shard2 drain OK shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬───────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 0 │ Running │ 0-3364-240612 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 0 │ Running │ 0-3365-289873 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 0 │ Running │ 0-3366-119848 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴───────────────┴──────────────────┘
The difference between drain and maintenance is that with drain, no new connections are allowed to the shard, but existing connections wait until they are closed. With maintenance, the connections are terminated immediately by force.
Observation of a MariaDB MaxScale sharding system
The MaxScale CLI client maxtrl can be used to query the status of the MariaDB MaxScale load balancer. There are numerous commands for this, mainly list and show:
shell> maxctrl show module schemarouter | head -n 12 ┌─────────────┬────────────────────────────────────────────────┐ │ Module │ schemarouter │ ├─────────────┼────────────────────────────────────────────────┤ │ Type │ Router │ ├─────────────┼────────────────────────────────────────────────┤ │ Version │ V1.0.0 │ ├─────────────┼────────────────────────────────────────────────┤ │ Maturity │ Beta │ ├─────────────┼────────────────────────────────────────────────┤ │ Description │ A database sharding router for simple sharding │ ├─────────────┼────────────────────────────────────────────────┤ │ Parameters │ ... │ shell> maxctrl list servers ┌────────┬──────────────┬──────┬─────────────┬─────────┬───────────────┬──────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard2 │ 10.139.158.1 │ 3364 │ 4 │ Running │ 0-3364-290859 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard3 │ 10.139.158.1 │ 3365 │ 4 │ Running │ 0-3365-322671 │ Sharding-Monitor │ ├────────┼──────────────┼──────┼─────────────┼─────────┼───────────────┼──────────────────┤ │ shard4 │ 10.139.158.1 │ 3366 │ 4 │ Running │ 0-3366-140018 │ Sharding-Monitor │ └────────┴──────────────┴──────┴─────────────┴─────────┴───────────────┴──────────────────┘
The information for the Connections column is confusing because in this case we only have 1, 1 and 2 connections open on each shard in this sharding system.
However, if you look at the situation on the respective shard with SHOW PROCESSLIST, you can see that MaxScale also establishes a connection on EACH shard for each incoming connection. So the display above is actually technically correct, just not what you would expect:
SQL> SHOW PROCESSLIST; +--------+------------------+----------------------+---------------+---------+------+----------+-----------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +--------+------------------+----------------------+---------------+---------+------+----------+-----------------------------------------------------------------+----------+ | 123 | root | localhost | customer_0021 | Query | 0 | starting | show processlist | 0.000 | | 68107 | maxscale_monitor | 10.139.158.211:35418 | NULL | Sleep | 0 | | NULL | 0.000 | | 113372 | app | 10.139.158.1:47548 | NULL | Sleep | 47 | | NULL | 0.000 | | 113538 | app | 10.139.158.1:49058 | NULL | Sleep | 41 | | NULL | 0.000 | | 113662 | app | 10.139.158.1:47072 | NULL | Sleep | 37 | | NULL | 0.000 | | 114789 | app | 10.139.158.1:39574 | customer_0022 | Query | 0 | Updating | UPDATE sales SET product = 'Prepare to delete' WHERE id = 15622 | 0.000 | +--------+------------------+----------------------+---------------+---------+------+----------+-----------------------------------------------------------------+----------+
This does not scale with large systems with hundreds or thousands of clients! Maybe the MariaDB thread pool feature is used in this case.
According to the MaxScale developer, this is expected behaviour... (MXS-4977)
shell> maxctrl list services
┌─────────────────┬──────────────┬─────────────┬───────────────────┬────────────────────────┐
│ Service │ Router │ Connections │ Total Connections │ Targets │
├─────────────────┼──────────────┼─────────────┼───────────────────┼────────────────────────┤
│ Sharded-Service │ schemarouter │ 4 │ 82776 │ shard2, shard3, shard4 │
└─────────────────┴──────────────┴─────────────┴───────────────────┴────────────────────────┘
shell> maxctrl list listeners
┌──────────────────────────┬──────┬──────┬─────────┬─────────────────┐
│ Name │ Port │ Host │ State │ Service │
├──────────────────────────┼──────┼──────┼─────────┼─────────────────┤
│ Sharded-Service-Listener │ 3306 │ :: │ Running │ Sharded-Service │
└──────────────────────────┴──────┴──────┴─────────┴─────────────────┘
shell> maxctrl list monitors
┌──────────────────┬─────────┬────────────────────────┐
│ Monitor │ State │ Servers │
├──────────────────┼─────────┼────────────────────────┤
│ Sharding-Monitor │ Running │ shard2, shard3, shard4 │
└──────────────────┴─────────┴────────────────────────┘
shell> maxctrl show server shard2 | head -n 20
┌─────────────────────┬──────────────────────────────────────────────┐
│ Server │ shard2 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Source │ /etc/maxscale.cnf │
├─────────────────────┼──────────────────────────────────────────────┤
│ Address │ 10.139.158.1 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Port │ 3364 │
├─────────────────────┼──────────────────────────────────────────────┤
│ State │ Running │
├─────────────────────┼──────────────────────────────────────────────┤
│ Version │ 10.11.7-MariaDB-log │
├─────────────────────┼──────────────────────────────────────────────┤
│ Uptime │ 178960 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Last Event │ server_down │
├─────────────────────┼──────────────────────────────────────────────┤
│ Triggered At │ Sun, 04 Feb 2024 07:37:17 GMT │
├─────────────────────┼──────────────────────────────────────────────┤
│ Services │ Sharded-Service │
├─────────────────────┼──────────────────────────────────────────────┤
│ Monitors │ Sharding-Monitor │
├─────────────────────┼──────────────────────────────────────────────┤
...
├─────────────────────┼──────────────────────────────────────────────┤
│ Current Connections │ 5 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Total Connections │ 27 │
├─────────────────────┼──────────────────────────────────────────────┤
│ Max Connections │ 5 │
shell> maxctrl show service Sharded-Service
┌─────────────────────┬──────────────────────────────────────────────────────┐
│ Service │ Sharded-Service │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Source │ /var/lib/maxscale/maxscale.cnf.d/Sharded-Service.cnf │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Router │ schemarouter │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ State │ Started │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Started At │ 3/18/2024, 1:52:30 PM │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Users Loaded At │ 3/18/2024, 1:52:30 PM │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Current Connections │ 4 │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Total Connections │ 84590 │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Max Connections │ 5 │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Cluster │ │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Servers │ shard2 │
│ │ shard3 │
│ │ shard4 │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Services │ │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Filters │ │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Parameters │ { │
│ │ "auth_all_servers": true, │
│ │ "connection_keepalive": "300000ms", │
│ │ "debug": true, │
│ │ "disable_sescmd_history": false, │
│ │ "enable_root_user": false, │
│ │ "force_connection_keepalive": false, │
│ │ "idle_session_pool_time": "-1ms", │
│ │ "ignore_tables": [], │
│ │ "ignore_tables_regex": null, │
│ │ "localhost_match_wildcard_host": true, │
│ │ "log_auth_warnings": true, │
│ │ "log_debug": true, │
│ │ "log_info": false, │
│ │ "log_notice": false, │
│ │ "log_warning": false, │
│ │ "max_connections": 0, │
│ │ "max_sescmd_history": 50, │
│ │ "max_staleness": "150000ms", │
│ │ "multiplex_timeout": "60000ms", │
│ │ "net_write_timeout": "0ms", │
│ │ "password": "*****", │
│ │ "prune_sescmd_history": true, │
│ │ "rank": "primary", │
│ │ "refresh_databases": false, │
│ │ "refresh_interval": "10000ms", │
│ │ "retain_last_statements": -1, │
│ │ "router": "schemarouter", │
│ │ "session_trace": false, │
│ │ "strip_db_esc": true, │
│ │ "type": "service", │
│ │ "user": "maxscale_admin", │
│ │ "user_accounts_file": null, │
│ │ "user_accounts_file_usage": "add_when_load_ok", │
│ │ "version_string": null, │
│ │ "wait_timeout": "0ms" │
│ │ } │
├─────────────────────┼──────────────────────────────────────────────────────┤
│ Router Diagnostics │ { │
│ │ "average_session": 0.028822357131634554, │
│ │ "longest_sescmd_chain": 4, │
│ │ "longest_session": 50, │
│ │ "queries": 761134, │
│ │ "sescmd_percentage": 44.44342257736483, │
│ │ "shard_map_hits": 84356, │
│ │ "shard_map_misses": 5, │
│ │ "shard_map_stale": 229, │
│ │ "shard_map_updates": 216, │
│ │ "shortest_session": 0, │
│ │ "times_sescmd_limit_exceeded": 0 │
│ │ } │
└─────────────────────┴──────────────────────────────────────────────────────┘
See also MaxScale SchemaRouter Router diagnostics.
shell> maxctrl show monitor Sharding-Monitor
┌─────────────────────┬──────────────────────────────────────────────────────────┐
│ Monitor │ Sharding-Monitor │
├─────────────────────┼──────────────────────────────────────────────────────────┤
│ Source │ /etc/maxscale.cnf │
├─────────────────────┼──────────────────────────────────────────────────────────┤
│ Module │ galeramon │
├─────────────────────┼──────────────────────────────────────────────────────────┤
│ State │ Running │
├─────────────────────┼──────────────────────────────────────────────────────────┤
│ Servers │ shard1 │
│ │ shard2 │
│ │ shard3 │
├─────────────────────┼──────────────────────────────────────────────────────────┤
│ Parameters │ { │
│ │ "available_when_donor": false, │
│ │ "backend_connect_attempts": 1, │
│ │ "backend_connect_timeout": "3000ms", │
│ │ "backend_read_timeout": "3000ms", │
│ │ "backend_write_timeout": "3000ms", │
│ │ "disable_master_failback": false, │
│ │ "disable_master_role_setting": false, │
│ │ "disk_space_check_interval": "0ms", │
│ │ "disk_space_threshold": null, │
│ │ "events": "all,master_down,master_up,...,new_donor", │
│ │ "journal_max_age": "28800000ms", │
│ │ "module": "galeramon", │
│ │ "monitor_interval": "1000ms", │
│ │ "password": "*****", │
│ │ "root_node_as_master": false, │
│ │ "script": null, │
│ │ "script_timeout": "90000ms", │
│ │ "set_donor_nodes": false, │
│ │ "type": "monitor", │
│ │ "use_priority": false, │
│ │ "user": "maxscale_monitor" │
│ │ } │
├─────────────────────┼──────────────────────────────────────────────────────────┤
│ Monitor Diagnostics │ { │
│ │ "disable_master_failback": false, │
│ │ "disable_master_role_setting": false, │
│ │ "root_node_as_master": false, │
│ │ "server_info": [ │
│ │ { │
│ │ "gtid_binlog_pos": "0-3363-26014", │
│ │ "gtid_current_pos": "0-3363-26014", │
│ │ "master_id": 0, │
│ │ "name": "shard1", │
│ │ "read_only": false, │
│ │ "server_id": 3363 │
│ │ }, │
│ │ { │
│ │ "gtid_binlog_pos": "0-3364-240612", │
│ │ "gtid_current_pos": "0-3364-240612", │
│ │ "master_id": 0, │
│ │ "name": "shard2", │
│ │ "read_only": false, │
│ │ "server_id": 3364 │
│ │ }, │
│ │ { │
│ │ "gtid_binlog_pos": "0-3365-289873", │
│ │ "gtid_current_pos": "0-3365-289873", │
│ │ "master_id": 0, │
│ │ "name": "shard3", │
│ │ "read_only": false, │
│ │ "server_id": 3365 │
│ │ } │
│ │ ], │
│ │ "set_donor_nodes": false, │
│ │ "use_priority": false │
│ │ } │
└─────────────────────┴──────────────────────────────────────────────────────────┘
shell> maxctrl list sessions;
┌───────┬──────┬──────────────┬───────────────────────┬───────┬─────────────────┬────────┬──────────────┐
│ Id │ User │ Host │ Connected │ Idle │ Service │ Memory │ I/O-Activity │
├───────┼──────┼──────────────┼───────────────────────┼───────┼─────────────────┼────────┼──────────────┤
│ 87240 │ app │ 10.139.158.1 │ 3/18/2024, 2:33:54 PM │ 0 │ Sharded-Service │ 68644 │ 33 │
├───────┼──────┼──────────────┼───────────────────────┼───────┼─────────────────┼────────┼──────────────┤
│ 72654 │ app │ 10.139.158.1 │ 3/18/2024, 2:25:27 PM │ 506.3 │ Sharded-Service │ 199328 │ 0 │
├───────┼──────┼──────────────┼───────────────────────┼───────┼─────────────────┼────────┼──────────────┤
│ 72364 │ app │ 10.139.158.1 │ 3/18/2024, 2:25:18 PM │ 516 │ Sharded-Service │ 199328 │ 0 │
├───────┼──────┼──────────────┼───────────────────────┼───────┼─────────────────┼────────┼──────────────┤
│ 72530 │ app │ 10.139.158.1 │ 3/18/2024, 2:25:23 PM │ 510.5 │ Sharded-Service │ 199328 │ 0 │
└───────┴──────┴──────────────┴───────────────────────┴───────┴─────────────────┴────────┴──────────────┘
shell> maxctrl show session 26
┌───────────────────────┬───────────────────────────────────────┐
│ Id │ 26 │
├───────────────────────┼───────────────────────────────────────┤
│ Service │ Sharded-Service │
├───────────────────────┼───────────────────────────────────────┤
│ State │ Session started │
├───────────────────────┼───────────────────────────────────────┤
│ User │ app │
├───────────────────────┼───────────────────────────────────────┤
│ Host │ 10.139.158.1 │
├───────────────────────┼───────────────────────────────────────┤
│ Port │ 42854 │
├───────────────────────┼───────────────────────────────────────┤
│ Database │ │
├───────────────────────┼───────────────────────────────────────┤
│ Connected │ 2/4/2024, 9:31:12 AM │
├───────────────────────┼───────────────────────────────────────┤
│ Idle │ 610.4 │
├───────────────────────┼───────────────────────────────────────┤
│ Parameters │ { │
│ │ "log_error": false, │
│ │ "log_info": false, │
│ │ "log_notice": false, │
│ │ "log_warning": false │
│ │ } │
├───────────────────────┼───────────────────────────────────────┤
│ Client TLS Cipher │ │
├───────────────────────┼───────────────────────────────────────┤
│ Connection attributes │ { │
│ │ "_client_name": "libmariadb", │
│ │ "_client_version": "3.3.8", │
│ │ "_os": "Linux", │
│ │ "_pid": "251037", │
│ │ "_platform": "x86_64", │
│ │ "_server_host": "10.139.158.211", │
│ │ "program_name": "mysql" │
│ │ } │
├───────────────────────┼───────────────────────────────────────┤
│ Connections │ shard1 │
│ │ shard2 │
│ │ shard3 │
├───────────────────────┼───────────────────────────────────────┤
│ Connection IDs │ 666 │
│ │ 139 │
│ │ 138 │
├───────────────────────┼───────────────────────────────────────┤
│ Queries │ │
├───────────────────────┼───────────────────────────────────────┤
│ Log │ │
├───────────────────────┼───────────────────────────────────────┤
│ Memory │ { │
│ │ "connection_buffers": { │
│ │ "backends": { │
│ │ "shard1": { │
│ │ "misc": 678, │
│ │ "readq": 65536, │
│ │ "total": 66214, │
│ │ "writeq": 0 │
│ │ }, │
│ │ "shard2": { │
│ │ "misc": 662, │
│ │ "readq": 0, │
│ │ "total": 662, │
│ │ "writeq": 0 │
│ │ }, │
│ │ "shard3": { │
│ │ "misc": 678, │
│ │ "readq": 65536, │
│ │ "total": 66214, │
│ │ "writeq": 0 │
│ │ } │
│ │ }, │
│ │ "client": { │
│ │ "misc": 654, │
│ │ "readq": 65536, │
│ │ "total": 66190, │
│ │ "writeq": 0 │
│ │ }, │
│ │ "total": 199280 │
│ │ }, │
│ │ "exec_metadata": 0, │
│ │ "last_queries": 0, │
│ │ "sescmd_history": 48, │
│ │ "total": 199328, │
│ │ "variables": 0 │
│ │ } │
├───────────────────────┼───────────────────────────────────────┤
│ I/O Activity │ 0 │
└───────────────────────┴───────────────────────────────────────┘
shell> maxctrl show listener Sharded-Service-Listener
┌────────────┬───────────────────────────────────────────┐
│ Name │ Sharded-Service-Listener │
├────────────┼───────────────────────────────────────────┤
│ Source │ /etc/maxscale.cnf │
├────────────┼───────────────────────────────────────────┤
│ Service │ Sharded-Service │
├────────────┼───────────────────────────────────────────┤
│ Parameters │ { │
│ │ "MariaDBProtocol": { │
│ │ "allow_replication": true │
│ │ }, │
│ │ "address": "::", │
│ │ "authenticator": null, │
│ │ "authenticator_options": null, │
│ │ "connection_init_sql_file": null, │
│ │ "connection_metadata": [ │
│ │ "character_set_client=auto", │
│ │ "character_set_connection=auto", │
│ │ "character_set_results=auto", │
│ │ "max_allowed_packet=auto", │
│ │ "system_time_zone=auto", │
│ │ "time_zone=auto", │
│ │ "tx_isolation=auto" │
│ │ ], │
│ │ "port": 3306, │
│ │ "protocol": "MariaDBProtocol", │
│ │ "proxy_protocol_networks": null, │
│ │ "service": "Sharded-Service", │
│ │ "socket": null, │
│ │ "sql_mode": "default", │
│ │ "ssl": false, │
│ │ "ssl_ca": null, │
│ │ "ssl_cert": null, │
│ │ "ssl_cert_verify_depth": 9, │
│ │ "ssl_cipher": null, │
│ │ "ssl_crl": null, │
│ │ "ssl_key": null, │
│ │ "ssl_verify_peer_certificate": false, │
│ │ "ssl_verify_peer_host": false, │
│ │ "ssl_version": "MAX", │
│ │ "type": "listener", │
│ │ "user_mapping_file": null │
│ │ } │
└────────────┴───────────────────────────────────────────┘
shell> maxctrl show module schemarouter
┌─────────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Module │ schemarouter │
├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Type │ Router │
├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Version │ V1.0.0 │
├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Maturity │ Beta │
├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Description │ A database sharding router for simple sharding │
├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Parameters │ [ │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Enable debug mode", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "debug", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": [], │
│ │ "description": "List of tables to ignore when checking for duplicates", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "ignore_tables", │
│ │ "type": "stringlist" │
│ │ }, │
│ │ { │
│ │ "description": "Regex of tables to ignore when checking for duplicates", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "ignore_tables_regex", │
│ │ "type": "regex" │
│ │ }, │
│ │ { │
│ │ "default_value": "150000ms", │
│ │ "description": "Maximum allowed staleness of a database map entry before clients block and wait for an update", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "max_staleness", │
│ │ "type": "duration", │
│ │ "unit": "ms" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Refresh database mapping information", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "refresh_databases", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": "300000ms", │
│ │ "description": "How often to refresh the database mapping information", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "refresh_interval", │
│ │ "type": "duration", │
│ │ "unit": "ms" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Retrieve users from all backend servers instead of only one", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "auth_all_servers", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": "300000ms", │
│ │ "description": "How ofted idle connections are pinged", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "connection_keepalive", │
│ │ "type": "duration", │
│ │ "unit": "ms" │
│ │ }, │
│ │ { │
│ │ "deprecated": true, │
│ │ "description": "Alias for 'wait_timeout'", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "connection_timeout", │
│ │ "type": "duration" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Disable session command history", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "disable_sescmd_history", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Allow the root user to connect to this service", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "enable_root_user", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Ping connections unconditionally", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "force_connection_keepalive", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": "-1ms", │
│ │ "description": "Put connections into pool after session has been idle for this long", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "idle_session_pool_time", │
│ │ "type": "duration", │
│ │ "unit": "ms" │
│ │ }, │
│ │ { │
│ │ "default_value": true, │
│ │ "description": "Match localhost to wildcard host", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "localhost_match_wildcard_host", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": true, │
│ │ "description": "Log a warning when client authentication fails", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "log_auth_warnings", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Log debug messages for this service (debug builds only)", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "log_debug", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Log info messages for this service", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "log_info", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Log notice messages for this service", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "log_notice", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Log warning messages for this service", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "log_warning", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": 0, │
│ │ "description": "Maximum number of connections", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "max_connections", │
│ │ "type": "count" │
│ │ }, │
│ │ { │
│ │ "default_value": 50, │
│ │ "description": "Session command history size", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "max_sescmd_history", │
│ │ "type": "count" │
│ │ }, │
│ │ { │
│ │ "default_value": "60000ms", │
│ │ "description": "How long a session can wait for a connection to become available", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "multiplex_timeout", │
│ │ "type": "duration", │
│ │ "unit": "ms" │
│ │ }, │
│ │ { │
│ │ "default_value": "0ms", │
│ │ "description": "Network write timeout", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "net_write_timeout", │
│ │ "type": "duration", │
│ │ "unit": "ms" │
│ │ }, │
│ │ { │
│ │ "description": "Password for the user used to retrieve database users", │
│ │ "mandatory": true, │
│ │ "modifiable": true, │
│ │ "name": "password", │
│ │ "type": "password" │
│ │ }, │
│ │ { │
│ │ "default_value": true, │
│ │ "description": "Prune old session command history if the limit is exceeded", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "prune_sescmd_history", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": "primary", │
│ │ "description": "Service rank", │
│ │ "enum_values": [ │
│ │ "primary", │
│ │ "secondary" │
│ │ ], │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "rank", │
│ │ "type": "enum" │
│ │ }, │
│ │ { │
│ │ "default_value": -1, │
│ │ "description": "Number of statements kept in memory", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "retain_last_statements", │
│ │ "type": "int" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "description": "Enable session tracing for this service", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "session_trace", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": false, │
│ │ "deprecated": true, │
│ │ "description": "Track session state using server responses", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "session_track_trx_state", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "default_value": true, │
│ │ "deprecated": true, │
│ │ "description": "Strip escape characters from database names", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "strip_db_esc", │
│ │ "type": "bool" │
│ │ }, │
│ │ { │
│ │ "description": "Username used to retrieve database users", │
│ │ "mandatory": true, │
│ │ "modifiable": true, │
│ │ "name": "user", │
│ │ "type": "string" │
│ │ }, │
│ │ { │
│ │ "description": "Load additional users from a file", │
│ │ "mandatory": false, │
│ │ "modifiable": false, │
│ │ "name": "user_accounts_file", │
│ │ "type": "path" │
│ │ }, │
│ │ { │
│ │ "default_value": "add_when_load_ok", │
│ │ "description": "When and how the user accounts file is used", │
│ │ "enum_values": [ │
│ │ "add_when_load_ok", │
│ │ "file_only_always" │
│ │ ], │
│ │ "mandatory": false, │
│ │ "modifiable": false, │
│ │ "name": "user_accounts_file_usage", │
│ │ "type": "enum" │
│ │ }, │
│ │ { │
│ │ "description": "Custom version string to use", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "version_string", │
│ │ "type": "string" │
│ │ }, │
│ │ { │
│ │ "default_value": "0ms", │
│ │ "description": "Connection idle timeout", │
│ │ "mandatory": false, │
│ │ "modifiable": true, │
│ │ "name": "wait_timeout", │
│ │ "type": "duration", │
│ │ "unit": "ms" │
│ │ } │
│ │ ] │
├─────────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Commands │ [ │
│ │ { │
│ │ "attributes": { │
│ │ "arg_max": 1, │
│ │ "arg_min": 1, │
│ │ "description": "Clear schemarouter shard map cache", │
│ │ "method": "POST", │
│ │ "parameters": [ │
│ │ { │
│ │ "description": "The schemarouter service", │
│ │ "required": true, │
│ │ "type": "SERVICE" │
│ │ } │
│ │ ] │
│ │ }, │
│ │ "id": "clear", │
│ │ "links": { │
│ │ "self": "http://127.0.0.1:8989/v1/modules/schemarouter/clear/" │
│ │ }, │
│ │ "type": "module_command" │
│ │ }, │
│ │ { │
│ │ "attributes": { │
│ │ "arg_max": 1, │
│ │ "arg_min": 1, │
│ │ "description": "Invalidate schemarouter shard map cache", │
│ │ "method": "POST", │
│ │ "parameters": [ │
│ │ { │
│ │ "description": "The schemarouter service", │
│ │ "required": true, │
│ │ "type": "SERVICE" │
│ │ } │
│ │ ] │
│ │ }, │
│ │ "id": "invalidate", │
│ │ "links": { │
│ │ "self": "http://127.0.0.1:8989/v1/modules/schemarouter/invalidate/" │
│ │ }, │
│ │ "type": "module_command" │
│ │ } │
│ │ ] │
└─────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
shell> maxctrl show commands schemarouter
┌────────────┬────────────┬──────────────────────────┐
│ Command │ Parameters │ Descriptions │
├────────────┼────────────┼──────────────────────────┤
│ clear │ SERVICE │ The schemarouter service │
├────────────┼────────────┼──────────────────────────┤
│ invalidate │ SERVICE │ The schemarouter service │
└────────────┴────────────┴──────────────────────────┘
shell> maxctrl show dbusers Sharded-Service
┌───────────────────────┬────────────────┬───────────────────────┬───────┬───────┬────────┬───────┬──────┐
│ User │ Host │ Plugin │ TLS │ Super │ Global │ Proxy │ Role │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ PUBLIC │ │ │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ app │ 10.139.158.% │ mysql_native_password │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ app_role │ │ │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ mariadb.sys │ localhost │ mysql_native_password │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ maxscale_admin │ 10.139.158.210 │ mysql_native_password │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ maxscale_admin │ 10.139.158.211 │ mysql_native_password │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ maxscale_admin_role │ │ │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ maxscale_monitor │ 10.139.158.210 │ mysql_native_password │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ maxscale_monitor │ 10.139.158.211 │ mysql_native_password │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ maxscale_monitor_role │ │ │ false │ false │ false │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ mysql │ localhost │ mysql_native_password │ false │ true │ true │ false │ │
├───────────────────────┼────────────────┼───────────────────────┼───────┼───────┼────────┼───────┼──────┤
│ root │ localhost │ mysql_native_password │ false │ true │ true │ false │ │
└───────────────────────┴────────────────┴───────────────────────┴───────┴───────┴────────┴───────┴──────┘
shell> maxctrl show commands mariadbmon
┌───────────────────────────┬─────────────────────────────┬───────────────────────────────────────────────────────────────────────────────┐
│ Command │ Parameters │ Descriptions │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ switchover │ MONITOR, [SERVER], [SERVER] │ Monitor name, New primary (optional), Current primary (optional) │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ switchover-force │ MONITOR, [SERVER], [SERVER] │ Monitor name, New primary (optional), Current primary (optional) │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-switchover │ MONITOR, [SERVER], [SERVER] │ Monitor name, New primary (optional), Current primary (optional) │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ failover │ MONITOR │ Monitor name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-failover │ MONITOR │ Monitor name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ rejoin │ MONITOR, SERVER │ Monitor name, Joining server │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-rejoin │ MONITOR, SERVER │ Monitor name, Joining server │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ reset-replication │ MONITOR, [SERVER] │ Monitor name, Primary server (optional) │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-reset-replication │ MONITOR, [SERVER] │ Monitor name, Primary server (optional) │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ release-locks │ MONITOR │ Monitor name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-release-locks │ MONITOR │ Monitor name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ fetch-cmd-result │ MONITOR │ Monitor name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ cancel-cmd │ MONITOR │ Monitor name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-cs-add-node │ MONITOR, STRING, STRING │ Monitor name, Hostname/IP of node to add to ColumnStore cluster, Timeout │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-cs-remove-node │ MONITOR, STRING, STRING │ Monitor name, Hostname/IP of node to remove from ColumnStore cluster, Timeout │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ cs-get-status │ MONITOR │ Monitor name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-cs-get-status │ MONITOR │ Monitor name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-cs-start-cluster │ MONITOR, STRING │ Monitor name, Timeout │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-cs-stop-cluster │ MONITOR, STRING │ Monitor name, Timeout │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-cs-set-readonly │ MONITOR, STRING │ Monitor name, Timeout │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-cs-set-readwrite │ MONITOR, STRING │ Monitor name, Timeout │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-rebuild-server │ MONITOR, SERVER, [SERVER] │ Monitor name, Target server, Source server (optional) │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-create-backup │ MONITOR, SERVER, STRING │ Monitor name, Source server, Backup name │
├───────────────────────────┼─────────────────────────────┼───────────────────────────────────────────────────────────────────────────────┤
│ async-restore-from-backup │ MONITOR, SERVER, STRING │ Monitor name, Target server, Backup name │
└───────────────────────────┴─────────────────────────────┴───────────────────────────────────────────────────────────────────────────────┘
Literature / Sources
- MaxScale: Simple Sharding with Two Servers
- MaxScale: SchemaRouter
Taxonomy upgrade extras:
- Shinguz's blog
- Log in or register to post comments