{"id":167,"date":"2009-11-08T09:19:55","date_gmt":"2009-11-08T07:19:55","guid":{"rendered":"http:\/\/code.openark.org\/forge\/?page_id=167"},"modified":"2010-02-10T09:17:35","modified_gmt":"2010-02-10T07:17:35","slug":"generating-human-reports","status":"publish","type":"page","link":"https:\/\/code.openark.org\/forge\/mycheckpoint\/documentation\/generating-human-reports","title":{"rendered":"Generating human reports"},"content":{"rendered":"<p>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.<\/p>\n<p><em>mycheckpoint<\/em> offers these views for generating human readable reports, which summarize the most interesting metrics: <strong>sv_report_human_sample<\/strong>, <strong>sv_report_human_hour<\/strong>, <strong>sv_report_human_day<\/strong>.<\/p>\n<p>All three have the exact same format. They differ in the period of report.<\/p>\n<ul>\n<li><strong>sv_report_human_sample<\/strong> generates reports on a sample basis.<\/li>\n<li><strong>sv_report_human_hour<\/strong> generates reports on hour aggregated data.<\/li>\n<li><strong>sv_report_human_day<\/strong> generates reports on day-aggregated data.<\/li>\n<\/ul>\n<p>Recall that nothing but raw data is stored by <em>mycheckpoint<\/em>. 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.<\/p>\n<p>The views generate report in ascending timestamps. Their structure being:<\/p>\n<blockquote>\n<pre>mysql&gt; DESC sv_report_human_hour ;\r\n+--------+----------+------+-----+---------+-------+\r\n| Field\u00a0 | Type\u00a0\u00a0\u00a0\u00a0 | Null | Key | Default | Extra |\r\n+--------+----------+------+-----+---------+-------+\r\n| id\u00a0\u00a0\u00a0\u00a0 | int(11)\u00a0 | YES\u00a0 |\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ts\u00a0\u00a0\u00a0\u00a0 | datetime | YES\u00a0 |\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| report | longblob | YES\u00a0 |\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+--------+----------+------+-----+---------+-------+<\/pre>\n<\/blockquote>\n<p>It is possible to get last sample&#8217;s report by:<\/p>\n<blockquote>\n<pre>SELECT report FROM sv_report_human_sample ORDER BY id DESC LIMIT 1 \\G<\/pre>\n<\/blockquote>\n<p>Previous hour&#8217;s report by:<\/p>\n<blockquote>\n<pre>SELECT report FROM sv_report_human_hour ORDER BY id DESC LIMIT 1,1 \\G<\/pre>\n<\/blockquote>\n<p>Last week&#8217;s report by:<\/p>\n<blockquote>\n<pre>SELECT report FROM sv_report_human_day WHERE ts = DATE(NOW()) - INTERVAL 7 DAY\\G<\/pre>\n<\/blockquote>\n<p>And all 24 of yesterday&#8217;s hourly reports by:<\/p>\n<blockquote>\n<pre>SELECT report FROM sv_report_human_hour WHERE ts &gt;= DATE(NOW()) - INTERVAL 1 DAY AND ts &lt; DATE(NOW())\\G<\/pre>\n<\/blockquote>\n<h4>Sample report<\/h4>\n<p>The structure of the human report is similar to that of the excellent <a href=\"http:\/\/hackmysql.com\/mysqlreport\">mysqlreport<\/a>. It is divided into distinct categories, and provides with hopefully clear and useful data.<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT report FROM sv_report_human_hour ORDER BY id DESC LIMIT 1,1 \\G\r\n*************************** 1. row ***************************\r\nreport: Report period: 2010-02-10 08:00:00 to 2010-02-10 09:00:00. Period is 60 minutes (1.00 hours)\r\nUptime: 100% (Up: 379 days, 02:12:28 hours)\r\n\r\nOS:\r\n Load average: 2.70\r\n CPU utilization: 17.8%\r\n Memory: 7470.1MB used out of 8177.3MB (Active: 6920.9MB)\r\n Swap: 4319.2MB used out of 8385.9MB\r\n\r\nInnoDB:\r\n innodb_buffer_pool_size: 4718592000 bytes (4500.0MB). Used: 100.0%\r\n Read hit: 99.92%\r\n Disk I\/O: 15.07 reads\/sec\u00a0 22.89 flushes\/sec\r\n Estimated log written per hour: 809.3MB\r\n Locks: 0.13\/sec\u00a0 current: 0\r\n\r\nMyISAM key cache:\r\n key_buffer_size: 33554432 bytes (32.0MB). Used: 18.2%\r\n Read hit: 99.8%\u00a0 Write hit: 100.0%\r\n\r\nDML:\r\n SELECT:\u00a0 82.97\/sec\u00a0 13.0%\r\n INSERT:\u00a0 66.70\/sec\u00a0 10.5%\r\n UPDATE:\u00a0 19.24\/sec\u00a0 3.0%\r\n DELETE:\u00a0 16.95\/sec\u00a0 2.7%\r\n REPLACE: 0.00\/sec\u00a0 0.0%\r\n SET:\u00a0\u00a0\u00a0\u00a0 158.79\/sec\u00a0 24.9%\r\n COMMIT:\u00a0 0.03\/sec\u00a0 0.0%\r\n slow:\u00a0\u00a0\u00a0 0.02\/sec\u00a0 0.0% (slow time: 1sec)\r\n\r\nSelects:\r\n Full scan: 5.69\/sec\u00a0 6.9%\r\n Full join: 0.00\/sec\u00a0 0.0%\r\n Range:\u00a0\u00a0\u00a0\u00a0 0.84\/sec\u00a0 1.0%\r\n Sort merge passes: 0.00\/sec\r\n\r\nLocks:\r\n Table locks waited:\u00a0 0.00\/sec\u00a0 0.0%\r\n\r\nTables:\r\n Table cache: 2048. Used: 88.5%\r\n Opened tables: 0.00\/sec\r\n\r\nTemp tables:\r\n Max tmp table size:\u00a0 67108864 bytes (64.0MB)\r\n Max heap table size: 67108864 bytes (64.0MB)\r\n Created:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6.15\/sec\r\n Created disk tables: 0.18\/sec\u00a0 2.8%\r\n\r\nConnections:\r\n Max connections: 200. Max used: 245\u00a0 122.5%\r\n Connections: 2.67\/sec\r\n Aborted:\u00a0\u00a0\u00a0\u00a0 0.07\/sec\u00a0 2.6%\r\n\r\nThreads:\r\n Thread cache: 32. Used: 68.8%\r\n Created: 0.02\/sec\r\n\r\nReplication:\r\n Master status file number: 2241, position: 524487864\r\n Relay log space limit: 10737418240, used: N\/A\u00a0 (N\/A%)\r\n Seconds behind master: N\/A\r\n Estimated time for slave to catch up: N\/A seconds (N\/A days, N\/A hours)\u00a0 ETA: N\/A\r\n<\/pre>\n<\/blockquote>\n<p>The MySQL server monitored in the above report is a master, and does not provide, therefore, slave information.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":147,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-167","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/167","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=167"}],"version-history":[{"count":8,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/167\/revisions"}],"predecessor-version":[{"id":215,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/167\/revisions\/215"}],"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=167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}