mycheckpoint (Rev. 118): alerts, email notifications and more

March 25, 2010

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

  • Conditional alerts
  • Email notifications
  • Revised HTML reports, including 24/7 reports.
  • Updated documentation

With this new revision mycheckpoint turns into a monitoring solution for MySQL. One can now:

  • Store measure metrics
  • Query for raw, aggregated or digested metrics
  • Generate charts for selected metrics
  • View HTML reports for selecetd metrics
  • Define alerts conditions, query for pending alerts
  • Be notified via email on raised or resolved alerts.

Conditional alerts

mycheckpoint is SQL oriented. As such, it allows for creation of alert conditions, which are nothing more than SQL conditions.

For example, we wish to raise an alerts when the slave stops replicating (just ping us with an email one this happens):

INSERT INTO alert_condition (condition_eval, description, alert_delay_minutes)
  VALUES ('seconds_behind_master IS NULL', 'Slave not replicating', 0);

Or is too far behind (but since we do maintenance work during the night, it's OK on those hours). We only want to be notified if this goes on for 10 minutes:

INSERT INTO alert_condition (condition_eval, description, alert_delay_minutes)
  VALUES ('(seconds_behind_master > 60) AND (HOUR(ts) NOT BETWEEN 2 AND 4)', 'Slave lags too far behind', 10);

We want to be notified when the datadir mount point disk quota exceeds 95% usage. Oh, and please keep nagging us about this, as long as it is unresolved:

INSERT INTO alert_condition (condition_eval, description, repetitive_alert)
  VALUES ('os_datadir_mountpoint_usage_percent > 95', 'datadir mount point is over 95%', 1);

There's much more to alert conditions. You can generate a pending alerts report, get a textual presentation of raised and pending alerts, view the query which determines what alerts are currently raised, and more.

Read more on the alerts documentation page.

Email notifications

Introducing email notifications, mycheckpoint now:

  • Sends email notification on alert conditions meeting. See sample email screenshot.
  • Sends email notification when it is unable to access the database.
  • Sends report via mail. Currently only HTML brief report is supported. Report is attached as HTML file in email message.

Alert notifications are automatically sent by mail (once SMTP configuration is in place, see following) when an alert is raised (alert condition becomes true) or resolved (alert condition turns false).

Email notifications require simple configuration for SMTP host, SMTP-from-address, SMTP-to-address. These can be made in the defaults file (revised), or through the command line. The following example shows how one can manually send an HTML brief report:

mycheckpoint --defaults-file=/etc/mycheckpoint.cnf email_brief_report

One should generally set up these parameters in the configuration file (aka defaults file) and forget all about it. mycheckpoint now has a default for the defaults file, which is /etc/mycheckpoint.cnf.

Read more on the emails documentation page.

Revised HTML reports

  • The brief HTML reports has been updated, see sample.
  • An HTML 24/7 report as been added, see sample. This report shows the distribution of popular metrics throughout the weekdays and hours.

Full HTML reports remain slow to load. I'm putting some work into this, but I'm not sure I can work around the optimizer's limitations of using indexes for GROUPing through views.

Updated documentation

The documentation has been revised, with more details put into the pages. Since mycheckpoint gains more and more features, I saw fit to write a Quick HOWTO page which gets you up to speed, no fuss around, with mycheckpoint's usage and features.

Read the mycheckpoint Quick HOWTO here.

Future plans

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

  • Custom monitoring + notifications. See my earlier post.
  • Monitoring InnoDB Plugin & XtraDB status.
  • PROCESSLIST dump on alerts.
  • Interactive charts. See my earlier post.
  • A proper man page...

Try it out

Try out mycheckpoint. It's a different kind of monitoring solution. It does not require to to have a web server or complicated dependencies. To the experienced DBA it can further provide with valuable, raw or digested information in the form of SQL accessible data. I have used it to find anomalies in passing months, doing SQL search for periods of time where several conditions applied -- it really gives you some extra power.

mycheckpoint is released under the New BSD License.
  • Pingback: mycheckpoint revision 118 is available | openark forge()

  • This looks really exciting but I've hit a snag. Using r118 against MySQL 5.0.51a on Ubuntu 8.04, I getting a syntax error when I try to deploy:

    root@db01:~# mycheckpoint --skip-disable-bin-log --skip-check-replication --defaults-file=/etc/mycheckpoint
    .cnf deploy
    (1140, 'Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause')
    -- Make sure `mycheckpoint` schema exists, e.g.
    -- CREATE DATABASE `mycheckpoint`
    -- Make sure the user has ALL PRIVILEGES on the `mycheckpoint` schema. e.g.
    -- GRANT ALL ON `mycheckpoint`.* TO 'my_user'@'my_host' IDENTIFIED BY 'my_password'
    -- The user will have to have the SUPER privilege in order to disable binary logging
    -- - Otherwise, use --skip-disable-bin-log (but then be aware that slaves replicate this server's status)
    -- In order to read master and slave status, the user must also be granted with REPLICATION CLIENT or SUPER privileges
    -- - Otherwise, use --skip-check-replication

  • @Jelder,

    Thank you.
    This has already been reported.

    It relates to a bug in MySQL, already fixed in later versions (sorry for not specifying the bug number right now). I'm still wondering if there is a workaround.

  • @Jelder,

    I'd appreciate if you could now test with revision 119:
    Downloads page.

    I believe it should now be fixed.
    I would also greatly appreciate if you could report back on the Issue 3 page.


Powered by Wordpress and MySQL. Theme by