{"id":3066,"date":"2010-11-08T12:45:45","date_gmt":"2010-11-08T10:45:45","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=3066"},"modified":"2010-11-08T12:45:45","modified_gmt":"2010-11-08T10:45:45","slug":"mycheckpoint-rev-208-aggregation-tables-enhanced-charting-rpm-distribution","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/mycheckpoint-rev-208-aggregation-tables-enhanced-charting-rpm-distribution","title":{"rendered":"mycheckpoint (rev 208): aggregation tables, enhanced charting, RPM distribution"},"content":{"rendered":"<p>Revision <strong>208<\/strong> of <a href=\"..\/..\/forge\/mycheckpoint\">mycheckpoint<\/a>, a MySQL monitoring solution, has  been released. New and updated in this revision:<\/p>\n<ul>\n<li><strong>Aggregation tables<\/strong>: aggregated data makes for fast reports on previously slow queries.<\/li>\n<li><strong>Enhanced charting<\/strong>: interactive charts now present time stamps dynamically (see <a href=\"http:\/\/mycheckpoint.googlecode.com\/svn\/trunk\/doc\/html\/sample\/http\/mcp_sql00\/sv_report_html_brief\"><strong>demo<\/strong><\/a>); &#8220;Zoom in&#8221; charts are available (see <a href=\"http:\/\/mycheckpoint.googlecode.com\/svn\/trunk\/doc\/html\/sample\/http\/mcp_sql00\/zoom\/questions\"><strong>demo<\/strong><\/a>) on <em>mycheckpoint<\/em>&#8216;s HTTP server.<\/li>\n<li><strong>RPM distribution<\/strong>: a &#8220;noarch&#8221; RPM <em>mycheckpoint<\/em> build is now available.<\/li>\n<li>Initial work on formalizing test environment<\/li>\n<\/ul>\n<p><em>mycheckpoint<\/em> celebrates one year of existence!<\/p>\n<h4>Aggregation tables<\/h4>\n<p>I really wanted to avoid using these: everything was so more beautiful with one single dataset and dozens of supporting views (OK, the views themselves are hardly &#8220;beautiful&#8221;).<\/p>\n<p>However it was impossible (for my level of expertise) to optimize query performance what with all those views on per-hour and per-day aggregation. The GROUP BYs and the JOINs did not make it possible for condition pushdown (i.e. using MERGE algorithm) where desired.<\/p>\n<p>As result, mycheckpoint now manages aggregation tables: per-hour and per-day. The impact on sample taking is neglect able (making for two additional fast queries), but the impact on reading aggregated data is overwhelming. Generating a HTML full report could take a few minutes to complete. It now returns in no time. This makes charting more attractive, and allows for enhanced charting, such as zooming in on charts, as described following.<\/p>\n<p>Aggregation tables will automatically be created and retroactively populated upon using revision 208. There&#8217;s nothing special to do; be advised that for one single execution of <em>mycheckpoint<\/em>, many INSERT queries are going to be executed. Shouldn&#8217;t take more than a couple minutes on commodity hardware and a few months of history.<\/p>\n<p>It is possible to disable aggregation tables, or make for a complete rebuild of tables; by default, though, aggregation is ON.<\/p>\n<h4>Enhanced charting<\/h4>\n<p>Two enhancements here:<!--more--><\/p>\n<ol>\n<li>The interactive line charts already know how to update legend data as mouse hovers over them. Now they also present accurate date &amp; time. This provides with fully informative charts.<\/li>\n<li>As with other monitoring tools, it is possible to &#8220;zoom in&#8221; on a chart: zooming in will present any chart in &#8220;last 24 hours&#8221;, &#8220;last 10 days&#8221; and &#8220;complete history&#8221; views, magnified on screen. See <a href=\"http:\/\/mycheckpoint.googlecode.com\/svn\/trunk\/doc\/html\/sample\/http\/mcp_sql00\/zoom\/questions\"><strong>demo<\/strong><\/a> here.<\/li>\n<\/ol>\n<h4>RPM distribution<\/h4>\n<p>No excuse for this being so late, I know. But RPM distribution is now <a href=\"http:\/\/code.google.com\/p\/mycheckpoint\/\">available<\/a>. Yeepee!<\/p>\n<p>This is a <em>noarch<\/em> distribution, courtesy of Python&#8217;s <a href=\"http:\/\/docs.python.org\/distutils\/\">distutils<\/a>; you should be able to install the package on any RPM supporting platform. I have only tested in on CentOS; feedback is welcome.<\/p>\n<h4>Future plans<\/h4>\n<p>Work is going on. These are the non-scheduled future tasks I see:<\/p>\n<ul>\n<li>Monitoring InnoDB Plugin &amp; XtraDB status.<\/li>\n<li>A proper <em>man<\/em> page.<\/li>\n<li>Anything else that interests me &amp; the users.<\/li>\n<\/ul>\n<h4>Try it out<\/h4>\n<p>Try out <em>mycheckpoint<\/em>. It\u2019s a different kind of monitoring    solution. Simple monitoring (charting) is immediate. For more  interesting results you will need basic SQL skills, and in return you\u2019ll  get a lot   of power under your hands.<\/p>\n<ul>\n<li>Download mycheckpoint <a href=\"https:\/\/code.google.com\/p\/mycheckpoint\/\">here<\/a><\/li>\n<li>Visit the project\u2019s <a href=\"..\/..\/forge\/mycheckpoint\">homepage<\/a><\/li>\n<li>Browse the <a href=\"..\/..\/forge\/mycheckpoint\/documentation\">documentation<\/a><\/li>\n<li>Report <a href=\"https:\/\/code.google.com\/p\/mycheckpoint\/issues\/list\">bugs<\/a><\/li>\n<\/ul>\n<p><em>mycheckpoint<\/em> is released under the <a href=\"http:\/\/www.opensource.org\/licenses\/bsd-license.php\">New BSD  License<\/a>.<\/p>\n<p>Umm, I&#8217;ll repeat this last one: <em>mycheckpoint<\/em> is released under the <a href=\"http:\/\/www.opensource.org\/licenses\/bsd-license.php\">New BSD  License<\/a>. Still, and will continue to be. Thanks for the <a href=\"http:\/\/code.openark.org\/blog\/mysql\/openark-kit-facebook-online-schema-change-and-thoughts-on-open-source-licenses#comments\">good advice<\/a> by Lenz, Domas and others.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Revision 208 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision: Aggregation tables: aggregated data makes for fast reports on previously slow queries. Enhanced charting: interactive charts now present time stamps dynamically (see demo); &#8220;Zoom in&#8221; charts are available (see demo) on mycheckpoint&#8216;s HTTP server. RPM distribution: a &#8220;noarch&#8221; [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[46,49,57,31,50,40],"class_list":["post-3066","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-monitoring","tag-mycheckpoint","tag-open-source","tag-python","tag-scripts","tag-web"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-Ns","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3066","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=3066"}],"version-history":[{"count":12,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3066\/revisions"}],"predecessor-version":[{"id":3079,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/3066\/revisions\/3079"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=3066"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=3066"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=3066"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}