System monitor

System monitor mk Sun, 03/17/2013 - 20:39

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().

sql>select * from sys.queue();
+------+---------+----------------------------+----------+----------+---------+------------+----------------------------+
| qtag | user    | started                    | estimate | progress | status  | tag        | query                      |
+======+=========+============================+==========+==========+=========+============+============================+
|  902 | monetdb | 2013-03-30 10:23:39.000000 | null     |     null | running | 15168688@0 | select * from sys.queue(); |
+------+---------+----------------------------+----------+----------+---------+------------+----------------------------+
1 tuple (0.446ms)

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 'tag' column references the query log tables, provided this facility has been turned on. The initial column 'qtag' provides a key to each active query .

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.

sql> select * from sys.queue;
+------+---------+----------------------------+----------------------------+----------+---------+-----------+--------------------------+
| qtag | user    | started                    | estimate                   | progress | status  | tag       | query                    |
+======+=========+============================+============================+==========+=========+===========+==========================+
|   52 | monetdb | 2013-12-23 12:34:50.000000 | null                       |     null | running | 1349763@0 | select * from sys.queue; |
+------+---------+----------------------------+----------------------------+----------+---------+-----------+--------------------------+
1 tuple (2.242ms)

--- start a query in another window
sql>select * from sys.queue;
+------+---------+----------------------------+----------------------------+------+---------+-----------+-------------------------------------------------------------+
| qtag | user    | started                    | estimate                   | prog | status  | tag       | query                                                       |
:      :         :                            :                            : ress :         :           :                                                             :
+======+=========+============================+============================+======+=========+===========+=============================================================+
|   56 | monetdb | 2013-12-23 12:35:03.000000 | null                       | null | running | 1353354@0 | 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 | monetdb | 2013-12-23 12:35:10.000000 | null                       | null | running | 1349763@0 | select * from sys.queue;                                    |
+------+---------+----------------------------+----------------------------+------+---------+-----------+-------------------------------------------------------------+
2 tuples (1.404ms)
sql>call sys.pause(56);
------+---------+----------------------------+----------------------------+------+---------+-----------+-------------------------------------------------------------+
| qtag | user    | started                    | estimate                   | prog | status  | tag       | query                                                       |
:      :         :                            :                            : ress :         :           :                                                             :
+======+=========+============================+============================+======+=========+===========+=============================================================+
|   56 | monetdb | 2013-12-23 12:35:03.000000 | null                       | null | paused  | 1353354@0 | 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;                                       :
|   65 | monetdb | 2013-12-23 12:36:03.000000 | null                       | null | running | 1349763@0 | select * from sys.queue;                                    |
+------+---------+----------------------------+----------------------------+------+---------+-----------+-------------------------------------------------------------+
2 tuples (3.082ms)
sql>call sys.stop(56);
sql>select * from sys.queue;
+------+---------+----------------------------+----------------------------+----------+---------+-----------+--------------------------+
| qtag | user    | started                    | estimate                   | progress | status  | tag       | query                    |
+======+=========+============================+============================+==========+=========+===========+==========================+
|   67 | monetdb | 2013-12-23 12:36:22.000000 | null                       |     null | running | 1349763@0 | select * from sys.queue; |
+------+---------+----------------------------+----------------------------+----------+---------+-----------+--------------------------+
1 tuple (2.799ms)