Skip to content

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

MySQL Monitoring mycheckpoint python March 25, 2010May 21, 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 --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.

http://code.openark.org/forge/mycheckpoint/documentation/quick-howto

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
4
Leave a Reply

4 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
2 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
mycheckpoint revision 118 is available | openark forge

[…] the full announcement on openark.org […]

Guest
jelder

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… Read more »

Author
shlomi

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

Author
shlomi

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

Regards

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.