Set TiDB instance single SQL memory limit based on different business requirements, e.g., instance 1 single SQL memory limit 2G, instance 2 single SQL memory limit 10G

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如根据不同业务设置tidb实例单个sql内存限制,例如实例1单个sql内存限制2G,实例2单个sql内存限制10G

| username: wluckdog

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.4
[Reproduction Path] I have 6 TiDB instances, 4 of which are used by Business 1 and 2 by Business 2. The report queries for Business 2 consume a large amount of memory. How can I set different SQL memory limits for different businesses? Currently, the parameter set global tidb_mem_quota_query=value, when set on one TiDB instance, applies the same value to all instances.
What I want to achieve is:
For example, set a single SQL memory limit of 2G for Instance 1 and 10G for Instance 2.
[Encountered Issues: Problem Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

| username: tidb菜鸟一只 | Original post link

For report queries, should we use TiFlash or TiDB?

| username: Kongdom | Original post link

Manually modifying the meta file under the node is possible, but it is not recommended for use in a production environment.

/home/tidb/.tiup/storage/cluster/clusters/cluster_name/meta.yaml

| username: wluckdog | Original post link

Report queries use TiFlash at the underlying level, but still need to go through TiDB nodes at the upper level.

| username: wluckdog | Original post link

Yes, this configuration should not be used in a production environment.

| username: 裤衩儿飞上天 | Original post link

Just allocate a separate TiDB server for TiFlash’s business :crazy_face:

| username: Jellybean | Original post link

You can try modifying the configuration of the tidb-server instance on the machine and then restart them.

Note: Next time if you use tiup to reload or upgrade the cluster, the meta configuration file on tiup (/home/tidb/.tiup/storage/cluster/clusters/{your_tidb_cluster_name}/meta.yaml) might overwrite your previous modifications. At that time, based on the actual situation, you may need to modify and restart the instance again to reapply the changes.

| username: wzf0072 | Original post link

For example, instance 1 has a single SQL memory limit of 2G, and instance 2 has a single SQL memory limit of 10G.

Set a memory limit of 10G on all TiDB Server instances (to ensure all services can run normally), optimize the SQL that uses a lot of memory on the TiDB Server monitoring service 1, or allocate a separate TiDB Server node (to ensure the efficiency of service 1).

Monitoring large memory SQL:

select id, time, info, mem from INFORMATION_SCHEMA.processlist a where a.info is not null
| username: xingzhenxiang | Original post link

select id from INFORMATION_SCHEMA.processlist a where a.info is not null and (mem >= 2147483648)
Exceeds kill

| username: wluckdog | Original post link

Report queries on a separate TiDB instance do indeed consume a large amount of memory. The concurrency is not very high, with fewer than 6 concurrent queries. The underlying TiFlash full table scan consumes a lot of memory, which is normal. Currently, a separate TiDB instance for report queries needs to be configured with a large memory quota for individual SQL queries. However, setting the global tidb_mem_quota_query parameter will affect all TiDB instances.