openark forge

Open source utilities
 

Custom monitoring

As of revision 132, mycheckpoint allows for custom monitoring. It provides:

  • User defined queries (under certain restrictions), which are executed by mycheckpoint.
  • Charts based on said custom queries.
  • HTML reports for custom queries, via sv_custom_html_brief and sv_custom_html.
  • Alerts for custom query results.

mycheckpoint stored the results of custom queries in the same status_variables table, used to store all monitored data. Therefore, charting and alerting comes out of the box for custom queries. However, mycheckpoint does specifically store and provide:

  • Custom query result’s value (stored).
  • Custom query execution time (stored).
  • Custom query result’s rate (derived by views).

mycheckpoint executes all custom queries right after it completes its standard parameters, status variables, replication data & OS data collection.

Creating custom queries

Custom queries are stored in the custom_query table. To add custom queries, INSERT rows into that table, as in the following example:

INSERT INTO
 custom_query (custom_query_id, enabled, query_eval, description, chart_type, chart_order)
 VALUES (0, 1, 'SELECT COUNT(*) FROM store.shopping_cart WHERE is_pending=1', 'Number of pending carts', 'value', 0);

INSERT INTO
 custom_query (custom_query_id, enabled, query_eval, description, chart_type, chart_order)
 VALUES (1, 1, 'SELECT MAX(shopping_cart_id) FROM store.shopping_cart', 'Created carts rate', 'value_psec', 0);

The columns of the custom_query table are:

  • custom_query_id: Unique identifier. This is not an AUTO_INCREMENT: you choose the id; you’ll reference it later on.
  • enabled: 1 for enabled, 0 for disabled (query will not get executed), value stored is NULL.
  • query_eval: The query to be executed. All tables must be fully qualified with database (Schema) scope. The query must return exactly one row, with exactly one column, which is a type of INTEGER.
  • description: A human readable explanation of the nature of the query. Used as title for custom charts.
  • chart_type: How to graphically represent the custom results. This is a type of enum(‘value’,'value_psec’,'time’).
    • ‘value’ means charting the query result’s value;
    • ‘value_psec’ charts the change per second of the value;
    • ‘time’ charts the time it took to execute the query (regardless of the result).
  • chart_order: chart position within the HTML reports (works as of revision 160).

Custom query results storage

The results are stored within the status_variables table. Each custom query has two columns in this table:

  • custom_X: value of the query result
  • custom_X_time: time it took to execute the query

Where ‘X’ stands for the custom_query_id.

The status_variables table is in turn used by the various mycheckpoint views. Therefore, an additional column is gained, called custom_X_psec (just as with all other columns). For example:

mysql> SELECT id, ts, created_tmp_tables_psec, custom_0, custom_1_psec FROM sv_sample WHERE ts >= NOW() - INTERVAL 1 HOUR;
+-------+---------------------+-------------------------+----------+---------------+
| id    | ts                  | created_tmp_tables_psec | custom_0 | custom_1_psec |
+-------+---------------------+-------------------------+----------+---------------+
| 50730 | 2010-05-21 19:05:01 |                   16.64 |      448 |          3.02 |
| 50731 | 2010-05-21 19:10:02 |                   20.97 |       89 |          1.73 |
| 50732 | 2010-05-21 19:15:01 |                   15.70 |      367 |          3.56 |
| 50733 | 2010-05-21 19:20:01 |                   18.32 |       54 |          1.43 |
| 50734 | 2010-05-21 19:25:01 |                   16.42 |       91 |          1.96 |
| 50735 | 2010-05-21 19:30:02 |                   21.93 |      233 |          2.11 |
| 50736 | 2010-05-21 19:35:02 |                   14.58 |      176 |          1.91 |
| 50737 | 2010-05-21 19:40:01 |                   21.61 |      168 |          1.93 |
| 50738 | 2010-05-21 19:45:01 |                   16.05 |      241 |          2.44 |
| 50739 | 2010-05-21 19:50:01 |                   19.70 |       46 |          1.19 |
| 50740 | 2010-05-21 19:55:01 |                   15.85 |      177 |          2.28 |
| 50741 | 2010-05-21 20:00:01 |                   19.04 |        8 |          0.82 |
+-------+---------------------+-------------------------+----------+---------------+

Charting

Since custom values are stored in the status_variables table, charting works for custom values just as it works for normal variables:

mysql> SELECT custom_1_psec FROM sv_report_chart_sample\G

HTML reports

In the above chart the title is fuzzy, and this is due to the nature of mycheckpoint being mostly ignorant of the data contained. However, mycheckpoint provides with special HTML report views to visualize custom query charts. The HTML reports bautify the charts, making them more verbose. HTML reports come in two flavors:

  • Brief reports, featuring last 24 hours, as in the example above. These are handled by the sv_custom_html_brief view.
  • Full reports, featuring last 24 hours, last 10 days, known history. These take longer to generate, and are handled by the sv_custom_html view.

See this sample HTML report. It was generated by issuing:

SELECT html FROM sv_custom_html_brief;

Alerts

Since custom query results are stored just as all other parameters, defining and generating alert conditions for custom values is as with normal variables. For example:

INSERT INTO alert_condition (condition_eval, description, alert_delay_minutes)
  VALUES ('custom_1_psec = 0', 'No new shopping carts detected in last 10 minutes', 10);

See the Alerts page for more on alert conditions.

 
Powered by Wordpress and MySQL. Theme by openark.org