Query queues

Overview of query queuing mechanism

Kognitio implements a query queuing mechanism to ensure that sufficient system resources are always available to allow efficient concurrent query performance.

By default there are two queues with queue numbers 0 and 1 in to which all non-SYS queries are placed; queries run by the SYS user are not queued. Queue 0 is an administration queue used by Kognitio tools and allows a very high number of concurrent queries. Queue 1 is the default queue used by everything else.

The maximum value for a queue number is 99999 and any number of queues up to this limit can be created. For example a system might have separate queues with appropriate characteristics defined for ad-hoc queries, high-priority queries, data quality queries and long-running KPI queries.

Monitoring queues

There are two virtual system tables that contain details of the query queues and their associated statistics, IPE_QUERY_QUEUES and IPE_QUERY_QUEUE_STATS. Entries only appear in these virtual tables for a queue when it has been used since the last database restart.

IPE_QUERY_QUEUES has an entry for each current query associated with a queue, indicating items such as:

  • its position in the queue

  • whether it is running (as opposed to queued)

  • how long it was queued for

  • how long it has been running to date.

IPE_QUERY_QUEUE_STATS has an entry per queue, indicating items such as:

  • how many queries are on that queue

  • how many are running and how many are queued

  • how many queries have been through the queue since server start time

  • how many queries are runnable at one time through the queue

  • the load over the last 5 minutes, 1 hour, and 5 hours, expressed in terms of the average length of the queue including running queries over that period (averages calculated in the same way as UNIX-like load averages)

  • the average queue time in seconds for the last 10, 50 and 100 queries

  • the average run time in seconds for the last 10, 50 and 100 queries

The system table IPE_ALLCURSESSIONS indicates a query is queued by setting the associated value of COMMAND_RUNNING to 4.

Manipulating queues

creating and dropping

Queues can be created and dropped as follows:

CREATE QUEUE queue-name;
DROP QUEUE queue-name;

setting default queues for users

A user can be added to a queue as follows:

ALTER USER user-name SET QUEUE TO queue-name

Thereafter, all the user’s queries will run in the specified queue. A user’s queue can also be specified at user creation time.

A global default queue can also be specified as follows:

ALTER QUEUE queue-name SET DEFAULT

pausing and unpausing

A queue can be paused; this prevents any subsequent jobs being run from being run in the queue. Queues can be paused and unpaused as follows:

ALTER QUEUE queue-name SET STATE TO PAUSED;
ALTER QUEUE queue-name SET STATE TO RUNNING;

It can be useful to ensure one or more queues are always paused at startup by putting entries of the form q<x>_pause=1 in the config file’s [runtime parameters] section, where <x> is the queue_id of the queue. This ensures no users from those queues can run queries until a startup script has been run. Typically this startup script would unpause queues at appropriate points, when the data the queue users rely on is available. For example, to ensure queue 17 is always paused at startup you would have a line like this in the [runtime parameters] section:

q17_pause=1

maxrun

Each queue has an associated MAXRUN value which specifies how many queries from the queue are permitted to be run concurrently; the default MAXRUN value is 10. MAXRUN can be altered dynamically:

ALTER QUEUE queue-name SET MAXRUN TO number

maxwait

Each queue has an associated MAXWAIT value which specifies how many queries can be queued; the default MAXWAIT value is 0 (unlimited). MAXWAIT can be altered dynamically:

ALTER QUEUE queue-name SET MAXWAIT TO number

If the MAXWAIT value prevents a query being queued, the query returns immediately with an appropriate error status code (QQ0001).

priority

It is possible to alter the priority of a query when it is submitted to a queue, which means that it may be inserted into the queue list above/below existing entries. By default, all entries are priority 100, so setting a higher priority of, say 50, will place a query above all existing priority 100 queries in the queue. The priority can be set for the current session:

set current_session parameter queue_pri to 50;

Or for a user:

set user parameter queue_pri to 50;

Setting the priority at the user level may only be useful if there are two or more users associated with one queue, as Kognitio does not look at priorities across different queues. However, it is possible to do this:

create user qnormal;
set user parameter queue_num to 11;
set user parameter queue_pri to 100;

create user qnormalpri;
set user parameter queue_num to 11;
set user parameter queue_pri to 50;

which would allow two priorities of jobs on the normal queue, depending on which username was used to submit the query.

barriers

A job can be specified to act as a barrier to jobs behind it in the queue; if the BARRIER value is set on a particular job, then no subsequent jobs will be run from the queue until it has completed. The BARRIER property is a column in IPE_QUERY_QUEUES, and the number of barriers is shown in IPE_QUERY_QUEUE_STATS. An example of setting a barrier is:

update ipe_query_queues
set barrier = 1 where queue = 100003 and job_id = 20

switching off the queuing mechanism

Queuing can only be switched off by setting qqon to 0 in the configuration file.

Examples of query queuing

The following example illustrates the key query queuing features; note that for brevity, columns associated with user/session/transaction information are not shown when IPE_QUERY_QUEUES is queried. Logged on as SYS:

-- Set queue 3 to just run one command at a time
alter queue q3 set maxrun to 1

-- Inspect the queue; note zero rows indicates nothing queued
select * from ipe_query_queues

/*
0 Row(s) Retrieved
*/

-- Now 2 adhoc users are assigned queue 3
alter user adhoc1 set queue to q3
alter user adhoc2 set queue to q3

-- Now the adhoc users kick off their queries in their own sessions.

-- Adhoc/1:

select count(*) from big_complex_view

-- and Adhoc/2:

select count(*) from big_complex_view

-- As SYS inspect the queue.
-- We have one job running and another queued

select queue q, job_id jid, pos, priority,
    running, secs_queued secq, secs_running secr,
    barrier, put_before pb
from ipe_query_queues
order by 2


+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| Q        | JID   | POS   | PRIORITY   | RUNNING   | SECQ   | SECR   | BARRIER   | PB   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 19    | 0     | 100        | 1         | 0      | 146    | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 20    | 1     | 100        | 0         | 8      | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+


-- Now SYS sets the barrier column on the queued query.
-- This will prevent any other queries running until this one has completed
update ipe_query_queues
set barrier = 1 where queue = 100003 and job_id = 20

-- Now other adhoc users are assigned queue 3

alter user adhoc3 set queue to q3
alter user adhoc4 set queue to q3

-- Adhoc3 sets off their query

select count(*) from big_complex_view

-- and Adhoc4 sets off theirs

select count(*) from big_complex_view

-- As SYS inspect the queue again.
-- This time we have one job running and 3 queued

select queue q, job_id jid, pos, priority,
    running, secs_queued secq, secs_running secr,
    barrier, put_before pb
from ipe_query_queues
order by 2


+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| Q        | JID   | POS   | PRIORITY   | RUNNING   | SECQ   | SECR   | BARRIER   | PB   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 19    | 0     | 100        | 1         | 0      | 1745   | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 20    | 1     | 100        | 0         | 1607   | 0      | 1         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 21    | 2     | 100        | 0         | 10     | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 22    | 3     | 100        | 0         | 5      | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+

-- Now SYS allows up to 3 jobs to run concurrently from queue 3::

alter queue q3 set maxrun to 3

-- Inspect the queue again

-- Job 20 is now running, but because of the barrier
-- jobs 21 and 22 are still queued

select queue q, job_id jid, pos, priority,
    running, secs_queued secq, secs_running secr,
    barrier, put_before pb
from ipe_query_queues
order by 2

+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| Q        | JID   | POS   | PRIORITY   | RUNNING   | SECQ   | SECR   | BARRIER   | PB   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 19    | 0     | 100        | 1         | 0      | 2003   | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 20    | 1     | 100        | 0         | 1636   | 229    | 1         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 21    | 2     | 100        | 0         | 268    | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 22    | 3     | 100        | 0         | 263    | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+

-- Now alter the order of the jobs in the queue, in this case we put job 22 ahead of job 21

update ipe_query_queues
set put_before = 21
where queue = 100003 and job_id = 22

-- Inspect the queue again
select queue q, job_id jid, pos, priority,
    running, secs_queued secq, secs_running secr,
    barrier, put_before pb
from ipe_query_queues
order by 2


+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| Q        | JID   | POS   | PRIORITY   | RUNNING   | SECQ   | SECR   | BARRIER   | PB   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 19    | 0     | 100        | 1         | 0      | 2403   | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 20    | 1     | 100        | 0         | 2036   | 229    | 1         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 21    | 3     | 100        | 0         | 668    | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 22    | 2     | 100        | 0         | 663    | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+

-- Now alter the priority of job 21, this moves it to first
-- position, but it still doesn’t run because the barrier job
-- has still to complete

alter user adhoc3 set queue priority to 1

-- Inspect the queue again
select queue q, job_id jid, pos, priority,
    running, secs_queued secq, secs_running secr,
    barrier, put_before pb
from ipe_query_queues
order by 2


+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| Q        | JID   | POS   | PRIORITY   | RUNNING   | SECQ   | SECR   | BARRIER   | PB   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 19    | 1     | 100        | 1         | 0      | 2403   | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 20    | 2     | 100        | 0         | 2036   | 229    | 1         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 21    | 0     | 100        | 0         | 668    | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+
| 100003   | 22    | 3     | 100        | 0         | 663    | 0      | 0         | -1   |
+----------+-------+-------+------------+-----------+--------+--------+-----------+------+

-- Now look at the queue statistics (query columns split in 2 for ease of reading)

select queue, len, nrunning nrun, nwaiting nwait,
     nbarriers, totaljobs jobs, maxrun, maxwait
from ipe_query_queue_stats

+----------+-------+--------+---------+-------------+--------+----------+-----------+
| QUEUE    | LEN   | NRUN   | NWAIT   | NBARRIERS   | JOBS   | MAXRUN   | MAXWAIT   |
+----------+-------+--------+---------+-------------+--------+----------+-----------+
| 100001   | 0     | 0      | 0       | 0           | 5      | 10       | 0         |
+----------+-------+--------+---------+-------------+--------+----------+-----------+
| 100003   | 4     | 2      | 2       | 1           | 19     | 3        | 0         |
+----------+-------+--------+---------+-------------+--------+----------+-----------+

select queue, load5m, load1h, load5h,
    queuetime10 qt10, queuetime50 qt50, queuetime100 qt100,
    runtime10 rt10, runtime50 rt50, runtime100 rt100
from ipe_query_queue_stats


+----------+----------+----------+----------+----------+---------+---------+---------+--------+---------+
| QUEUE    | LOAD5M   | LOAD1H   | LOAD5H   | QT0      | QT50    | QT100   | RT10    | RT50   | RT100   |
+----------+----------+----------+----------+----------+---------+---------+---------+--------+---------+
| 100001   | 0.00     | 0.00     | 0.00     | 0.00     | 0.00    | 0.00    | 11.05   | 2.45   | 1.24    |
+----------+----------+----------+----------+----------+---------+---------+---------+--------+---------+
| 100003   | 3.99     | 2.46     | 0.70     | 162.67   | 34.94   | 17.65   | 23.03   | 7.61   | 4.05    |
+----------+----------+----------+----------+----------+---------+---------+---------+--------+---------+

Query queue damping

Query queue damping gives Kognitio time to adjust to a dramatic increase in the number of concurrent query requests gracefully. If Kognitio is under a constant workload and then that workload suddenly increases significantly, the queue damping feature smooths the change out by holding back some of the new jobs for a small amount of time or until something else exits.

For example, consider a Kognitio system where MAXRUN is set to 16 and 4 jobs are running; if 10 new jobs are submitted simultaneously, it may take around a minute before all the new jobs actually start running.