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.
One thought on “mycheckpoint (rev. 132): custom monitoring, custom charts, process list dump”