ocp监控中租户qps 2万+ 但TPS一直不超过3

AntTech_GASIPE · 2025 年3 月 13 日 16:26

旭辉 · 2025 年3 月 13 日 16:40

然后再麻烦发下 3月12日 21:00:00~ 21:00:05的 TPS监控截图

AntTech_GASIPE · 2025 年3 月 13 日 17:40

旭辉 · 2025 年3 月 13 日 18:37

性能监控选全部zone和全部observer，SQL诊断选全部observer

3月12日 21:00:00~ 21:00:05的截图看下呢

AntTech_GASIPE · 2025 年3 月 13 日 18:47

是一样的，我们所有SQL都只在这一个zone 上执行的

旭辉 · 2025 年3 月 13 日 18:56

好的，我再看下

辞霜 · 2025 年3 月 14 日 10:18

麻烦执行下这个sql查下看看
select ACTION,count(*) from from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip=’’ group by action;

独善其身 · 2025 年3 月 14 日 10:32

看不透

AntTech_GASIPE · 2025 年3 月 14 日 11:57

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        7 |
| COMMIT |        1 |
+--------+----------+
2 rows in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        9 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        4 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        3 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        4 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        7 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |       11 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        5 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        2 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        4 |
| COMMIT |        1 |
+--------+----------+
2 rows in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        7 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| COMMIT |        1 |
| START  |        7 |
+--------+----------+
2 rows in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        3 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        3 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        3 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        2 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        1 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        4 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        1 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        1 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        6 |
| COMMIT |        1 |
+--------+----------+
2 rows in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        7 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        6 |
| COMMIT |        1 |
+--------+----------+
2 rows in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        8 |
+--------+----------+
1 row in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        6 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        7 |
+--------+----------+
1 row in set (0.00 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| COMMIT |        1 |
| START  |        3 |
+--------+----------+
2 rows in set (0.01 sec)

MySQL [oceanbase]> select ACTION,count(*)  from GV$OB_TRANSACTION_PARTICIPANTS where svr_ip='10.10.17.204' group by action;
+--------+----------+
| ACTION | count(*) |
+--------+----------+
| START  |        4 |
+--------+----------+
1 row in set (0.01 sec)

AntTech_QWWMD2 · 2025 年3 月 18 日 07:23

学到了！

辞霜 · 2025 年3 月 20 日 10:58

你好麻烦通过ocp的sql诊断提供一下回滚事务的trace id。再根据时间提供一下当时的observer日志

AntTech_GASIPE · 2025 年3 月 20 日 15:20

请教一下 ocp sql 诊断如何看回滚事物的trace id ?

倒是有个事物诊断但里面也只有【其它事务】里会有信息，但这些事务也不是一直在的，过一会就不见了。

辞霜 · 2025 年3 月 20 日 15:41

如果确认是存在回滚导致的tps低，可以通过事务的sql去gv$ob_sql_audit视图使用query_sql字段去晒出相关trace id

AntTech_GASIPE · 2025 年3 月 20 日 16:28

目前的情况是，我们业务是正常进行中的，TPS应该是在300~500左右（通过SQL诊段的commit 数量计算出来也是这个量级）
现在就是发现OCP中这个监控里面，TPS低，回滚的TPS也是低，都是个位数。现在是想找到为什么这个监控中显示的TPS这么低的原因，目前我能想到的是以下两点：

最初想法可能是OCP监控采集数据有问题，目前看这个可能已经排除。
2.如果不是OCP数据采集问题，那可能就是OB集群自身数据统计有问题。

AntTech_GASIPE · 2025 年4 月 2 日 22:19

更新一下：升级ob到 4.3.5_bp1 后就正常了，

研发反馈找到了一个已知问题：诊断框架重构后，查询gv$sesstat 和gv$session_wait视图缺少部分session 结果
和这个类似，在4.3.5.1修复了，您看测试环境是否可以升级到4.3.5.1观察下看看

这里特别感谢一下官方几位大佬的帮助！@ 辞霜 @ 旭辉

AntTech_F3VXVZ · 2025 年4 月 9 日 09:33

学习学习