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.
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.
- 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).
Human readable reports
- The sv_report_human_* views provide human readable reports. Refer to Generating human reports.