Read/write separation

The user connects to the Coordinator, executes SQL statements, and eventually the modification and query of the data will access the Datanode. Generally Datanode has synchronous or asynchronous standby nodes. In case of synchronous standby node, the data is consistent with the master node in real time, so the standby node can provide the function of data query. However, when the master node is working normally, the standby node only provides the function of data backup, which causes the waste of machine resources.

AntDB implements read-write separation at the kernel level, and by turning on the switch of read-write separation, it can be realized that the executed read operation accesses the standby node and the write operation accesses the master node, which is completely transparent to the application layer, effectively improving the utilization rate of the master and slave machine resources and increasing the throughput of the database.

Read-write separation control parameters

There are two switches involved in read-write separation, and the read-write separation function is enabled on adbmgr by means of parameter settings as follows:

In case of synchronous nodes:

set coordinator all(enable_readsql_on_slave = on);

In case there are no synchronous nodes, only asynchronous nodes, and you want the asynchronous nodes to provide read functionality:

set coordinator all(enable_readsql_on_slave = on);
set coordinator all( enable_readsql_on_slave_async = on);

• enable_readsql_on_slave: The read-write separation master switch.

• enable_readsql_on_slave_async: switch to allow asynchronous nodes to provide read functionality.

The relationship between the two is as follows:

Note: In the scenario of concurrent read/write, if the asynchronous node provides the read function, there may be a problem that the data just written cannot be queried due to the time difference of data synchronization.

In some scenarios, when read-write separation is enabled and master stream replication is synchronized, DML operations are performed on the master node, and when the backup node queries immediately, it is found that the corresponding data cannot be found. The reason is that the DML operation of the master node has not been applied in the slave node, so the query operation sent to the slave node cannot read the latest data. If you want to avoid this situation, you can set thesynchronous_commit parameter of datanode asremote_apply to ensure that the master's operation is applied on the slave before returning the message to the client.

Login to mgr, execute the following command, and restart.

set datanode all(synchronous_commit = 'remote_apply');

Note that there will be some performance loss after settingremote_apply.