ApsaraDB for ClickHouse：查詢日誌分析

前提條件

確保查詢日誌已開啟。

注意事項

• 查詢日誌可能會包含敏感資訊，請妥善管理查詢日誌資訊。

• 定期清理歸檔查詢日誌，避免記錄檔太大。

  說明

  • ClickHouse中為query_log表預設配置了15天的TTL策略，查詢日誌產生後會在15天后自動刪除。

  • 如果您希望減少查詢日誌相關的系統資料表佔用的磁碟空間，可以通過控制台參數管理功能修改query_log表的TTL策略。為了方便定位執行個體問題，建議您query_log表的TTL策略至少設定為7天。如何修改參數，請參見配置config.xml參數。

樣本環境

以下樣本以s-2-r-0節點為基礎環境，在實際使用過程中，請根據您的情境修改對應參數。如果您不知道如何擷取節點名稱，可以通過以下方式擷取。

• 通過控制台：您可以在叢集監控頁面，擷取節點名稱。如何進入叢集監控頁面，請參見查看叢集監控資訊。

• 通過SQL語句：您可以執行以下語句，擷取叢集所有節點的名稱。

  SELECT * FROM system.clusters;

查看最近報錯的Query

查看報錯日誌對於提升系統的穩定性和安全性具有重要意義。主要體現在以下幾個方面：

• 快速定位問題：通過報錯資訊可以直接定位到導致問題的具體原因，從而快速修複問題。

• 趨勢分析：通過分析報錯日誌，識別錯誤發生的時間與模式，可以進行防禦性的代碼最佳化或者配置調整。

• 安全：通過分析錯誤記錄檔，發現系統存在的潛在安全問題，比如SQL注入、非法訪問等。

查詢範本

SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time,
    exception
FROM clusterAllReplicas('default',system.query_log) ql
WHERE ((event_time >= '<startTime>') 
       AND 
       (event_time <= '<endTime>')) AND (lowerUTF8(query) LIKE '%insert into sdk_event_record_local%') 
       AND (type != 'QueryStart')
       AND (exception_code != 0)
       [AND substring(hostname(),38,8) = '<nodeName>']
ORDER BY event_time DESC
[LIMIT <x>]

參數說明

樣本

查詢2021-11-22 22:00:00 至 2021-11-22 23:00:00時間段內，s-2-r-0節點上的執行查詢的錯誤記錄檔資訊，返回結果中的前30行資料。

SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time,
    exception
FROM clusterAllReplicas('default',system.query_log) ql
WHERE (lowerUTF8(query) LIKE '%insert into sdk_event_record_local%') AND 
 ((event_time >= '2021-11-22 22:00:00') 
       AND 
       (event_time <= '2021-11-22 23:00:00')) 
       AND (type != 'QueryStart')
       AND (exception_code != 0)
AND substring(hostname(),38,8) = 's-2-r-0'
ORDER BY event_time DESC
LIMIT 30

查看最近SQL

--查看最新寫入的SQL，每個batch的行數和bytes大小：
SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time
FROM clusterAllReplicas('default',system.query_log) ql
WHERE ((event_time >= '<startTime>') 
       AND (event_time <= '<endTime>'))
       AND (lowerUTF8(query) ILIKE '%insert into%') AND (type != 'QueryStart')
       [AND substring(hostname(),38,8) = '<nodeName>']
ORDER BY event_time DESC
[LIMIT x]

--查看最新寫入的SQL，每個batch的行數和bytes大小： 
SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time
FROM clusterAllReplicas('default',system.query_log) ql
WHERE ((event_time >= '2021-11-22 22:00:00') 
       AND (event_time <= '2021-11-22 23:00:00'))
       AND (lowerUTF8(query) LIKE '%insert into sdk_event_record_local%') 
       AND (type != 'QueryStart')
       AND substring(hostname(),38,8) = 's-2-r-0'
ORDER BY event_time DESC
LIMIT 30

SELECT   
    event_time,   
    user,   
    query_id AS query,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    exception  
FROM clusterAllReplicas('default', system.query_log)  
WHERE (event_date = today()) AND (event_time >= (now() - <time>)) AND (is_initial_query = 1) AND (query NOT ILIKE 'INSERT INTO%'  [AND substring(hostname(),38,8) = '<nodeName>'])  
ORDER BY event_time DESC  
[LIMIT x]

SELECT   
    event_time,   
    user,   
    query_id AS query,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    exception  
FROM clusterAllReplicas('default', system, query_log)  
WHERE (event_date = today()) AND (event_time >= (now() - 60)) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%')  
AND substring(hostname(),38,8) = 's-2-r-0'
ORDER BY event_time DESC  
LIMIT 100

查看時間段內執行次數超過n次的非寫入語句

查詢範本

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('default', system.query_log) ql
   WHERE event_time > toDateTime('<startTime>')
     AND event_time < toDateTime('<endTime>')
     AND query not like '%INSERT INTO%'
     AND substring(hostname(), 38, 8) = '<nodeName>'
   GROUP BY SQL
   ORDER BY avgTime DESC)
WHERE queryNum > <queryNum> 
[LIMIT <x>]

參數說明

樣本

查詢2022-09-23 12:00:00 至 2022-09-23 17:00:00時間段內，s-2-r-0節點上查詢次數超過1000次的非寫入的SQL語句。

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('default', system.query_log) ql
   WHERE event_time > toDateTime('2022-09-23 12:00:00')
     AND event_time < toDateTime('2022-09-23 17:00:00')
     AND query not like '%INSERT INTO%'
     AND substring(hostname(), 38, 8) = 's-2-r-0'
   GROUP BY SQL
   ORDER BY avgTime DESC)
WHERE queryNum > 1000
LIMIT 50

查詢一段時間內每小時或每分鐘執行查詢的統計

--按照每小時彙總
SELECT toHour(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('default', system.query_log) ql
WHERE event_time > toDateTime('<startTime>')
  AND event_time < toDateTime('<endTime>')
  AND query not like '%INSERT INTO%'
  AND query like '%異常容器%'
  AND read_rows != 0
  AND substring(hostname(), 38, 8) = '<nodeName>'
GROUP BY t
[LIMIT x]

--按照每小時彙總
SELECT toHour(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('default', system.query_log) ql
WHERE event_time > toDateTime('2022-09-23 08:00:00')
  AND event_time < toDateTime('2022-09-23 17:00:00')
  AND query not like '%INSERT INTO%'
  AND query like '%異常容器%'
  AND read_rows != 0
  AND substring(hostname(), 38, 8) = 's-2-r-0'
GROUP BY t
LIMIT 50

--按照每分鐘彙總
SELECT toMinute(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('default', system.query_log) ql
WHERE event_time > toDateTime('<startTime>')
  AND event_time < toDateTime('<endTime>')
  AND query not like '%INSERT INTO%'
  AND query like '%異常容器%'
  AND substring(hostname(), 38, 8) = '<nodeName>'
  AND read_rows != 0
GROUP BY t
[LIMIT x]

--按照每分鐘彙總
SELECT toMinute(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
FROM clusterAllReplicas('default', system.query_log) ql
WHERE event_time > toDateTime('2022-09-23 12:00:00')
  AND event_time < toDateTime('2022-09-23 13:00:00')
  AND query not like '%INSERT INTO%'
  AND query like '%異常容器%'
  AND substring(hostname(), 38, 8) = 's-2-r-0'
  AND read_rows != 0
GROUP BY t
LIMIT 50

查看LEFT JOIN的查詢個數

查詢一個時間段內LEFT JOIN的查詢個數。

查詢範本

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('default', system.query_log) ql
   WHERE query like '%JOIN%'
     AND read_rows != 0
     AND event_time > toDateTime('<startTime>')
     AND event_time < toDateTime('<endTime>')
     AND query not like '%INSERT INTO%'
     AND substring(hostname(), 38, 8) = '<nodeName>'
   GROUP BY SQL
   ORDER BY queryNum DESC)

參數說明

查詢樣本

查詢2024-06-25 12:00:00 至 2024-06-25 15:00:00時間段內，s-2-r-0節點上執行LEFT JOIN語句的個數。

SELECT *
FROM
  (SELECT LEFT(query, 100) AS SQL,
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   FROM clusterAllReplicas('default', system.query_log) ql
   WHERE query like '%JOIN%'
     AND read_rows != 0
     AND event_time > toDateTime('2024-06-25 12:00:00')
     AND event_time < toDateTime('2024-06-25 15:00:00')
     AND query not like '%INSERT INTO%'
     AND substring(hostname(), 38, 8) = 's-2-r-0'
   GROUP BY SQL
   ORDER BY queryNum DESC)

查詢使用者執行非寫入SQL數量排行

查詢範本

SELECT   
    user,   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows  
FROM clusterAllReplicas('default', system, query_log)  
WHERE (event_date = yesterday()) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%' AND substring(hostname(),38,8) = '<nodeName>')  
GROUP BY user  
ORDER BY query_times DESC  
[LIMIT x]

參數說明

樣本

查詢在s-2-r-0節點上執行非寫入查詢語句的前十名使用者。

SELECT   
    user,   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows  
FROM clusterAllReplicas('default', system, query_log)  
WHERE (event_date = yesterday()) AND (is_initial_query = 1) AND (query NOT LIKE 'INSERT INTO%' AND substring(hostname(),38,8) = 's-2-r-0')  
GROUP BY user  
ORDER BY query_times DESC  
LIMIT 10