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

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 --smtp-from=monitor@my-server-company.com --smtp-to=dba@my-server-company.com --smtp-host=mail.my-server-company.com 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.

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

mycheckpoint is released under the New BSD License.