SHOW-QUERY-PROFILE

Name

SHOW QUERY PROFILE

Description

该语句是用来查看QUERY操作的树状Profile信息，该功能需要用户打开 Profile 设置，0.15 之前版本执行下面的设置：

  1. SET is_report_success=true;

0.15 及之后的版本执行下面的设置：

  1. SET [GLOBAL] enable_profile=true;

语法：

  1. show query profile "/";

这个命令会列出当前保存的所有query操作的 Profile 。

  1. show query profile "/queryId"\G;
  2. show query profile "/queryId/fragment_id/instance_id";

获取指定query id树状profile信息,返回profile 简易树形图。指定fragment_id和instance_id则返回对应的详细profile树形图。

Example

  1. 列出所有的 query Profile

    1. mysql> show query profile "/";
    2. +-----------------------------------+------+-------------------------+--------------------+-----------+---------------------+---------------------+-----------+------------+
    3. | QueryId                           | User | DefaultDb               | SQL                | QueryType | StartTime           | EndTime             | TotalTime | QueryState |
    4. +-----------------------------------+------+-------------------------+--------------------+-----------+---------------------+---------------------+-----------+------------+
    5. | 327167e0db4749a9-adce3b3d770b2bb1 | root | default_cluster:test_db | select * from test | Query     | 2022-08-09 10:50:09 | 2022-08-09 10:50:09 | 19ms      | EOF        |
    6. +-----------------------------------+------+-------------------------+--------------------+-----------+---------------------+---------------------+-----------+------------+
    7. 1 row in set (0.00 sec)

  2. 列出指定QueryId的 query Profile

    1. mysql> show query profile "/327167e0db4749a9-adce3b3d770b2bb1"\G
    2. *************************** 1. row ***************************
    3. Fragments: ┌────────────────────────┐
    4. │[-1: VDataBufferSender] │
    5. │Fragment: 0             │
    6. │MaxActiveTime: 783.263us│
    7. └────────────────────────┘
    8.             ┌┘
    9.             │
    10.   ┌───────────────────┐
    11.   │[1: VEXCHANGE_NODE]│
    12.   │Fragment: 0        │
    13.   └───────────────────┘
    14.             └┐
    15.              │
    16. ┌────────────────────────┐
    17. │[1: VDataStreamSender]  │
    18. │Fragment: 1             │
    19. │MaxActiveTime: 847.612us│
    20. └────────────────────────┘
    21.              │
    22.              │
    23.   ┌────────────────────┐
    24.   │[0: VOLAP_SCAN_NODE]│
    25.   │Fragment: 1         │
    26.   └────────────────────┘
    27.             ┌┘
    28.             │
    29.      ┌─────────────┐
    30.      │[OlapScanner]│
    31.      │Fragment: 1  │
    32.      └─────────────┘
    33.             │
    34.             │
    35.    ┌─────────────────┐
    36.    │[SegmentIterator]│
    37.    │Fragment: 1      │
    38.    └─────────────────┘
    39. 1 row in set (0.00 sec)

  3. 列出指定 Fragment 的 Instance 概况

    1. mysql> show query profile "/327167e0db4749a9-adce3b3d770b2bb1/1/"\G
    2. *************************** 1. row ***************************
    3.  Instances: 327167e0db4749a9-adce3b3d770b2bb2
    4.       Host: 172.26.0.1:9111
    5. ActiveTime: 847.612us
    6. 1 row in set (0.01 sec)

  4. 继续查看某一个具体的 Instance 上各个算子的详细 Profile

    1. mysql> show query profile "/327167e0db4749a9-adce3b3d770b2bb1/1/327167e0db4749a9-adce3b3d770b2bb2"\G
    2. *************************** 1. row ***************************
    3. Instance: ┌───────────────────────────────────────┐
    4. │[1: VDataStreamSender]                 │
    5. │(Active: 36.944us, non-child: 0.20)    │
    6. │  - Counters:                          │
    7. │      - BytesSent: 0.00                │
    8. │      - IgnoreRows: 0                  │
    9. │      - LocalBytesSent: 20.00 B        │
    10. │      - OverallThroughput: 0.0 /sec    │
    11. │      - PeakMemoryUsage: 0.00          │
    12. │      - SerializeBatchTime: 0ns        │
    13. │      - UncompressedRowBatchSize: 0.00 │
    14. └───────────────────────────────────────┘
    15.                     │
    16.                     │
    17. ┌───────────────────────────────────────┐
    18. │[0: VOLAP_SCAN_NODE]                   │
    19. │(Active: 563.241us, non-child: 3.00)   │
    20. │  - Counters:                          │
    21. │      - BatchQueueWaitTime: 444.714us  │
    22. │      - BytesRead: 37.00 B             │
    23. │      - NumDiskAccess: 1               │
    24. │      - NumScanners: 2                 │
    25. │      - PeakMemoryUsage: 320.00 KB     │
    26. │      - RowsRead: 4                    │
    27. │      - RowsReturned: 4                │
    28. │      - RowsReturnedRate: 7.101K /sec  │
    29. │      - ScannerBatchWaitTime: 206.40us │
    30. │      - ScannerSchedCount : 2          │
    31. │      - ScannerWorkerWaitTime: 34.640us│
    32. │      - TabletCount : 2                │
    33. │      - TotalReadThroughput: 0.0 /sec  │
    34. └───────────────────────────────────────┘
    35.                     │
    36.                     │
    37.    ┌─────────────────────────────────┐
    38.    │[OlapScanner]                    │
    39.    │(Active: 0ns, non-child: 0.00)   │
    40.    │  - Counters:                    │
    41.    │      - BlockConvertTime: 0ns    │
    42.    │      - BlockFetchTime: 183.741us│
    43.    │      - ReaderInitTime: 180.741us│
    44.    │      - RowsDelFiltered: 0       │
    45.    │      - RowsPushedCondFiltered: 0│
    46.    │      - ScanCpuTime: 388.576us   │
    47.    │      - ScanTime: 0ns            │
    48.    │      - ShowHintsTime_V1: 0ns    │
    49.    └─────────────────────────────────┘
    50.                     │
    51.                     │
    52.  ┌─────────────────────────────────────┐
    53.  │[SegmentIterator]                    │
    54.  │(Active: 0ns, non-child: 0.00)       │
    55.  │  - Counters:                        │
    56.  │      - BitmapIndexFilterTimer: 124ns│
    57.  │      - BlockLoadTime: 179.202us     │
    58.  │      - BlockSeekCount: 5            │
    59.  │      - BlockSeekTime: 18.792us      │
    60.  │      - BlocksLoad: 4                │
    61.  │      - CachedPagesNum: 2            │
    62.  │      - CompressedBytesRead: 0.00    │
    63.  │      - DecompressorTimer: 0ns       │
    64.  │      - IOTimer: 0ns                 │
    65.  │      - IndexLoadTime_V1: 0ns        │
    66.  │      - NumSegmentFiltered: 0        │
    67.  │      - NumSegmentTotal: 2           │
    68.  │      - RawRowsRead: 4               │
    69.  │      - RowsBitmapIndexFiltered: 0   │
    70.  │      - RowsBloomFilterFiltered: 0   │
    71.  │      - RowsConditionsFiltered: 0    │
    72.  │      - RowsKeyRangeFiltered: 0      │
    73.  │      - RowsStatsFiltered: 0         │
    74.  │      - RowsVectorPredFiltered: 0    │
    75.  │      - TotalPagesNum: 2             │
    76.  │      - UncompressedBytesRead: 0.00  │
    77.  │      - VectorPredEvalTime: 0ns      │
    78.  └─────────────────────────────────────┘
    80. 1 row in set (0.01 sec)

Keywords

  1. SHOW, QUERY, PROFILE

Best Practice