This article is applicable to the scenario of independent deployment of obdiag. It can be executed with one click to display the topsql information of the cluster in the terminal.
Instructions for use
obdiag display scene run --scene=observer.topsql [options]
The options are explained below:
Option name |
Is it required |
Data type |
Default value |
Description |
|---|---|---|---|---|
| -c | No | string | ~/.obdiag/config.yml |
Configuration file path. |
| --inner_config | No | string | Default is empty | obdiag's own configuration. |
| --config | No | string | Default is empty | Configuration of the cluster to be diagnosed by obdiag, fixed style: --config key1=value1 --config key2=value2.
ExplanationParameters that support configuration through this option can be found in obdiag configuration. |
| --env | Yes | string | Default is empty | Additional parameters required for obdiag display, fixed style: --env tenant_name=test --env mtime=10. |
| --config_password | No | string | Default is empty | obdiag When using an encrypted configuration file, you need to pass in the corresponding password through this option.
ExplanationFor details, please see Configuration file encryption. |
Description
In the parameter
--env,tenant_name=testneeds to be configured as the business tenant name to be displayed topsql information.In the parameter
--env,mtimerefers to viewing the latest topsql time, in minutes.
Usage example
Method 1: Use without configuration file (out of the box)
# Use --env for tenant connection; last 10 minutes of TopSQL
$ obdiag display scene run --scene=observer.topsql --env tenant_name=test --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 with configuration file
You need to ensure that the login information of the node to be collected has been configured in the obdiag configuration file config.yml. For related detailed configuration introduction, see obdiag configuration.
# Use --env to specify the tenant connection
obdiag display scene run --scene=observer.topsql --env tenant_name=test --env mtime=10
Interpretation of results
After the command is executed, the results will be displayed on the current terminal.
$ obdiag display scene run --scene=observer.topsql --env tenant_name=sys --env mtime=10
display_scenes_run start ...
execute tasks: observer.topsql
[obdiag display]: Top SQL time consumption in the last 10 minutes
+-------------+----------------------------------+----------------------+----------+-------------------+-------------------+-----------------+--------------------+--------------------+
| tenant_name | SQL_ID | query_sql | count(1) | avg(ELAPSED_TIME) | avg(EXECUTE_TIME) | avg(QUEUE_TIME) | avg(AFFECTED_ROWS) | avg(GET_PLAN_TIME) |
+-------------+----------------------------------+----------------------+----------+-------------------+-------------------+-----------------+--------------------+--------------------+
| sys | 549981E00A3D95F03DD5A9EF6044AA20 | SHOW DATABASES | 494 | 2597.0445 | 160.7753 | 15.7206 | 0.0000 | 2393.9534 |
| sys | 24D486F20861EBD9E0217692DB0E9C61 | update __all_we | 1980 | 322.5197 | 249.1025 | 0.0000 | 1.0000 | 73.4172 |
| sys | 1532BA78C664771E7113567D8E951B51 | SELECT FIELD FROM `o | 393 | 766.3639 | 736.8550 | 0.0000 | 0.0000 | 29.5089 |
| sys | B6F2A13C4C81145FFAA2F2A5CF9587A9 | select /*+query_time | 133 | 857.4135 | 731.4436 | 0.0000 | 0.0000 | 125.9699 |
| sys | 735537F7B5DB7C4E0E946C9B26108560 | SELECT * FROM __all_ | 397 | 160.9194 | 99.8866 | 0.0000 | 0.0000 | 61.0327 |
| sys | 91FD35069BC21287826257AFF83A850F | select a.tenant_id a | 397 | 151.4584 | 99.4584 | 0.0000 | 0.0000 | 52.0000 |
| sys | 32AB97A0126F566064F84DDDF4936F82 | SELECT * FROM __all_ | 397 | 143.4559 | 91.8363 | 0.0000 | 0.0000 | 51.6196 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 395 | 106.7696 | 56.3949 | 11.0101 | 0.0000 | 28.0734 |
| sys | 1D0BA376E273B9D622641124D8C59264 | COMMIT | 380 | 100.3184 | 81.4342 | 2.6395 | 0.0000 | 15.2474 |
| sys | 95C9A6346A2B1546D243358F32684FAB | SELECT *, time_to_us | 278 | 109.2770 | 70.1223 | 0.0000 | 0.0000 | 39.1547 |
| sys | 91C89692ADAF4C644A7BE9ABF335152F | SELECT * FROM __all_ | 199 | 148.9497 | 75.0151 | 0.0000 | 0.0000 | 73.9347 |
| sys | 3468E7114C438006DD0574C9657EF3BF | select * from __all_ | 160 | 158.3938 | 73.3938 | 0.0000 | 0.0000 | 85.0000 |
| sys | 7E63F1D55520F7F0FAC4F1CD0DDC928C | UPDATE `task_maintai | 99 | 242.0808 | 173.3636 | 9.1616 | 1.0000 | 40.4141 |
| sys | 858015B92843B43D99CA77541C4C2592 | SELECT VALUE FROM __ | 79 | 295.7468 | 202.9114 | 0.0000 | 0.0000 | 92.8354 |
| sys | 4BC4AF5CEA94EC33ABCDA867BCA6F702 | select * from __all_ | 198 | 114.2929 | 54.9141 | 0.0000 | 0.0000 | 59.3788 |
| sys | A5CC9FA9C8AAB2A2A93A722831E3FE6C | SELECT * FROM __all_ | 133 | 157.6842 | 78.9323 | 0.0000 | 0.0000 | 78.7519 |
| sys | 17605A1DA6B6A2150E9FBCA5D4C7653A | SELECT row_id, colum | 134 | 144.5149 | 119.9851 | 0.0000 | 0.0000 | 24.5299 |
| sys | ABBD34D4E1A757B0EC70910F5E10E2BC | select /*+ workload_ | 1 | 18895.0000 | 15633.0000 | 0.0000 | 0.0000 | 3262.0000 |
| sys | 69D4EC82CE11CB6BA481591FD96467E2 | SELECT value from oc | 119 | 155.7479 | 88.3529 | 0.0000 | 0.0000 | 67.3950 |
| sys | 1B5159E5A7962D8F5A091276DFAB5B9F | INSERT /*+ WORKLOAD_ | 1 | 18137.0000 | 17774.0000 | 0.0000 | 0.0000 | 363.0000 |
+-------------+----------------------------------+----------------------+----------+-------------------+-------------------+-----------------+--------------------+--------------------+
[obdiag display]: Top-N SQL queries ranked by request count in the last 10 minutes
+-------------+----------------------------------+----------------------+------+-----------+
| tenant_name | SQL_ID | query_sql | QPS | RT |
+-------------+----------------------------------+----------------------+------+-----------+
| sys | 24D486F20861EBD9E0217692DB0E9C61 | update __all_we | 1980 | 322.5197 |
| sys | 549981E00A3D95F03DD5A9EF6044AA20 | SHOW DATABASES | 494 | 2597.0445 |
| sys | 735537F7B5DB7C4E0E946C9B26108560 | SELECT * FROM __all_ | 397 | 160.9194 |
| sys | 32AB97A0126F566064F84DDDF4936F82 | SELECT * FROM __all_ | 397 | 143.4559 |
| sys | 91FD35069BC21287826257AFF83A850F | select a.tenant_id a | 397 | 151.4584 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 395 | 106.7696 |
| sys | 1532BA78C664771E7113567D8E951B51 | SELECT FIELD FROM `o | 393 | 766.3639 |
| sys | 1D0BA376E273B9D622641124D8C59264 | COMMIT | 380 | 100.3184 |
| sys | 95C9A6346A2B1546D243358F32684FAB | SELECT *, time_to_us | 278 | 109.2770 |
| sys | 7F33FD22651F99E8AB2BAC5428623BCD | START TRANSACTION WI | 201 | 35.5622 |
| sys | 1032E4307F4B68B0548C5016BF927151 | SELECT job_id FROM _ | 200 | 54.5650 |
| sys | 91C89692ADAF4C644A7BE9ABF335152F | SELECT * FROM __all_ | 199 | 148.9497 |
| sys | FA5F24F77A8CD9A570B277492D6FD7C4 | SELECT zone FROM __a | 199 | 70.7337 |
| sys | 31F2D49F8660DCD961B5C66C4DFB1B07 | select distinct(zone | 199 | 87.7588 |
| sys | E784E48A4BE516FCBBF5792E1F1ABBBA | select * from __all_ | 198 | 63.7879 |
| sys | 4BC4AF5CEA94EC33ABCDA867BCA6F702 | select * from __all_ | 198 | 114.2929 |
| sys | B7A6FA97FEC98C06F9586D23935AC4C6 | START TRANSACTION | 179 | 77.4190 |
| sys | 3468E7114C438006DD0574C9657EF3BF | select * from __all_ | 160 | 158.3938 |
| sys | 17605A1DA6B6A2150E9FBCA5D4C7653A | SELECT row_id, colum | 134 | 144.5149 |
| sys | A5CC9FA9C8AAB2A2A93A722831E3FE6C | SELECT * FROM __all_ | 133 | 157.6842 |
+-------------+----------------------------------+----------------------+------+-----------+
[obdiag display]: The SQL that consumes the most CPU among all SQLs in the last 10 minutes
+-------------+----------------------------------+----------------------+----------+------+--------------------+-------------------+
| tenant_name | sql_id | query_sql | cpu_time | cnt | avg(get_plan_time) | avg(execute_time) |
+-------------+----------------------------------+----------------------+----------+------+--------------------+-------------------+
| sys | 549981E00A3D95F03DD5A9EF6044AA20 | SHOW DATABASES | 1275174 | 494 | 2393.9534 | 160.7753 |
| sys | 24D486F20861EBD9E0217692DB0E9C61 | update __all_we | 638589 | 1980 | 73.4172 | 249.1025 |
| sys | 1532BA78C664771E7113567D8E951B51 | SELECT FIELD FROM `o | 301181 | 393 | 29.5089 | 736.8550 |
| sys | B6F2A13C4C81145FFAA2F2A5CF9587A9 | select /*+query_time | 114036 | 133 | 125.9699 | 731.4436 |
| sys | 735537F7B5DB7C4E0E946C9B26108560 | SELECT * FROM __all_ | 63885 | 397 | 61.0327 | 99.8866 |
| sys | 91FD35069BC21287826257AFF83A850F | select a.tenant_id a | 60129 | 397 | 52.0000 | 99.4584 |
| sys | 32AB97A0126F566064F84DDDF4936F82 | SELECT * FROM __all_ | 56952 | 397 | 51.6196 | 91.8363 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 37825 | 395 | 28.0734 | 56.3949 |
| sys | 1D0BA376E273B9D622641124D8C59264 | COMMIT | 37118 | 380 | 15.2474 | 81.4342 |
| sys | 95C9A6346A2B1546D243358F32684FAB | SELECT *, time_to_us | 30379 | 278 | 39.1547 | 70.1223 |
| sys | 91C89692ADAF4C644A7BE9ABF335152F | SELECT * FROM __all_ | 29641 | 199 | 73.9347 | 75.0151 |
| sys | 3468E7114C438006DD0574C9657EF3BF | select * from __all_ | 25343 | 160 | 85.0000 | 73.3938 |
| sys | 858015B92843B43D99CA77541C4C2592 | SELECT VALUE FROM __ | 23364 | 79 | 92.8354 | 202.9114 |
| sys | 7E63F1D55520F7F0FAC4F1CD0DDC928C | UPDATE `task_maintai | 23059 | 99 | 40.4141 | 173.3636 |
| sys | 4BC4AF5CEA94EC33ABCDA867BCA6F702 | select * from __all_ | 22630 | 198 | 59.3788 | 54.9141 |
| sys | A5CC9FA9C8AAB2A2A93A722831E3FE6C | SELECT * FROM __all_ | 20972 | 133 | 78.7519 | 78.9323 |
| sys | 17605A1DA6B6A2150E9FBCA5D4C7653A | SELECT row_id, colum | 19365 | 134 | 24.5299 | 119.9851 |
| sys | ABBD34D4E1A757B0EC70910F5E10E2BC | select /*+ workload_ | 18895 | 1 | 3262.0000 | 15633.0000 |
| sys | 69D4EC82CE11CB6BA481591FD96467E2 | SELECT value from oc | 18534 | 119 | 67.3950 | 88.3529 |
| sys | 1B5159E5A7962D8F5A091276DFAB5B9F | INSERT /*+ WORKLOAD_ | 18137 | 1 | 363.0000 | 17774.0000 |
+-------------+----------------------------------+----------------------+----------+------+--------------------+-------------------+
[obdiag display]: Check whether there have been a large number of unreasonable remote execution requests for SQL executions in the past 10 minutes
+----------+-----------+
| count(*) | plan_type |
+----------+-----------+
| 997 | 1 |
| 322 | 0 |
+----------+-----------+
[obdiag display]: SQL for querying a full table scan
+-------------+----------------------------------+----------------------+--------------+
| tenant_name | SQL_ID | query_sql | elapsed_time |
+-------------+----------------------------------+----------------------+--------------+
| sys | 60AFB7A8BC31178F580ACBB064AEC077 | select /*+read_consi | 9715 |
| sys | 1C76556BC0AC0497E55B57AAE65A1AA6 | select /*+read_consi | 8930 |
| sys | A434D08FC628433CD3BE1230D0453F84 | select /*+read_consi | 8575 |
| sys | 27035962BF2891B45EF08DD6CCC4D07B | select /*+read_consi | 8425 |
| sys | B7E2B2EB900D06AB1166B0E70925B286 | select /*+read_consi | 7385 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 161 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 156 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 151 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 148 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 147 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 146 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 144 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 141 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 140 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 139 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 139 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 138 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 138 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 137 |
| sys | 2983E372002117946E00660266EB3683 | SELECT * FROM `dag_i | 136 |
+-------------+----------------------------------+----------------------+--------------+
Trace ID: 8907f7a2-acb9-11ef-bd8d-00163e01c7ce