[Feature] expose query_log metrics from system.query_log table about clickhouse-operator HOT 4 CLOSED

Would the ch-operator team entertain a PR to expose these per-query statistics?

If you would like to make PR with new functionality, we'd consider it, of course. But it would be very good to discuss architectural/implementation details prior. We can use this thread to discuss as much details as possible.

from clickhouse-operator.

Sounds good!

The main per-query metrics I am interested in, and would propose as an MVP, would be

1. query_duration_ms
2. memory_usage
3. result_bytes

Each with the following labels

1. user
2. address

Then this could be exposed as a Prometheus Histogram with set quantiles, i.e. 0.50, 0.90, 0.95, 0.99.

The main architectural question in my mind is how to populate a histogram with the polling fetcher. One idea is to filter queries in the log that happened within the last 30s AND after the last query we have already processed. Would be great to get your thoughts on this, or potentially a better idea.

from clickhouse-operator.

Usually query_log is full of very different queries, and number of queries cluster-wise can be quite big.

So that data can be useful only in one very special case - when you need to monitor the execution of some very particular query which is expected to be executed close to constant time. Do you mean something like that? Do you plan to run some particular query (which always affect same number of rows) in some separated user?

That usecase sound like very-very limited, as usually you run different queries, with different conditions, affecting different time ranges, and putting them in some kind of graph seems useless. So direct comparison of such a different queries is simply not possible, and if you will try to draw a graph from that - it rather will be very noisy and useless.

Also AFAIR query_duration can be affected by client / network speed.

from clickhouse-operator.

Thanks for the thoughtful response. Our customers can perform many different queries via our API, but we want all of them to meet a certain latency requirement. Upon further thinking about this, we can also log query statistics in the query code (we are using FORMAT JSONCompact, so all of the statistics come back with the query). It probably makes the most sense to do it in real-time there and avoid needing clickhouse to log all of the queries.

Closing for now, thanks again!

from clickhouse-operator.