system.trace_log

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the clusterAllReplicas function. See here for further details.

Description

Contains stack traces collected by the sampling query profiler.

ClickHouse creates this table when the trace_log server configuration section is set. Also see settings: query_profiler_real_time_period_ns, query_profiler_cpu_time_period_ns, memory_profiler_step, memory_profiler_sample_probability, trace_profile_events.

When symbolization is enabled (the default), the demangled function names and source locations are already available in the symbols and lines columns, so you can analyze the logs directly without introspection functions. The symbolize setting applies to profiler-collected trace types; rows with the Instrumentation trace type are symbolized regardless of it. Symbolization is supported on ELF platforms (such as Linux) and macOS; on FreeBSD the symbols and lines columns are always empty. Function names in symbols come from the binary's symbol table and are available by default, while source locations in lines are best-effort: they require debug info (a .dSYM bundle on macOS) and, on ELF platforms, are resolved only for frames inside the main ClickHouse binary; unresolved frames have empty lines entries. If symbolization is disabled, or you want to resolve the raw addresses in the trace column on the fly (for example, to expand inline frames), use the addressToLine, addressToLineWithInlines, addressToSymbol and demangle introspection functions. These functions are available on the same platforms as symbolization (ELF platforms such as Linux, and macOS); on FreeBSD they are not compiled in either, so the addresses in trace have to be resolved outside the server.

Columns

• hostname (LowCardinality(String)) — Hostname of the server executing the query.
• event_date (Date) — Date of sampling moment.
• event_time (DateTime) — Timestamp of the sampling moment.
• event_time_microseconds (DateTime64(6)) — Timestamp of the sampling moment with microseconds precision.
• timestamp_ns (UInt64) — Timestamp of the sampling moment in nanoseconds.
• revision (UInt32) — ClickHouse server build revision. When connecting to the server by clickhouse-client, you see a string similar to Connected to ClickHouse server version 19.18.1.. This field contains the revision, but not the version of a server.
• trace_type (Enum8('Real' = 0, 'CPU' = 1, 'Memory' = 2, 'MemorySample' = 3, 'MemoryPeak' = 4, 'ProfileEvent' = 5, 'JemallocSample' = 6, 'MemoryAllocatedWithoutCheck' = 7, 'Instrumentation' = 8)) — Trace type: Real represents collecting stack traces by wall-clock time. CPU represents collecting stack traces by CPU time. Memory represents collecting allocations and deallocations when memory allocation exceeds the subsequent watermark. MemorySample represents collecting random allocations and deallocations. MemoryPeak represents collecting updates of peak memory usage. ProfileEvent represents collecting of increments of profile events. JemallocSample represents collecting of jemalloc samples. MemoryAllocatedWithoutCheck represents collection of significant allocations (>16MiB) that is done with ignoring any memory limits (for ClickHouse developers only).Instrumentation represents traces collected by the instrumentation performed through XRay.
• cpu_id (UInt64) — CPU identifier.
• thread_id (UInt64) — Thread identifier.
• thread_name (LowCardinality(String)) — Thread name.
• query_id (String) — Query identifier that can be used to get details about a query that was running from the query_log system table.
• trace (Array(UInt64)) — Stack trace at the moment of sampling. For profiler-collected trace types, on ELF platforms except FreeBSD, addresses inside the main ClickHouse binary are stored as physical file offsets, and other addresses are virtual memory addresses inside the ClickHouse server process. Instrumentation trace rows are an exception: they store raw virtual memory addresses.
• size (Int64) — For trace types Memory, MemorySample, MemoryAllocatedWithoutCheck or MemoryPeak is the amount of memory allocated, for other trace types is 0.
• ptr (UInt64) — The address of the allocated chunk.
• memory_context (Enum8('Unknown' = -1, 'Global' = 0, 'User' = 1, 'Process' = 2, 'Thread' = 3, 'Max' = 4)) — Memory Tracker context (only for Memory/MemoryPeak): Unknown context is not defined for this trace_type. Global represents server context. User represents user/merge context. Process represents process (i.e. query) context. Thread represents thread (thread of particular process) context. Max this is a special value means that memory tracker is not blocked (for blocked_context column).
• memory_blocked_context (Enum8('Unknown' = -1, 'Global' = 0, 'User' = 1, 'Process' = 2, 'Thread' = 3, 'Max' = 4)) — Context for which memory tracker is blocked (for ClickHouse developers only): Unknown context is not defined for this trace_type. Global represents server context. User represents user/merge context. Process represents process (i.e. query) context. Thread represents thread (thread of particular process) context. Max this is a special value means that memory tracker is not blocked (for blocked_context column).
• event (LowCardinality(String)) — For trace type ProfileEvent is the name of updated profile event, for other trace types is an empty string.
• increment (Int64) — For trace type ProfileEvent is the amount of increment of profile event, for other trace types is 0.
• symbols (Array(LowCardinality(String))) — If the symbolization is enabled, contains demangled symbol names, corresponding to the trace. Symbolization can be enabled or disabled in the symbolize setting under trace_log in the server configuration file; the setting applies to profiler-collected trace types, while rows with the Instrumentation trace type are symbolized regardless of it. Symbolization is supported on ELF platforms (such as Linux) and macOS; on FreeBSD this column is always empty.
• lines (Array(LowCardinality(String))) — If the symbolization is enabled, contains strings with file names with line numbers, corresponding to the trace. The symbolize setting applies to profiler-collected trace types, while rows with the Instrumentation trace type are symbolized regardless of it. Symbolization is supported on ELF platforms (such as Linux) and macOS; on FreeBSD this column is always empty. Source locations are best-effort: they require debug info (a .dSYM bundle on macOS) and, on ELF platforms, are resolved only for frames inside the main ClickHouse binary; unresolved frames have empty entries.
• function_id (Nullable(Int32)) — For trace type Instrumentation, ID assigned to the function in xray_instr_map section of elf-binary.
• function_name (Nullable(String)) — For trace type Instrumentation, name of the instrumented function.
• handler (Nullable(String)) — For trace type Instrumentation, handler of the instrumented function.
• entry_type (Nullable(Enum8('Entry' = 0, 'Exit' = 1))) — For trace type Instrumentation, entry type of the instrumented function.
• duration_nanoseconds (Nullable(UInt64)) — For trace type Instrumentation, time the function was running for in nanoseconds.

Aliases:

• build_id — Alias for 'E2EEB60ED9503BFFA825B628D480CCDC40B2D416'.

Symbolization can be enabled or disabled with the symbolize setting under trace_log in the server's configuration file. It is enabled by default. The setting applies to profiler-collected trace types; rows with the Instrumentation trace type are symbolized regardless of it.

Example

SELECT * FROM system.trace_log LIMIT 1 \G

Row 1:
──────
hostname:                clickhouse.eu-central1.internal
event_date:              2025-11-11
event_time:              2025-11-11 11:53:59
event_time_microseconds: 2025-11-11 11:53:59.128333
timestamp_ns:            1762862039128333000
revision:                54504
trace_type:              Instrumentation
cpu_id:                  19
thread_id:               3166432 -- 3.17 million
query_id:                ef462508-e189-4ea2-b231-4489506728e8
trace:                   [350594916,447733712,447742095,447727324,447726659,221642873,450882315,451852359,451905441,451885554,512404306,512509092,612861767,612863269,612466367,612455825,137631896259267,137631896856768]
size:                    0
ptr:                     0
memory_context:          Unknown
memory_blocked_context:  Unknown
event:
increment:               0
symbols:                 ['StackTrace::StackTrace()','DB::InstrumentationManager::createTraceLogElement(DB::InstrumentationManager::InstrumentedPointInfo const&, XRayEntryType, std::__1::chrono::time_point<std::__1::chrono::system_clock, std::__1::chrono::duration<long long, std::__1::ratio<1l, 1000000l>>>) const','DB::InstrumentationManager::profile(XRayEntryType, DB::InstrumentationManager::InstrumentedPointInfo const&)','DB::InstrumentationManager::dispatchHandlerImpl(int, XRayEntryType)','DB::InstrumentationManager::dispatchHandler(int, XRayEntryType)','__xray_FunctionEntry','DB::QueryMetricLog::startQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, std::__1::chrono::time_point<std::__1::chrono::system_clock, std::__1::chrono::duration<long long, std::__1::ratio<1l, 1000000l>>>, unsigned long)','DB::logQueryStart(std::__1::chrono::time_point<std::__1::chrono::system_clock, std::__1::chrono::duration<long long, std::__1::ratio<1l, 1000000l>>> const&, std::__1::shared_ptr<DB::Context> const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, unsigned long, std::__1::shared_ptr<DB::IAST> const&, DB::QueryPipeline const&, DB::IInterpreter const*, bool, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, bool)','DB::executeQueryImpl(char const*, char const*, std::__1::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, std::__1::unique_ptr<DB::ReadBuffer, std::__1::default_delete<DB::ReadBuffer>>&, std::__1::shared_ptr<DB::IAST>&, std::__1::shared_ptr<DB::ImplicitTransactionControlExecutor>, std::__1::function<void ()>)','DB::executeQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, std::__1::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum)','DB::TCPHandler::runImpl()','DB::TCPHandler::run()','Poco::Net::TCPServerConnection::start()','Poco::Net::TCPServerDispatcher::run()','Poco::PooledThread::run()','Poco::ThreadImpl::runnableEntry(void*)','start_thread','__clone3']
lines:                   ['./build/../src/Common/StackTrace.cpp:395','./src/Common/StackTrace.h:62','./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:738','./build/./src/Interpreters/InstrumentationManager.cpp:257','./build/./src/Interpreters/InstrumentationManager.cpp:225','','./build/./src/Interpreters/QueryMetricLog.cpp:0','./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:667','./build/./src/Interpreters/executeQuery.cpp:0','./build/./src/Interpreters/executeQuery.cpp:0','./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:744','./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:583','./build/../base/poco/Net/src/TCPServerConnection.cpp:54','../contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:80','./build/../base/poco/Foundation/src/ThreadPool.cpp:219','../base/poco/Foundation/include/Poco/AutoPtr.h:77','','']
function_id:             231255
function_name:           DB::QueryMetricLog::startQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, std::__1::chrono::time_point<std::__1::chrono::system_clock, std::__1::chrono::duration<long long, std::__1::ratio<1l, 1000000l>>>, unsigned long)
handler:                 profile
entry_type:              Exit
duration_nanoseconds:   58435

Converting to Chrome Event Trace Format

The profiling data can be converted to Chrome's Event Trace Format with the following query. Save the query to a chrome_trace.sql file:

WITH traces AS (
    SELECT * FROM system.trace_log
    WHERE event_date >= today() AND trace_type = 'Instrumentation' AND handler = 'profile'
    ORDER BY event_time, entry_type
)
SELECT
    format(
        '{{"traceEvents": [{}\n]}}',
        arrayStringConcat(
            groupArray(
                format(
                    '\n{{"name": "{}", "cat": "clickhouse", "ph": "{}", "ts": {}, "pid": 1, "tid": {}, "args": {{"query_id": "{}", "cpu_id": {}, "stack": [{}]}}}},',
                    function_name,
                    if(entry_type = 0, 'B', 'E'),
                    timestamp_ns/1000,
                    toString(thread_id),
                    query_id,
                    cpu_id,
                    arrayStringConcat(arrayMap((x, y) -> concat('"', x, ': ', y, '", '), lines, symbols))
                )
            )
        )
    )
FROM traces;

And executing it with ClickHouse Client to export it to a trace.json file that we can import either with Perfetto or speedscope.

echo $(clickhouse client --query "$(cat chrome_trace.sql)") > trace.json

We can omit the stack part if we want a more compact but less informative trace.

See Also

• SYSTEM INSTRUMENT — Add or remove instrumentation points.
• system.instrumentation — Inspect instrumented points.
• system.symbols — Inspect symbols to add instrumentation points.