mycheckpoint (rev. 132): custom monitoring, custom charts, process list dump

Revision 132 of mycheckpoint has been released. New and updated in this revision:

Custom monitoring: monitoring & charting for user defined queries
HTML reports for custom monitoring
Process list dump upon alert notifications

Custom monitoring & charts

Custom monitoring allows the user to supply with a query, the results of which will be monitored.

That is, mycheckpoint monitors the status variables, replication status, OS metrics. But it cannot by itself monitor one’s application. Which is why a user may supply with such query as:

SELECT COUNT(*) FROM shopping_cart WHERE is_pending=1

Such a query will tell an online store how many customers are in the midst of shopping. There is no argument that this number is worth monitoring for. Given the above query, mycheckpoint will execute it per sample, and store the query’s result along with all sampled data, to be then aggregated by complex views to answer for:

What was the value per given sample?
What is the value difference for each sample?
What is the change per second, i.e. the rate?

mycheckpoint goes one step forward, and explicity records another metric:

How much time did it take to take that sample?

As another example, a query worth testing for rate:

SELECT MAX(shopping_cart_id) FROM shopping_cart

The above will provide with the last id. Assuming this is AUTO_INCREMENT, and assuming we’re on auto_increment_increment=1, two samples will allow us to get the number of created carts between those samples. Now, here’s a metric I’d like to read:

How many carts are created per second, for each hour of the day?

We get all these for free with mycheckpoint, which already does this analysis. All we need to provide is the query, and how we would like it to be visualized (visualization is optional, it is not the only way to diagnose monitored data) graphically:

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);

We can later query for these values, just like we do for normal monitored values:

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 |
+-------+---------------------+-------------------------+----------+---------------+

Of course, it is also possible to harness mycheckpoint‘s views power to generate charts:

mysql> SELECT custom_1_psec FROM sv_report_chart_sample\G

The rules are:

There can (currently) only be 18 custom queries.
The custom_query_id must range 0-17 (to be lifted soon).
A custom query must return with exactly one row, with exactly one column, which is a kind of integer.

Please read my earlier post on custom monitoring to get more background.

Custom monitoring HTML reports

Custom monitoring comes with a HTML reports, featuring requested charts. See a sample custom report.

In this sample report, a few queries are monitored for value (pending rentals, pending downloads) and a few for rates (downloads per second, emails per second etc.).

Custom 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.

The sample report was generated by issuing:

SELECT html FROM sv_custom_html_brief;

I won’t go into details here as for how this view generates the HTML code. There is a myriad of view dependencies, with many interesting tricks on the way. But do remember it’s just a view. You don’t need an application (not even mycheckpoint itself) to generate the report. All it takes is a query.

Processlist dump

When an alert notification fires (an email is prepared to inform on some alert condition), a processlist dump summary is taken and included in email report. It may be useful to understand why the slave is lagging, or exactly why there are so many active threads.

The dump summary presents the processlist much as you would see it on SHOW PROCESSLIST, but only lists the active threads, noting down how many sleeping processes there are (PS, thread & process are the same in the terminology of MySQL connections). An example dump looks like this:

PROCESSLIST summary:

     Id: 3
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 3168098
  State: Waiting for master to send event
   Info: NULL
-------

     Id: 4
   User: system user
   Host:
     db: prod_db
Command: Connect
   Time: 612
  State: Updating
   Info: UPDATE user SET is_offline = 1 WHERE id IN (50440010,50440011)
-------

     Id: 8916579
   User: prod_user
   Host: localhost
     db: prod_db
Command: Query
   Time: 1
  State: Sending data
   Info: INSERT IGNORE INTO archive.stat_archive (id, origin, path, ts, content
-------

     Id: 8916629
   User: mycheckpoint
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
-------
Sleeping: 3 processes

Future plans

Work is going on. These are the non-scheduled future tasks I see:

Monitoring InnoDB Plugin & XtraDB status.
Interactive charts. See my earlier post.
Monitoring for swap activity (Linux only).
Enhanced custom queries handling, including auto-deploy upon change of custom queries.
A proper man page.
Anything else that interests me.

Try it out

Try out mycheckpoint. It’s a different kind of monitoring solution. You will need basic SQL skills, and in return you’ll get a lot of power under your hands.

Download mycheckpoint here
Visit the project’s homepage
Browse the documentation
Report bugs

mycheckpoint is released under the New BSD License.