Error: "Query execution memory use (XXX MB) has reached the maximum memory for its resource pool 'xxx' (XX%). “

This error occurs due to resource limits for users that you configured. It indicates that the query was run by a user belonging to the resource pool named "general". The sum of memory use permitted to users in that pool is XX% of the maximum_memory limit which you set on each node. If queries in that pool use XX% of memory, then the newest query will not run. Instead, it will be killed with this error message.

Issue Example: Query execution memory use (13118.625633 MB) has reached the maximum memory for its resource pool 'general' (85%).

Solution: Perform the following troubleshooting steps.

1. Review current resource pool limits

Check whether the resource limit you set for this pool is adequate. To determine what users are in the pool, run the command:

SHOW USERS;

+--------------------+--------+-------------+------------+-----------------------+
| User               | Type   | Connections | Is deleted | Default resource pool |
+--------------------+--------+-------------+------------+-----------------------+
| 'general_user'@'%' | Native |           0 |            | general               |
| 'root'@'%'         | Native |           1 |            |                       |
+--------------------+--------+-------------+------------+-----------------------+

Or

SELECT * FROM INFORMATION_SCHEMA.USERS;

+--------------+------+-------------+------------+---------------------+-----------------------+
| USER         | HOST | CONNECTIONS | IS_DELETED | LAST_UPDATED        | DEFAULT_RESOURCE_POOL |
+--------------+------+-------------+------------+---------------------+-----------------------+
| root         | %    |           1 |          0 | 2018-06-29 11:51:51 |                       |
| general_user | %    |           0 |          0 | 2018-06-29 11:54:38 | general               |
+--------------+------+-------------+------------+---------------------+-----------------------+

The Default resource pool column indicates the default pool assigned to each user. Any users in the pool named "general" are subject to the limit set for this resource pool.

2. A single expensive query?

If the limit is correct for the users, then consider what queries the users run. What query is killed with this error? Does it use a disproportionate amount of memory according to the workload? Here the query that was killed was using 13 GB. Is that normal for your workload? If this is not normal, then investigate ways to improve its performance by adding hints or restructuring your query text. Also look into the possibility of SingleStore handling its shape better.

3. Many inexpensive queries?

If 13 GB is not expensive for your cluster, then are you running many inexpensive queries concurrently? Consider spreading out your workload, or reducing concurrency.

4. Is one expensive query bogging down the rest of the workload?

It is also possible that there is already one expensive query running, which is affecting the rest of your workload. For example, maybe by holding locks. Always be sure to run expensive analytical queries like large reports, backups, or other cluster maintenance when the standard workload is not running. Alternatively, run your expensive analytical queries on the secondary cluster if you are using Cluster Replication (CR).

In CR to determine whether it is caused by one expensive query, or many inexpensive queries, or something expensive blocking other workloads and causing inexpensive queries to build up or queue. To collect a cluster report run:

sdb-report collect