This topic describes how to use OceanBase Diagnostic Tool (obdiag) that is independently deployed to collect and display the information about top SQL statements in a cluster in a terminal.
Syntax
obdiag display scene run --scene=observer.topsql [options]
The following table describes the options.
| Option | Required? | Data type | Default value | Description |
|---|---|---|---|---|
| -c | No | string | ~/.obdiag/config.yml |
The path of the configuration file. |
| --inner_config | No | string | Empty | The configurations of obdiag. |
| --config | No | string | Empty | The configurations of the cluster diagnosed by obdiag, in the format of --config key1=value1 --config key2=value2. |
| --env | Yes | string | Empty | Additional parameters required for the obdiag display command, in the format of --env db_connect='-h127.0.0.1 -P2881 -utest@test -p****** -Dtest' --env mtime=10'. |
Note
In the
--envoption,db_connect='-h127.0.0.1 -P2881 -utest@test -p****** -Dxxxx'indicates the connection string of the business tenant.In the
--envoption,mtimeindicates the period for which top SQL statements are to be queried, in minutes.
Examples
Method 1: Use obdiag out-of-the-box without a configuration file
Note
For more information about the parameters used in the command in this section, see Configure obdiag.
# The --env option is passed to specify the connection string of the tenant whose top SQL statements within the last 10 minutes are to be queried.
$ obdiag display scene run --scene=observer.topsql --env db_connect='-h127.0.0.1 -P2881 -utest@test -p****** -Dtest' --env mtime=10 \
--config db_host=xx.xx.xx.xx \
--config db_port=xxxx \
--config tenant_sys.user=root@sys \
--config tenant_sys.password=***
# The --env option is not passed to specify the connection string of the tenant whose top SQL statements are to be queried. By default, the top SQL statements of all tenants are queried from the sys tenant.
obdiag display scene run --scene=observer.topsql --env mtime=10 \
--config db_host=xx.xx.xx.xx \
--config db_port=xxxx \
--config tenant_sys.user=root@sys \
--config tenant_sys.password=***
Method 2: Use obdiag with a configuration file
Before you use obdiag, make sure that you have configured the login information of the target nodes in the config.yml configuration file of obdiag. For more information, see Configure obdiag.
# The --env option is passed to specify the connection string of the tenant whose top SQL statements are to be queried.
obdiag display scene run --scene=observer.topsql --env db_connect='-h127.0.0.1 -P2881 -utest@test -p****** -Dtest' --env mtime=10
# The --env option is not passed to specify the connection string of the tenant whose top SQL statements are to be queried. By default, the top SQL statements of all tenants are queried from the sys tenant.
obdiag display scene run --scene=observer.topsql --env mtime=10
Results
After the command is executed, the execution result is displayed in the current terminal.
$ obdiag display scene run --scene=observer.topsql --env mtime=10
display_scenes_run start ...
execute tasks: observer.topsql
+-------------+----------------------------------+-----+------------------+----------------------------------------------------+
| tenant_name | SQL_ID | QPS | ELAPSED_TIME_AVG | substr(query_sql,1,50) |
+-------------+----------------------------------+-----+------------------+----------------------------------------------------+
| sys | 549981E00A3D95F03DD5A9EF6044AA20 | 378 | 3495.1931 | SHOW DATABASES |
| sys | 2983E372002117946E00660266EB3683 | 302 | 170.1093 | SELECT * FROM `dag_instance` WHERE is_finished = f |
| sys | B7A6FA97FEC98C06F9586D23935AC4C6 | 76 | 136.6053 | START TRANSACTION |
| sys | 7E63F1D55520F7F0FAC4F1CD0DDC928C | 76 | 314.5658 | UPDATE `task_maintainer` SET `agent_ip`=?,`agent_p |
| sys | 1D0BA376E273B9D622641124D8C59264 | 76 | 153.5132 | COMMIT |
| sys | C64BE5C6E91CDAD08370B7189C583332 | 7 | 197.1429 | SET NAMES utf8mb4 |
| sys | B82478EC6D092CFD8C6DEBDF1145B82B | 6 | 110.3333 | SET SESSION ob_query_timeout=3216672000000000; |
| sys | 57FAD36F68CEFB8F65A592FA1010293D | 6 | 211.6667 | SET SESSION ob_trx_timeout=3216672000000000; |
| sys | C2321A290BD094638AAE0DB1434529C6 | 3 | 63.0000 | select version(); |
| sys | 6071A916B12AC78D2EBC495CD798FA1A | 2 | 182094.0000 | select tenant_name, SQL_ID, count(*) as QPS, avg(t |
+-------------+----------------------------------+-----+------------------+----------------------------------------------------+
Trace ID: 0ba8ddb0-8a2e-11ef-a171-00163e01c7ce
Note
The query results are sorted by queries per second (QPS). ELAPSED_TIME_AVG indicates the average execution time of an SQL statement, in microseconds.