Queries Queue

The MonetDB kernel maintains an active queue of all running and recently finished queries. This queue is available to all users to inspect the status of their own queries. The system administrator can inspect it to overlook the complete workload on the database server.

The queries queue is accessible as a table producing system function sys.queue() and system view sys.queue. If a user queries the view or the function without arguments, it returns the recently send queries for that user.

select * from sys.queue where status = 'running';
tagsessionidusernamestartedstatusqueryfinishedmaxworkersfootprint
5021monetdb2022-09-28 21:26:38.000000runningselect * from sys.queue where status = 'running'\n;null81

The schema structure is largely self-explanatory. The initial column 'tag' provides a unique id to each active or recently finished query.

started and finished column reports timestamp in UTC.

Alternatively, a system administrator (or user with sysadmin role privilege) can see the server queue by calling select * from queue('ALL'); or even filter a specific user with select * from queue('user1');.

The queries queue is limited to keep only the last N queries. The default is 64. It can be controlled via the max_clients setting (--set max_clients=99) when the MonetDB server is started. To view this startup setting use SQL:

select * from environment where name = 'max_clients';

Controlling long running queries

To list the running queries from current user use SQL:

select * from sys.queue where status = 'running';

To list the running queries from all users (this requires system administrator privileges) use SQL:

select * from sys.queue('ALL') where status = 'running';

To control the long running queries or statements you can use the system procedures: sys.pause(...), sys.resume(...) and sys.stop(...).

Note that sys.pause() and sys.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 tables this may take some seconds (up to minutes).

If a query is stopped, its status is marked has aborted instead of finished.

A system administrator is able to stop, pause or resume other users queries execution, e.g., using call sys.stop(842, 'user1').

ProcedureDescriptionAdmin OnlyExample
sys.pause(tag bigint)pause the execution of a running query using its tag id.Falsecall sys.pause(842);
sys.resume(tag bigint)resume execution of a previouly paused queryFalsecall sys.resume(842);
sys.stop(tag bigint)abort execution of a running or paused queryFalsecall sys.stop(842);
sys.pause(tag bigint, username string)pause the execution of a running query from a specific user, using its tag idTruecall sys.pause(842, 'user1');
sys.resume(tag bigint, username string)resume execution of a previouly paused query from a specific userTruecall sys.resume(842, 'user1');
sys.stop(tag bigint, username string)abort execution of a running or paused query started by a specific userTruecall sys.stop(842, 'user1');

Below we show how sys.pause(...), sys.resume(...) and sys.stop(...) can be used.

--- start a long running query
select * from longrunningquery;

select * from sys.queue where status <> 'finished';
tagsessionidusernamestartedstatusqueryfinishedmaxworkersfootprint
5041monetdb2022-09 (...)runningselect * from longrunningquery\n;null81
5051monetdb2022-09 (...)runningselect * from sys.queue where status <> 'finished'\n;null81

Query 504 is a long running query. We can pause it.

call sys.pause(504);
select * from sys.queue where status <> 'finished';
tagsessionidusernamestartedstatusqueryfinishedmaxworkersfootprint
5041monetdb2022-09 (...)pausedselect * from longrunningquery\n;null81
5071monetdb2022-09 (...)runningselect * from sys.queue where status <> 'finished'\n;null81

Query 504 is paused. We can resume it.

call sys.resume(504);
select * from sys.queue where status <> 'finished';
tagsessionidusernamestartedstatusqueryfinishedmaxworkersfootprint
5041monetdb2022-09 (...)runningselect * from longrunningquery\n;null81
5091monetdb2022-09 (...)runningselect * from sys.queue where status <> 'finished'\n;null81

Query 504 is running. We can stop it running.

call sys.stop(504);
select * from sys.queue where status <> 'finished';
tagsessionidusernamestartedstatusqueryfinishedmaxworkersfootprint
5041monetdb2022-09 (...)abortedselect * from longrunningquery\n;null81
5111monetdb2022-09 (...)runningselect * from sys.queue where status <> 'finished'\n;null81

Query 504 execution has been aborted.