Best practise to load balance the database-query instances?

[flaneur2020]

currently databend-query have some in-memory states, like query logs, metrics, etc (mostly are the in memory system tables?). a SQL fired on a different databend-query instance may get a different result, like:

SELECT * FROM system.metrics;
KILL 12;

if I want run databend-query instances in a HA manner with an Round Robin load balancer, The proxy can not produce a consistent result between queries except enable a sticky session.

also, as the system administrator, i hope to manage the cluster running state in a global manner, if someone produces a nuclear bomb query, i can KILL it in time to save the entire cluster. but currently i need execute SHOW PROCESSLIST on every instances to find which instance the SQL is fired.

any suggestion about this?

[ZhiHanZ]

I think it also relevant to query fallback retry and we should also be able to fetch query result consistently from any of the instance in a cluster.
Currently their should have some candidates:

1. use proxy mode databend-query in the frontend to show consistent result and handle query processes.
2. databend-query perform some aggregations to support distributed system data collection and consistent result fetching.
3. use meta service to handle with consistency related query issues and expose some rpc interface

[BohuTANG]

1. This is not a user-side requirements, the system informations no need to show a consistency view.
2. If you want to kill a query from any node of the cluster, I think the kill should distributed by the databend-query kernel, because a query is distrubuted to many nodes, we can't kill it in one node, what do you think @zhang2014
3. If it's just a kill requirement, I don't think we need to design it so complicated, are there any other scenarios that require this feature?

For the cloud, there is no SHOW PROCESSLIST anymore.

[flaneur2020]

fair enough, we can weak the instance-wise system tables in the distributed environment.

[zhang2014]

This is helpful for killing query without switching databend-query nodes(may be helpful for non cloud users)?

for example:

Node 1 has [query_1, query_3],
Node 2 has [query_4]
Node 3 has [query_2]

shell : > mysql -h node1 -uroot -p
: ) SHOW PROCESSLIST;
query1
query3
query4
query2

:) KILL query2;
:) SHOW PROCESSLIST;
query1
query3
query4

[zhangbc97]

Hello, I encounter load balancing problems when using HTTP Handler. If a large amount of data is returned, data will be returned in paging mode. When the load balancing policy is RoundRobin, paging requests will be randomly sent to different query nodes, resulting in error when query ids cannot be found. If the IP Hash policy is used, requests (even different queries) from clients on the same node will be routed to the same query node, resulting in inefficient utilization of multiple query nodes to improve throughput (for example, the client traverses the entire database by a category field).
I have observed that some related solutions have been proposed in the above discussion, will these be supported in future releases?

[BohuTANG]

Currently, we do not have definitive plans to support these solutions in future updates. However, we highly recommend configuring your Router to direct the same request (with same query id) always to one server.

[zhangbc97]

thanks~

[George-Patterson-Imprivata]

I have a follow up question. Query ID is not always provided. For the first query, a header can be returned with the query ID. Subsequent queries use no such header and instead include the query ID in the url (at least with bendsql or the Rust client driver). What is the best way to handle this currently? It would take some pretty advanced load balance stickiness to associate a header value to a portion of a url path or query string.