Skip to content

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

MySQL Graphs Monitoring mycheckpoint python scripts June 4, 2010June 4, 2010

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.

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

###
1
Leave a Reply

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

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

Subscribe
Notify of
mycheckpoint revision 132 is available | openark forge

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

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.