[Proposal] Add query monitor detail in FE.

[chaoyli]

Doris is absent of query monitor. It is not convenient when analyzing the query workload.
A query monitor should cover such features.

1. It can exhibit running queries at present.
2. It can analysis the query latency(mean/99 percentile) at defined interval.
    For example, DBA will want to about the mean latency between 11:00 and 11:05.
3. It can observe the query profile of queries over a period of time.
    For example, how much resource a query consumed is critical for analysis the hot spot.

To address the above problem, Doris should support query details for outside monitor to collect and analyze.

[chaoyli]

I think the FE detail can provide the following detail about a query.

query_detail(query_id, start_time, end_time, latency, state, profile)

If I want to get the latency and percentile latency, I can use SQL if I store details in MySQL like this.

mean : select avg(latency) from query_detail where end_time >= "2020-05-26 19:01:00" and end_time <= "2020-05-26 19:01:59" and state = 'FINISHED';

50 quantile : select percentile_approx(latency, 0.5) from query_detail where end_time >= "2020-04-21 19:01:00" and end_time <= "2020-04-21 19:01:59" and state = 'FINISHED';

Because the QPS in Doris is somewhat high, the streaming load of Doris can not support such workload. So I abandon using Doris as the storage system.
Supporting using MySQL is the outside system. The problem is how to send the detail to outside system(MySQL).

Schema 1:
Doris Fe write MySQL directly, which should modify the FE, and make Doris coupled with MySQL.

Schema 2:
Doris log the query detail in audit log(fe.audit.log). It will have two log entry for one entry in fe.audit.log. When receiving the query, the end_time/state/profile is null. After finished, It will updated.

Schema 3:
Doris FE cached query detail in memory for fixed number query, for example 10000. Doris Fe provided a HTTP interface to get the query detail. This schema relied on the outside monitor, It should collect frequently.

[morningman]

Can't this meet your needs?
http://doris.incubator.apache.org/master/zh-CN/extending-doris/audit-plugin.html

[chaoyli]

Can't this meet your needs?
http://doris.incubator.apache.org/master/zh-CN/extending-doris/audit-plugin.html

It can only analysis the history query. But not analysis the current query profile to get the hotspot.

[morningman]

You means profile of current running queries?
But in your proposal, you only talk about the finished query audit?

[chaoyli]

You means profile of current running queries?
But in your proposal, you only talk about the finished query audit?

All finished query comes from current query. If current query is send to MySQL, and it finished, it will be history queries.

[morningman]

I probably understand what you mean, but I am not sure whether the specific plan is feasible. In your proposal, only the query about latency is mentioned, which can be known through historical results. But what runtime state do we need for the query being executed? And how to collect these status?
Because I understand that most of the runtime state is at the BE node, if all are exposed through the FE node, will it cause the FE node to become a bottleneck?

[chaoyli]

I probably understand what you mean, but I am not sure whether the specific plan is feasible. In your proposal, only the query about latency is mentioned, which can be known through historical results. But what runtime state do we need for the query being executed? And how to collect these status?
Because I understand that most of the runtime state is at the BE node, if all are exposed through the FE node, will it cause the FE node to become a bottleneck?

1. Another field like profile can be added easily in following development.
   As you say, the profile is now in BE node, only report upon finishing query, So I not add the
   profile field now. If query lasts for a long time, It will be better to report at interval.
2. I will only store a fixed number(10000) query detail in FE memory. If one query has 100K, It wil
   consume 1G memory which is acceptable. Considering QPS is 1000, Query Monitor collect
   detail at 10 seconds can cover the query details statistics.
3. Query Monitor will collect the query detail at interval. If restart FE, It has non-sense, because
   there is no query in system. The problem is Query Monitor outside is not connected to FE, it will
   lose some detail. I think it's also OK, because some queries takes no consideration have a little
   thing.