Lightweight, SQL oriented monitoring for MySQL
mycheckpoint is an open source monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.
It is SQL oriented: charts, reports and advances metrics are generated on the fly with views. There is no need for an external program to diagnose the data. In fact, mycheckpoint’s main duty is to to a one-time creation of a special purpose schema.
Recording of data is performed via a single INSERT command, which takes measurements from GLOBAL VARIABLES, GLOBAL STATUS, MASTER STATUS and SLAVE STATUS. There is no requirement that this measurement is taken by mycheckpoint itself.
Once the data is recorded, it is as easy to get complex presentations and diagnostics, such as charting and reporting, as it is to issue simple SELECT statements. You can even get HTML reporting pages (see sample). Detecting parameters change is also easily achieved.
Please refer to the documentation for further discussion, to download for obtaining mycheckpoint.
SELECT innodb_read_hit_percent, DML FROM sv_report_chart_hour;
Above: querying for Google Chart URLs for measured metrics
Below: querying for human readable hourly report
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
mycheckpoint is released under the BSD license.

