{"id":1695,"date":"2009-12-16T21:12:07","date_gmt":"2009-12-16T19:12:07","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1695"},"modified":"2009-12-16T21:12:07","modified_gmt":"2009-12-16T19:12:07","slug":"new-and-noteworthy-in-mycheckpoint-rev-57","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/new-and-noteworthy-in-mycheckpoint-rev-57","title":{"rendered":"New and noteworthy in mycheckpoint (rev. 57)"},"content":{"rendered":"<p>Rev. 57 of <a href=\"http:\/\/code.openark.org\/forge\/mycheckpoint\">mycheckpoint<\/a> has been released and is available for <a href=\"http:\/\/code.google.com\/p\/mycheckpoint\/\">download<\/a>.<\/p>\n<p>New and updated in this revision:<\/p>\n<ul>\n<li><a href=\"#Remote host monitoring\">Remote host monitoring<\/a><\/li>\n<li><a href=\"#Improved charting\">Improved charting<\/a><\/li>\n<li><a href=\"#Flexible charting\">Flexible charting<\/a><\/li>\n<li><a href=\"#Fix to questions vs. queries issues\">Fix to questions vs. queries issues<\/a><\/li>\n<\/ul>\n<p><a name=\"Remote host monitoring\"><\/a><\/p>\n<h4>Remote host monitoring<\/h4>\n<p>It is now possible to monitor one host, while writing into another. Either or both could be remote hosts:<\/p>\n<blockquote>\n<pre>mycheckpoint --host=localhost --monitored-host=192.168.10.178<\/pre>\n<\/blockquote>\n<p>The above monitors the MySQL server on 192.168.10.178, and writes down to localhost (to be queried later)<\/p>\n<blockquote>\n<pre>mycheckpoint --monitored-host=127.0.0.1 --host=192.168.10.178<\/pre>\n<\/blockquote>\n<p>The above monitors the MySQL server on 127.0.0.1, and writes down to 192.168.10.178.<\/p>\n<p><!--more-->As result of the above addition, binary logging for monitoring statements are now enabled by default. The previous solution, in which binary logging were disabled by default and the same schema was utilized by different servers was far from being a clean solution. <em>[Read more on <a href=\"http:\/\/code.openark.org\/forge\/mycheckpoint\/documentation\/remote-multiple-hosts\">Remote &amp; multiple hosts<\/a> monitoring]<\/em><\/p>\n<p><a name=\"Improved charting\"><\/a><\/p>\n<h4>Improved charting<\/h4>\n<p>The google charts reports have been improved significantly. Here&#8217;s how:<\/p>\n<blockquote><p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1702\" title=\"Before\" src=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-old-sample11.png\" alt=\"Before\" width=\"400\" height=\"200\" srcset=\"https:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-old-sample11.png 400w, https:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-old-sample11-300x150.png 300w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1703\" title=\"After\" src=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-new-sample1.png\" alt=\"After\" width=\"400\" height=\"200\" srcset=\"https:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-new-sample1.png 400w, https:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-new-sample1-300x150.png 300w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><br \/>\n<strong>Before<\/strong> on left (or above), <strong>After<\/strong> on right (or below)<\/p><\/blockquote>\n<p>And<\/p>\n<blockquote><p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1708\" title=\"Before\" src=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-old-sample2.png\" alt=\"Before\" width=\"400\" height=\"200\" srcset=\"https:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-old-sample2.png 400w, https:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-old-sample2-300x150.png 300w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1709\" title=\"After\" src=\"http:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-new-sample2.png\" alt=\"After\" width=\"400\" height=\"200\" srcset=\"https:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-new-sample2.png 400w, https:\/\/code.openark.org\/blog\/wp-content\/uploads\/2009\/12\/chart-new-sample2-300x150.png 300w\" sizes=\"auto, (max-width: 400px) 100vw, 400px\" \/><br \/>\n<strong>Before<\/strong> on left (or above), <strong>After<\/strong> on right (or below)<\/p><\/blockquote>\n<p><em>Recap<\/em>: the charts are not stored anywhere; they are being calculated and generated by SQL queries &amp; views, based on the raw data. For example, to generate the above, I do:<\/p>\n<blockquote>\n<pre>SELECT innodb_estimated_log_mb_written_per_hour FROM sv_report_chart_sample\\G\r\n*************************** 1. row ***************************\r\ninnodb_estimated_log_mb_written_per_hour: http:\/\/chart.apis.google.com\/chart?cht=lc&amp;chs=400x200&amp;chts=303030,12&amp;chtt=Latest+24+hours:+Dec+8,+08:20++-++Dec+9,+08:20&amp;chdl=innodb_estimated_log_mb_written_per_hour&amp;chdlp=b&amp;chco=ff8c00&amp;chd=s:OQOTSPPQPSROUQMTRSQTPSURQTQQPSOSRTRRUTRRTSRRRSRSTRRQSURSUVRRXQTVRUTTVUTOYTXSYTTSYUSUTWUPRRPQSQRQTPPSPQMPPQOQMQPMOMOLQORNNPNRNNOPQOOMQQPNRNMNORQMSTQQPTPRUQVQTTVSURUVVVSSSVWQVUSTVSWSSUURWRVRTWdotpQNROPQRPQQMPLRO9PWNRPNNNPXUMLNNPQQPSONPLLNWXZTQROSQMOONQPLJNOOQQKMPSMMPLfPSSSRTUQSORSSRSPNRSQSQ&amp;chxt=x,y&amp;chxr=1,0,2708.0&amp;chxl=0:|09:00||||13:00||||17:00||||21:00||||01:00||||05:00||||&amp;chxs=0,505050,10&amp;chg=4.17,25,1,3,2.78,0&amp;chxp=0,2.78,6.95,11.12,15.29,19.46,23.63,27.80,31.97,36.14,40.31,44.48,48.65,52.82,56.99,61.16,65.33,69.50,73.67,77.84,82.01,86.18,90.35,94.52,98.69<\/pre>\n<\/blockquote>\n<p>If you thought I was going over the edge in <a href=\"http:\/\/code.openark.org\/blog\/mysql\/auto-scaling-scaled-sql-graphs-concluded\">Auto scaling, scaled SQL graphs concluded<\/a> or <a href=\"http:\/\/code.openark.org\/blog\/mysql\/sql-pie-chart\">SQL pie chart<\/a>, allow me to assure you: generation of the above charts using SQL &amp; views is <em>SQL blasphemy<\/em>. While cool, the Google Charts API does not do too much on its own, and expects the caller to do the math for putting in the grids, scales and labels (and though all of them are tightly related, they must all be explicitly specified). I&#8217;ll write a later post on this.<\/p>\n<p><a name=\"Flexible charting\"><\/a><\/p>\n<h4>Flexible charting<\/h4>\n<p>A new table, called <strong>charts_api<\/strong>, is introduced. It contains one row, which lists charting configuration.<\/p>\n<blockquote>\n<pre>SELECT * FROM charts_api;\r\n+-------------+--------------+----------------------------------------------------------------+------------------------------------+\r\n| chart_width | chart_height | simple_encoding\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | service_url\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------+--------------+----------------------------------------------------------------+------------------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 400 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 200 | ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789 | http:\/\/chart.apis.google.com\/chart |\r\n+-------------+--------------+----------------------------------------------------------------+------------------------------------+<\/pre>\n<\/blockquote>\n<p>To change generated charts dimensions, simply update <strong>chart_width<\/strong> or <strong>chart_height<\/strong>.<\/p>\n<p>To use other chart APIs, update <strong>service_url<\/strong>. I&#8217;ve tried out <a href=\"http:\/\/www.jfree.org\/eastwood\/\">Eastwood Charts<\/a>, a Java-based service, which implements the Google Charts API. There&#8217;s still some stuff missing, such as the positioning of labels and grids; and it&#8217;s slightly less polished (but configurable). But if you&#8217;re concerned about sending requests to Google, it&#8217;s a viable alternative.<\/p>\n<p>So, drop the WAR file into your <a href=\"http:\/\/tomcat.apache.org\/\">Tomcat<\/a> \/ <a href=\"http:\/\/www.jboss.org\/\">JBoss<\/a> \/ other j2ee server, then follow <strong>http:\/\/127.0.0.1\/eastwood\/chart?<\/strong>&#8230;<\/p>\n<p><a name=\"Fix to questions vs. queries issues\"><\/a><\/p>\n<h4>Fix to questions vs. queries issues<\/h4>\n<p><em>mycheckpoint<\/em> now correctly identifies whether to use <a href=\"http:\/\/code.openark.org\/blog\/mysql\/questions-or-queries\">questions or queries<\/a> when analyzing DML queries. If you got some <strong>570%<\/strong> for <strong>com_update<\/strong> out of total queries and wondered about it, it should now be resolved to normal values.<\/p>\n<h4>On upgrading your current installation<\/h4>\n<ol>\n<li>Download &amp; install via debian or python installers<\/li>\n<li>Issue <em>mycheckpoint<\/em> once with &#8220;deploy&#8221; as argument<\/li>\n<li>Done<\/li>\n<\/ol>\n<h4>Future plans<\/h4>\n<p>I&#8217;m working on 24&#215;7 charting; minimal operating system monitoring; auto-detection and auto-deploy on version upgrade; and more.<\/p>\n<p>Stay tuned!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Rev. 57 of mycheckpoint has been released and is available for download. New and updated in this revision: Remote host monitoring Improved charting Flexible charting Fix to questions vs. queries issues Remote host monitoring It is now possible to monitor one host, while writing into another. Either or both could be remote hosts: mycheckpoint &#8211;host=localhost [&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":[41,46,49],"class_list":["post-1695","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-graphs","tag-monitoring","tag-mycheckpoint"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-rl","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1695","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=1695"}],"version-history":[{"count":48,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1695\/revisions"}],"predecessor-version":[{"id":1767,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1695\/revisions\/1767"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1695"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1695"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1695"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}