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';
| tag | sessionid | username | started | status | query | finished | maxworkers | footprint |
|---|---|---|---|---|---|---|---|---|
| 502 | 1 | monetdb | 2022-09-28 21:26:38.000000 | running | select * from sys.queue where status = 'running'\n; | null | 8 | 1 |
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';
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 becomes 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').
| Procedure | Description | Admin Only | Example |
|---|---|---|---|
| sys.pause(tag bigint) | pause the execution of a running query using its tag id. | False | call sys.pause(842); |
| sys.resume(tag bigint) | resume execution of a previouly paused query | False | call sys.resume(842); |
| sys.stop(tag bigint) | abort execution of a running or paused query | False | call sys.stop(842); |
| sys.pause(tag bigint, username string) | pause the execution of a running query from a specific user, using its tag id | True | call sys.pause(842, 'user1'); |
| sys.resume(tag bigint, username string) | resume execution of a previouly paused query from a specific user | True | call sys.resume(842, 'user1'); |
| sys.stop(tag bigint, username string) | abort execution of a running or paused query started by a specific user | True | call sys.stop(842, 'user1'); |
sys.pause(), sys.resume() and sys.stop()--- start a long running query
select * from longrunningquery;
select * from sys.queue where status <> 'finished';
| tag | sessionid | username | started | status | query | finished | maxworkers | footprint |
|---|---|---|---|---|---|---|---|---|
| 504 | 1 | monetdb | 2022-09 (...) | running | select * from longrunningquery\n; | null | 8 | 1 |
| 505 | 1 | monetdb | 2022-09 (...) | running | select * from sys.queue where status <> 'finished'\n; | null | 8 | 1 |
Query 504 is a long running query. We can pause it.
call sys.pause(504);
select * from sys.queue where status <> 'finished';
| tag | sessionid | username | started | status | query | finished | maxworkers | footprint |
|---|---|---|---|---|---|---|---|---|
| 504 | 1 | monetdb | 2022-09 (...) | paused | select * from longrunningquery\n; | null | 8 | 1 |
| 507 | 1 | monetdb | 2022-09 (...) | running | select * from sys.queue where status <> 'finished'\n; | null | 8 | 1 |
Query 504 is paused. We can resume it.
call sys.resume(504);
select * from sys.queue where status <> 'finished';
| tag | sessionid | username | started | status | query | finished | maxworkers | footprint |
|---|---|---|---|---|---|---|---|---|
| 504 | 1 | monetdb | 2022-09 (...) | running | select * from longrunningquery\n; | null | 8 | 1 |
| 509 | 1 | monetdb | 2022-09 (...) | running | select * from sys.queue where status <> 'finished'\n; | null | 8 | 1 |
Query 504 is running. We can stop it running.
call sys.stop(504);
select * from sys.queue where status <> 'finished';
| tag | sessionid | username | started | status | query | finished | maxworkers | footprint |
|---|---|---|---|---|---|---|---|---|
| 504 | 1 | monetdb | 2022-09 (...) | aborted | select * from longrunningquery\n; | null | 8 | 1 |
| 511 | 1 | monetdb | 2022-09 (...) | running | select * from sys.queue where status <> 'finished'\n; | null | 8 | 1 |
Query 504 execution has been aborted.