Skip to content

Instantly share code, notes, and snippets.

@javisantana
Last active February 25, 2021 10:11
Show Gist options
  • Save javisantana/d0d7fe5b16e4d88f185eceb08538ac99 to your computer and use it in GitHub Desktop.
Save javisantana/d0d7fe5b16e4d88f185eceb08538ac99 to your computer and use it in GitHub Desktop.

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:0020288118.4436119873817 │
│ 2021-02-24 12:00:001594285.94304353280643 │
│ 2021-02-24 22:00:002544851.757387613957874 │
│ 2021-02-25 07:00:003100659.56853512223441 │
│ 2021-02-24 20:00:001298858.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'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment