Announcing mycheckpoint: lightweight, SQL oriented monitoring for MySQL

I’m proud to announce mycheckpoint, a monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.

mycheckpoint is a different kind of monitoring tool. It leaves the power in the user’s hand. It’s power is not with script-based calculations of recorded data. It’s with the creation of a view hierarchy, which allows the user to access computed metrics directly.

mycheckpoint is needed first, to deploy a monitoring schema. It may be needed next, so as to INSERT recorded data (GLOBAL STATUS, GLOBAL VARIABLES, MASTER STATUS, SLAVE STATUS) — but this is just a simple INSERT; anyone can do that, even another monitoring tool.

It is then that you do not need it anymore: everything is laid at your fingertips. Consider:

SELECT innodb_read_hit_percent, DML FROM sv_report_chart_hour;

mycheckpoint provides the views which take raw data (just innodb_buffer_pool_read_requests, com_select, innodb_buffer_pool_size, table_open_cache, seconds_behind_master etc.) and generate Google Charts URLs, HTML reports, human readable reports, or otherwise easily accessible data.

Data is provided in different time resolutions:

• Per sampling
• Per hour aggregated data
• Per day aggregated data

It is thus easy to get a fine grained or a daily overview of your status. In fact, the SQL-generated HTML report lays them all together.

[Read more on generating Google Charts and HTML reports]

It is more about data accessibility

Charts are cool to look at, but they are not useful for detailed analysis. The user is free to ask anything of the supporting views:

I want to see the average number of SELECT queries per second in the last 5 hours:

mysql> SELECT ts, com_select_psec FROM sv_hour ORDER BY id DESC LIMIT 5;
+---------------------+-----------------+
| ts                  | com_select_psec |
+---------------------+-----------------+
| 2009-11-09 11:00:00 |          294.17 |
| 2009-11-09 10:00:00 |          198.37 |
| 2009-11-09 09:00:00 |          151.29 |
| 2009-11-09 08:00:00 |           90.06 |
| 2009-11-09 07:00:00 |           82.98 |
+---------------------+-----------------+

Hmm. Seems like too many SELECTs in the last hour.

Unrelated, is the InnoDB buffer pool being utilized well?

mysql> SELECT ts, innodb_buffer_pool_used_percent, innodb_read_hit_percent
       FROM sv_report_sample
       ORDER BY id DESC LIMIT 5;
+---------------------+---------------------------------+-------------------------+
| ts                  | innodb_buffer_pool_used_percent | innodb_read_hit_percent |
+---------------------+---------------------------------+-------------------------+
| 2009-11-09 12:35:01 |                           100.0 |                   99.93 |
| 2009-11-09 12:30:01 |                           100.0 |                   99.89 |
| 2009-11-09 12:25:01 |                           100.0 |                   99.60 |
| 2009-11-09 12:20:01 |                           100.0 |                   99.14 |
| 2009-11-09 12:15:01 |                           100.0 |                   98.99 |
+---------------------+---------------------------------+-------------------------+

Apparently, innodb_buffer_pool_size could use some more memory.

When did we have excessive amount of writes?

mysql> SELECT ts, com_insert_psec
       FROM sv_hour
       WHERE com_insert_psec > (SELECT 2*AVG(com_insert_psec) FROM sv_hour);
+---------------------+-----------------+
| ts                  | com_insert_psec |
+---------------------+-----------------+
| 2009-10-27 00:00:00 |          133.66 |
| 2009-10-28 00:00:00 |          121.79 |
| 2009-10-29 00:00:00 |          138.88 |
| 2009-10-30 00:00:00 |          120.79 |
| 2009-10-31 00:00:00 |          131.78 |
+---------------------+-----------------+

Something is going on on those midnights!

[Read more on querying for data]

Human reports

But while we’re at it: it’s nice to let the user the ability to ask around; but why not provide with some niceties? Special views aggregate monitored data to present human readable reports:

SELECT report FROM sv_report_human_hour ORDER BY id DESC LIMIT 1,1 \G

Report period: 2009-11-08 14:00:00 to 2009-11-08 15:00:00. Period is 60 minutes (1.00 hours)
Uptime: 100.0% (Up: 285 days, 07:17:28 hours)

InnoDB:
    innodb_buffer_pool_size: 4718592000 bytes (4500.0MB). Used: 100.0%
    Read hit: 99.75%
    Disk I/O: 83.00 reads/sec  20.33 flushes/sec
    Estimated log written per hour: 797.0MB
    Locks: 0.32/sec  current: 0

MyISAM key cache:
    key_buffer_size: 33554432 bytes (32.0MB). Used: 18.3%
    Read hit: 99.7%  Write hit: 100.0%

DML:
    SELECT:  149.88/sec  34.1%
    INSERT:  55.84/sec  12.7%
    UPDATE:  17.55/sec  4.0%
    DELETE:  20.68/sec  4.7%
    REPLACE: 0.00/sec  0.0%
    SET:     170.05/sec  38.7%
    COMMIT:  0.02/sec  0.0%
    slow:    2.28/sec  0.5% (slow time: 2sec)

Selects:
    Full scan: 8.37/sec  5.6%
    Full join: 0.00/sec  0.0%
    Range:     40.45/sec  27.0%
    Sort merge passes: 0.00/sec

Locks:
    Table locks waited:  0.00/sec  0.0%

Tables:
    Table cache: 2048. Used: 26.5%
    Opened tables: 0.00/sec

Temp tables:
    Max tmp table size:  67108864 bytes (64.0MB)
    Max heap table size: 67108864 bytes (64.0MB)
    Created:             7.15/sec
    Created disk tables: 0.51/sec  7.1%

Connections:
    Max connections: 200. Max used: 245  122.5%
    Connections: 3.31/sec
    Aborted:     0.07/sec  2.1%

Threads:
    Thread cache: 32. Used: 50.0%
    Created: 0.06/sec

Replication:
    Master status file number: 1494, position: 404951764
    Relay log space limit: 10737418240, used: N/A  (N/A%)
    Seconds behind master: N/A
    Estimated time for slave to catch up: N/A seconds (N/A days, N/A hours)  ETA: N/A

The above is a SQL-generated report. The view’s CREATE statement is ugly, trust me! But the user needs not be aware of this — all is generated behind the scenes. Since it is SQL-generated, the report is not actually stored anywhere; and one can generate reports for as long as data exists. A three months old data can still be evaluated and used to produce a fresh report.

The above report resembles the ever-so-useful mysqlreport by Daniel Nichter. I have drawn many ideas from this tool.

[Read more on generating human readable reports]

Tracking change of parameters

Since mycheckpoint records server variables, it’s easy enough to detect a change in variable. Did you dynamically change a variable and forgot to update my.cnf? Were you baffled when the server restarted and everything started behaving differently? Just ask away:

mysql> SELECT * FROM sv_param_change;
+---------------------+-----------------+-----------+-----------+
| ts                  | variable_name   | old_value | new_value |
+---------------------+-----------------+-----------+-----------+
| 2009-11-04 13:00:01 | max_connections |       500 |       200 |
+---------------------+-----------------+-----------+-----------+

Doh! That’s how we got 122.5% max used connections!

[Read more on detecting parameters change]

Additional notes

Just recently, a somewhat similar project, sar-sql was announced by Gerry Narvaja (Ex-Pythian). When sar-sql (formerly MySAR) was announced, my own code and ideas were at late stages. I’ve pondered about this, and have decided to go on with a separate project. While both make use of the same ideas, the implementation is quite different.

With proper setup, mycheckpoint can be used as an add-on to other monitoring tools. I currently have no plans for doing that, but time will tell.

I believe the ease of access to monitored data is a compelling reason to try out mycheckpoint. Please visit the mycheckpoint home page, read through the documentation, and take some downloads with you!

As always, community feedback is welcome. Feel free to throw in valueable feedback, bug reports or even a couple of tomatoes!

mycheckpoint is released under the BSD license.