Skip to main content

Comparing metrics between queries

· 2 min read

To compare metrics between two queries, you must first obtain the query_id for both queries.

You can then run the following query:

WITH
initial_query_id = '82142964-0b5d-4263-b996-302ce14bd779' AS second,
initial_query_id = '7ea39e31-2f89-4085-843c-7246cb3baa5c' AS first
SELECT
PE.1 AS metric,
sumIf(PE.2, first) AS v1,
sumIf(PE.2, second) AS v2,
10 * log10(v2 / v1) AS dB
FROM clusterAllReplicas(
default, system.query_log)
ARRAY JOIN ProfileEvents AS PE
WHERE (first OR second)
AND (event_date >= today() - 3) AND (type = 2)
GROUP BY metric
HAVING v1 != v2
ORDER BY
dB DESC,
v2 DESC,
metric ASC FORMAT PrettyCompactMonoBlock

You will receive a table with metrics comparing the two queries:

Query id: d7747d26-a231-47c8-ae8c-284895b1aeaf

┌─metric──────────────────────────────────────┬─────────v1─┬─────────v2─┬───────────────────────dB─┐
│ SystemTimeMicroseconds │ 13812127240819382.4143087099482767
│ SoftPageFaults │ 265188740568891.846381108610876
│ DiskReadElapsedMicroseconds │ 111394712737860.582319430863304
│ CachedReadBufferReadFromCacheMicroseconds │ 112650512854500.57322064922068
│ OSCPUVirtualTimeMicroseconds │ 70301588800453770.5637111926869545
│ RealTimeMicroseconds │ 86686457963394710.4585300419916516
│ QueryProfilerRuns │ 1571740.4464959587336597
│ NetworkSendBytes │ 8681979408590.349062627796429
│ NetworkReceiveElapsedMicroseconds │ 1611740.3372337225075003
│ ArenaAllocBytes │ 148058931214973665280.04893510724370622
│ OSWriteBytes │ 3809283850240.04644905045763538
│ ArenaAllocChunks │ 215321570.00806115279057892
│ FileOpen │ 751175160.0028900944828012766
│ OpenedFileCacheMisses │ 751175160.0028900944828012766
│ ContextLock │ 588058810.0007385332589917156
│ OSReadChars │ 23407914322340789818-0.000002994506583727971
│ OSWriteChars │ 25213102513992-0.012623549714419216
│ AggregationPreallocatedElementsInHashTables │ 128039910127563540-0.016187974135432794
│ OSCPUWaitMicroseconds │ 15436431536999-0.018732829140838268
│ OpenedFileCacheHits │ 539534-0.040475081581823065
│ UserTimeMicroseconds │ 5649084055961729-0.04086908559606555
│ WaitMarksLoadMicroseconds │ 388571359985-0.3318598023153847
│ ThreadpoolReaderTaskMicroseconds │ 38166693392522-0.5116182478775457
│ NetworkSendElapsedMicroseconds │ 47454122-0.6112822932011739
│ AsynchronousReadWaitMicroseconds │ 23802842025078-0.7018702173136342
│ NetworkReceiveBytes │ 516372-1.4210676174531387
└─────────────────────────────────────────────┴────────────┴────────────┴──────────────────────────┘

26 rows in set. Elapsed: 0.173 sec. Processed 5.86 million rows, 2.40 GB (33.92 million rows/s., 13.92 GB/s.)