ApsaraDB for ClickHouseのクエリログの表示 - ApsaraDB for ClickHouse

クエリログを分析して、クエリの実行ステータスを表示し、低速クエリを識別できます。これにより、システムパフォーマンスの向上と潜在的な問題の解決に役立ちます。このトピックでは、SQL文を使用してApsaraDB for ClickHouseのクエリログを表示する方法について説明します。

前提条件

クエリログ機能が有効になっています。

説明

デフォルトでは、ApsaraDB for ClickHouseではクエリログが有効になっています。
SHOW settings like 'log_queries'; ステートメントを実行して、log_queriesパラメーターの値を照会し、クエリログが有効になっているかどうかを確認できます。値が1の場合、クエリログが有効になります。値が0の場合、クエリログは無効になります。次のSQL文を実行して、クエリログを有効にします。
```
SET GLOBAL ON CLUSTERデフォルトlog_queries = 1;
```

使用上の注意

クエリログには機密情報が含まれている場合があります。クエリログを適切に管理する必要があります。
過剰なログファイルを避けるために、クエリログを定期的にクリーンアップしてアーカイブする必要があります。
説明
- デフォルトでは、ApsaraDB for ClickHouseのquery_logテーブルは、15日間の有効期間 (TTL) で設定されています。これは、ログが生成されてから15日後にクエリログが自動的に削除されることを示します。
- query_logテーブルが占有するディスク容量を削減する場合は、ApsaraDB for ClickHouseコンソールの [パラメーター設定] ページでquery_logテーブルのTTL期間を変更できます。クラスターの問題の特定を容易にするために、TTL値を少なくとも7日間に設定することを推奨します。パラメーターを変更する方法の詳細については、「config.xmlファイルのパラメーターの設定」をご参照ください。

サンプル環境

このトピックでは、s-2-r-0ノードを基本環境として使用します。実際の使用では、ビジネス要件に基づいてパラメーターを変更します。ノード名がわからない場合は、次のいずれかの方法でノード名を取得できます。

ノード名は、ApsaraDB for ClickHouseコンソールの [クラスターモニタリング] タブで取得できます。 [クラスターモニタリング] タブページに移動する方法の詳細については、「クラスターモニタリング情報の表示」をご参照ください。
次のSQL文を実行して、クラスター内のすべてのノードの名前を取得できます。
```
SELECT * FROM system.clusters;
```

エラーが報告された最近のクエリを表示する

エラーログを表示すると、次の利点が得られ、システムの安定性とセキュリティが向上します。

クイック問題の識別: エラーメッセージは、問題の原因を直接特定し、問題をタイムリーにトラブルシューティングするのに役立ちます。
トレンド分析: エラーログを分析することで、エラーの時間とパターンを知ることができます。これにより、プロアクティブなコード最適化と構成変更を実装できます。
セキュリティ問題の防止: エラーログを分析することで、SQLインジェクションや不正アクセスなどの潜在的なセキュリティ脆弱性を特定できます。

クエリテンプレート

SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time,
    例外
clusterAllReplicas('default',system.query_log) qlから
WHERE (((event_time >= '<startTime>')) 
       AND 
       (event_time <= '<endTime>')) AND (lowerUTF8 (クエリ) LIKE '% insert into sdk_event_record_local %') 
       AND (type != 'QueryStart')
       AND (exception_code != 0)
       [AND部分文字列 (ホスト名 () 、38,8) = '<nodeName>']
event_time DESCによる注文
[LIMIT <x>]

Parameters

パラメーター	説明	例:
startTime	照会する期間の開始時刻です。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2021-11-22 22:00:00
endTime	照会する期間の終了時刻を設定します。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2021-11-22 23:00:00
nodeName	クラスターノードの名前。	s-2-r-0
x	クエリ結果の開始から返される行数。	30

例

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,
    例外
clusterAllReplicas('default',system.query_log) qlから
WHERE (lowerUTF8 (クエリ) 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部分文字列 (ホスト名 () 、38,8) = 's-2-r-0'
event_time DESCによる注文
リミット30

最近のSQLクエリの表示

書き込み型SQLクエリ

クエリテンプレート

-- 各バッチの行数とバイトサイズを含む、最近の書き込み型SQLクエリを表示します。
SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time
clusterAllReplicas('default',system.query_log) qlから
WHERE (((event_time >= '<startTime>')) 
       AND (event_time <= '<endTime>')
       AND (lowerUTF8 (クエリ) ILIKE '% insert into %') AND (type != 'QueryStart')
       [AND部分文字列 (ホスト名 () 、38,8) = '<nodeName>']
event_time DESCによる注文
[LIMIT x]

Parameters

パラメーター	説明	例:
startTime	照会する期間の開始時刻です。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2021-11-22 22:00:00
endTime	照会する期間の終了時刻を設定します。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2021-11-22 23:00:00
nodeName	クラスターノードの名前。	s-2-r-0
x	クエリ結果の開始から返される行数。	30

例

2021年11月22日の22:00:00から2021年11月22日の23:00:00までの期間にs-2-r-0ノードで実行された書き込み型SQLクエリのログを表示し、クエリ結果の最初の30行が返されるように指定します。

-- 各バッチの行数とバイトサイズを含む、最近の書き込み型SQLクエリを表示します。 
SELECT
    written_rows,
    written_bytes,
    query_duration_ms,
    event_time
clusterAllReplicas('default',system.query_log) qlから
どこ (((event_time >= '2021-11-22 22:00:00 ')) 
       AND (event_time <= '2021-11-22 23:00:00 '))
       AND (lowerUTF8 (クエリ) LIKE '% insert into sdk_event_record_local %') 
       AND (type != 'QueryStart')
       AND部分文字列 (ホスト名 () 、38,8) = 's-2-r-0'
event_time DESCによる注文
リミット30

非書き込みSQLクエリ

クエリテンプレート

SELECT   
    event_time,   
    ユーザー、   
    クエリとしてquery_id,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    例外
clusterAllReplicasから ('default', system.query_log)
WHERE (event_date = today()) AND (event_time >= (now() - <time>)) AND (is_initial_query = 1) AND (クエリNOT ILIKE 'INSERT INTO % ' [AND部分文字列 (ホスト名 () 、38,8) = '<nodeName>'])
event_time DESCによる注文
[LIMIT x]

Parameters

パラメーター	説明	例：
time	データ照会のために現在の時刻から振り返る時間範囲。単位は分です。	60
nodeName	クラスターノードの名前。	s-2-r-0
x	クエリ結果の開始から返される行数。	100

例

過去60分以内にs-2-r-0ノードで実行された非書き込みSQLクエリを表示し、クエリ結果の最初の100行が返されるように指定します。

SELECT   
    event_time,   
    ユーザー、   
    クエリとしてquery_id,   
    read_rows,   
    read_bytes,   
    result_rows,   
    result_bytes,   
    memory_usage,   
    例外
clusterAllReplicasから ('default', system, query_log)
WHERE (event_date = today()) AND (event_time >= (now() - 60)) AND (is_initial_query = 1) AND (クエリNOT LIKE 'INSERT INTO % ')
AND部分文字列 (ホスト名 () 、38,8) = 's-2-r-0'
event_time DESCによる注文
LIMIT 100

特定の期間内にN回以上実行される非書き込みステートメントの表示

クエリテンプレート

SELECT *
から
  (SELECT LEFT (クエリ、100) AS SQL、
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   clusterAllReplicas('default', system.query_log) qlから
   WHERE event_time > toDateTime('<startTime>')
     AND event_time < toDateTime('<endTime>')
     '% INSERT INTO %' のようではないクエリ
     AND部分文字列 (hostname(), 38,8) = '<nodeName>'
   SQLによるグループ化
   avgTime DESCによる注文)
WHERE queryNum > <queryNum>
[LIMIT <x>]

パラメーター

パラメーター	説明	例：
startTime	照会する期間の開始時刻です。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2022-09-23 12:00:00
endTime	照会する期間の終了時刻を設定します。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2022-09-23 17:00:00
queryNum	ステートメントの実行数の最小しきい値。	1000
nodeName	クラスターノードの名前。	s-2-r-0
x	クエリ結果の開始から返される行数。	50

例

2022年9月23日の12:00:00から2022年9月23日の17:00:00までの期間にs-2-r-0ノードで1,000回以上実行された非書き込みSQL文を表示します。

SELECT *
から
  (SELECT LEFT (クエリ、100) AS SQL、
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   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 ')
     '% INSERT INTO %' のようではないクエリ
     AND部分文字列 (hostname(), 38,8) = 's-2-r-0'
   SQLによるグループ化
   avgTime DESCによる注文)
WHERE queryNum > 1000
リミット50

特定の期間内の時間または分ごとのクエリの実行統計を表示する

実行統計を時間単位で表示する

特定の期間内の時間ごとのクエリの数と平均期間に関する統計を表示します。

クエリテンプレート

-- 実行統計を時間単位で表示する
SELECT toHour(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
clusterAllReplicas('default', system.query_log) qlから
WHERE event_time > toDateTime('<startTime>')
  AND event_time < toDateTime('<endTime>')
  '% INSERT INTO %' のようではないクエリ
  そして '% Faulty container %' のようなクエリ
  そしてread_rows! =0
  AND部分文字列 (hostname(), 38,8) = '<nodeName>'
グループBY t
[LIMIT x]

Parameters

パラメーター	説明	例：
startTime	クエリする時間範囲の開始。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2022-09-23 12:00:00
endTime	照会する期間の終了時刻を設定します。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2022-09-23 17:00:00
nodeName	クラスターノードの名前。	s-2-r-0
x	クエリ結果の開始から返される行数。	50

例

2022年9月23日の08:00:00から2022年9月23日の17:00:00までの期間に、s-2-r-0ノードで実行されたクエリの数と平均時間に関する統計を時間単位で表示します。

-- 実行統計を時間単位で表示する
SELECT toHour(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
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 ')
  '% INSERT INTO %' のようではないクエリ
  そして '% Faulty container %' のようなクエリ
  そしてread_rows! =0
  AND部分文字列 (hostname(), 38,8) = 's-2-r-0'
グループBY t
リミット50

実行統計を分単位で表示する

特定の期間内のクエリの数と平均時間に関する統計を分単位で表示します。

クエリテンプレート

-- 実行統計を分単位で表示する
SELECT to Minute(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
clusterAllReplicas('default', system.query_log) qlから
WHERE event_time > toDateTime('<startTime>')
  AND event_time < toDateTime('<endTime>')
  '% INSERT INTO %' のようではないクエリ
  そして '% Faulty container %' のようなクエリ
  AND部分文字列 (hostname(), 38,8) = '<nodeName>'
  そしてread_rows! =0
グループBY t
[LIMIT x]

Parameters

パラメーター	説明	例:
startTime	照会する期間の開始時刻です。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2022-09-23 12:00:00
endTime	照会する期間の終了時刻を設定します。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2022-09-23 17:00:00
nodeName	クラスターノードの名前。	s-2-r-0
x	クエリ結果の開始から返される行数。	50

例

2022年9月23日08:00:00から2022年9月23日17:00:00までの期間に、s-2-r-0ノードで実行されたクエリの数と平均期間の統計を分単位で表示します。

-- 実行統計を分単位で表示する
SELECT to Minute(event_time) AS t,
       count() AS queryNum,
       sum(query_duration_ms) AS totalTime,
       totalTime/queryNum AS avgTime
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 ')
  '% INSERT INTO %' のようではないクエリ
  そして '% Faulty container %' のようなクエリ
  AND部分文字列 (hostname(), 38,8) = 's-2-r-0'
  そしてread_rows! =0
グループBY t
リミット50

LEFT JOINクエリの数を表示する

特定の期間内のLEFT JOINクエリの数を表示します。

クエリテンプレート

SELECT *
から
  (SELECT LEFT (クエリ、100) AS SQL、
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   clusterAllReplicas('default', system.query_log) qlから
   '% JOIN %' のようなクエリの場所
     そしてread_rows! =0
     AND event_time > toDateTime('<startTime>')
     AND event_time < toDateTime('<endTime>')
     '% INSERT INTO %' のようではないクエリ
     AND部分文字列 (hostname(), 38,8) = '<nodeName>'
   SQLによるグループ化
   注文BY queryNum DESC)

Parameters

パラメーター	説明	例:
startTime	照会する期間の開始時刻です。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2022-09-23 12:00:00
endTime	照会する期間の終了時刻を設定します。時刻はyyyy-mm-dd hh:mm:ss形式で指定します。	2022-09-23 21:00:00
nodeName	クラスターノードの名前。	s-2-r-0

例

2024年6月25日の12:00:00から2024年6月25日の15:00:00までの期間にs-2-r-0ノードで実行されたLEFT JOINクエリの数を表示します。

SELECT *
から
  (SELECT LEFT (クエリ、100) AS SQL、
          count() AS queryNum,
          sum(query_duration_ms) AS totalTime,
          totalTime/queryNum AS avgTime
   clusterAllReplicas('default', system.query_log) qlから
   '% JOIN %' のようなクエリの場所
     そしてread_rows! =0
     AND event_time > toDateTime('2024-06-25 12:00:00 ')
     AND event_time < toDateTime('2024-06-25 15:00:00 ')
     '% INSERT INTO %' のようではないクエリ
     AND部分文字列 (hostname(), 38,8) = 's-2-r-0'
   SQLによるグループ化
   注文BY queryNum DESC)

ユーザーが実行した非書き込みSQLクエリの数に基づくユーザーのランキングの表示

クエリテンプレート

SELECT   
    ユーザー、   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows
clusterAllReplicasから ('default', system, query_log)
WHERE (event_date=昨日 ()) AND (is_initial_query = 1) AND (クエリNOT LIKE 'INSERT INTO % 'AND部分文字列 (ホスト名 () 、38,8) = '<nodeName>')
ユーザーによるグループ
query_times DESCによるORDER
[LIMIT x]

Parameters

パラメーター	説明	例:
nodeName	クラスターノードの名前。	s-2-r-0
x	返すユーザーの数。	10

例

s-2-r-0ノードで書き込み以外のクエリを実行した上位10人のユーザーを表示します。

SELECT   
    ユーザー、   
    count(1) AS query_times,   
    sum(read_bytes) AS query_bytes,   
    sum(read_rows) AS query_rows
clusterAllReplicasから ('default', system, query_log)
WHERE (event_date=昨日 ()) AND (is_initial_query = 1) AND (クエリNOT LIKE 'INSERT INTO % 'AND部分文字列 (ホスト名 () 、38,8) = 's-2-r-0')
ユーザーによるグループ
query_times DESCによるORDER
リミット10