Monitoring

Monitoring giulia Mon, 02/24/2020 - 10:42

 

 

Systems Monitor

Systems Monitor giulia Mon, 05/04/2020 - 17:17

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 .

One can use the SQL functions 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.

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 long running 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);
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 | 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.resume(56);
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;                                       :
|   65 | monetdb | 2013-12-23 12:36:03.000000 | null     | null | running | 1349763@0 | select * from sys.queue;                                    |
+------+---------+----------------------------+----------+------+---------+-----------+-------------------------------------------------------------+
2 tuples (12.320ms)
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)

Table Statistics

Table Statistics giulia Mon, 05/04/2020 - 17:15

Statistics gathered over tables in the database can be found in the system table sys.statistics. This table is initially empty and explicitly filled or updated using the ANALYZE command:

ANALYZE schemaname [ '.' tablename [ '('columnname , ...')' ] ]
      [ SAMPLE size ]
      [ MINMAX ]

You can gather statistics for a) all tables in a schema or b) all columns of a specific table or c) a list of specific columns of one table. You can only gather statistics of tables with real physical column data, so not for views.

Since statistics gathering involves accessing and profiling all table columns data, it can take considerable time, especially if the tables are large or you analyze all tables in a schema. You may consider to add an optional MINMAX which directs exclusion of the expensive unique count operation. Likewise, a SAMPLE size can be used to provide a quick, but imprecise impression.

You can remove statistics data via SQL command: DELETE FROM sys.statistics. For example removing statistics for table: my_schema.my_table:

DELETE FROM sys.statistics
 WHERE column_id IN (SELECT c.id FROM sys.columns c
 WHERE c.table_id IN (SELECT t.id FROM sys.tables t
 WHERE t.schema_id IN (SELECT s.id FROM sys.schemas s
 WHERE s.name = 'my_schema') AND t.name = 'my_table'));