Question about resource pool

Off-Topic
1

hi, there.
I created a resource pool in user01 as shown below.

I want to perform 200 simultaneous queries with jmeter to see the workload status.
However, it is not possible to check for ‘queued queries’ and ‘running queries’. The value will remain as ‘0’.
Is there any problem?

The table(information_schema.processlist) checked the status of ‘executing’ but maintained a different value of 23 than the set value(max_concurrency).
Also, the value of the Status column(‘queued’) was not confirmed.
The query limit i set for the resource pool does not seem to work properly.

memsql> show resource pools;
    +-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+
    | Pool_Name             | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage | Hard_CPU_Limit_Percentage | Max_Queue_Depth |
    +-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+
    | default_pool          |               100 |          NULL |            NULL |                      NULL |                      NULL |            NULL |
    | system_auto           |               100 |          NULL |            NULL |                      NULL |                      NULL |            NULL |
    | system_optimizer_pool |               100 |          NULL |            NULL |                      NULL |                         5 |            NULL |
    | executive             |               100 |          NULL |              40 |                      NULL |                        65 |              20 |
    | general               |               100 |          NULL |              20 |                      NULL |                        30 |              10 |
    +-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+
    5 rows in set (0.00 sec)


memsql> select current_resource_pool();
+-------------------------+
| current_resource_pool() |
+-------------------------+
| executive               |
+-------------------------+
1 row in set (0.00 sec)


memsql> SHOW WORKLOAD MANAGEMENT STATUS;
+-------------------------------------------------------+-------+
| Stat                                                  | Value |
+-------------------------------------------------------+-------+
| Queued Queries (from global queue)                    |     0 |
| Queued Queries (from local queue)                     |     0 |
| Total Queued Queries Since Startup                    |     0 |
| Running Queries (from global queue)                   |     0 |
| Running Queries (from local queue)                    |     0 |
| Running Memory (MB) On Leaves (from global queue)     |     0 |
| Running Memory (MB) On Leaves (from local queue)      |     0 |
| Allocated Memory (MB) On Leaves (for local queue)     | 28776 |
| Required Memory (MB) On Leaves (from local queue)     |     0 |
| Avg Required Memory (MB) On Leaves (from local queue) |     0 |
| Running Threads Per Leaf (from local queue)           |     0 |
| Allocated Threads Per Leaf (for local queue)          |  2048 |
| Required Threads Per Leaf (from local queue)          |     0 |
| Avg Required Threads Per Leaf (from local queue)      |     0 |
| Running Connections Per Leaf (from local queue)       |     0 |
| Allocated Connections Per Leaf (for local queue)      |  2500 |
| Required Connections Per Leaf (from local queue)      |     0 |
| Avg Required Connections Per Leaf (from local queue)  |     0 |
| Memory Threshold (MB) to Queue Locally                |   575 |
| Memory Threshold (MB) to Queue Globally               | 28776 |
| Connections Threshold to Queue Globally               |  2500 |
| Threads Threshold to Queue Globally                   |  2048 |
+-------------------------------------------------------+-------+
22 rows in set (0.00 sec)

thanks. in advance.

2

If the queries you are running in your test are “small” queries because they don’t take much query execution memory then they won’t be queued, ever. Try some queries that do a large group-by or hash join that takes a lot of memory. That may cause some queuing. See here for a discussion of what makes a query “medium” or “large:”

3

If I perform 200 simultaneous queries, shouldn’t the query wait in the queue except for the setting values that are limited in the resource pool?

Is there anything I’m misunderstanding?

Thanks your reply.

4

I think you may be confused between Resource Pools (SingleStoreDB Cloud · SingleStore Documentation) and Workload Management (SingleStoreDB Cloud · SingleStore Documentation)

The command to see queries running within resource pools is:

select * from information_schema.MV_RESOURCE_POOL_STATUS;

This will show you running/queued queries within resource pools across all your nodes.

On the other hand,
SHOW WORKLOAD MANAGEMENT STATUS;

shows the queries queued by workload management on that specific node. Workload Managment and Resource Governance are separate components, each queueing queries independently of each other.

Yes, you can also use STATE column in information_schema.PROCESSLIST. If it is QUEUED, it means either Workload Management OR a Resource Pool has queued the query.

Also, bear this in mind:
(From SingleStoreDB Cloud · SingleStore Documentation)

 New queries will be queued on a given aggregator if the number of currently running queries is 
 equal to FLOOR(C/N), where C is the maximum concurrency of the resource group pool and N is 
the number of aggregators. Even if FLOOR(C/N) = 0 for a resource pool, MemSQL allows the 
resource pool to run at least one query per aggregator. If an actively running query requires more 
memory than is available in its pool, it will be terminated when that memory limit is reached.

If you want more assistance, can you provide me with the following information:

  1. Your Memsql Version
  2. How many Aggregators/Leaves in your cluster? Are you launching all your 200 queries from the same Aggregator?
  3. Output of select * from information_schema.MV_RESOURCE_POOL_STATUS; and select * from information_schema.processlist