Skip to main content

System monitor

The MonetDB kernel maintains an active queue of all running 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, called sys.queue() or via system view sys.queue which selects from sys.queue().

sql>select * from sys.queue();
+------+-----------+----------+----------------------------+---------+----------------------------+----------+---------+--------+
| tag  | sessionid | username | started                    | status  | query                      | progress | workers | memory |
+======+===========+==========+============================+=========+============================+==========+=========+========+
|   50 |         1 | monetdb  | 2013-12-23 12:31:50.000000 | running | select * from sys.queue(); |        0 |       0 |      0 |
+------+-----------+----------+----------------------------+---------+----------------------------+----------+---------+--------+
1 tuple

The initial column 'tag' provides a key to each active query. For more information on the result columns see Query Catalog.

One can use the SQL functions sys.pause(tag#), sys.resume(tag#) and sys.stop(tag#) to control the execution of (long) running queries.

NOTE that sys.pause(tag#) and sys.stop(tag#) 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.

sql> select * from sys.queue;
+------+-----------+----------+----------------------------+---------+--------------------------+----------+---------+--------+
| tag  | sessionid | username | started                    | status  | query                    | progress | workers | memory |
+======+===========+==========+============================+=========+==========================+==========+=========+========+
|   50 |         1 | monetdb  | 2013-12-23 12:34:50.000000 | running | select * from sys.queue; |        0 |       0 |      0 |
+------+-----------+----------+----------------------------+---------+--------------------------+----------+---------+--------+
1 tuple

--- start a long running query in another session window

sql>select * from sys.queue;
+------+-----------+----------+----------------------------+---------+--------------------------------+----------+---------+--------+
| tag  | sessionid | username | started                    | status  | query                          | progress | workers | memory |
+======+===========+==========+============================+=========+================================+==========+=========+========+
|   56 |         2 |  monetdb | 2013-12-23 12:35:03.000000 | running | select                                                       |
:      :           :          :                            :         :  sum(l_extendedprice * l_discount) as revenue                :
:      :           :          :                            :         :  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;                                       :
|   57 |         1 | monetdb  | 2013-12-23 12:36:11.000000 | running | select * from sys.queue;       |        0 |       0 |      0 |
+------+-----------+----------+----------------------------+---------+--------------------------------+----------+---------+--------+
2 tuples

sql>call sys.pause(56);
sql>select * from sys.queue;
+------+-----------+----------+----------------------------+---------+--------------------------------+----------+---------+--------+
| tag  | sessionid | username | started                    | status  | query                          | progress | workers | memory |
+======+===========+==========+============================+=========+================================+==========+=========+========+
|   56 |         2 |  monetdb | 2013-12-23 12:35:03.000000 | paused  | select                                                       |
:      :           :          :                            :         :  sum(l_extendedprice * l_discount) as revenue                :
:      :           :          :                            :         :  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;                                       :
|   59 |         1 | monetdb  | 2013-12-23 12:37:21.000000 | running | select * from sys.queue;       |        0 |       0 |      0 |
+------+-----------+----------+----------------------------+---------+--------------------------------+----------+---------+--------+
2 tuples

sql>call sys.resume(56);
sql>select * from sys.queue;
+------+-----------+----------+----------------------------+---------+--------------------------------+----------+---------+--------+
| tag  | sessionid | username | started                    | status  | query                          | progress | workers | memory |
+======+===========+==========+============================+=========+================================+==========+=========+========+
|   56 |         2 |  monetdb | 2013-12-23 12:35:03.000000 | running | select                                                       |
:      :           :          :                            :         :  sum(l_extendedprice * l_discount) as revenue                :
:      :           :          :                            :         :  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;                                       :
|   62 |         1 | monetdb  | 2013-12-23 12:38:44.000000 | running | select * from sys.queue;       |        0 |       0 |      0 |
+------+-----------+----------+----------------------------+---------+--------------------------------+----------+---------+--------+
2 tuples

sql>call sys.stop(56);
sql>select * from sys.queue;
+------+-----------+----------+----------------------------+---------+--------------------------+----------+---------+--------+
| tag  | sessionid | username | started                    | status  | query                    | progress | workers | memory |
+======+===========+==========+============================+=========+==========================+==========+=========+========+
|   64 |         1 | monetdb  | 2013-12-23 12:39:55.000000 | running | select * from sys.queue; |        0 |       0 |      0 |
+------+-----------+----------+----------------------------+---------+--------------------------+----------+---------+--------+
1 tuple