SHOW PROCESSLIST;
svr_session_count 会话数量 。 这个该如何理解？
客户端跟obproxy的连接称之为前端连接，obproxy跟observer的连接称之为后端连接。
通过obproxy连接ob租户的时候，刚开始的时候只有一个前端连接和一个后端连接。
obclient [TPCC]> show processlist; 21:53:47 [58/1876]
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| 604879 | oboracle | sys | 10.0.0.61:10982 | TPCC | 0 | 1 | MCS_ACTIVE_READER | 13004 | 12993 |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.002 sec)
此时 svr_session_count 就是这一笔连接记录对应后端连接的数量。
当开始表的数据时，如果这个表的主副本位置跟当前后端连接不在一个observer节点的时候，就会再生成一个后端连接。
obclient [TPCC]> create table t_hash(id number not null primary key, c1 varchar2(50),c3 date default sysdate) partition by hash(id) partitions 4;
Query OK, 0 rows affected (0.395 sec)
obclient [TPCC]> insert into t_hash(id,c1) values(1,'A'),(2,'b'),(3,'C'),(4,'d');
Query OK, 4 rows affected (0.093 sec)
Records: 4 Duplicates: 0 Warnings: 0
obclient [TPCC]> select * from t_hash;
+----+------+-----------+
| ID | C1 | C3 |
+----+------+-----------+
| 2 | b | 20-JUN-24 |
| 1 | A | 20-JUN-24 |
| 4 | d | 20-JUN-24 |
| 3 | C | 20-JUN-24 |
+----+------+-----------+
4 rows in set (0.040 sec)
obclient [TPCC]> show processlist;
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| 604879 | oboracle | sys | 10.0.0.61:10982 | TPCC | 0 | 2 | MCS_ACTIVE_READER | 13004 | 12993 |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.002 sec)
obclient [TPCC]> show proxysession;
+--------------------+--------+---------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
| proxy_sessid | Id | Cluster | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid | using_ssl |
+--------------------+--------+---------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
| 720576206856200699 | 604879 | OB4216 | oboracle | sys | 10.0.0.61:10982 | TPCC | 0 | 2 | MCS_ACTIVE_READER | 13004 | 12993 | 0 |
+--------------------+--------+---------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+-----------+
1 row in set (0.002 sec)
这里的 svr_session_count 后端连接数变为 2 了。
当然这个例子的第二个查询结果是有一定随机性的，其原因跟第一个后端链接是随机有关。所以有些人跑这个测试可能第二个查询的 svr_session_count 后还是 1.
但是跑下面 SQL 一定是 2 或者 3 。当然前提是租户是两副本或三副本（我这里是 2F1A，属于两副本）。
obclient [TPCC]> show processlist;
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| 604983 | oboracle | sys | 10.0.0.61:12432 | TPCC | 0 | 1 | MCS_ACTIVE_READER | 13004 | 12993 |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.003 sec)
obclient [TPCC]> select * from t_hash where id=1;
+----+------+-----------+
| ID | C1 | C3 |
+----+------+-----------+
| 1 | A | 20-JUN-24 |
+----+------+-----------+
1 row in set (0.004 sec)
obclient [TPCC]> show processlist;
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| 604983 | oboracle | sys | 10.0.0.61:12432 | TPCC | 0 | 2 | MCS_ACTIVE_READER | 13004 | 12993 |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.002 sec)
obclient [TPCC]> select * from t_hash where id=2;
+----+------+-----------+
| ID | C1 | C3 |
+----+------+-----------+
| 2 | b | 20-JUN-24 |
+----+------+-----------+
1 row in set (0.005 sec)
obclient [TPCC]> show processlist;
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| Id | Tenant | User | Host | db | trans_count | svr_session_count | state | tid | pid |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
| 604983 | oboracle | sys | 10.0.0.61:12432 | TPCC | 0 | 2 | MCS_ACTIVE_READER | 13004 | 12993 |
+--------+----------+------+-----------------+------+-------------+-------------------+-------------------+-------+-------+
1 row in set (0.002 sec)
上面测试是在 4.2 里跑的。如果你在 2.2 或 3.2 里跑结果可能跟我这个又不一样。因为参数 ob_proxy_readonly_transaction_routing_policy 也会影响 obproxy 后端连接的路由。oracle 默认是开启事务的，事务里的第一个只读查询在 2.2 和 3.2 里是开启事务路由特性的。4.2 里是不算的。
obclient [TPCC]> show parameters like '%readonly%';
+-------+----------+-----------+----------+----------------------------------------------+-----------+-------+------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+
| ZONE | SVR_TYPE | SVR_IP | SVR_PORT | NAME | DATA_TYPE | VALUE | INFO | SECTION | SCOPE | SOURCE | EDIT_LEVEL |
+-------+----------+-----------+----------+----------------------------------------------+-----------+-------+------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+
| zone2 | observer | 10.0.0.63 | 2882 | ob_proxy_readonly_transaction_routing_policy | NULL | False | Proxy route policy for readonly sql: whether regard begining read only stmts as in transaction | OBSERVER | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 10.0.0.62 | 2882 | ob_proxy_readonly_transaction_routing_policy | NULL | False | Proxy route policy for readonly sql: whether regard begining read only stmts as in transaction | OBSERVER | TENANT | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+-----------+----------+----------------------------------------------+-----------+-------+------------------------------------------------------------------------------------------------+----------+--------+---------+-------------------+
2 rows in set (0.012 sec)
所以小小字段 svr_session_count 是能看出后端连接数。如果租户的资源机器数是 4台 或 6 台，这个值最大可能就是4 或 6 。
这个字段只有通过 obproxy 连接的时候 查看 show processlist 才会有。
要验证上面的猜想，可以另开两个窗口，单独直连 后端 observer 的2881 端口（不经过obproxy），然后通过 show processlist 找到前面obproxy对应的两个后端连接（每个observer 1 个）。然后 随机 kill 掉其中一个后端连接，再查看obproxy 里的 show processlist 有一定概率会出现说连接断开重连，或者不重连只是 svr_session_count 变少了一个。
万变不离其宗，关键点就是 obproxy的前端连接和后端连接原理。
阅读 obproxy.log 也能看到有关前端连接和后端链接建立的过程。
更多详细原理可以阅读：《 OBCE V3 培训实验：应用到数据库全链路分析（上） (qq.com)》。
3 个赞
我要打赏，这里缺少一个打赏的按钮。 官方可以该讲下。
1 个赞
必须要赞一个