clickhouse_query_log_replicas.md

When you a clickhouse cluster and you run queries on all the replicas it's not easy to get all the queries ran. I use system.query_log all the time to check timings, errors and so on.

So what I do is create a global query_log:

:) create view query_log_all on cluster my_cluster as select * from remote('10.0.0.1,10.0.0.2', 'system.query_log')

So I can inspect queries in all the replicas with a single query:

SELECT 
    toStartOfHour(query_start_time) AS hour,
    count() AS c,
    avg(query_duration_ms)
FROM query_log_all
WHERE query_start_time > (now() - toIntervalHour(24))
GROUP BY hour

┌────────────────hour─┬─────c─┬─avg(query_duration_ms)─┐
│ 2021-02-24 11:00:00 │ 20288 │      118.4436119873817 │
│ 2021-02-24 12:00:00 │ 15942 │      85.94304353280643 │
│ 2021-02-24 22:00:00 │ 25448 │     51.757387613957874 │
│ 2021-02-25 07:00:00 │ 31006 │      59.56853512223441 │
│ 2021-02-24 20:00:00 │ 12988 │      58.15121650754543 │
...

You can add as many ip addresses as you replicas you have. You can get that list with the following SQL.

select arrayStringConcat(groupArray(host_name), ',') as hosts from system.clusters where cluster = 'my_cluster'