本文主要介紹 OceanBase 數據庫中租戶資源統計項及其查詢方法。
適用版本
OceanBase 數據庫 V4.1.x、V4.2.x 版本。
CPU 資源統計項
-
邏輯 CPU 使用率（線程處理請求的時間占比）。
通過虛擬表?
__all_virtual_sysstat?在 SYS 系統租戶下，查看租戶的?cpu_usage，表示一段時間內（1s）租戶工作線程的處理請求時間占比和租戶?unit?規格，粗算的邏輯 CPU 使用率，數值代表百分比，例如 400 指的是 400%。obclient> SELECT * FROM oceanbase.__all_virtual_sysstat WHERE tenant_id = 1 AND name = 'cpu usage';輸出結果如下：
+-----------+--------------+----------+------------+-------+------------+---------+-----------+-------+-------------+ | 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 時間。obclient> SELECT * FROM oceanbase.__all_virtual_sysstat WHERE tenant_id = 1 AND name = 'cpu time';輸出結果如下：
+-----------+--------------+----------+------------+-------+------------+---------+----------+-------+-------------+ | 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?的內存占用情況。obclient> SELECT * FROM oceanbase.__all_virtual_memory_info WHERE tenant_id = 1 LIMIT 1;輸出結果如下：
+-----------+--------------+----------+--------+-----------------+----------------+----------+--------+-----------------+-------+-----------+-----------+-------+ | 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?。
-
ctx 和 mod 相關的內存信息。
通過?
observer.log?文件中包含 [MEMORY] 標簽的日志，可以查看租戶 ctx 和 mod 相關的內存信息，10 秒一次。grep "\[MEMORY\]" observer.log*輸出結果如下：
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?查看調用棧。示例。
-
-
IO 統計信息的表主要是?
__all_virtual_io_quota?表，這個表中實時展示了不同 group 的 iops 情況。
-
IO 調度信息的表主要是?
__all_virtual_io_scheduler?表這個表中展示了當前 IO 請求排隊和調度信息，包括下一個請求的發送時間。
Session 會話信息統計項
-
租戶 session 會話信息。
通過查詢?
__all_virtual_session_info?表，在 SYS 系統租戶下，查看租戶 session 會話信息。SELECT * FROM oceanbase.__all_virtual_session_info WHERE tenant = 'sys' LIMIT 1;輸出結果如下：
+------------+------+--------+--------------------+-----------+---------+----------------------------------+------+--------+---------------------------------------------------------------------+--------------+----------+----------+--------------+---------------+----------------+-----------+----------+-----------+------------+------------------------------------+-----------+-----------+-------------+ | 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 系統租戶下，查看租戶會話統計數據。SELECT * FROM oceanbase.__all_virtual_sesstat WHERE tenant_id = 1 LIMIT 1;輸出結果如下：
+------------+--------------+----------+------------+-----------+-------+-------------+ | 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 會話等待情況。SELECT * FROM oceanbase.__all_virtual_session_wait WHERE tenant_id = 1 LIMIT 1;輸出結果如下：
+------------+--------------+----------+-----------+----------+--------+-----+--------+-----+--------+----+-------+---------------+-------------+------------+-------------------+-----------------+----------------------+----------------------------+ | 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 會話等待歷史記錄。SELECT * FROM oceanbase.__all_virtual_session_wait_history WHERE tenant_id = 1 LIMIT 1;輸出結果如下：
+------------+--------------+----------+------+-----------+--------+----------+--------+-----+--------+-----+--------+----+-------+-----------------+----------------------------+------------+ | 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 | +------------+--------------+----------+------+-----------+--------+----------+--------+-----+--------+-----+--------+----+-------+-----------------+----------------------------+------------+
附錄：
OceanBase?敏捷診斷工具(OceanBase?Diagnostic Tool,?簡稱obdiag)?是一款源代碼開源敏捷黑屏診斷工具，可以對OceanBase集群進行一鍵集群巡檢、一鍵分析、一鍵根因分析以及一鍵診斷信息收集。
obdiag 下載地址：https://www.oceanbase.com/softwarecenter
obdiag 官方文檔：https://www.oceanbase.com/docs/obdiag-cn
obdiag github地址：https://github.com/oceanbase/obdiag
obdiag?SIG 文檔：https://oceanbase.yuque.com/org-wiki-obtech-vh7w9r/imzr6c
?
)
