Sys Queue

The MonetDB kernel maintains an active queue of all running and recently finished queries. This queue is available for all users to inspect the status of his own queries. The system administrator can inspect it to overlook the complete workload on the system. The queue is made visible as a table producing function sys.queue() and system view sys.queue.

sql>select * from sys.queue order by tag desc limit 1;
+------+-------+----------+----------------------------+----------+----------------------------------------------------+----------------------------+-------+-------+
| tag  | sessi | username | started                    | status   | query                                              | finished                   | maxwo | footp |
:      : onid  :          :                            :          :                                                    :                            : rkers : rint  :
+======+=======+==========+============================+==========+====================================================+============================+=======+=======+
|  502 |     1 | monetdb  | 2022-09-28 21:26:38.000000 | running  | select * from sys.queue order by tag desc limit 1; | null                       |     8 |     1 |
+------+-------+----------+----------------------------+----------+----------------------------------------------------+----------------------------+-------+-------+
1 tuple

The schema structure is largely self-explanatory. If the query (template) is ran multiple times, then the system can derive a progress indicator and calculate an estimated time of completion. The initial column 'tag' provides a unique id to each active query.

One can use the SQL procedures PAUSE, RESUME and STOP to control the execution of (long) running queries.

NOTE that PAUSE and STOP takes effect at the first safe point within the query plan, which often is after the current MALĀ instruction has been finished. For complex queries over large database this may take seconds (up to minutes).

ProcedureDescriptionExample
sys.pause(tag bigint)pause the execution of a (long) running statement using its tag id. run query: select * from sys.queue; to list the running statementscall sys.pause(842);
sys.resume(tag bigint)resume execution of a previouly paused statementcall sys.resume(842);
sys.stop(tag bigint)stop/abort execution of a running statementcall sys.stop(842);
sql>select * from sys.queue order by tag desc limit 2;
+------+--------+----------+----------------------------+----------+----------------------------------------------------+----------------------------+---------+---------+
| tag  | sessio | username | started                    | status   | query                                              | finished                   | maxwork | footpri |
:      : nid    :          :                            :          :                                                    :                            : ers     : nt      :
+======+========+==========+============================+==========+====================================================+============================+=========+=========+
|  503 |      1 | monetdb  | 2022-09-28 21:33:44.000000 | running  | select * from sys.queue order by tag desc limit 2; | null                       |       8 |       1 |
|  502 |      1 | monetdb  | 2022-09-28 21:28:17.000000 | finished | select * from sys.queue order by tag desc limit 1; | 2022-09-28 21:28:17.000000 |       8 |       1 |
+------+--------+----------+----------------------------+----------+----------------------------------------------------+----------------------------+---------+---------+
2 tuples

--- start a long running query in another session

sql>select * from sys.queue order by tag desc limit 2;
+------+--------+----------+----------------------------+----------+-----------------------------------------------------+----------------------------+---------+---------+
| tag  | sessio | username | started                    | status   | query                                               | finished                   | maxwork | footpri |
:      : nid    :          :                            :          :                                                     :                            : ers     : nt      :
+======+========+==========+============================+==========+=====================================================+============================+=========+=========+
|  505 |      1 | monetdb  | 2022-09-28 21:36:11.000000 | running  | select * from sys.queue order by tag desc limit 2;  | null                       |       8 |       1 |
|  504 |      2 | monetdb  | 2022-09-28 21:35:23.000000 | running  | select sum(l_extendedprice * l_discount) as revenue | null                       |       8 |       1 |
:      :        :          :                            :          :  from lineitem                                      :                            :         :         :
:      :        :          :                            :          : where l_shipdate >= date \'1994-01-01\'             :                            :         :         :
:      :        :          :                            :          :  and l_shipdate < date \'1994-01-01\' +             :                            :         :         :
:      :        :          :                            :          :      interval \'1\' year                            :                            :         :         :
:      :        :          :                            :          :  and l_discount between 0.06 - 0.01 and 0.06 + 0.01 :                            :         :         :
:      :        :          :                            :          :  and l_quantity < 24;                               :                            :         :         :
+------+--------+----------+----------------------------+----------+-----------------------------------------------------+----------------------------+---------+---------+
2 tuples

sql>call sys.pause(504);
sql>select * from sys.queue order by tag desc limit 3;
+------+--------+----------+----------------------------+----------+-----------------------------------------------------+----------------------------+---------+---------+
| tag  | sessio | username | started                    | status   | query                                               | finished                   | maxwork | footpri |
:      : nid    :          :                            :          :                                                     :                            : ers     : nt      :
+======+========+==========+============================+==========+=====================================================+============================+=========+=========+
|  506 |      1 | monetdb  | 2022-09-28 21:41:05.000000 | running  | select * from sys.queue order by tag desc limit 2;  | null                       |       8 |       1 |
|  505 |      1 | monetdb  | 2022-09-28 21:36:11.000000 | finished | select * from sys.queue order by tag desc limit 2;  | 2022-09-28 21:33:44.000000 |       8 |       1 |
|  504 |      2 | monetdb  | 2022-09-28 21:35:23.000000 | paused   | select sum(l_extendedprice * l_discount) as revenue | null                       |       8 |       1 |
:      :        :          :                            :          :  from lineitem                                      :                            :         :         :
:      :        :          :                            :          : where l_shipdate >= date \'1994-01-01\'             :                            :         :         :
:      :        :          :                            :          :  and l_shipdate < date \'1994-01-01\' +             :                            :         :         :
:      :        :          :                            :          :      interval \'1\' year                            :                            :         :         :
:      :        :          :                            :          :  and l_discount between 0.06 - 0.01 and 0.06 + 0.01 :                            :         :         :
:      :        :          :                            :          :  and l_quantity < 24;                               :                            :         :         :
+------+--------+----------+----------------------------+----------+-----------------------------------------------------+----------------------------+---------+---------+
3 tuples

sql>call sys.resume(504);
sql>select * from sys.queue order by tag desc limit 4;
+------+--------+----------+----------------------------+----------+-----------------------------------------------------+----------------------------+---------+---------+
| tag  | sessio | username | started                    | status   | query                                               | finished                   | maxwork | footpri |
:      : nid    :          :                            :          :                                                     :                            : ers     : nt      :
+======+========+==========+============================+==========+=====================================================+============================+=========+=========+
|  507 |      1 | monetdb  | 2022-09-28 21:48:54.000000 | running  | select * from sys.queue order by tag desc limit 2;  | null                       |       8 |       1 |
|  506 |      1 | monetdb  | 2022-09-28 21:41:05.000000 | finished | select * from sys.queue order by tag desc limit 2;  | 2022-09-28 21:41:05.000000 |       8 |       1 |
|  505 |      1 | monetdb  | 2022-09-28 21:36:11.000000 | finished | select * from sys.queue order by tag desc limit 2;  | 2022-09-28 21:36:11.000000 |       8 |       1 |
|  504 |      2 | monetdb  | 2022-09-28 21:35:23.000000 | running  | select sum(l_extendedprice * l_discount) as revenue | null                       |       8 |       1 |
:      :        :          :                            :          :  from lineitem                                      :                            :         :         :
:      :        :          :                            :          : where l_shipdate >= date \'1994-01-01\'             :                            :         :         :
:      :        :          :                            :          :  and l_shipdate < date \'1994-01-01\' +             :                            :         :         :
:      :        :          :                            :          :      interval \'1\' year                            :                            :         :         :
:      :        :          :                            :          :  and l_discount between 0.06 - 0.01 and 0.06 + 0.01 :                            :         :         :
:      :        :          :                            :          :  and l_quantity < 24;                               :                            :         :         :
+------+--------+----------+----------------------------+----------+-----------------------------------------------------+----------------------------+---------+---------+
4 tuples

sql>call sys.stop(504);
sql>select * from sys.queue order by tag desc limit 5;
+------+--------+----------+----------------------------+----------+-----------------------------------------------------+----------------------------+---------+---------+
| tag  | sessio | username | started                    | status   | query                                               | finished                   | maxwork | footpri |
:      : nid    :          :                            :          :                                                     :                            : ers     : nt      :
+======+========+==========+============================+==========+=====================================================+============================+=========+=========+
|  508 |      1 | monetdb  | 2022-09-28 21:51:48.000000 | running  | select * from sys.queue order by tag desc limit 2;  | null                       |       8 |       1 |
|  507 |      1 | monetdb  | 2022-09-28 21:48:54.000000 | finished | select * from sys.queue order by tag desc limit 2;  | 2022-09-28 21:48:54.000000 |       8 |       1 |
|  506 |      1 | monetdb  | 2022-09-28 21:41:05.000000 | finished | select * from sys.queue order by tag desc limit 2;  | 2022-09-28 21:41:05.000000 |       8 |       1 |
|  505 |      1 | monetdb  | 2022-09-28 21:36:11.000000 | finished | select * from sys.queue order by tag desc limit 2;  | 2022-09-28 21:36:11.000000 |       8 |       1 |
|  504 |      2 | monetdb  | 2022-09-28 21:35:23.000000 | finished | select sum(l_extendedprice * l_discount) as revenue | 2022-09-28 21:50:25.000000 |       8 |       1 |
:      :        :          :                            :          :  from lineitem                                      :                            :         :         :
:      :        :          :                            :          : where l_shipdate >= date \'1994-01-01\'             :                            :         :         :
:      :        :          :                            :          :  and l_shipdate < date \'1994-01-01\' +             :                            :         :         :
:      :        :          :                            :          :      interval \'1\' year                            :                            :         :         :
:      :        :          :                            :          :  and l_discount between 0.06 - 0.01 and 0.06 + 0.01 :                            :         :         :
:      :        :          :                            :          :  and l_quantity < 24;                               :                            :         :         :
+------+--------+----------+----------------------------+----------+-----------------------------------------------------+----------------------------+---------+---------+
5 tuples