--- title: OceanBase 数据库中租户资源统计项及其查询方法-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 OceanBase 数据库中租户资源统计项及其查询方法相关的常见问题和使用技巧，帮助您快速解决 OceanBase 数据库中租户资源统计项及其查询方法的难题。 --- 切换语言 - 简体中文 - English 划线反馈 # OceanBase 数据库中租户资源统计项及其查询方法 更新时间：2024-05-10 08:16 适用版本： V4.1.x、V4.2.x 内容类型：TechNote 本文主要介绍 OceanBase 数据库中租户资源统计项及其查询方法。 ## 适用版本 OceanBase 数据库 V4.1.x、V4.2.x 版本。 ## CPU 资源统计项 - 逻辑 CPU 使用率（线程处理请求的时间占比）。 通过虚拟表 `__all_virtual_sysstat` 在 SYS 系统租户下，查看租户的 `cpu_usage`，表示一段时间内（1s）租户工作线程的处理请求时间占比和租户 `unit` 规格，粗算的逻辑 CPU 使用率，数值代表百分比，例如 400 指的是 400%。 ```shell obclient> SELECT * FROM oceanbase.__all_virtual_sysstat WHERE tenant_id = 1 AND name = 'cpu usage'; ``` 输出结果如下： ```shell +-----------+--------------+----------+------------+-------+------------+---------+-----------+-------+-------------+ | tenant_id | svr_ip | svr_port | statistic# | value | value_type | stat_id | name | class | can_visible | +-----------+--------------+----------+------------+-------+------------+---------+-----------+-------+-------------+ | 1 | xx.xxx.xx.xx | 57234 | 573 | 10 | SET_VALUE | 140006 | cpu usage | 64 | 1 | +-----------+--------------+----------+------------+-------+------------+---------+-----------+-------+-------------+ ``` - 租户线程消耗的 CPU 总时间。 通过虚拟表 `__all_virtual_sysstat` 在 SYS 系统租户下，查看租户的 `cpu_time`，不管是否开启 `cgroup`，都能获取到 cpu time 的值。区别在于开启了 `cgroup` 后，会通过 `cgroup` 功能从 `cpuacct.usage` 中获取 CPU 时间；否则，不开启 `cgroup` 。只会通过 /proc/$pid/task/$tid/stat 目录来读取 CPU 时间。 ```shell obclient> SELECT * FROM oceanbase.__all_virtual_sysstat WHERE tenant_id = 1 AND name = 'cpu time'; ``` 输出结果如下： ```shell +-----------+--------------+----------+------------+-------+------------+---------+----------+-------+-------------+ | tenant_id | svr_ip | svr_port | statistic# | value | value_type | stat_id | name | class | can_visible | +-----------+--------------+----------+------------+-------+------------+---------+----------+-------+-------------+ | 1 | xx.xxx.xx.xx | 57234 | 580 | 0 | SET_VALUE | 140013 | cpu time | 64 | 1 | +-----------+--------------+----------+------------+-------+------------+---------+----------+-------+-------------+ ``` ## 内存资源统计项 - mod 内存占用情况。 通过虚拟表 `__all_virtual_memory_info` 在 SYS 系统租户下，查询租户的每个 `mod_name` 的内存占用情况。 ```shell obclient> SELECT * FROM oceanbase.__all_virtual_memory_info WHERE tenant_id = 1 LIMIT 1; ``` 输出结果如下： ```shell +-----------+--------------+----------+--------+-----------------+----------------+----------+--------+-----------------+-------+-----------+-----------+-------+ | tenant_id | svr_ip | svr_port | ctx_id | label | ctx_name | mod_type | mod_id | mod_name | zone | hold | used | count | +-----------+--------------+----------+--------+-----------------+----------------+----------+--------+-----------------+-------+-----------+-----------+-------+ | 1 | xx.xxx.xx.xx | 57234 | 0 | MysqlRequesReco | DEFAULT_CTX_ID | user | 0 | MysqlRequesReco | zone1 | 146604032 | 146551808 | 33 | +-----------+--------------+----------+--------+-----------------+----------------+----------+--------+-----------------+-------+-----------+-----------+-------+ ``` 有关 mod 详细信息参见：[V$OB_MEMORY](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000640751) 。 - ctx 和 mod 相关的内存信息。 通过 `observer.log` 文件中包含 [MEMORY] 标签的日志，可以查看租户 ctx 和 mod 相关的内存信息，10 秒一次。 ```shell grep "\[MEMORY\]" observer.log* ``` 输出结果如下： ```shell observer.log.20230203145702002:[MEMORY] tenant_id= 1001 ctx_id= DEFAULT_CTX_ID hold= 374,960,128 used= 337,967,264 limit= 9,223,372,036,854,775,807 observer.log.20230203145702002:[MEMORY] idle_size= 0 free_size= 0 observer.log.20230203145702002:[MEMORY] wash_related_chunks= 0 washed_blocks= 0 washed_size= 0 observer.log.20230203145702002:[MEMORY] hold= 100,143,104 used= 100,086,048 count= 37 avg_used= 2,705,028 block_cnt= 37 chunk_cnt= 37 mod=IoControl observer.log.20230203145702002:[MEMORY] hold= 80,019,456 used= 80,000,000 count= 1 avg_used= 80,000,000 block_cnt= 1 chunk_cnt= 1 mod=MysqlRequesReco observer.log.20230203145702002:[MEMORY] hold= 41,963,520 used= 41,943,040 count= 1 avg_used= 41,943,040 block_cnt= 1 chunk_cnt= 1 mod=LogGroupBuffer observer.log.20230203145702002:[MEMORY] hold= 18,878,080 used= 17,829,440 count= 129 avg_used= 138,212 block_cnt= 129 chunk_cnt= 12 mod=SqlDtl observer.log.20230203145702002:[MEMORY] hold= 16,039,936 used= 16,000,000 count= 2 avg_used= 8,000,000 block_cnt= 2 chunk_cnt= 2 mod=SqlPlanManger ``` ## IO 资源统计项 - IO 日志信息有以下几类。 通过不同的 grep 命令查看。 - grep "[IO STATUS]" 查看所有租户不同 group 的实时 iops 值（对应 `__all_virtual_io_quota` 表），有 IO 流量的情况下每秒打印一次。 - grep "IO STATUS SENDER" 查看 IO 请求的调度信息，包括所有调度线程中排队的IO请求数量和下一个请求发出时间（对应 `__all_virtual_io_scheduler` 表），每秒打印一次。 - grep "IO STATUS TRACER" 查看 IO 引用计数统计，用以排查内存泄漏问题（前提是开了 io_trace 配置项），如果 `req_count` 持续上涨那么可能存在有泄漏的问题，可以通过 `backtrace` 查看调用栈。 示例。 ![y98u0-gy683-778u3-6783j](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240223resource0001.png) - IO 统计信息的表主要是 `__all_virtual_io_quota` 表，这个表中实时展示了不同 group 的 iops 情况。 ![y98u0-gy683-778u3-6783j](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/knowledge-base/database/sql/20240223resource0002.png) - IO 调度信息的表主要是 `__all_virtual_io_scheduler` 表这个表中展示了当前 IO 请求排队和调度信息，包括下一个请求的发送时间。 ## Session 会话信息统计项 - 租户 session 会话信息。 通过查询 `__all_virtual_session_info` 表，在 SYS 系统租户下，查看租户 session 会话信息。 ```shell SELECT * FROM oceanbase.__all_virtual_session_info WHERE tenant = 'sys' LIMIT 1; ``` 输出结果如下： ```shell +------------+------+--------+--------------------+-----------+---------+----------------------------------+------+--------+---------------------------------------------------------------------+--------------+----------+----------+--------------+---------------+----------------+-----------+----------+-----------+------------+------------------------------------+-----------+-----------+-------------+ | id | user | tenant | host | db | command | sql_id | time | state | info | svr_ip | svr_port | sql_port | proxy_sessid | master_sessid | user_client_ip | user_host | trans_id | thread_id | ssl_cipher | trace_id | ref_count | backtrace | trans_state | +------------+------+--------+--------------------+-----------+---------+----------------------------------+------+--------+---------------------------------------------------------------------+--------------+----------+----------+--------------+---------------+----------------+-----------+----------+-----------+------------+------------------------------------+-----------+-----------+-------------+ | 3221487625 | root | sys | xx.xxx.xx.xx:xxxxx | oceanbase | Query | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 0 | ACTIVE | select * from __all_virtual_session_info where tenant='sys' limit 1 | xx.xxx.xx.xx | 57234 | 57235 | NULL | NULL | xx.xxx.xx.xx | % | 0 | 117243 | NULL | xxxxxxxxxxxxx-xxxxxxxxxxxxxxxx-x-x | 0 | | | +------------+------+--------+--------------------+-----------+---------+----------------------------------+------+--------+---------------------------------------------------------------------+--------------+----------+----------+--------------+---------------+----------------+-----------+----------+-----------+------------+------------------------------------+-----------+-----------+-------------+ ``` - 租户会话统计数据。 通过查询 `__all_virtual_sesstat` 表，在 SYS 系统租户下，查看租户会话统计数据。 ```shell SELECT * FROM oceanbase.__all_virtual_sesstat WHERE tenant_id = 1 LIMIT 1; ``` 输出结果如下： ```shell +------------+--------------+----------+------------+-----------+-------+-------------+ | session_id | svr_ip | svr_port | statistic# | tenant_id | value | can_visible | +------------+--------------+----------+------------+-----------+-------+-------------+ | 3221225472 | xx.xxx.xx.xx | 57234 | 0 | 1 | 1 | 1 | +------------+--------------+----------+------------+-----------+-------+-------------+ ``` - 租户 session 会话等待情况。 通过查询 `__all_virtual_session_wait` 表，在 SYS 系统租户下，查看租户 session 会话等待情况。 ```shell SELECT * FROM oceanbase.__all_virtual_session_wait WHERE tenant_id = 1 LIMIT 1; ``` 输出结果如下： ```shell +------------+--------------+----------+-----------+----------+--------+-----+--------+-----+--------+----+-------+---------------+-------------+------------+-------------------+-----------------+----------------------+----------------------------+ | session_id | svr_ip | svr_port | tenant_id | event | p1text | p1 | p2text | p2 | p3text | p3 | level | wait_class_id | wait_class# | wait_class | state | wait_time_micro | time_remaining_micro | time_since_last_wait_micro | +------------+--------------+----------+-----------+----------+--------+-----+--------+-----+--------+----+-------+---------------+-------------+------------+-------------------+-----------------+----------------------+----------------------------+ | 3221225472 | xx.xxx.xx.xx | 57234 | 1 | sync rpc | pcode | 257 | size | 286 | | 0 | 0 | 107 | 7 | NETWORK | WAITED KNOWN TIME | 103613812 | NULL | 2410634833 | +------------+--------------+----------+-----------+----------+--------+-----+--------+-----+--------+----+-------+---------------+-------------+------------+-------------------+-----------------+----------------------+----------------------------+ ``` - 租户 session 会话等待历史记录。 通过查询 `__all_virtual_session_wait_history` 表，在 SYS 系统租户下，查看租户 session 会话等待历史记录。 ```shell SELECT * FROM oceanbase.__all_virtual_session_wait_history WHERE tenant_id = 1 LIMIT 1; ``` 输出结果如下： ```shell +------------+--------------+----------+------+-----------+--------+----------+--------+-----+--------+-----+--------+----+-------+-----------------+----------------------------+------------+ | session_id | svr_ip | svr_port | seq# | tenant_id | event# | event | p1text | p1 | p2text | p2 | p3text | p3 | level | wait_time_micro | time_since_last_wait_micro | wait_time | +------------+--------------+----------+------+-----------+--------+----------+--------+-----+--------+-----+--------+----+-------+-----------------+----------------------------+------------+ | 3221225472 | xx.xxx.xx.xx | 57234 | 1 | 1 | 20 | sync rpc | pcode | 257 | size | 286 | | 0 | 0 | 103613812 | 2442809561 | 10361.3812 | +------------+--------------+----------+------+-----------+--------+----------+--------+-----+--------+-----+--------+----+-------+-----------------+----------------------------+------------+ ``` Previous [系统 sys 租户 CPU 使用率长时间 100% 或者 sys 租户报错不可用](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005437445) Next [MySQL 租户存在多个同名 user 时，将导致 dbms_scheduler 无法工作](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002498969) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线