How ClickHouse Query Profiler works ?

How does ClickHouse Query Profiler work?

The core objective of ClickHouse Query Profiler is to measure the performance metrics of SQLs and understand where the application is spending most of the time, This helps to successfully complete ClickHouse performance audit/diagnostics/forensics by tracing CPU time against wall-clock time including idle time. At ChistaDATA we spend a considerable amount of time troubleshooting (of course lots of research included) ClickHouse performance and we are committed to building optimal & reliable ClickHouse infrastructure for customers worldwide.

Configuring ClickHouse Query Profiler

ClickHouse trace_log system table records the profiler operations, This setting is configured by default. But, The data on this table is valid only for a running server so after the ClickHouse server restarts, ClickHouse does not clean up this table and so all the persistent virtual memory addresses will become invalid. We have copied below the default server configuration file (config.xml) for your reference:

ID.Parameter NameDescription
1databaseDatabase for storing a table.
2table Table name.
3partition_by Custom partitioning key for a system table. Can't be used if engine defined.
4engine MergeTree Engine Definition for a system table. Can't be used if partition_by defined
5flush_interval_millisecondsInterval for flushing data from the buffer in memory to the table.

How to configure query_log and other _log tables for set up TTL, and/or some other cleanup procedures:

cat /etc/clickhouse-server/config.d/query_log.xml
    <query_log replace="1">
ENGINE = MergeTree
PARTITION BY event_date
ORDER BY (event_time)
TTL event_date + interval 90 day
SETTINGS ttl_only_drop_parts=1

The configuration parameters query_profiler_real_time_period_ns and query_profiler_cpu_time_period_ns dump stacktraces all the threads which execute the query. By default, it collects information only about queries when runs longer than 1 sec (and collects stacktraces every second).

To record how RAM is used by ClickHouse queries you can configure the setting memory_profiler_sample_probability

To read more about ClickHouse’s Query Profiler, do consider reading the following articles:

About ChistaDATA Inc. 11 Articles
We are an full-stack ClickHouse infrastructure operations Consulting, Support and Managed Services provider with core expertise in performance, scalability and data SRE. Based out of California, Our consulting and support engineering team operates out of San Francisco, Vancouver, London, Germany, Russia, Ukraine, Australia, Singapore and India to deliver 24*7 enterprise-class consultative support and managed services. We operate very closely with some of the largest and planet-scale internet properties like PayPal, Garmin, Honda cars IoT project, Viacom, National Geographic, Nike, Morgan Stanley, American Express Travel, VISA, Netflix, PRADA, Blue Dart, Carlsberg, Sony, Unilever etc