ClickHouse 罗列最耗费资源的查询

使用如下 SQL 获取 ClickHouse 中最耗资源的查询:^[1]

SELECT
    type,
    event_time,
    initial_query_id,
    query_id,
    formatReadableSize(memory_usage) AS memory,
    ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')] AS userCPU,
    ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')] AS systemCPU,
    normalizedQueryHash(query) AS normalized_query_hash
FROM clusterAllReplicas(default, system.query_log)
ORDER BY memory_usage DESC
LIMIT 10;

示例结果：

Query id: a43c2256-3c5b-4c3c-a5d5-ed2abaa96481

┌─type─────────────────────┬──────────event_time─┬─initial_query_id─────────────────────┬─query_id─────────────────────────────┬─memory───┬──userCPU─┬─systemCPU─┬─normalized_query_hash─┐
│ ExceptionWhileProcessing │ 2024-04-07 20:20:55 │ b91f74be-2e32-49ef-a98c-359686e2e155 │ 07ec5bb4-d514-41dc-85bf-60a1163ca3a4 │ 9.34 GiB │ 32598688 │  20413147 │   4943776753234722989 │
│ ExceptionWhileProcessing │ 2024-04-17 15:40:35 │ 4c4619f1-8419-4cbb-ae9c-bb6376f773d5 │ 4c4619f1-8419-4cbb-ae9c-bb6376f773d5 │ 9.31 GiB │ 41022584 │  16245392 │  11205584491604841210 │
│ ExceptionWhileProcessing │ 2024-04-17 15:40:33 │ 54c74978-ad50-4a2e-9902-2d8012526e97 │ 54c74978-ad50-4a2e-9902-2d8012526e97 │ 9.31 GiB │ 33588243 │  15246764 │   8580835109757658255 │
│ ExceptionWhileProcessing │ 2024-04-17 16:21:23 │ b6c741ac-5922-4553-8b6f-e90a1d75d826 │ b6c741ac-5922-4553-8b6f-e90a1d75d826 │ 9.28 GiB │ 29644360 │  15181790 │  12441910277135372220 │
│ ExceptionWhileProcessing │ 2024-04-17 16:21:23 │ b6c741ac-5922-4553-8b6f-e90a1d75d826 │ eb5210b4-e827-4220-9356-77182b9412c6 │ 9.24 GiB │ 34350181 │  13839329 │   4943776753234722989 │
│ ExceptionWhileProcessing │ 2024-04-07 20:20:57 │ b91f74be-2e32-49ef-a98c-359686e2e155 │ b91f74be-2e32-49ef-a98c-359686e2e155 │ 9.23 GiB │ 30488624 │  13330132 │  12441910277135372220 │
│ QueryFinish              │ 2024-04-17 16:28:28 │ f7feeb93-7e69-4c66-82be-8356b899ec5a │ 7b4e6a06-ae4f-4203-a05a-d378bd057bb3 │ 9.07 GiB │ 36238870 │   7083616 │  17476065002450252526 │
│ ExceptionWhileProcessing │ 2024-04-17 15:40:36 │ 4c4619f1-8419-4cbb-ae9c-bb6376f773d5 │ 0c43dae5-2c5f-4fbc-afb7-81f2f157b366 │ 9.04 GiB │ 39353744 │  10765228 │   8242560646225578250 │
│ ExceptionWhileProcessing │ 2024-04-17 15:40:33 │ 54c74978-ad50-4a2e-9902-2d8012526e97 │ bca0541d-e46a-45f4-98d2-f52f1a8cd71a │ 8.98 GiB │ 24659912 │   9288654 │   3816642744554455470 │
│ QueryFinish              │ 2024-04-17 16:28:28 │ f7feeb93-7e69-4c66-82be-8356b899ec5a │ f7feeb93-7e69-4c66-82be-8356b899ec5a │ 8.57 GiB │ 38775266 │   7718131 │   5669486195630697968 │
└──────────────────────────┴─────────────────────┴──────────────────────────────────────┴──────────────────────────────────────┴──────────┴──────────┴───────────┴───────────────────────┘

10 rows in set. Elapsed: 1.057 sec. Processed 1.52 million rows, 1.37 GB (1.44 million rows/s., 1.29 GB/s.)