The amount of server variables and status variables is overwhelming. Some of them are more important than others. Some are only relevant in relation to others.

mycheckpoint offers these views for generating human readable reports, which summarize the most interesting metrics: sv_report_human_sample, sv_report_human_hour, sv_report_human_day.

All three have the exact same format. They differ in the period of report.

• sv_report_human_sample generates reports on a sample basis.
• sv_report_human_hour generates reports on hour aggregated data.
• sv_report_human_day generates reports on day-aggregated data.

Recall that nothing but raw data is stored by mycheckpoint. The human reports compute a textual page based on stored measurements. As such, it is possible to ask for any report, from any time period, for as long as recorder data from that period exists.

The views generate report in ascending timestamps. Their structure being:

mysql> DESC sv_report_human_hour ;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | YES  |     | NULL    |       |
| ts     | datetime | YES  |     | NULL    |       |
| report | longblob | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+

It is possible to get last sample’s report by:

SELECT report FROM sv_report_human_sample ORDER BY id DESC LIMIT 1 \G

Previous hour’s report by:

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

Last week’s report by:

SELECT report FROM sv_report_human_day WHERE ts = DATE(NOW()) - INTERVAL 7 DAY\G

And all 24 of yesterday’s hourly reports by:

SELECT report FROM sv_report_human_hour WHERE ts >= DATE(NOW()) - INTERVAL 1 DAY AND ts < DATE(NOW())\G

Sample report

The structure of the human report is similar to that of the excellent mysqlreport. It is divided into distinct categories, and provides with hopefully clear and useful data.

mysql> SELECT report FROM sv_report_human_hour ORDER BY id DESC LIMIT 1,1 \G
*************************** 1. row ***************************
report:
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 MySQL server monitored in the above report is a master, and does not provide, therefore, slave information.