{"id":203,"date":"2009-11-08T12:28:25","date_gmt":"2009-11-08T10:28:25","guid":{"rendered":"http:\/\/code.openark.org\/forge\/?page_id=203"},"modified":"2010-11-08T12:29:39","modified_gmt":"2010-11-08T10:29:39","slug":"general-schema-structure","status":"publish","type":"page","link":"https:\/\/code.openark.org\/forge\/mycheckpoint\/documentation\/general-schema-structure","title":{"rendered":"General schema structure"},"content":{"rendered":"<p>There is just one data table in <em>mycheckpoint<\/em>&#8216;s schema: <strong>status_variables<\/strong>. This table includes hundreds of columns, which include most of the <strong>GLOBAL STATUS<\/strong>, <strong>GLOBAL VARIABLES<\/strong>, <strong>MASTER STATUS<\/strong> and <strong>SLAVE STATUS<\/strong> metrics.<\/p>\n<p>Apart from a helper <strong>numbers<\/strong> table, the rest of the schema consists of views only. The following image presents the schema&#8217;s layout:<\/p>\n<blockquote>\n<pre class=\"mceTemp\"><dl id=\"attachment_230\" class=\"wp-caption alignnone\" style=\"width: 950px;\">\r\n<dt class=\"wp-caption-dt\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-233\" title=\"mycheckpoint-schema-layout\" src=\"http:\/\/code.openark.org\/forge\/wp-content\/uploads\/2009\/11\/mycheckpoint-schema-layout2.png\" alt=\"mycheckpoint-schema-layout\" width=\"940\" height=\"580\" srcset=\"https:\/\/code.openark.org\/forge\/wp-content\/uploads\/2009\/11\/mycheckpoint-schema-layout2.png 940w, https:\/\/code.openark.org\/forge\/wp-content\/uploads\/2009\/11\/mycheckpoint-schema-layout2-300x185.png 300w\" sizes=\"auto, (max-width: 940px) 100vw, 940px\" \/>\r\n<\/dt>\r\n<\/dl>\r\n<\/pre>\n<\/blockquote>\n<p><strong>sv_diff<\/strong> is the major view: it joins <strong>status_variables<\/strong> against itself, to find the difference in all values.<\/p>\n<p>The rest of the views all &#8220;inherit&#8221; from <strong>sv_diff<\/strong> in one way or another.<\/p>\n<h4>The three diagnostics periods<\/h4>\n<p>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.<\/p>\n<ul>\n<li><em>Per-sample views<\/em> relate to changes and status as reflected by every sampling (recording) of status. these are the most fine-grained.<\/li>\n<li><em>Per-hour views<\/em> are aggregations of samples in same hour. It is best when there&#8217;s enough samples per hour. These views provide good status overlook.<\/li>\n<li><em>Per-day views<\/em> are aggregations of samples in same day. They provide grand scale diagnostics, and reflect long running changes.<\/li>\n<\/ul>\n<h4>Basic views<\/h4>\n<p>The <strong>sv_*<\/strong> (* being sample\/hour\/day) views provide with the basic metrics required to build more complicated diagnostics. They provide with:<\/p>\n<ul>\n<li>Original measured values (e.g. <strong>com_select<\/strong>)<\/li>\n<li>Differential values (e.g. number of <strong>com_select<\/strong> within sampling\/hour\/day period) in the form of _diff columns (e.g. <strong>com_select_diff<\/strong>)<\/li>\n<li>Per-second values (e.g. <strong>com_select_psec<\/strong>) 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.<\/li>\n<\/ul>\n<h4>Basic reporting views<\/h4>\n<p>The <strong>sv_report_*<\/strong> (* being sample\/hour\/day) views provide with selected metrics.<\/p>\n<ul>\n<li>Some of these metrics are raw values (e.g. <strong>innodb_buffer_pool_size<\/strong> or <strong>opened_tables<\/strong>).<\/li>\n<li>Others are basic calculated (e.g. <strong>com_select_psec<\/strong>, discussed above).<\/li>\n<li>Yet others are carefully calculated metrics (e.g. 100 &#8211; 100*<strong>innodb_buffer_pool_pages_free<\/strong>\/<strong>innodb_buffer_pool_pages_total<\/strong> AS <strong>innodb_buffer_pool_used_percent<\/strong>).<\/li>\n<\/ul>\n<p>These views do not provide all possible metrics, and are used to present the most usefull, important and interesting metrics.<\/p>\n<h4>Chart reporting views<\/h4>\n<ul>\n<li>The <strong>sv_report_chart_*<\/strong> views present with Google chart URLs.<\/li>\n<li>The <strong>sv_report_chart_24_7<\/strong> view presents with 24\/7 aggregation charts.<\/li>\n<\/ul>\n<p>Refer to <a href=\"generating-google-charts\">Generating Google charts<\/a>.<\/p>\n<h4>HTML reporting<\/h4>\n<ul>\n<li>The <strong>sv_report_html<\/strong> view aggregates the three charting views to present an HTML report page (see <a href=\"http:\/\/mycheckpoint.googlecode.com\/svn\/trunk\/doc\/html\/sample\/http\/mcp_sql00\/sv_report_html\">sample<\/a>).<\/li>\n<li>The <strong>sv_report_html_brief<\/strong> view presents with some per-sample charts (see <a href=\"http:\/\/mycheckpoint.googlecode.com\/svn\/trunk\/doc\/html\/sample\/http\/mcp_sql00\/sv_report_html_brief\">sample<\/a>).<\/li>\n<\/ul>\n<p>See <a href=\"generating-html-reports\">Generating <\/a><a href=\"generating-html-reports\">HTML reports<\/a>.<\/p>\n<h4>Human readable reports<\/h4>\n<ul>\n<li>The <strong>sv_report_human_*<\/strong> views provide human readable reports. Refer to <a href=\"generating-human-reports\">Generating human reports<\/a>.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>There is just one data table in mycheckpoint&#8216;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&#8217;s layout: sv_diff is [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":147,"menu_order":-5,"comment_status":"open","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-203","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/203","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/comments?post=203"}],"version-history":[{"count":12,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/203\/revisions"}],"predecessor-version":[{"id":610,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/203\/revisions\/610"}],"up":[{"embeddable":true,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/147"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/media?parent=203"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}