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).
| Procedure | Description | Example |
|---|---|---|
| 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 statements | call sys.pause(842); |
| sys.resume(tag bigint) | resume execution of a previouly paused statement | call sys.resume(842); |
| sys.stop(tag bigint) | stop/abort execution of a running statement | call 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 3; | 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 4; | null | 8 | 1 |
| 506 | 1 | monetdb | 2022-09-28 21:41:05.000000 | finished | select * from sys.queue order by tag desc limit 3; | 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 5; | null | 8 | 1 |
| 507 | 1 | monetdb | 2022-09-28 21:48:54.000000 | finished | select * from sys.queue order by tag desc limit 4; | 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 3; | 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