{"id":248,"date":"2009-11-09T11:01:08","date_gmt":"2009-11-09T09:01:08","guid":{"rendered":"http:\/\/code.openark.org\/forge\/?page_id=248"},"modified":"2009-11-09T14:59:26","modified_gmt":"2009-11-09T12:59:26","slug":"querying-for-data","status":"publish","type":"page","link":"https:\/\/code.openark.org\/forge\/mycheckpoint\/documentation\/querying-for-data","title":{"rendered":"Querying for data"},"content":{"rendered":"<p>When directly querying for data, one must choose the right time resolution. Data can be viewed in raw format (<strong>sv_diff<\/strong> view), in <em>per-sample<\/em>, <em>per-hour<\/em> and <em>per-day<\/em> (<strong>sv_*<\/strong> views) metrics, or in modified, calculated format, (<strong>sv_report_*<\/strong> views).<\/p>\n<p>For example, looking at sampled <strong>com_select<\/strong> values:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT ts, com_select FROM sv_sample LIMIT 5;\r\n+---------------------+------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | com_select |\r\n+---------------------+------------+\r\n| 2009-10-22 20:55:01 |\u00a0 853202052 |\r\n| 2009-10-22 21:00:01 |\u00a0 853248581 |\r\n| 2009-10-22 21:05:01 |\u00a0 853292775 |\r\n| 2009-10-22 21:10:01 |\u00a0 853330226 |\r\n| 2009-10-22 21:15:01 |\u00a0 853379080 |\r\n+---------------------+------------+<\/pre>\n<\/blockquote>\n<p>Looking at the <em>differences<\/em> in <strong>com_select<\/strong> values:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT ts, com_select_diff FROM sv_sample LIMIT 5;\r\n+---------------------+-----------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | com_select_diff |\r\n+---------------------+-----------------+\r\n| 2009-10-22 20:55:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29300 |\r\n| 2009-10-22 21:00:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 46529 |\r\n| 2009-10-22 21:05:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 44194 |\r\n| 2009-10-22 21:10:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 37451 |\r\n| 2009-10-22 21:15:01 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 48854 |\r\n+---------------------+-----------------+<\/pre>\n<\/blockquote>\n<p>Looking at average <strong>com_select<\/strong> per second on an hourly aggregation:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT ts, com_select_psec FROM sv_hour LIMIT 5;\r\n+---------------------+-----------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | com_select_psec |\r\n+---------------------+-----------------+\r\n| 2009-10-22 20:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 97.67 |\r\n| 2009-10-22 21:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 127.81 |\r\n| 2009-10-22 22:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 132.62 |\r\n| 2009-10-22 23:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 134.92 |\r\n| 2009-10-23 00:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 87.99 |\r\n+---------------------+-----------------+<\/pre>\n<\/blockquote>\n<p>Measuring the percentage of com_select queries out of all questions:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT ts, ROUND(100*com_select_diff\/questions_diff, 1) AS com_select_percent\r\n       FROM sv_hour LIMIT 5;\r\n+---------------------+--------------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | com_select_percent |\r\n+---------------------+--------------------+\r\n| 2009-10-22 20:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28.0 |\r\n| 2009-10-22 21:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28.3 |\r\n| 2009-10-22 22:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28.6 |\r\n| 2009-10-22 23:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29.7 |\r\n| 2009-10-23 00:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16.8 |\r\n+---------------------+--------------------+<\/pre>\n<\/blockquote>\n<h4>Comparing status variables and server variables<\/h4>\n<p>Some status values only make sense when compared with server variables. Since these are all stored together, it&#8217;s very easily done:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT ts, ROUND(100 - 100*(key_blocks_unused * key_cache_block_size)\/key_buffer_size, 1)\r\n         AS key_buffer_used_percent\r\n       FROM sv_hour LIMIT 5;\r\n+---------------------+-------------------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | key_buffer_used_percent |\r\n+---------------------+-------------------------+\r\n| 2009-10-22 20:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n| 2009-10-22 21:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n| 2009-10-22 22:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n| 2009-10-22 23:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n| 2009-10-23 00:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n+---------------------+-------------------------+<\/pre>\n<\/blockquote>\n<p>The above identical values indicate little or no MyISAM activity. Indeed, the diagnosed database is InnoDB only, with just the system tables being MyISAM.<\/p>\n<p>MySQL 5.0 and 5.1 have different tabel cache implementations. Still, look at how easy it is to check for the tabel cache use percent:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT ts, ROUND(100*open_tables\/(IFNULL(table_cache, 0) + IFNULL(table_open_cache, 0)), 1)\r\n         AS table_cache_use_percent\r\n       FROM sv_hour ORDER BY id DESC LIMIT 5;\r\n+---------------------+-------------------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | table_cache_use_percent |\r\n+---------------------+-------------------------+\r\n| 2009-11-09 11:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.5 |\r\n| 2009-11-09 10:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.2 |\r\n| 2009-11-09 09:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11.1 |\r\n| 2009-11-09 08:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10.8 |\r\n| 2009-11-09 07:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10.3 |\r\n+---------------------+-------------------------+<\/pre>\n<\/blockquote>\n<p>We&#8217;ve now looked at last 5 sampled hours.<\/p>\n<h4>Pre-measured metrics<\/h4>\n<p>The above examples are so common and important that the <strong>sv_report_*<\/strong> views provide them built in:<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT com_select_percent FROM sv_report_hour LIMIT 5;\r\n+--------------------+\r\n| com_select_percent |\r\n+--------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28.0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28.3 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 28.6 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 29.7 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 16.8 |\r\n+--------------------+\r\n\r\nmysql&gt; SELECT key_buffer_used_percent FROM sv_report_hour ORDER BY id DESC LIMIT 5;\r\n+-------------------------+\r\n| key_buffer_used_percent |\r\n+-------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 18.2 |\r\n+-------------------------+\r\n\r\nmysql&gt; SELECT table_cache_use_percent FROM sv_report_hour ORDER BY id DESC LIMIT 5;\r\n+-------------------------+\r\n| table_cache_use_percent |\r\n+-------------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.5 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.2 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11.1 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10.8 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10.3 |\r\n+-------------------------+<\/pre>\n<\/blockquote>\n<h4>Selecting by conditions<\/h4>\n<p>And, of course, one can SELECT by range of dates, look for particular hour in the night, search for rows where some values were considerable higher than average, what have you!<\/p>\n<blockquote>\n<pre>mysql&gt; SELECT ts, com_select_psec\r\n       FROM sv_hour\r\n       WHERE\r\n         ts BETWEEN '2009-10-25' AND '2009-10-29'\r\n         AND com_select_psec &gt; (SELECT 2*AVG(com_select_psec) FROM sv_hour);\r\n+---------------------+-----------------+\r\n| ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | com_select_psec |\r\n+---------------------+-----------------+\r\n| 2009-10-26 00:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 212.83 |\r\n| 2009-10-26 01:00:00 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 214.27 |\r\n+---------------------+-----------------+<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>When directly querying for data, one must choose the right time resolution. Data can be viewed in raw format (sv_diff view), in per-sample, per-hour and per-day (sv_* views) metrics, or in modified, calculated format, (sv_report_* views). For example, looking at sampled com_select values: mysql&gt; SELECT ts, com_select FROM sv_sample LIMIT 5; +&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;+&#8212;&#8212;&#8212;&#8212;+ | ts\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":147,"menu_order":-3,"comment_status":"open","ping_status":"open","template":"","meta":{"footnotes":""},"class_list":["post-248","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/248","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=248"}],"version-history":[{"count":4,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/248\/revisions"}],"predecessor-version":[{"id":251,"href":"https:\/\/code.openark.org\/forge\/wp-json\/wp\/v2\/pages\/248\/revisions\/251"}],"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=248"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}