maxscale的causal_reads参数

一 需求描述

maxscale可以实现mariadb读写分离（在主库写，在从库读）。mariadb主从复制是异步的，很有可能存在从库延迟于主库的情况。

有时候，有的业务无法容忍mariadb从库延迟，如一个接口执行完写入操作后，其他接口要判断是否能查到该写入的数据，然后做进一步DML处理，如果查的数据不准确，则会导致数据错乱，影响业务逻辑。

如果maxscale启用了causal_reads，客户端连接修改了数据库,则在从库上执行的任何后续读取都将以防止复制延迟影响结果的方式进行。这仅适用于客户端连接自己进行的修改。如果从库在配置的时间内没有赶上主库，则将在主库上重试。

根据具体业务谨慎选型吧，个人不建议用，详细请查阅‘四 实验步骤’。

二 参数介绍

2.1 causal_reads

如果从库不延迟，则查从库，如果延迟了，则自动查主库。

以下是官网原文：

Enable causal reads. This parameter is disabled by default and was introduced in MaxScale 2.3.0.

If a client connection modifies the database and causal_reads is enabled, any subsequent reads performed on slave servers will be done in a manner that prevents replication lag from affecting the results. This only applies to the modifications done by the client itself.

Note: This feature requires MariaDB 10.2.16 or newer to function. In addition to this, the session_track_system_variables parameter must be set to last_gtid.

Note: This feature does not work with prepared statements. Only SQL statements executed individually (inside a COM_QUERY packet) can be handled by the causal read mechanism.

Note: This feature does not work with Galera or any other non-standard replication mechanisms. As Galera does not update the gtid_slave_pos variable when events are replicated via the Galera library, the MASTER_GTID_WAIT function used by MaxScale to synchronize reads will wait until the timeout. With Galera this is not a serious issue as it, by nature, is a mostly-synchronous replication mechanism.

A practical example can be given by the following set of SQL commands executed with autocommit=1.

 As the statements are not executed inside a transaction, from the load balancers point of view, the latter statement can be routed to a slave server. The problem with this is that if the value that was inserted on the master has not yet replicated to the server where the SELECT statement is being performed, it can appear as if the value we just inserted is not there.

By prefixing these types of SELECT statements with a command that guarantees consistent results for the reads, read scalability can be improved without sacrificing consistency.

The set of example SQL above will be translated by MaxScale into the following statements.

 The SET command will synchronize the slave to a certain logical point in the replication stream (see MASTER_GTID_WAIT for more details).

If the slave has not caught up to the master within the configured time, it will be retried on the master. In MaxScale 2.3.0 an error was returned to the client when the slave timed out.

2.2 causal_reads_timeout

The timeout for the slave synchronization done by causal_reads. The default value is 10 seconds.

The timeout is specified as documented here. If no explicit unit is provided, the value is interpreted as seconds in MaxScale 2.4. In subsequent versions a value without a unit may be rejected. Note that since the granularity of the timeout is seconds, a timeout specified in milliseconds will be rejected, even if the duration is longer than a second.

三 解决办法

3.1 修改mariadb里session_track_system_variables变量

修改session_track_system_variables变量，让其包含last_gtid。

#在线修改(发现在主库上修改变量不能自动同步到从库，那就在主从上都执行以下sql)：

set global session_track_system_variables='autocommit,character_set_client,character_set_connection,character_set_results,time_zone,last_gtid';

退出当前会话，重新登录，发现变量修改成功了。

#永久修改

修改mariadb配置文件，

在[mysqld]下添加一行：

session_track_system_variables='autocommit,character_set_client,character_set_connection,character_set_results,time_zone,last_gtid'

3.2 修改maxscale参数

在读写分离服务模块下添加：

causal_reads=on

示例：

#重启maxscale

systemctl restart maxscale

四 实验步骤

4.1 修改配置前的表现

在从库上加个全局读锁：

flush tables with read lock;

 由于对从库加了读锁，所以没法往从库插入23这条数据，由于读写分离，所以就查不到这条数据。

4.2 修改配置后的表现

在从库上加个全局读锁

 发现虽然从库有延迟，但是等待了10秒后，就去查主库的数据去了。

在10秒内在从库上解锁(unlock tables)了的话，很快就能返回数据了，不用等十秒。

后来实验发现，同一个会话里一次执行多个sql，修改了a表的记录，若从库有延迟，查其他没修改的记录，也需要等待，这样的话，影响范围比较大。

而且等待10秒也太长了吧？可是若将causal_reads_timeout改短点儿，若延迟的话，就都去读主库了，主库上的压力就比较大了。

我后来将插入和查询sql放不同数据库连接里，发现查询没有等待，直接查的是从库，没查主库，所以这个casual_reads是针对的同一个数据库连接里的sql。

根据具体业务谨慎选型吧，个人不建议用。

本篇文章参考了

Readwritesplit - MariaDB Knowledge Base