How to use Multi-master Cluster (Limitless) Edition - PolarDB

PolarDB for MySQL Multi-master Cluster (Limitless) Edition upgrades the architecture from a one-writer-multiple-reader model to a multiple-writer-multiple-reader model. It supports concurrent writes to different databases or data objects on different compute nodes. It also supports dynamic scheduling of databases and data objects across nodes, with switches completed in seconds. This feature greatly improves the concurrent read and write capabilities of the cluster. Supported data objects include tables, views, triggers, events, stored procedures, and user-defined functions. This topic describes how to use the Multi-master Cluster (Limitless) Edition.

Prerequisites

You have purchased a Multi-master Cluster (Limitless) Edition cluster. For more information, see Custom purchase and Purchase a subscription cluster.
You have created a privileged account. For more information, see Create a privileged account.
You have connected to the database cluster. For more information, see Connect to a database cluster.
The Multi-master Cluster (Limitless) Edition is supported on PolarDB for MySQL 8.0.

Limits

Data for each database or data object can be written from only one node. You cannot read or write data on a node that does not have the corresponding database or data object assigned to it. By default, operations are performed at the database level. To perform operations at the data object level, you must use the specified syntax to switch the isolation level.
Cross-RW node data queries are not supported. If a query SQL statement involves databases or data objects on multiple RW nodes, the system reports an error. Before you run such a query, you must move the endpoints of all involved databases or data objects to a single RW node.
Only cluster endpoints, are supported, but not primary endpoints.
The following modes are supported for switching endpoints:
- At the database isolation level, you can switch the database endpoint.
- At the data object isolation level, you can switch the data object endpoint.

Specify an RW node when you create a database

You can create a database on a specified RW node. The syntax is as follows:

CREATE DATABASE name [POLARDB_WRITE_NODE master_id];

Note

In database isolation mode, data for each database can be written from only one node.
If you omit [POLARDB_WRITE_NODE master_id], the RW node used to create the database is determined by the value of the loose_innodb_mm_default_master_id parameter. If the value of the loose_innodb_mm_default_master_id parameter is 0, the system randomly selects an RW node on which to create the database. You can go to the Configuration And Management > Parameter Settings page in the PolarDB console to view and modify cluster or node parameters.
To view the distribution of databases across nodes, see Query the database distribution.

Example: Create a database named db1 on RW1.

CREATE DATABASE db1 POLARDB_WRITE_NODE 1;

To create the database db1 on RW2, change 1 to 2 in the preceding example.

Delete a database on a specified RW node

You can delete a database on a specified RW node. The syntax is as follows:

DROP DATABASE name;

Example: Delete the database db1 from the RW1 node.

DROP DATABASE db1;

You do not need to specify POLARDB_WRITE_NODE when you delete a database.

Switch the database endpoint

You can switch the endpoint of a database to another RW node. The syntax is as follows:

ALTER DATABASE name POLARDB_WRITE_NODE master_id;

Example: Switch the endpoint of the database db1 to RW2.

ALTER DATABASE db1 POLARDB_WRITE_NODE 2;

Note

Switching an endpoint is typically a time-consuming operation. The running time depends on the following two factors:

The number of tables in the database. The switch is slower if the database contains many tables.
The Data Manipulation Language (DML) pressure on the database during the switch. The switch is slower if the DML pressure is high.

Switch from database isolation level to data object isolation level

By default, the isolation level for a Multi-master Cluster is the database level. This means that all data objects within the same database can be accessed from only one RW node. To allow data objects in the same database to be accessed from multiple RW nodes, you can change the isolation level from the database level to the data object level. The syntax is as follows:

ALTER DATABASE name TO TABLE_LOCK POLARDB_WRITE_NODE master_id;

In this statement, name is the name of the database and master_id is the endpoint of the data object.

Example: Change the isolation level for the database db1 from the database level to the data object level, and set the endpoint to RW2.

ALTER DATABASE db1 TO TABLE_LOCK POLARDB_WRITE_NODE 2;

Note

Switching the isolation level is typically a time-consuming operation. The running time depends on the following two factors:

The number of objects in the database. The switch is slower if the database contains many objects.
The DML pressure on the database during the switch. The switch is slower if the DML pressure is high.

Switch from data object isolation level to database isolation level

After you change the isolation granularity of a database to the data object level, you can switch it back to the database isolation level for easier management. To do this, use the following statement:

ALTER DATABASE name TO DB_LOCK POLARDB_WRITE_NODE master_id;

In this statement, name is the name of the database and master_id is the endpoint of the database.

Example: Change the isolation level for the database db1 from the data object level to the database level, and set the endpoint to RW1.

ALTER DATABASE db1 TO DB_LOCK POLARDB_WRITE_NODE 1;

Note

Switching the isolation level is typically a time-consuming operation. The running time depends on the following two factors:

The number of objects in the database. The switch is slower if the database contains many objects.
The DML pressure on the database during the switch. The switch is slower if the DML pressure is high.

Switch the data object endpoint

After you change the isolation level of a Multi-master Cluster to the data object level, a single database can contain multiple object types, such as TABLE, VIEW, TRIGGER, FUNCTION, PROCEDURE, and EVENT. To switch the endpoints for these objects, use the following statement:

ALTER obj_type name POLARDB_WRITE_NODE master_id;

The valid values for obj_type are TABLE, VIEW, TRIGGER, FUNCTION, PROCEDURE, and EVENT. name is the name of the data object.

Example 1: Switch the endpoint of the t1 table in the database db1 to RW3.

ALTER TABLE db1.t1 POLARDB_WRITE_NODE 3;

Example 2: Switch the endpoint of the t2 VIEW in the current database to RW2.

ALTER VIEW t2 POLARDB_WRITE_NODE 2;

Example 3: Switch the endpoints of function f1 and function f2 in the database db2 to RW1.

ALTER FUNCTION db2.f1, db2.f2 POLARDB_WRITE_NODE 1;

Note

Switching an endpoint is typically a time-consuming operation. The running time depends on the following factors:

The DML pressure on the data object during the switch. The switch is slower if the DML pressure is high.
Objects can be associated with each other. If associated objects do not have their endpoints on the same RW node, the objects can become invalid.
For example, a view named VIEW1 depends on a table named t1. If the endpoint for VIEW1 is on RW1 but the endpoint for t1 is on RW2, an error occurs when you try to access VIEW1 on RW1. Similarly, if objects referenced by a FUNCTION, PROCEDURE, or EVENT do not have the correct endpoints, their execution fails. If a TRIGGER and its associated TABLE do not have endpoints on the same node, you cannot modify data in the TABLE.
If a foreign key constraint exists between table t1 and table t2, changing the endpoint of one table automatically changes the endpoint of the other.

Specify the RW node for an SQL statement

Important

This feature applies only to non-data query statements, such as statements that query information_schema or status variables. To query data using statements such as SELECT * FROM table1, you do not need to specify an RW node. The database proxy automatically selects the correct RW node on which to run the query.

To send an SQL statement to a specific RW node, you can run the following SQL statement to lock an RW node:

ALTER SESSION POLARDB_WRITE_NODE master_id;

Example: Query the value of the innodb_buffer_pool_size variable on the RW1 node.

ALTER SESSION POLARDB_WRITE_NODE 1;   # Send the SQL statement to the RW1 node.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  # Query the value of innodb_buffer_pool_size on the RW1 node.

Note

If you do not specify an RW node when you run an SQL statement, the database proxy randomly selects an RW node on which to run the statement.

You can run the following command to unlock the specified RW node for running SQL statements:

RESET SESSION POLARDB_WRITE_NODE;

Query the database distribution

You can go to the Configuration And Management > Database Management page in the PolarDB console to view the distribution of all databases in the cluster.

You can run the following commands to query the database distribution on a specific RW node:

ALTER SESSION POLARDB_WRITE_NODE master_id;
SELECT * FROM INFORMATION_SCHEMA.INNODB_MASTER_GLOBAL_LOCK_INFO WHERE LOCK_MODE = 'SLS_X';

Example: Query the database distribution on the RW1 node.

ALTER SESSION POLARDB_WRITE_NODE 1;
SELECT * FROM INFORMATION_SCHEMA.INNODB_MASTER_GLOBAL_LOCK_INFO WHERE LOCK_MODE = 'SLS_X';

The query result is as follows:

 SELECT * FROM INFORMATION_SCHEMA.INNODB_MASTER_GLOBAL_LOCK_INFO WHERE LOCK_MODE = 'SLS_X';

+------------+---------------------+----------+--------------+-----------+----------+-------------+-------------+---------------------+-----------------+
| table_name | table_id            | space_id | s_lock_count | lock_mode | object   | current_lsn | hold_thread | hold_start_time     | hold_total_time |
+------------+---------------------+----------+--------------+-----------+----------+-------------+-------------+---------------------+-----------------+
| test3/f1   | 9149389368458135753 |        0 |            0 | SLS_X     | function |    28076635 |          17 | 2024-07-10 21:35:20 |             214 |
| test3/e1   | 9149389368458332874 |        0 |            0 | SLS_X     | event    |    28077248 |          17 | 2024-07-10 21:35:30 |             204 |
| test3/v1   | 9149389368457234649 |        0 |            0 | SLS_X     | view     |    28075972 |          17 | 2024-07-10 21:35:08 |             226 |
| sbtest     | 2107518311328629409 |        0 |            0 | SLS_X     | db       |    28034927 |  4294967295 | 2024-07-07 23:04:41 |          254053 |
| test       | 7190879906290573778 |        0 |            0 | SLS_X     | db       |    28034927 |  4294967295 | 2024-07-10 11:20:57 |           37077 |
| test2      | 3381728963524265351 |        0 |            0 | SLS_X     | db       |    28034927 |  4294967295 | 2024-07-10 11:13:09 |           37545 |
+------------+---------------------+----------+--------------+-----------+----------+-------------+-------------+---------------------+-----------------+

6 rows in set (0.00 sec)

Each row in the query result provides information about a database or data object, even though the column is named table_name. In the result, sbtest, test, and test2 use the database isolation level. function test3.f1, event test3.e1, and view test3.v1 use the data object isolation level. You may also see an object named mysql/global_ddl_lock with the object type Table. This is internal information that you can ignore.

You can run the following command to query the distribution of all databases in the cluster:

Note

This query can be run only using a privileged account. You cannot run this query with a newly created account.

SELECT * FROM INFORMATION_SCHEMA.INNODB_CC_GLOBAL_LOCK_INFO WHERE LOCK_MODE = 'SLS_X';

The query result is as follows:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_CC_GLOBAL_LOCK_INFO WHERE LOCK_MODE = 'SLS_X';
+-----------+------------+---------------------+-----------+-----------+-------------+---------------------+-----------------+
| master_id | table_name | table_id            | lock_mode | object    | current_lsn | hold_start_time     | hold_total_time |
+-----------+------------+---------------------+-----------+-----------+-------------+---------------------+-----------------+
|         1 | test3/v1   | 9149389368457234649 | SLS_X     | view      |    28075972 | 2024-07-10 21:35:08 |             754 |
|         2 | test5/t1   | 9149389447232697561 | SLS_X     | table     |     7256175 | 2024-07-10 21:46:36 |              66 |
|         1 | test2      | 3381728963524265351 | SLS_X     | db        |    28034927 | 2024-07-10 11:13:09 |           38073 |
|         2 | test4      | 3381728963524272009 | SLS_X     | db        |     7255352 | 2024-07-10 21:46:27 |              75 |
|         1 | test3/f1   | 9149389368458135753 | SLS_X     | function  |    28076635 | 2024-07-10 21:35:20 |             742 |
|         1 | test3/e1   | 9149389368458332874 | SLS_X     | event     |    28077248 | 2024-07-10 21:35:30 |             732 |
|         1 | test       | 7190879906290573778 | SLS_X     | db        |    28034927 | 2024-07-10 11:20:57 |           37605 |
|         2 | test5/p1   | 9149389447233473757 | SLS_X     | procedure |     7257051 | 2024-07-10 21:46:45 |              57 |
|         1 | sbtest     | 2107518311328629409 | SLS_X     | db        |    28034927 | 2024-07-07 23:04:41 |          254581 |
+-----------+------------+---------------------+-----------+-----------+-------------+---------------------+-----------------+

9 rows in set (0.00 sec)

Each row in the query result provides information about a database or data object, even though the column is named table_name. The result indicates the RW node on which the database or data object resides. You may also see an object named mysql/global_ddl_lock with the object type Table. This is internal information that you can ignore.

How to set up binary logging

Multi-master Cluster (Limitless) Edition is fully compatible with MySQL binary logging. It integrates the operation logs from all RW nodes in the cluster to generate globally unified and logically ordered binary logs.

You can configure the loose_polar_log_bin parameter to enable the binary logging feature of Multi-master Cluster (Limitless) Edition. You can also configure the binlog_expire_logs_seconds parameter to set the retention period of binary logs for Multi-master Cluster (Limitless) Edition. For more information, see Enable binary logging.

Note

A Multi-master Cluster (Limitless) Edition cluster can be used as a source or destination for Data Transmission Service (DTS) to perform one-way or two-way data synchronization.