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.