maxconcurrency doesn't seem to work in Resource Pooling

[amine]

Hi all,

So I created a low priority user that is assigned to a resource pool with maxconcurrency set to 8, but I was surprised to seeing a lot more than that running at a given time. Here's more information to help everyone understand what I am running through and help me understand why there aren't only 8 open requests at a time for that specific user:

Code: Select all

// there are 32 concurrent queries now running for lp_user
nba=> select count(*) from sessions where user_name = 'lp_user';
 count
-------
    32
(1 row)

// lp_user is assigned to lp_pool
nba=> select * from V_CATALOG.USERS where user_name = 'lp_user';
      user_id      | user_name | is_super_user | profile_name | is_locked | lock_time | resource_pool | memory_cap_kb | temp_space_cap_kb | run_time_cap | all_roles | default_roles |                    search_path
-------------------+-----------+---------------+--------------+-----------+-----------+---------------+---------------+-------------------+--------------+-----------+---------------+---------------------------------------------------
 45035996418446764 | lp_user   | f             | default      | f         |           | lp_pool       | unlimited     | unlimited         | unlimited    |           |               | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

// lp_pool's maxconcurrency is set to 8
nba=> select * from V_CATALOG.RESOURCE_POOLS where name = 'lp_pool';
      pool_id      |  name   | is_internal | memorysize | maxmemorysize | executionparallelism | priority | runtimepriority | runtimeprioritythreshold | queuetimeout | plannedconcurrency | maxconcurrency | runtimecap | singleinitiator
-------------------+---------+-------------+------------+---------------+----------------------+----------+-----------------+--------------------------+--------------+--------------------+----------------+------------+-----------------
 45035996418451182 | lp_pool | f           | 25%        | 25%           | AUTO                 |        0 | MEDIUM          |                        0 |          300 | AUTO               |              8 |            | f
(1 row)

Thanks in advance.

[JimKnicely]

Hi,

There may be 32 sessions, but I wonder if only 8 queries are running concurrently, where the rest are queued.

Run your test again, and check the query_profiles table like this:

select count(*) from v_monitor.query_profiles where user_name = 'lp_user' and is_executing;

Also take a look at the v_monitor.resource_queues table.

[amine]

Hi knicely87,

Thank you for the tip, resource_queues returned what I was looking for:

Code: Select all

nba=> select * from v_monitor.resource_queues where pool_name = 'lp_pool';
   node_name    |  transaction_id   | statement_id | pool_name | memory_requested_kb | priority | position_in_queue |     queue_entry_timestamp
----------------+-------------------+--------------+-----------+---------------------+----------+-------------------+-------------------------------
 v_nba_node0001 | 49539595906374391 |            4 | lp_pool   |              603261 |        0 |                 1 | 2014-11-04 16:40:52.676196+00
 v_nba_node0001 | 49539595906374381 |            4 | lp_pool   |              620663 |        0 |                 2 | 2014-11-04 16:40:52.722306+00
 v_nba_node0002 | 49539595906374391 |            4 | lp_pool   |              606043 |        0 |                 1 | 2014-11-04 16:40:52.6719+00
 v_nba_node0002 | 49539595906374381 |            4 | lp_pool   |              624665 |        0 |                 2 | 2014-11-04 16:40:52.717513+00
 v_nba_node0002 | 54043195533125443 |           12 | lp_pool   |              614009 |        0 |                 3 | 2014-11-04 16:40:53.09221+00
 v_nba_node0003 | 49539595906374391 |            4 | lp_pool   |              603261 |        0 |                 1 | 2014-11-04 16:40:52.672596+00
 v_nba_node0003 | 49539595906374381 |            4 | lp_pool   |              620663 |        0 |                 2 | 2014-11-04 16:40:52.718823+00
 v_nba_node0003 | 54043195533125443 |           12 | lp_pool   |              618365 |        0 |                 3 | 2014-11-04 16:40:53.093498+00
(8 rows)

[amine]

Actually, I take that back, as it seems to be a queue for 2 statements only.
Also running this:

Code: Select all

nba=> select count(*) from v_monitor.query_profiles where user_name = 'lp_user' and is_executing;
 count
-------
   238
(1 row)

shows that there are 238 queries being executed, so I guess I am still back at square 1, and I don't get why I can't find anything that matches my 8 maxconcurrent.


EDIT:

Even by altering the pool to have plannedconcurrency 8, the issue is still happening.

Thanks

[JimKnicely]

Hi,

Are you sure all the queries you see are using the lp_pool? the following query should show you a count of queries executing using your pool:

Code: Select all

 select count(*)
   from v_monitor.query_profiles qp
   join v_monitor.resource_acquisitions ra
     on ra.transaction_id = qp.transaction_id
    and ra.statement_id = qp.statement_id
  where qp.user_name = 'lp_user'
    and qp.is_executing
    and ra.pool_name = 'lp_pool'
    and ra.is_executing;

[amine]

Hi Jim,

Thank you for the answer, and the system tables tip.
So I tweaked your query a little bit to see if there are any other pools used, and the result is no, only lp_pool is used:

Code: Select all

nba=>  select ra.pool_name, count(*)
nba->    from v_monitor.query_profiles qp
nba->    join v_monitor.resource_acquisitions ra
nba->      on ra.transaction_id = qp.transaction_id
nba->     and ra.statement_id = qp.statement_id
nba->       where qp.user_name = 'lp_user'
nba->     and qp.is_executing
nba->     and ra.is_executing
nba-> group by 1;
 pool_name | count
-----------+-------
 lp_pool   |    11
(1 row)

[JimKnicely]

Can you try only looking at only statement_id = 1?

Code: Select all

select ra.pool_name, count(*)
  from v_monitor.query_profiles qp
  join v_monitor.resource_acquisitions ra
    on ra.transaction_id = qp.transaction_id
   and ra.statement_id = qp.statement_id
 where qp.user_name = 'lp_user'
   and qp.is_executing
   and ra.is_executing
   and ra.statement_id = 1
 group by 1;