OceanBase 读写分离探讨

版本信息

OceanBase: 4.2.1.10

OBProxy: 4.3.3.0

租户类型： MySQL租户

弱一致性读

官方声明 默认情况下,所有的请求都是发送到数据的 Leader 副本上，即强一致性的请求，因为 OLAP 的分析计算，一般对于数据的一致性要求不高，因此可以在开启弱一致性查询之后，实现请求访问到 Follower 副本。

SQL级别设置

SQL级别设置，即在请求的SQL中，加上弱一致性读的Hint，方法如下：

mysql> select /*+READ_CONSISTENCY(WEAK)*/ * from bmsql_config limit 1;
+------------+-----------+
| cfg_name   | cfg_value |
+------------+-----------+
| warehouses | 50        |
+------------+-----------+
1 row in set (0.02 sec)mysql>

会话级别设置

分Global级别和Session级别，修改变量 ob_read_consistency，该参数用于设置读一致性级别

设置 ob_read_consistency

mysql> set global ob_read_consistency='weak';
Query OK, 0 rows affected (0.19 sec)mysql>
mysql>
mysql> show variables like 'ob_read_consistency';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| ob_read_consistency | WEAK  |
+---------------------+-------+
1 row in set (0.01 sec)mysql>

OBProxy配置项设置

可以指定连接某个OBProxy的所有请求都为弱一致性读，修改 obproxy_read_consistency 配置

该配置项取值为 0 和 1，默认为 0，表示强读（需要读到最新的数据）。1 表示弱读。

使用sys租户登录 OBProxy(指定2883端口) 执行以下语句

[root@server061 ~]# mysql -h10.0.0.61 -P2883 -uroot@sys#obdemo61 -paaAA11__
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 854
Server version: 5.6.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec  4 2024 20:33:18)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> alter proxyconfig set obproxy_read_consistency = 1;
Query OK, 0 rows affected (0.02 sec)mysql>
mysql> show proxyconfig like 'obproxy_read_consistency';
+--------------------------+-------+--------------------------------------------------+-------------+---------------+-------+--------------+
| name                     | value | info                                             | need_reboot | visible_level | range | config_level |
+--------------------------+-------+--------------------------------------------------+-------------+---------------+-------+--------------+
| obproxy_read_consistency | 1     | request target: 0 means leader; 1 means follower | false       | SYS           | [0,1] | LEVEL_VIP    |
+--------------------------+-------+--------------------------------------------------+-------------+---------------+-------+--------------+
1 row in set (0.00 sec)mysql>

小结

官方声明如下：

通过Hint的方式，需要修改SQL，如果SQL数量比较多，则修改起来工作量大，并且对业务代码有一定侵入性，会话级别的设置，如果是全局设置，则会导致所有的连接会话都开启弱一致性读，如果只开启Session级别，则每开启一个Session都需要先执行以下命令，通过修改OBProxy配置，在分析请求比较多且复杂的场景，则是最方便的方式，所有连接这个OBProxy的SQL默认开启弱一致性读

开启弱一致性读只是配置读写分离的第一步，在开启弱一致性读时，如果没有设置LDC策略，所有的请求还是会按照 OBProxy 路由策略，优先发送到primary zone，如果未设置primary zone，即primary zone为random，那么请求会按照随机路由的策略，随机发送到任意副本，因此时有可能发送到 Leader 副本，并未实现完全的读写分离。

LDC路由

官网声明如下

LDC 包含了 IDC 机房信息和 Region 城市信息。一个城市，包含一个或多个IDC，每个IDC中可部署一个或多个Zone

OBServer设置

使用sys租户设置OceanBase 集群的LDC配置

[root@server061 ~]# mysql -h10.0.0.61 -P2883 -uroot@sys#obdemo61 -paaAA11__
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7616
Server version: 5.6.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec  4 2024 20:33:18)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> ALTER SYSTEM MODIFY zone "zone1" SET region = "zhejiang";
Query OK, 0 rows affected (0.08 sec)mysql> ALTER SYSTEM MODIFY zone "zone1" SET idc = "hanzghou1"-> ;
Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM oceanbase.DBA_OB_ZONES;
+-------+----------------------------+----------------------------+--------+-----------+----------+-----------+
| ZONE  | CREATE_TIME                | MODIFY_TIME                | STATUS | IDC       | REGION   | TYPE      |
+-------+----------------------------+----------------------------+--------+-----------+----------+-----------+
| zone1 | 2024-12-29 14:35:19.466142 | 2025-01-28 23:49:47.159742 | ACTIVE | hanzghou1 | zhejiang | ReadWrite |
+-------+----------------------------+----------------------------+--------+-----------+----------+-----------+
1 row in set (0.01 sec)mysql>

OBProxy设置

使用sys租户登录设置ODP的LDC 配置

mysql> ALTER PROXYCONFIG SET proxy_idc_name= hanzghou1;
Query OK, 0 rows affected (0.00 sec)mysql>
mysql>
mysql> SHOW PROXYINFO IDC;
+-----------------+--------------+----------------+-----------------+--------------+-------------+--------------+
| global_idc_name | cluster_name | match_type     | regions_name    | same_idc     | same_region | other_region |
+-----------------+--------------+----------------+-----------------+--------------+-------------+--------------+
| hanzghou1       | obdemo61     | MATCHED_BY_IDC | [[0]"zhejiang"] | [[0]"zone1"] | []          | []           |
+-----------------+--------------+----------------+-----------------+--------------+-------------+--------------+
1 row in set (0.01 sec)mysql>

小结

此时，业务访问hanghou1的这个OBProxy，默认会将请求路由到zone1的OBServer上，但是这样也只是保证了就近原则，无法保证OLAP类的读请求都访问到 Follower副本

ODP路由设置(proxy_route_policy设置)

在设置了LDC路由策略之后，弱一致性的读请求只会就近访问，为了保证弱一致性的读请求能够优先路由到Follower 副本上，还需要对OBProxy设置 proxy_route_policy 参数

参数值如下:

follower_first：弱读请求优先路由到备副本，如果备副本都不可用，弱读请求路由到主副本。
follower_only：弱读请求路由到备副本，如果备副本都不可用，断开和客户端的连接。

使用sys租户登录 OBProxy(指定2883端口) 执行以下语句

[root@server061 ~]# mysql -h10.0.0.61 -P2883 -uroot@sys#obdemo61 -paaAA11__
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 873
Server version: 5.6.25 OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec  4 2024 20:33:18)Copyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> alter proxyconfig set proxy_route_policy="follower_first";
Query OK, 0 rows affected (0.00 sec)mysql> show proxyconfig like "%proxy_route_policy%";
+--------------------+----------------+---------------------------------+-------------+---------------+-------+--------------+
| name               | value          | info                            | need_reboot | visible_level | range | config_level |
+--------------------+----------------+---------------------------------+-------------+---------------+-------+--------------+
| proxy_route_policy | follower_first | proxy route policy for weakread | false       | SYS           |       | LEVEL_VIP    |
+--------------------+----------------+---------------------------------+-------------+---------------+-------+--------------+
1 row in set (0.01 sec)mysql>