OceanBase-弱读节点搭建及测试

1. 查看租户的primary_zone

MySQL [oceanbase]> select * from __all_tenant\G
*************************** 1. row ***************************
                 gmt_create: 2023-10-04 16:37:26.546793
               gmt_modified: 2023-10-04 16:37:26.546793
                  tenant_id: 1
                tenant_name: sys
                replica_num: -1
                  zone_list: zone1;zone2;zone3
               primary_zone: zone3;zone2;zone1
                     locked: 0
             collation_type: 0
                       info: system tenant
                  read_only: 0
      rewrite_merge_version: 0
                   locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
        logonly_replica_num: 0
          previous_locality:
     storage_format_version: 0
storage_format_work_version: 0
      default_tablegroup_id: -1
         compatibility_mode: 0
           drop_tenant_time: -1
                     status: TENANT_STATUS_NORMAL
              in_recyclebin: 0
*************************** 2. row ***************************
                 gmt_create: 2023-09-28 15:05:01.913577
               gmt_modified: 2023-09-28 15:05:01.913577
                  tenant_id: 1001
                tenant_name: testdbf
                replica_num: -1
                  zone_list: zone1;zone2;zone3
               primary_zone: zone1;zone2;zone3
                     locked: 0
             collation_type: 0
                       info:
                  read_only: 0
      rewrite_merge_version: 0
                   locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3
        logonly_replica_num: 0
          previous_locality:
     storage_format_version: 0
storage_format_work_version: 0
      default_tablegroup_id: -1
         compatibility_mode: 1
           drop_tenant_time: -1
                     status: TENANT_STATUS_NORMAL
              in_recyclebin: 0
2 rows in set (0.003 sec)

2. 修改zone的region和idc属性

ALTER SYSTEM MODIFY zone "zone1" SET region = "TEST_REGION";
ALTER SYSTEM MODIFY zone "zone2" SET region = "TEST_REGION";
ALTER SYSTEM MODIFY zone "zone3" SET region = "TEST_REGION";

ALTER SYSTEM MODIFY zone "zone1" SET idc = "TEST_IDC1";
ALTER SYSTEM MODIFY zone "zone2" SET idc = " TEST_IDC2";
ALTER SYSTEM MODIFY zone "zone3" SET idc = " TEST_IDC3";


MySQL [(none)]> select * from oceanbase.__all_zone where name in ('region','idc');
+----------------------------+----------------------------+-------+--------+-------+-------+
| gmt_create | gmt_modified | zone | name | value | info |
+----------------------------+----------------------------+-------+--------+-------+-------+
| 2023-07-22 10:33:34.058663 | 2023-10-09 15:54:03.671680 | zone1 | idc |     0 | TEST_IDC1 |
| 2023-07-22 10:33:34.058663 | 2023-10-09 15:53:57.019920 | zone1 | region | 0 | TEST_REGION |
| 2023-07-22 10:33:34.059717 | 2023-10-09 15:54:03.675897 | zone2 | idc |     0 | TEST_IDC2 |
| 2023-07-22 10:33:34.059717 | 2023-10-09 15:53:57.025899 | zone2 | region | 0 | TEST_REGION |
| 2023-07-22 10:33:34.060769 | 2023-10-09 15:54:03.680114 | zone3 | idc |     0 | TEST_IDC3 |
| 2023-07-22 10:33:34.060769 | 2023-10-09 15:53:57.031163 | zone3 | region | 0 | TEST_REGION |
+----------------------------+----------------------------+-------+--------+-------+-------+
6 rows in set (0.011 sec)

3. 安装弱读proxy，选择其他的机器做proxy节点

设置obproxy参数proxy_idc_name=test_idc2,这里使用ocp安装obproxy即可。

[root@kdobyyf-4 ~]$ mysql -hproxy_host_ip -P2883 -uroot@sys#obtestdbf -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 668
Server version: 5.6.25 OceanBase 3.2.4.5 (r105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb) (Built Aug 15 2023 13:52:10)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show proxyconfig like '%idc%';
+---------------------------+-------+-----------------------------------------------------------------------------------------------------------------------------------+-------------+---------------+
| name | value | info | need_reboot | visible_level |
+---------------------------+-------+-----------------------------------------------------------------------------------------------------------------------------------+-------------+---------------+
| proxy_idc_name | HB_2 | idc name for proxy ldc route. If is empty or invalid, treat as do not use ldc. User session vars 'proxy_session_ldc' can cover it | false       | SYS |
| idc_list_refresh_interval | 2h | the interval to refresh idc list for getting newest region-idc, [10s, 1d] | false       | USER |
| refresh_idc_list | False | when refresh config server, update all idc list if refresh_idc_list is true                                                       | false       | MEMORY |
+---------------------------+-------+-----------------------------------------------------------------------------------------------------------------------------------+-------------+---------------+
3 rows in set (0.002 sec)

弱读节点测试：

注：这里不要忘记加-c 参数要不然加hint不生效。

[root@kdobyyf-4 ~]$ obclient -hproxy_host_ip -P2883 -uSYS@testdbf#obtestdbf -p -c
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 34190
Server version: OceanBase 3.2.4.5 (r105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb) (Built Aug 15 2023 13:52:10)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [TEST]> select /*+read_consistency(weak)*/1 from dual;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.004 sec)

查看结果，发现select查询已经到副本zone2上查询：

MySQL [(none)]> select * from oceanbase.gv$sql_audit where query_sql like 'select /*+read_consistency(weak)*/1 from dual%';
+---------------+----------+------------+-------------+-----------------------------------+------------+----------------+-------------+-----------+-------------+---------------------+------------------+-----------+------------+----------------+------------------+---------+----------------------------------+------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+----------------------+--------+----+--------+----+--------+----+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+-----------------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------------------+--------------+-----------------------+---------------+---------------------+--------------------+-----------------------+--------------+
| SVR_IP | SVR_PORT | REQUEST_ID | SQL_EXEC_ID | TRACE_ID | SID | CLIENT_IP | CLIENT_PORT | TENANT_ID | TENANT_NAME | EFFECTIVE_TENANT_ID | USER_ID | USER_NAME | USER_GROUP | USER_CLIENT_IP | DB_ID | DB_NAME | SQL_ID | QUERY_SQL | PLAN_ID | AFFECTED_ROWS | RETURN_ROWS | PARTITION_CNT | RET_CODE | QC_ID | DFO_ID | SQC_ID | WORKER_ID | EVENT | P1TEXT | P1 | P2TEXT | P2 | P3TEXT | P3 | LEVEL | WAIT_CLASS_ID | WAIT_CLASS# | WAIT_CLASS | STATE | WAIT_TIME_MICRO | TOTAL_WAIT_TIME_MICRO | TOTAL_WAITS | RPC_COUNT | PLAN_TYPE | IS_INNER_SQL | IS_EXECUTOR_RPC | IS_HIT_PLAN | REQUEST_TIME | ELAPSED_TIME | NET_TIME | NET_WAIT_TIME | QUEUE_TIME | DECODE_TIME | GET_PLAN_TIME | EXECUTE_TIME | APPLICATION_WAIT_TIME | CONCURRENCY_WAIT_TIME | USER_IO_WAIT_TIME | SCHEDULE_TIME | ROW_CACHE_HIT | BLOOM_FILTER_CACHE_HIT | BLOCK_CACHE_HIT | BLOCK_INDEX_CACHE_HIT | DISK_READS | RETRY_CNT | TABLE_SCAN | CONSISTENCY_LEVEL | MEMSTORE_READ_ROW_COUNT | SSSTORE_READ_ROW_COUNT | REQUEST_MEMORY_USED | EXPECTED_WORKER_COUNT | USED_WORKER_COUNT | SCHED_INFO | FUSE_ROW_CACHE_HIT | PS_CLIENT_STMT_ID | PS_INNER_STMT_ID | TRANSACTION_HASH | REQUEST_TYPE | IS_BATCHED_MULTI_STMT | OB_TRACE_INFO | PLAN_HASH | LOCK_FOR_READ_TIME | WAIT_TRX_MIGRATE_TIME | PARAMS_VALUE |
+---------------+----------+------------+-------------+-----------------------------------+------------+----------------+-------------+-----------+-------------+---------------------+------------------+-----------+------------+----------------+------------------+---------+----------------------------------+------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+----------------------+--------+----+--------+----+--------+----+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+-----------------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------------------+--------------+-----------------------+---------------+---------------------+--------------------+-----------------------+--------------+
| 192.168.121.38 |     2882 | 375 |     8627632 | YB420AE6A726-000606EE6167FFE9-0-0 | 3222943466 | 192.168.122.159 |       17124 | 1001 | testdbf | 1001 | 1100611139403779 | SYS |          0 | 192.168.121.2 | 1100611139404874 | TEST | FECAE987CDAC9C6A85FB98927554CE7A | select /*+read_consistency(weak)*/1 from dual |     177 | 0 |           1 | 0 |        0 | 0 |      0 | 0 |         0 | system internal wait |        | 0 |        | 0 |        | 0 |     0 | 100 |           0 | OTHER | MAX_WAIT TIME ZERO | 0 |                     0 | 0 |         0 | 1 |            0 | 0 |           0 | 1696841417065952 |         1558 | 0 |             3 | 23 |           1 | 1437 |           71 | 0 |                     0 | 0 |             0 | 0 |                      0 | 0 |                     0 | 0 |         0 | 0 |                 2 | 0 |                      0 | 2096128 |                     0 | 0 | NULL | 0 |                -1 | -1 | 17198521346730479670 | 2 |                     0 | NULL | 7402781817827006738 | 0 |                     0 | |
+---------------+----------+------------+-------------+-----------------------------------+------------+----------------+-------------+-----------+-------------+---------------------+------------------+-----------+------------+----------------+------------------+---------+----------------------------------+------------------------------------------------+---------+---------------+-------------+---------------+----------+-------+--------+--------+-----------+----------------------+--------+----+--------+----+--------+----+-------+---------------+-------------+------------+--------------------+-----------------+-----------------------+-------------+-----------+-----------+--------------+-----------------+-------------+------------------+--------------+----------+---------------+------------+-------------+---------------+--------------+-----------------------+-----------------------+-------------------+---------------+---------------+------------------------+-----------------+-----------------------+------------+-----------+------------+-------------------+-------------------------+------------------------+---------------------+-----------------------+-------------------+------------+--------------------+-------------------+------------------+----------------------+--------------+-----------------------+---------------+---------------------+--------------------+-----------------------+--------------+
1 row in set (41.984 sec)




MySQL [oceanbase]> select * from __all_server;
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+-------------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| gmt_create | gmt_modified | svr_ip | svr_port | id | zone | inner_port | with_rootserver | status | block_migrate_in_time | build_version | stop_time | start_service_time | first_sessid | with_partition | last_offline_time |
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+-------------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
| 2023-07-22 10:33:23.784053 | 2023-10-06 15:00:14.905669 | XXX.XXX.121.16 | 2882 |  3 | zone3 |       2881 | 1 | active | 0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) | 0 |   1695539887662090 | 0 |              1 | 0 |
| 2023-07-22 10:33:23.761426 | 2023-10-06 15:00:14.906047 | XXX.XXX.121.2 |     2882 | 1 | zone1 | 2881 |               0 | active |                     0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) |         0 | 1695534646080502 |            0 | 1 |                 0 |
| 2023-07-22 10:33:57.037801 | 2023-10-06 15:00:14.906429 | XXX.XXX.121.20 | 2882 |  9 | zone1 |       2881 | 0 | active | 0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) | 0 |   1693204634903636 | 0 |              1 | 0 |
| 2023-07-22 10:33:23.764036 | 2023-10-06 15:00:14.906747 | XXX.XXX.121.21 |     2882 | 2 | zone2 | 2881 |               0 | active |                     0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) |         0 | 1693680891904756 |            0 | 1 |                 0 |
| 2023-07-22 10:33:56.953497 | 2023-10-06 15:00:14.907130 | XXX.XXX.121.26 | 2882 |  5 | zone3 |       2881 | 0 | active | 0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) | 0 |   1693204637876128 | 0 |              1 | 0 |
| 2023-07-22 10:33:57.015679 | 2023-10-06 15:00:14.907453 | XXX.XXX.121.29 |     2882 | 8 | zone1 | 2881 |               0 | active |                     0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) |         0 | 1696469887562433 |            0 | 1 |                 0 |
| 2023-07-22 10:33:56.933486 | 2023-10-06 15:00:14.907709 | XXX.XXX.121.33 | 2882 |  4 | zone3 |       2881 | 0 | active | 0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) | 0 |   1696470038929664 | 0 |              1 | 0 |
| 2023-07-22 10:33:56.995639 | 2023-10-06 15:00:14.908058 | XXX.XXX.121.34 |     2882 | 7 | zone2 | 2881 |               0 | active |                     0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) |         0 | 1693204644579090 |            0 | 1 |                 0 |
| 2023-07-22 10:33:56.974571 | 2023-10-06 15:00:14.908378 | XXX.XXX.121.38 | 2882 |  6 | zone2 |       2881 | 0 | active | 0 | 3.2.4.5_105000012023081513-b9078c99710d2e2ba9c6b4b9756559e7e60ea7eb(Aug 15 2023 13:52:10) | 0 |   1696470572958889 | 0 |              1 | 0 |
+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+-------------------------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+
9 rows in set (0.007 sec)

END

本文作者：翟诚德(上海新炬中北团队）

本文来源：“IT那活儿”公众号

想了解更多干货，可通过下方扫码关注