system.query_thread_log

Contains information about threads that execute queries, for example, thread name, thread start time, duration of query processing.

To start logging:

  1. Configure parameters in the query_thread_log section.
  2. Set log_query_threads to 1.

The flushing period of data is set in flush_interval_milliseconds parameter of the query_thread_log server settings section. To force flushing, use the SYSTEM FLUSH LOGS query.

ClickHouse does not delete data from the table automatically. See Introduction for more details.

You can use the log_queries_probability setting to reduce the number of queries, registered in the query_thread_log table.

Columns:

  • event_date (Date) — The date when the thread has finished execution of the query.
  • event_time (DateTime) — The date and time when the thread has finished execution of the query.
  • event_time_microsecinds (DateTime) — The date and time when the thread has finished execution of the query with microseconds precision.
  • query_start_time (DateTime) — Start time of query execution.
  • query_start_time_microseconds (DateTime64) — Start time of query execution with microsecond precision.
  • query_duration_ms (UInt64) — Duration of query execution.
  • read_rows (UInt64) — Number of read rows.
  • read_bytes (UInt64) — Number of read bytes.
  • written_rows (UInt64) — For INSERT queries, the number of written rows. For other queries, the column value is 0.
  • written_bytes (UInt64) — For INSERT queries, the number of written bytes. For other queries, the column value is 0.
  • memory_usage (Int64) — The difference between the amount of allocated and freed memory in context of this thread.
  • peak_memory_usage (Int64) — The maximum difference between the amount of allocated and freed memory in context of this thread.
  • thread_name (String) — Name of the thread.
  • thread_number (UInt32) — Internal thread ID.
  • thread_id (Int32) — thread ID.
  • master_thread_id (UInt64) — OS initial ID of initial thread.
  • query (String) — Query string.
  • is_initial_query (UInt8) — Query type. Possible values:
    • 1 — Query was initiated by the client.
    • 0 — Query was initiated by another query for distributed query execution.
  • user (String) — Name of the user who initiated the current query.
  • query_id (String) — ID of the query.
  • address (IPv6) — IP address that was used to make the query.
  • port (UInt16) — The client port that was used to make the query.
  • initial_user (String) — Name of the user who ran the initial query (for distributed query execution).
  • initial_query_id (String) — ID of the initial query (for distributed query execution).
  • initial_address (IPv6) — IP address that the parent query was launched from.
  • initial_port (UInt16) — The client port that was used to make the parent query.
  • interface (UInt8) — Interface that the query was initiated from. Possible values:
    • 1 — TCP.
    • 2 — HTTP.
  • os_user (String) — OS’s username who runs clickhouse-client.
  • client_hostname (String) — Hostname of the client machine where the clickhouse-client or another TCP client is run.
  • client_name (String) — The clickhouse-client or another TCP client name.
  • client_revision (UInt32) — Revision of the clickhouse-client or another TCP client.
  • client_version_major (UInt32) — Major version of the clickhouse-client or another TCP client.
  • client_version_minor (UInt32) — Minor version of the clickhouse-client or another TCP client.
  • client_version_patch (UInt32) — Patch component of the clickhouse-client or another TCP client version.
  • http_method (UInt8) — HTTP method that initiated the query. Possible values:
    • 0 — The query was launched from the TCP interface.
    • 1 — GET method was used.
    • 2 — POST method was used.
  • http_user_agent (String) — The UserAgent header passed in the HTTP request.
  • quota_key (String) — The “quota key” specified in the quotas setting (see keyed).
  • revision (UInt32) — ClickHouse revision.
  • ProfileEvents (Map(String, UInt64)) — ProfileEvents that measure different metrics for this thread. The description of them could be found in the table system.events.

Example

  1.  SELECT * FROM system.query_thread_log LIMIT 1 \G
  1. Row 1:
  2. ──────
  3. event_date:                    2020-09-11
  4. event_time:                    2020-09-11 10:08:17
  5. event_time_microseconds:       2020-09-11 10:08:17.134042
  6. query_start_time:              2020-09-11 10:08:17
  7. query_start_time_microseconds: 2020-09-11 10:08:17.063150
  8. query_duration_ms:             70
  9. read_rows:                     0
  10. read_bytes:                    0
  11. written_rows:                  1
  12. written_bytes:                 12
  13. memory_usage:                  4300844
  14. peak_memory_usage:             4300844
  15. thread_name:                   TCPHandler
  16. thread_id:                     638133
  17. master_thread_id:              638133
  18. query:                         INSERT INTO test1 VALUES
  19. is_initial_query:              1
  20. user:                          default
  21. query_id:                      50a320fd-85a8-49b8-8761-98a86bcbacef
  22. address:                       ::ffff:127.0.0.1
  23. port:                          33452
  24. initial_user:                  default
  25. initial_query_id:              50a320fd-85a8-49b8-8761-98a86bcbacef
  26. initial_address:               ::ffff:127.0.0.1
  27. initial_port:                  33452
  28. interface:                     1
  29. os_user:                       bharatnc
  30. client_hostname:               tower
  31. client_name:                   ClickHouse
  32. client_revision:               54437
  33. client_version_major:          20
  34. client_version_minor:          7
  35. client_version_patch:          2
  36. http_method:                   0
  37. http_user_agent:
  38. quota_key:
  39. revision:                      54440
  40. ProfileEvents:        {'Query':1,'SelectQuery':1,'ReadCompressedBytes':36,'CompressedReadBufferBlocks':1,'CompressedReadBufferBytes':10,'IOBufferAllocs':1,'IOBufferAllocBytes':89,'ContextLock':15,'RWLockAcquiredReadLocks':1}

See Also

  • system.query_log — Description of the query_log system table which contains common information about queries execution.
  • system.query_views_log — This table contains information about each view executed during a query.