openark forge

Open source utilities

Generating human reports

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: 2010-02-10 08:00:00 to 2010-02-10 09:00:00. Period is 60 minutes (1.00 hours)
Uptime: 100% (Up: 379 days, 02:12:28 hours)

 Load average: 2.70
 CPU utilization: 17.8%
 Memory: 7470.1MB used out of 8177.3MB (Active: 6920.9MB)
 Swap: 4319.2MB used out of 8385.9MB

 innodb_buffer_pool_size: 4718592000 bytes (4500.0MB). Used: 100.0%
 Read hit: 99.92%
 Disk I/O: 15.07 reads/sec  22.89 flushes/sec
 Estimated log written per hour: 809.3MB
 Locks: 0.13/sec  current: 0

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

 SELECT:  82.97/sec  13.0%
 INSERT:  66.70/sec  10.5%
 UPDATE:  19.24/sec  3.0%
 DELETE:  16.95/sec  2.7%
 REPLACE: 0.00/sec  0.0%
 SET:     158.79/sec  24.9%
 COMMIT:  0.03/sec  0.0%
 slow:    0.02/sec  0.0% (slow time: 1sec)

 Full scan: 5.69/sec  6.9%
 Full join: 0.00/sec  0.0%
 Range:     0.84/sec  1.0%
 Sort merge passes: 0.00/sec

 Table locks waited:  0.00/sec  0.0%

 Table cache: 2048. Used: 88.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:             6.15/sec
 Created disk tables: 0.18/sec  2.8%

 Max connections: 200. Max used: 245  122.5%
 Connections: 2.67/sec
 Aborted:     0.07/sec  2.6%

 Thread cache: 32. Used: 68.8%
 Created: 0.02/sec

 Master status file number: 2241, position: 524487864
 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.

Powered by Wordpress and MySQL. Theme by