There is just one data table in mycheckpoint‘s schema: status_variables. This table includes hundreds of columns, which include most of the GLOBAL STATUS, GLOBAL VARIABLES, MASTER STATUS and SLAVE STATUS metrics.

Apart from a helper numbers table, the rest of the schema consists of views only. The following image presents the schema’s layout:

sv_diff is the major view: it joins status_variables against itself, to find the difference in all values.

The rest of the views all “inherit” from sv_diff in one way or another.

The three diagnostics periods

The majority of views are divided into three categories: per-sample views, per-hour views and per-day views. The three views categories have the exact same structure.

• Per-sample views relate to changes and status as reflected by every sampling (recording) of status. these are the most fine-grained.
• Per-hour views are aggregations of samples in same hour. It is best when there’s enough samples per hour. These views provide good status overlook.
• Per-day views are aggregations of samples in same day. They provide grand scale diagnostics, and reflect long running changes.

Basic views

The sv_* (* being sample/hour/day) views provide with the basic metrics required to build more complicated diagnostics. They provide with:

• Original measured values (e.g. com_select)
• Differential values (e.g. number of com_select within sampling/hour/day period) in the form of _diff columns (e.g. com_select_diff)
• Per-second values (e.g. com_select_psec) which denote the change per second (differential values divided by elapsed seconds). Note that on per-hour and per-day views, this is not just the difference between the two extreme points, but the average of entire sampling values throughout the period.

Basic reporting views

The sv_report_* (* being sample/hour/day) views provide with selected metrics.

• Some of these metrics are raw values (e.g. innodb_buffer_pool_size or opened_tables).
• Others are basic calculated (e.g. com_select_psec, discussed above).
• Yet others are carefully calculated metrics (e.g. 100 – 100*innodb_buffer_pool_pages_free/innodb_buffer_pool_pages_total AS innodb_buffer_pool_used_percent).

These views do not provide all possible metrics, and are used to present the most usefull, important and interesting metrics.

Chart reporting views

• The sv_report_chart_* views present with Google chart URLs.
• The sv_report_chart_24_7 view presents with 24/7 aggregation charts.

Refer to Generating Google charts.

HTML reporting

• The sv_report_html view aggregates the three charting views to present an HTML report page (see sample).
• The sv_report_html_brief view presents with some per-sample charts (see sample).

See Generating HTML reports.

Human readable reports

• The sv_report_human_* views provide human readable reports. Refer to Generating human reports.