{"parse":{"errors":[]},"run":{"fired":[{"name":"\u6162\u67e5\u8be2\u65f6\u95f4","formula":"long_query_time","test":"value >= 10","issue":"{long_query_time} \u5f53\u524d\u8bbe\u4e3a 10 \u79d2\u6216\u66f4\u957f\uff0c\u56e0\u6b64\u8d85\u8fc7 10 \u79d2\u7684\u67e5\u8be2\u624d\u88ab\u8bb0\u4e3a\u6162\u67e5\u8be2\u3002","recommendation":"\u5efa\u8bae\u6839\u636e\u60a8\u7684\u73af\u5883\u5c06 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=long_query_time\">long_query_time<\/a> \u8c03\u77ed\u3002\u901a\u5e38\u5efa\u8bae\u8bbe\u4e3a 1-5 \u79d2\u3002","justification":"long_query_time \u5f53\u524d\u88ab\u8bbe\u4e3a 10 \u79d2\u3002","id":"Long query time"},{"name":"\u6162\u67e5\u8be2\u8bb0\u5f55","precondition":"PMA_MYSQL_INT_VERSION >= 50600","formula":"slow_query_log","test":"value == 'OFF'","issue":"\u6162\u67e5\u8be2\u8bb0\u5f55\u5df2\u7981\u7528\u3002","recommendation":"\u8bf7\u5c06 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=slow_query_log\">slow_query_log<\/a> \u8bbe\u4e3a 'ON' \u6765\u542f\u7528\u6162\u67e5\u8be2\u8bb0\u5f55\u3002\u8fd9\u5c06\u6709\u52a9\u4e8e\u89e3\u51b3\u67e5\u8be2\u95ee\u9898\u3002","justification":"slow_query_log \u88ab\u8bbe\u4e3a 'OFF'","id":"Slow query logging"},{"name":"\u53d1\u884c","formula":"version_comment","test":"preg_match('\/source\/i',value)","issue":"\u4ece\u6e90\u4ee3\u7801\u7f16\u8bd1\uff0c\u4e0d\u662f MySQL \u5b98\u65b9\u4e8c\u8fdb\u5236\u3002","recommendation":"\u5982\u679c\u60a8\u6ca1\u6709\u4ece\u6e90\u4ee3\u7801\u7f16\u8bd1\uff0c\u60a8\u53ef\u80fd\u4f7f\u7528\u4e86\u518d\u53d1\u884c\u7684\u4fee\u6539\u7248\u672c\u3002MySQL \u624b\u518c\u4ec5\u9002\u7528\u4e8e\u5b98\u65b9\u4e8c\u8fdb\u5236\uff0c\u800c\u975e\u5176\u5b83\u518d\u53d1\u884c\u5305 (\u5982 RedHat\u3001Debian\/Ubuntu \u7b49\u7b49)\u3002","justification":"\u7248\u672c\u6ce8\u91ca\u4e2d\u542b\u6709 'source'","id":"Distribution"},{"name":"\u67e5\u8be2\u7f13\u5b58\u5df2\u7981\u7528","formula":"query_cache_size","test":"value == 0 || query_cache_type == 'OFF' || query_cache_type == '0'","issue":"\u67e5\u8be2\u7f13\u5b58\u6ca1\u6709\u542f\u7528\u3002","recommendation":"\u82e5\u6b63\u786e\u8bbe\u7f6e\u67e5\u8be2\u7f13\u5b58\u5c06\u5e26\u6765\u6027\u80fd\u4e0a\u7684\u6781\u5927\u63d0\u5347\u3002\u60a8\u53ef\u4ee5\u901a\u8fc7\u8bbe\u7f6e <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=query_cache_size\">query_cache_size<\/a> \u4e3a 2 \u4f4d\u6570\u7684 MB \u503c\u548c\u8bbe\u7f6e <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=query_cache_type\">query_cache_type<\/a> \u4e3a 'ON'\u3002<b>\u6ce8\u610f:<\/b> \u82e5\u60a8\u6b63\u5728\u4f7f\u7528 memcached\uff0c\u8bf7\u5ffd\u7565\u6b64\u5efa\u8bae\u3002","justification":"query_cache_size \u88ab\u8bbe\u4e3a 0 \u6216 query_cache_type \u88ab\u8bbe\u4e3a 'OFF'","id":"Query cache disabled"},{"name":"\u884c\u6392\u5e8f","formula":"Sort_rows \/ Uptime","test":"value * 60 >= 1","issue":"\u5927\u91cf\u884c\u88ab\u6392\u5e8f\u3002","recommendation":"While there is nothing wrong with a high amount of row sorting, you might want to make sure that the queries which require a lot of sorting use indexed columns in the ORDER BY clause, as this will result in much faster sorting.","justification":"\u884c\u6392\u5e8f\u7387: 1.89 \u6bcf\u79d2","id":"Sort rows"},{"name":"\u65e0\u7d22\u5f15\u8054\u5408\u67e5\u8be2\u7387","formula":"(Select_range_check + Select_scan + Select_full_join) \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u6709\u592a\u591a\u7684\u8054\u5408\u67e5\u8be2\u672a\u4f7f\u7528\u7d22\u5f15\u3002","recommendation":"This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins.","justification":"\u8868\u8054\u5408\u7387: 1.97 \u6bcf\u79d2\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6","id":"Rate of joins without indexes"},{"name":"\u9996\u7d22\u5f15\u5165\u53e3\u8bfb\u53d6\u7387","formula":"Handler_read_first \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u8bfb\u53d6\u7b2c\u4e00\u4e2a\u7d22\u5f15\u5165\u53e3\u6bd4\u4f8b\u8fc7\u9ad8\u3002","recommendation":"\u8fd9\u901a\u5e38\u8bf4\u660e\u9891\u7e41\u7684\u5168\u7d22\u5f15\u626b\u63cf\u3002\u5168\u7d22\u5f15\u626b\u63cf\u867d\u7136\u6bd4\u5168\u8868\u626b\u63cf\u5feb\uff0c\u4f46\u5728\u5927\u8868\u4e2d\u4ecd\u7136\u9700\u8981\u4e00\u5b9a CPU \u5468\u671f\uff0c\u5982\u679c\u8fd9\u4e9b\u8868\u6709\u6216\u6709\u8fc7\u5927\u91cf UPDATE \u548c DELETE\uff0c\u8fd0\u884c 'OPTIMIZE TABLE' \u53ef\u51cf\u5c11\u5168\u7d22\u5f15\u626b\u63cf\u7684\u6b21\u6570\u4ee5\u53ca\u63d0\u9ad8\u901f\u5ea6\u3002\u5176\u5b83\u4f1a\u51cf\u5c11\u5168\u7d22\u5f15\u626b\u63cf\u7684\u662f\u91cd\u5199\u67e5\u8be2\u3002","justification":"\u7d22\u5f15\u626b\u63cf\u7387: 5.32 \u6bcf\u5206\u949f\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6","id":"Rate of reading first index entry"},{"name":"\u56fa\u5b9a\u4f4d\u7f6e\u8bfb\u53d6\u7387","formula":"Handler_read_rnd \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u4ece\u56fa\u5b9a\u4f4d\u7f6e\u8bfb\u53d6\u6570\u636e\u7684\u6bd4\u4f8b\u8fc7\u9ad8\u3002","recommendation":"\u8fd9\u8bf4\u660e\u5f88\u591a\u67e5\u8be2\u9700\u8981\u7ed3\u679c\u6392\u5e8f\u4e14\/\u6216\u6267\u884c\u4e00\u6b21\u5168\u8868\u626b\u63cf\uff0c\u5305\u62ec\u65e0\u7d22\u5f15\u7684\u8054\u5408\u67e5\u8be2\u3002\u8bf7\u5728\u5408\u9002\u7684\u5730\u65b9\u6dfb\u52a0\u7d22\u5f15\u3002","justification":"\u56fa\u5b9a\u4f4d\u7f6e\u8bfb\u53d6\u7387: 1.89 \u6bcf\u79d2\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6","id":"Rate of reading fixed position"},{"name":"\u4e0b\u4e00\u884c\u8bfb\u53d6\u7387","formula":"Handler_read_rnd_next \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u8bfb\u53d6\u4e0b\u4e00\u884c\u7684\u6bd4\u4f8b\u8fc7\u9ad8\u3002","recommendation":"\u8fd9\u8bf4\u660e\u5f88\u591a\u67e5\u8be2\u90fd\u9700\u8981\u5168\u8868\u626b\u63cf\u3002\u8bf7\u5728\u5408\u9002\u7684\u5730\u65b9\u6dfb\u52a0\u7d22\u5f15\u3002","justification":"\u4e0b\u4e00\u884c\u8bfb\u53d6\u7387: 48.13 \u6bcf\u79d2\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6","id":"Rate of reading next table row"},{"name":"\u4e34\u65f6\u78c1\u76d8\u4f7f\u7528\u7387","precondition":"!fired('Percentage of temp tables on disk')","formula":"Created_tmp_disk_tables \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u5f88\u591a\u4e34\u65f6\u8868\u88ab\u521b\u5efa\u5728\u78c1\u76d8\u4e0a\u800c\u975e\u5185\u5b58\u4e2d\u3002","recommendation":"\u589e\u52a0 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=max_heap_table_size\">max_heap_table_size<\/a> \u548c <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=tmp_table_size\">tmp_table_size<\/a> \u53ef\u80fd\u4f1a\u6709\u5e2e\u52a9\u3002\u4f46\u6709\u4e9b\u4e34\u65f6\u8868\u603b\u662f\u4f1a\u5199\u5165\u786c\u76d8\uff0c\u548c\u8fd9\u4e9b\u53d8\u91cf\u65e0\u5173\u3002\u8981\u907f\u514d\u8fd9\u4e9b\uff0c\u60a8\u9700\u8981\u91cd\u5199\u60a8\u7684\u67e5\u8be2\u6765\u907f\u514d <a href=\".\/url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Finternal-temporary-tables.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL \u6587\u6863<\/a>\u4e2d\u6240\u63d0\u5230\u7684\u8fd9\u4e9b\u6761\u4ef6 (\u4e34\u65f6\u8868\u5185: \u5177\u6709 BLOB \u6216 TEXT \u5b57\u6bb5\u6216\u5177\u6709\u5927\u4e8e 512 \u5b57\u8282\u7684\u5b57\u6bb5)","justification":"\u4e34\u65f6\u8868\u786c\u76d8\u5199\u5165\u7387: 14.24 \u6bcf\u5206\u949f\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6","id":"Temp disk rate"},{"name":"\u6700\u5927 % MyISAM \u7d22\u5f15\u7f13\u5b58\u4ece\u672a\u4f7f\u7528","precondition":"key_buffer_size > 0","formula":"Key_blocks_used * key_cache_block_size \/ key_buffer_size * 100","test":"value < 95","issue":"MyISAM \u7d22\u5f15\u7f13\u5b58 % \u4f7f\u7528\u7387\u4f4e\u3002","recommendation":"\u60a8\u53ef\u80fd\u9700\u8981\u51cf\u5c0f <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=key_buffer_size\">key_buffer_size<\/a> \u7684\u5927\u5c0f\uff0c\u91cd\u65b0\u68c0\u67e5\u60a8\u7684\u8868\u662f\u5426\u5220\u9664\u4e86\u7d22\u5f15\u6216\u68c0\u67e5\u67e5\u8be2\u671f\u671b\u4f7f\u7528\u7684\u7d22\u5f15\u3002","justification":"\u6700\u5927 % MyISAM \u7d22\u5f15\u7f13\u5b58\u4ece\u672a\u4f7f\u7528: 0.1%\uff0c\u8be5\u503c\u5e94\u9ad8\u4e8e 95%","id":"Max % MyISAM key buffer ever used"},{"name":"\u8868\u6253\u5f00\u7387","formula":"Opened_tables \/ Uptime","test":"value*60*60 > 10","issue":"\u6253\u5f00\u8868\u7684\u6bd4\u4f8b\u8fc7\u9ad8\u3002","recommendation":"\u6253\u5f00\u8868\u9700\u8981\u5f88\u8017\u65f6\u7684\u78c1\u76d8 I\/O\u3002\u589e\u52a0 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=table_open_cache\">table_open_cache<\/a> \u907f\u514d\u591a\u6b21\u6253\u5f00\u3002","justification":"\u5df2\u6253\u5f00\u8868\u7684\u6bd4\u7387: 6.29 \u6bcf\u5206\u949f\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 10 \u6bcf\u5c0f\u65f6","id":"Rate of table open"},{"name":"\u7ebf\u7a0b\u7f13\u5b58","formula":"thread_cache_size","test":"value < 1","issue":"\u7ebf\u7a0b\u7f13\u5b58\u88ab\u7981\u7528\uff0c\u6709\u65b0\u8fde\u63a5\u5230 MySQL \u65f6\u5c06\u5bfc\u81f4\u989d\u5916\u5f00\u9500\u3002","recommendation":"\u901a\u8fc7\u8bbe\u7f6e <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=thread_cache_size\">thread_cache_size<\/a> \u5927\u4e8e 0 \u542f\u7528\u7ebf\u7a0b\u7f13\u5b58\u3002","justification":"\u7ebf\u7a0b\u7f13\u5b58\u88ab\u8bbe\u4e3a 0","id":"Thread cache"}],"notfired":[{"name":"\u8fd0\u884c\u65f6\u95f4\u5c11\u4e8e\u4e00\u5929","formula":"Uptime","test":"value < 86400","issue":"\u8fd0\u884c\u65f6\u95f4\u5c11\u4e8e\u4e00\u5929\uff0c\u6027\u80fd\u8c03\u6574\u5efa\u8bae\u53ef\u80fd\u4e0d\u51c6\u786e\u3002","recommendation":"\u8981\u83b7\u5f97\u66f4\u52a0\u51c6\u786e\u7684\u6570\u636e\uff0c\u5efa\u8bae\u5728\u8fd0\u884c\u5206\u6790\u5668\u4e4b\u524d\u5148\u8ba9\u670d\u52a1\u5668\u8fd0\u884c\u81f3\u5c11\u4e00\u5929","justification":"\u8fd0\u884c\u65f6\u95f4\u53ea\u6709 4 \u5929 4 \u5c0f\u65f6\uff0c42 \u5206 48 \u79d2","id":"Uptime below one day"},{"name":"\u5185\u90e8\u67e5\u8be2\u5c11\u4e8e 1000","formula":"Questions","test":"value < 1000","issue":"\u5185\u90e8\u67e5\u8be2\u5c11\u4e8e 1000 \u3002\u5efa\u8bae\u53ef\u80fd\u4e0d\u51c6\u786e\u3002","recommendation":"\u8ba9\u670d\u52a1\u5668\u8fd0\u884c\u4e00\u6bb5\u65f6\u95f4\u4ee5\u4f7f\u67e5\u8be2\u6570\u8d85\u8fc7\u8be5\u503c\u3002","justification":"\u5f53\u524d\u5185\u90e8\u67e5\u8be2\u6570: 1328721","id":"Questions below 1,000"},{"name":"\u6162\u67e5\u8be2\u7387","precondition":"Questions > 0","formula":"Slow_queries \/ Questions * 100","test":"value >= 5","issue":"\u6162\u67e5\u8be2\u7684\u6570\u91cf\u5728\u6240\u6709\u67e5\u8be2\u4e2d\u6240\u5360\u7684\u6bd4\u4f8b\u5f88\u5927\u3002","recommendation":"\u4f60\u53ef\u80fd\u9700\u8981\u589e\u52a0 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=long_query_time\">long_query_time<\/a> \u503c\u6216\u4f18\u5316\u5728\u6162\u67e5\u8be2\u65e5\u5fd7\u4e2d\u6240\u5217\u51fa\u7684\u67e5\u8be2","justification":"\u6162\u67e5\u8be2\u7387\u5e94\u4f4e\u4e8e 5%\uff0c\u5f53\u524d\u4e3a 0%\u3002","id":"Percentage of slow queries"},{"name":"\u6162\u67e5\u8be2\u7387","precondition":"Questions > 0","formula":"(Slow_queries \/ Questions * 100) \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u6162\u67e5\u8be2\u7387\u8fc7\u9ad8\u3002","recommendation":"\u4f60\u53ef\u80fd\u9700\u8981\u589e\u52a0 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=long_query_time\">long_query_time<\/a> \u503c\u6216\u4f18\u5316\u5728\u6162\u67e5\u8be2\u65e5\u5fd7\u4e2d\u6240\u5217\u51fa\u7684\u67e5\u8be2","justification":"\u5f53\u524d\u6162\u67e5\u8be2\u7387\u4e3a <0.01 \u6bcf\u5929 \u6bcf\u5c0f\u65f6\uff0c\u6b64\u503c\u4e0d\u5e94\u9ad8\u4e8e 1% \u6bcf\u5c0f\u65f6\u3002","id":"Slow query rate"},{"name":"\u53d1\u884c\u7248\u7cfb\u5217","formula":"version","test":"substr(value,0,2) <= '5.' && substr(value,2,1) < 1","issue":"MySQL \u670d\u52a1\u5668\u7248\u672c\u4f4e\u4e8e 5.1\u3002","recommendation":"\u60a8\u5e94\u8be5\u5347\u7ea7\u5230 MySQL 5.1 \u6216 5.5\uff0c\u4ee5\u83b7\u5f97\u65b0\u7248\u672c\u7684\u66f4\u9ad8\u6027\u80fd\u3002","justification":"\u5f53\u524d\u7248\u672c\uff1a10.1.21-MariaDB","id":"Release Series"},{"name":"\u5b50\u7248\u672c","precondition":"! fired('Release Series')","formula":"version","test":"substr(value,0,2) <= '5.' && substr(value,2,1) <= 1 && substr(value,4,2) < 30","issue":"\u7248\u672c\u4f4e\u4e8e 5.1.30 (5.1 \u7684\u7b2c\u4e00\u4e2a GA \u7248\u672c)\u3002","recommendation":"\u60a8\u5e94\u8be5\u5347\u7ea7\u5230\u6700\u65b0\u7684 MySQL 5.1 \u6216 5.5\uff0c\u4ee5\u83b7\u5f97\u65b0\u7248\u672c\u7684\u66f4\u9ad8\u6027\u80fd\u3002","justification":"\u5f53\u524d\u7248\u672c\uff1a10.1.21-MariaDB","id":"Minor Version"},{"name":"\u5b50\u7248\u672c","precondition":"! fired('Release Series')","formula":"version","test":"substr(value,0,1) == 5 && substr(value,2,1) == 5 && substr(value,4,2) < 8","issue":"\u7248\u672c\u4f4e\u4e8e 5.5.8 (5.5 \u7684\u7b2c\u4e00\u4e2a GA \u7248\u672c)\u3002","recommendation":"You should upgrade, to a stable version of MySQL 5.5.","justification":"\u5f53\u524d\u7248\u672c\uff1a10.1.21-MariaDB","id":"Minor Version"},{"name":"\u53d1\u884c","formula":"version_comment","test":"preg_match('\/percona\/i',value)","issue":"MySQL \u624b\u518c\u4ec5\u9002\u7528\u4e8e\u5b98\u65b9\u4e8c\u8fdb\u5236\u3002","recommendation":"\u6709\u5173Percona\u7684\u6587\u6863\uff0c\u8bf7\u8bbf\u95ee\u8bbf\u95ee<a href=\".\/url.php?url=https%3A%2F%2Fwww.percona.com%2Fsoftware%2Fdocumentation%2F\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/www.percona.com\/software\/documentation\/<\/a>","justification":"\u7248\u672c\u6ce8\u91ca\u4e2d\u542b\u6709 'percona'","id":"Distribution"},{"name":"MySQL \u67b6\u6784","formula":"system_memory","test":"value > 3072*1024 && !preg_match('\/64\/',version_compile_machine) && !preg_match('\/64\/',version_compile_os)","issue":"MySQL \u6ca1\u6709\u4ee5 64 \u4f4d\u7f16\u8bd1\u3002","recommendation":"\u60a8\u7684\u5185\u5b58\u5927\u5c0f\u8d85\u8fc7 3 GB (\u82e5\u670d\u52a1\u5668\u5c31\u5728\u672c\u5730)\uff0cMySQL \u53ef\u80fd\u65e0\u6cd5\u8bbf\u95ee\u6240\u6709\u5185\u5b58\u3002\u60a8\u9700\u8981\u8003\u8651\u5b89\u88c5 64 \u4f4d\u7248\u672c\u7684 MySQL\u3002","justification":"\u6b64\u670d\u52a1\u5668\u4e0a\u7684\u53ef\u7528\u5185\u5b58: 15.12 GB","id":"MySQL Architecture"},{"name":"\u4f7f\u7528\u4e34\u65f6\u8868\u7684\u6392\u5e8f\u7387","precondition":"Sort_scan + Sort_range > 0","formula":"Sort_merge_passes \/ (Sort_scan + Sort_range) * 100","test":"value > 10","issue":"\u8fc7\u591a\u6392\u5e8f\u5f15\u53d1\u4e34\u65f6\u8868\u521b\u5efa\u3002","recommendation":"Consider increasing <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=sort_buffer_size\">sort_buffer_size<\/a> and\/or <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=read_rnd_buffer_size\">read_rnd_buffer_size<\/a>, depending on your system memory limits.","justification":"0% \u7684\u6392\u5e8f\u5f15\u53d1\u4e34\u65f6\u8868\u521b\u5efa\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 10%\u3002","id":"Percentage of sorts that cause temporary tables"},{"name":"\u6392\u5e8f\u4f7f\u7528\u4e34\u65f6\u8868\u7684\u521b\u5efa\u7387","formula":"Sort_merge_passes \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u8fc7\u591a\u6392\u5e8f\u5f15\u53d1\u4e34\u65f6\u8868\u521b\u5efa\u3002","recommendation":"Consider increasing <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=sort_buffer_size\">sort_buffer_size<\/a> and\/or <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=read_rnd_buffer_size\">read_rnd_buffer_size<\/a>, depending on your system memory limits.","justification":"\u4e34\u65f6\u8868\u521b\u5efa\u7387: <0.01 \u6bcf\u5929\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6\u3002","id":"Rate of sorts that cause temporary tables"},{"name":"Different tmp_table_size and max_heap_table_size","formula":"tmp_table_size - max_heap_table_size","test":"value !=0","issue":"{tmp_table_size} \u548c {max_heap_table_size} \u4e0d\u4e00\u81f4\u3002","recommendation":"\u82e5\u60a8\u6545\u610f\u6539\u53d8\u4e86\u5176\u4e2d\u4e00\u4e2a\u503c: \u670d\u52a1\u5668\u4f7f\u7528\u8f83\u4f4e\u7684\u503c\u6765\u786e\u5b9a\u5185\u5b58\u8868\u7684\u6700\u5927\u5927\u5c0f\u3002\u5982\u679c\u60a8\u60f3\u589e\u52a0\u5185\u5b58\u8868\u7684\u5927\u5c0f\u60a8\u5e94\u8be5\u540c\u65f6\u4fee\u6539\u53e6\u4e00\u4e2a\u503c\u3002","justification":"\u5f53\u524d\u503c\u4e3a tmp_table_size: 16 MB\uff0cmax_heap_table_size: 16 MB","id":"Different tmp_table_size and max_heap_table_size"},{"name":"\u78c1\u76d8\u4e34\u65f6\u8868\u6bd4\u4f8b","precondition":"Created_tmp_tables + Created_tmp_disk_tables > 0","formula":"Created_tmp_disk_tables \/ (Created_tmp_tables + Created_tmp_disk_tables) * 100","test":"value > 25","issue":"\u5f88\u591a\u4e34\u65f6\u8868\u88ab\u521b\u5efa\u5728\u78c1\u76d8\u4e0a\u800c\u975e\u5185\u5b58\u4e2d\u3002","recommendation":"\u589e\u52a0 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=max_heap_table_size\">max_heap_table_size<\/a> \u548c <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=tmp_table_size\">tmp_table_size<\/a> \u53ef\u80fd\u4f1a\u6709\u5e2e\u52a9\u3002\u4f46\u6709\u4e9b\u4e34\u65f6\u8868\u603b\u662f\u4f1a\u5199\u5165\u786c\u76d8\uff0c\u548c\u8fd9\u4e9b\u53d8\u91cf\u65e0\u5173\u3002\u8981\u907f\u514d\u8fd9\u4e9b\uff0c\u60a8\u9700\u8981\u91cd\u5199\u60a8\u7684\u67e5\u8be2\u6765\u907f\u514d <a href=\".\/url.php?url=https%3A%2F%2Fwww.facebook.com%2Fnote.php%3Fnote_id%3D10150111255065841%26comments\" target=\"_blank\" rel=\"noopener noreferrer\">Pythian \u5c0f\u7ec4\u7684\u6587\u7ae0<\/a>\u5f00\u5934\u6240\u63d0\u5230\u7684\u8fd9\u4e9b\u6761\u4ef6 (\u4e34\u65f6\u8868\u5185: \u5177\u6709 BLOB \u6216 TEXT \u5b57\u6bb5\u6216\u5177\u6709\u5927\u4e8e 512 \u5b57\u8282\u7684\u5b57\u6bb5)","justification":"7.9% \u7684\u4e34\u65f6\u8868\u88ab\u521b\u5efa\u5728\u78c1\u76d8\u4e0a\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 25%","id":"Percentage of temp tables on disk"},{"name":"MyISAM \u7d22\u5f15\u7f13\u5b58\u5927\u5c0f","formula":"key_buffer_size","test":"value == 0","issue":"\u7d22\u5f15\u7f13\u5b58\u672a\u521d\u59cb\u5316\u3002MyISAM \u7d22\u5f15\u4e0d\u4f1a\u88ab\u7f13\u5b58\u3002","recommendation":"\u6839\u636e MyISAM \u7d22\u5f15\u5927\u5c0f\u8bbe\u7f6e <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=key_buffer_size\">key_buffer_size<\/a>\u300264M \u901a\u5e38\u53ef\u4ee5\u6ee1\u8db3\u666e\u901a\u9700\u6c42\u3002","justification":"key_buffer_size \u4e3a 0","id":"MyISAM key buffer size"},{"name":"\u4ece\u5185\u5b58\u8bfb\u53d6\u7d22\u5f15\u7684\u767e\u5206\u6bd4","precondition":"Key_read_requests > 0","formula":"100 - (Key_reads \/ Key_read_requests * 100)","test":"value < 95","issue":"% \u4f7f\u7528\u7d22\u5f15\u7f13\u5b58\u7684\u6bd4\u4f8b\u662f\u4f4e\u7684\u3002","recommendation":"\u4f60\u9700\u8981\u589e\u5927 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=key_buffer_size\">key_buffer_size<\/a>\u3002","justification":"\u4ece\u5185\u5b58\u8bfb\u53d6\u7d22\u5f15\u7387: 98.3%\uff0c\u8be5\u503c\u5e94\u9ad8\u4e8e 95%","id":"Percentage of index reads from memory"},{"name":"\u6253\u5f00\u6587\u4ef6\u9650\u5236\u4f7f\u7528\u7387","formula":"Open_files \/ open_files_limit * 100","test":"value > 85","issue":"\u6253\u5f00\u6587\u4ef6\u7684\u6570\u91cf\u63a5\u8fd1\u6253\u5f00\u6587\u4ef6\u7684\u6700\u5927\u6570\u91cf\u3002\u60a8\u53ef\u80fd\u4f1a\u6536\u5230 \"\u8fc7\u591a\u6253\u5f00\u7684\u6587\u4ef6\" \u9519\u8bef\u3002","recommendation":"\u8003\u8651\u589e\u52a0 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=open_files_limit\">open_files_limit<\/a>\uff0c\u5e76\u5728\u4fee\u6539 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=open_files_limit\">open_files_limit<\/a> \u91cd\u542f\u540e\u68c0\u67e5\u9519\u8bef\u65e5\u5fd7\u3002","justification":"\u6253\u5f00\u7684\u6587\u4ef6\u6570\u91cf\u4e3a\u6700\u5927\u6570\u91cf\u7684 6.3%\u3002\u8be5\u503c\u5e94\u4f4e\u4e8e 85%","id":"Percentage of used open files limit"},{"name":"\u6253\u5f00\u6587\u4ef6\u7684\u6bd4\u7387","formula":"Open_files \/ Uptime","test":"value * 60 * 60 > 5","issue":"\u5f53\u524d\u6253\u5f00\u6587\u4ef6\u6570\u6bd4\u7387\u5f88\u9ad8\u3002","recommendation":"\u8003\u8651\u589e\u52a0 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=open_files_limit\">open_files_limit<\/a>\uff0c\u5e76\u5728\u4fee\u6539 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=open_files_limit\">open_files_limit<\/a> \u91cd\u542f\u540e\u68c0\u67e5\u9519\u8bef\u65e5\u5fd7\u3002","justification":"\u5df2\u6253\u5f00\u6587\u4ef6\u7684\u6bd4\u7387\uff1a15.49 \u6bcf\u5929,\u8fd9\u4e2a\u503c\u5e94\u8be5\u4f4e\u4e8e5\u6bcf\u5c0f\u65f6","id":"Rate of open files"},{"name":"\u7acb\u5373\u9501\u8868 %","precondition":"Table_locks_waited + Table_locks_immediate > 0","formula":"Table_locks_immediate \/ (Table_locks_waited + Table_locks_immediate) * 100","test":"value < 95","issue":"\u8fc7\u591a\u7684\u9501\u8868\u6ca1\u6709\u7acb\u5373\u6267\u884c\u3002","recommendation":"\u4f18\u5316\u67e5\u8be2\u5e76\/\u6216\u4f7f\u7528 InnoDB \u6765\u51cf\u5c11\u9501\u7b49\u5f85\u3002","justification":"\u7acb\u5373\u9501\u8868\u7387: 100%\uff0c\u8be5\u503c\u5e94\u9ad8\u4e8e 95%","id":"Immediate table locks %"},{"name":"\u9501\u8868\u7b49\u5f85\u7387","formula":"Table_locks_waited \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u8fc7\u591a\u7684\u9501\u8868\u6ca1\u6709\u7acb\u5373\u6267\u884c\u3002","recommendation":"\u4f18\u5316\u67e5\u8be2\u5e76\/\u6216\u4f7f\u7528 InnoDB \u6765\u51cf\u5c11\u9501\u7b49\u5f85\u3002","justification":"\u9501\u8868\u7b49\u5f85\u7387: <0.01 \u6bcf\u5929\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6","id":"Table lock wait rate"},{"name":"\u7ebf\u7a0b\u542f\u52a8\u7f13\u6162","precondition":"slow_launch_time > 0","formula":"Slow_launch_threads","test":"value > 0","issue":"\u5927\u91cf\u7ebf\u7a0b\u8fd0\u884c\u7f13\u6162\u3002","recommendation":"\u8fd9\u901a\u5e38\u662f\u56e0\u4e3a\u7cfb\u7edf\u8d1f\u8f7d\u8fc7\u9ad8\u3002\u60a8\u9700\u8981\u76d1\u63a7\u60a8\u7684\u7cfb\u7edf\u8d1f\u8f7d\u3002","justification":"0 \u4e2a\u7ebf\u7a0b\u542f\u52a8\u65f6\u95f4\u8d85\u8fc7 2 \u79d2\uff0c\u8be5\u6570\u503c\u5e94\u4e3a 0","id":"Threads that are slow to launch"},{"name":"\u6162\u542f\u52a8\u65f6\u95f4","formula":"slow_launch_time","test":"value > 2","issue":"Slow_launch_time is above 2s.","recommendation":"Set <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=slow_launch_time\">slow_launch_time<\/a> to 1s or 2s to correctly count threads that are slow to launch.","justification":"slow_launch_time \u5df2\u8bbe\u4e3a 2","id":"Slow launch time"},{"name":"\u5df2\u7528\u8fde\u63a5\u7387","formula":"Max_used_connections \/ max_connections * 100","test":"value > 80","issue":"\u6700\u5927\u5df2\u7528\u8fde\u63a5\u6570\u63a5\u8fd1 {max_connections}\u3002","recommendation":"\u589e\u52a0 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=max_connections\">max_connections<\/a>\uff0c\u6216\u51cf\u5c11 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=wait_timeout\">wait_timeout<\/a> \u4ee5\u4f7f\u6ca1\u6709\u6b63\u5e38\u5173\u95ed\u8fde\u63a5\u7684\u8fde\u63a5\u66f4\u5feb\u88ab\u6740\u6b7b\u3002\u786e\u4fdd\u4ee3\u7801\u4e2d\u6b63\u5e38\u5173\u95ed\u6570\u636e\u5e93\u8fde\u63a5\u3002","justification":"Max_used_connections \u4e3a max_connections \u7684 11.9%\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 80%","id":"Percentage of used connections"},{"name":"\u5df2\u4e2d\u6b62\u8fde\u63a5\u7387","formula":"Aborted_connects \/ Connections * 100","test":"value > 1","issue":"\u592a\u591a\u8fde\u63a5\u5df2\u4e2d\u6b62\u3002","recommendation":"\u8fde\u63a5\u901a\u5e38\u56e0\u4e3a\u65e0\u6cd5\u88ab\u6388\u6743\u800c\u4e2d\u6b62\u3002<a href=\".\/url.php?url=https%3A%2F%2Fwww.percona.com%2Fblog%2F2008%2F08%2F23%2Fhow-to-track-down-the-source-of-aborted_connects%2F\" target=\"_blank\" rel=\"noopener noreferrer\">\u8fd9\u7bc7\u6587\u7ae0<\/a>\u5bf9\u60a8\u8ffd\u8e2a\u6765\u6e90\u53ef\u80fd\u6709\u6240\u5e2e\u52a9\u3002","justification":"0% \u7684\u8fde\u63a5\u5df2\u4e2d\u6b62\u3002\u8be5\u503c\u5e94\u4f4e\u4e8e 1%","id":"Percentage of aborted connections"},{"name":"\u5df2\u4e2d\u6b62\u8fde\u63a5\u7684\u6bd4\u4f8b","formula":"Aborted_connects \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u592a\u591a\u8fde\u63a5\u5df2\u4e2d\u6b62\u3002","recommendation":"\u8fde\u63a5\u901a\u5e38\u56e0\u4e3a\u65e0\u6cd5\u88ab\u6388\u6743\u800c\u4e2d\u6b62\u3002<a href=\".\/url.php?url=https%3A%2F%2Fwww.percona.com%2Fblog%2F2008%2F08%2F23%2Fhow-to-track-down-the-source-of-aborted_connects%2F\" target=\"_blank\" rel=\"noopener noreferrer\">\u8fd9\u7bc7\u6587\u7ae0<\/a>\u5bf9\u60a8\u8ffd\u8e2a\u6765\u6e90\u53ef\u80fd\u6709\u6240\u5e2e\u52a9\u3002","justification":"\u5df2\u4e2d\u6b62\u8fde\u63a5\u7387\u4e3a <0.01 \u6bcf\u5929\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6","id":"Rate of aborted connections"},{"name":"\u5df2\u4e2d\u6b62\u5ba2\u6237\u7aef\u6bd4\u4f8b","formula":"Aborted_clients \/ Connections * 100","test":"value > 2","issue":"\u592a\u591a\u7684\u5ba2\u6237\u7aef\u5df2\u4e2d\u6b62\u3002","recommendation":"\u5ba2\u6237\u7aef\u4e2d\u6b62\u901a\u5e38\u662f\u56e0\u4e3a\u5b83\u4eec\u6ca1\u6709\u6b63\u786e\u5173\u95ed\u5230 MySQL \u670d\u52a1\u5668\u7684\u8fde\u63a5\u3002\u8fd9\u53ef\u80fd\u7531\u7f51\u7edc\u95ee\u9898\u6216\u4ee3\u7801\u4e2d\u6ca1\u6709\u6b63\u786e\u5173\u95ed\u6570\u636e\u5e93\u8fde\u63a5\u5f15\u8d77\u3002\u8bf7\u68c0\u67e5\u60a8\u7684\u7f51\u7edc\u548c\u4ee3\u7801\u3002","justification":"0% \u7684\u5ba2\u6237\u7aef\u5df2\u53d6\u6d88\u3002\u6b64\u503c\u4e0d\u5e94\u9ad8\u4e8e 2%","id":"Percentage of aborted clients"},{"name":"\u5ba2\u6237\u7aef\u7684\u53d6\u6d88\u7387","formula":"Aborted_clients \/ Uptime","test":"value * 60 * 60 > 1","issue":"\u592a\u591a\u7684\u5ba2\u6237\u7aef\u5df2\u4e2d\u6b62\u3002","recommendation":"\u5ba2\u6237\u7aef\u4e2d\u6b62\u901a\u5e38\u662f\u56e0\u4e3a\u5b83\u4eec\u6ca1\u6709\u6b63\u786e\u5173\u95ed\u5230 MySQL \u670d\u52a1\u5668\u7684\u8fde\u63a5\u3002\u8fd9\u53ef\u80fd\u7531\u7f51\u7edc\u95ee\u9898\u6216\u4ee3\u7801\u4e2d\u6ca1\u6709\u6b63\u786e\u5173\u95ed\u6570\u636e\u5e93\u8fde\u63a5\u5f15\u8d77\u3002\u8bf7\u68c0\u67e5\u60a8\u7684\u7f51\u7edc\u548c\u4ee3\u7801\u3002","justification":"\u5ba2\u6237\u7aef\u53d6\u6d88\u7387\u4e3a <0.01 \u6bcf\u5929\uff0c\u6b64\u503c\u5e94\u4f4e\u4e8e 1 \u6bcf\u5c0f\u65f6","id":"Rate of aborted clients"},{"name":"InnoDB\u7684\u65e5\u5fd7\u5927\u5c0f","precondition":"innodb_buffer_pool_size > 0","formula":"innodb_log_file_size \/ innodb_buffer_pool_size * 100","test":"value < 20 && innodb_log_file_size \/ (1024 * 1024) < 256","issue":"InnoDB \u65e5\u5fd7\u6587\u4ef6\u5927\u5c0f\u4e0d\u5408\u9002\uff0c\u6b64\u5173\u7cfb\u5230 InnoDB \u7f13\u51b2\u6c60\u3002","recommendation":"\u5728\u4e00\u4e2a InnoDB \u8868\u5199\u5165\u5f88\u591a\u7684\u7cfb\u7edf\u4e0a\u60a8\u5e94\u8be5\u5c06 <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=innodb_log_file_size\">innodb_log_file_size<\/a> \u8bbe\u4e3a <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=innodb_buffer_pool_size\">innodb_buffer_pool_size<\/a> \u7684 25% \u3002\u56e0\u4e3a\u8be5\u503c\u8d8a\u5927\uff0c\u5f53\u6570\u636e\u5e93\u5d29\u6e83\u65f6\u6062\u590d\u7684\u65f6\u95f4\u5c31\u8d8a\u957f\uff0c\u6240\u4ee5\u8be5\u503c\u4e0d\u5e94\u9ad8\u4e8e 256 MB \u3002\u8bf7\u6ce8\u610f\u60a8\u4e0d\u80fd\u7b80\u5355\u7684\u4fee\u6539\u8be5\u53d8\u91cf\u7684\u503c\u3002\u60a8\u9700\u8981\u5173\u95ed\u670d\u52a1\u5668\uff0c\u5220\u9664 InnoDB \u65e5\u5fd7\u6587\u4ef6\uff0c\u5728 my.cnf \u4e2d\u8bbe\u7f6e\u65b0\u7684\u503c\uff0c\u542f\u52a8\u670d\u52a1\u5668\uff0c\u4e00\u5207\u6b63\u5e38\u540e\u518d\u68c0\u67e5\u9519\u8bef\u65e5\u5fd7\u3002\u53c2\u89c1<a href=\".\/url.php?url=https%3A%2F%2Fmysqldatabaseadministration.blogspot.com%2F2007%2F01%2Fincrease-innodblogfilesize-proper-way.html\" target=\"_blank\" rel=\"noopener noreferrer\">\u8fd9\u7bc7\u535a\u5ba2<\/a>","justification":"\u60a8\u7684 InnoDB \u65e5\u5fd7\u5927\u5c0f\u4e3a InnoDB \u7f13\u51b2\u6c60\u5927\u5c0f\u7684 31.3%\uff0c\u8be5\u503c\u4e0d\u5e94\u4f4e\u4e8e 20%","id":"InnoDB log size"},{"name":"InnoDB\u7f13\u51b2\u6c60\u5927\u5c0f","precondition":"system_memory > 0","formula":"innodb_buffer_pool_size \/ system_memory * 100","test":"value < 60","issue":"\u4f60\u7684 InnoDB \u7f13\u51b2\u6c60\u76f8\u5f53\u5c0f\u3002","recommendation":"The InnoDB buffer pool has a profound impact on performance for InnoDB tables. Assign all your remaining memory to this buffer. For database servers that use solely InnoDB as storage engine and have no other services (e.g. a web server) running, you may set this as high as 80% of your available memory. If that is not the case, you need to carefully assess the memory consumption of your other services and non-InnoDB-Tables and set this variable accordingly. If it is set too high, your system will start swapping, which decreases performance significantly. See also <a href=\".\/url.php?url=https%3A%2F%2Fwww.percona.com%2Fblog%2F2007%2F11%2F03%2Fchoosing-innodb_buffer_pool_size%2F\" target=\"_blank\" rel=\"noopener noreferrer\">this article<\/a>","justification":"\u60a8\u5f53\u524d\u7684 InnoDB \u7f13\u51b2\u6c60\u4f7f\u7528\u4e86\u5185\u5b58\u7684 105.83752316763% \u3002\u6b64\u89c4\u5219\u5728\u60a8\u5206\u914d\u5c11\u4e8e 60% \u65f6\u88ab\u89e6\u53d1\uff0c\u7136\u800c\u8fd9\u4e5f\u53ef\u80fd\u56e0\u4e3a\u60a8\u6ca1\u6709\u592a\u591a InnoDB \u8868\u6240\u4ee5\u8fd9\u6837\u8db3\u591f\u6216\u8005\u670d\u52a1\u5668\u4e0a\u8fd8\u8fd0\u884c\u4e86\u5176\u5b83\u670d\u52a1\u3002","id":"InnoDB buffer pool size"},{"name":"MyISAM \u5e76\u53d1\u63d2\u5165","formula":"concurrent_insert","test":"value === 0 || value === 'NEVER'","issue":"\u8bbe\u7f6e {concurrent_insert} \u7684\u503c\u4e3a1\u6765\u542f\u7528\uff08\u5e76\u53d1\u63d2\u5165\uff09","recommendation":"\u8bbe\u7f6e <a href=\"server_variables.php?token=067f2f7abcd1fb641c3adfd47c9f515a&filter=concurrent_insert\">concurrent_insert<\/a> \u4e3a 1 \u53ef\u4ee5\u51cf\u5c11\u5728\u76f8\u540c\u8868\u4e0a\u7684\u8bfb\u53d6\u548c\u5199\u5165\u51b2\u7a81\u3002\u53c2\u89c1 <a href=\".\/url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fconcurrent-inserts.html\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL \u6587\u6863<\/a>","justification":"concurrent_insert \u88ab\u8bbe\u4e3a 0","id":"MyISAM concurrent inserts"}],"unchecked":[{"name":"Slow query logging","precondition":"PMA_MYSQL_INT_VERSION < 50600","formula":"log_slow_queries","test":"value == 'OFF'","issue":"The slow query log is disabled.","recommendation":"Enable slow query logging by setting {log_slow_queries} to 'ON'. This will help troubleshooting badly performing queries.","justification":"log_slow_queries is set to 'OFF'"},{"name":"Query caching method","precondition":"!fired('Query cache disabled')","formula":"Questions \/ Uptime","test":"value > 100","issue":"Suboptimal caching method.","recommendation":"You are using the MySQL Query cache with a fairly high traffic database. It might be worth considering to use <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.5\/en\/ha-memcached.html\">memcached<\/a> instead of the MySQL Query cache, especially if you have multiple slaves.","justification":"The query cache is enabled and the server receives %d queries per second. This rule fires if there is more than 100 queries per second. | round(value,1)"},{"name":"Query cache efficiency (%)","precondition":"Com_select + Qcache_hits > 0 && !fired('Query cache disabled')","formula":"Qcache_hits \/ (Com_select + Qcache_hits) * 100","test":"value < 20","issue":"Query cache not running efficiently, it has a low hit rate.","recommendation":"Consider increasing {query_cache_limit}.","justification":"The current query cache hit rate of %s% is below 20% | round(value,1)"},{"name":"Query Cache usage","precondition":"!fired('Query cache disabled')","formula":"100 - Qcache_free_memory \/ query_cache_size * 100","test":"value < 80","issue":"Less than 80% of the query cache is being utilized.","recommendation":"This might be caused by {query_cache_limit} being too low. Flushing the query cache might help as well.","justification":"The current ratio of free query cache memory to total query cache size is %s%. It should be above 80% | round(value,1)"},{"name":"Query cache fragmentation","precondition":"!fired('Query cache disabled')","formula":"Qcache_free_blocks \/ (Qcache_total_blocks \/ 2) * 100","test":"value > 20","issue":"The query cache is considerably fragmented.","recommendation":"Severe fragmentation is likely to (further) increase Qcache_lowmem_prunes. This might be caused by many Query cache low memory prunes due to {query_cache_size} being too small. For a immediate but short lived fix you can flush the query cache (might lock the query cache for a long time). Carefully adjusting {query_cache_min_res_unit} to a lower value might help too, e.g. you can set it to the average size of your queries in the cache using this formula: (query_cache_size - qcache_free_memory) \/ qcache_queries_in_cache","justification":"The cache is currently fragmented by %s% , with 100% fragmentation meaning that the query cache is an alternating pattern of free and used blocks. This value should be below 20%. | round(value,1)"},{"name":"Query cache low memory prunes","precondition":"Qcache_inserts > 0 && !fired('Query cache disabled')","formula":"Qcache_lowmem_prunes \/ Qcache_inserts * 100","test":"value > 0.1","issue":"Cached queries are removed due to low query cache memory from the query cache.","recommendation":"You might want to increase {query_cache_size}, however keep in mind that the overhead of maintaining the cache is likely to increase with its size, so do this in small increments and monitor the results.","justification":"The ratio of removed queries to inserted queries is %s%. The lower this value is, the better (This rules firing limit: 0.1%) | round(value,1)"},{"name":"Query cache max size","precondition":"!fired('Query cache disabled')","formula":"query_cache_size","test":"value > 1024 * 1024 * 128","issue":"The query cache size is above 128 MiB. Big query caches may cause significant overhead that is required to maintain the cache.","recommendation":"Depending on your environment, it might be performance increasing to reduce this value.","justification":"Current query cache size: %s | ADVISOR_formatByteDown(value, 2, 2)"},{"name":"Query cache min result size","precondition":"!fired('Query cache disabled')","formula":"query_cache_limit","test":"value == 1024*1024","issue":"The max size of the result set in the query cache is the default of 1 MiB.","recommendation":"Changing {query_cache_limit} (usually by increasing) may increase efficiency. This variable determines the maximum size a query result may have to be inserted into the query cache. If there are many query results above 1 MiB that are well cacheable (many reads, little writes) then increasing {query_cache_limit} will increase efficiency. Whereas in the case of many query results being above 1 MiB that are not very well cacheable (often invalidated due to table updates) increasing {query_cache_limit} might reduce efficiency.","justification":"query_cache_limit is set to 1 MiB"},{"name":"Percentage of MyISAM key buffer used","precondition":"key_buffer_size > 0 && !fired('Max % MyISAM key buffer ever used')","formula":"( 1 - Key_blocks_unused * key_cache_block_size \/ key_buffer_size) * 100","test":"value < 95","issue":"MyISAM key buffer (index cache) % used is low.","recommendation":"You may need to decrease the size of {key_buffer_size}, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.","justification":"% MyISAM key buffer used: %s%, this value should be above 95% | round(value,1)"},{"name":"Thread cache hit rate %","precondition":"thread_cache_size > 0","formula":"100 - Threads_created \/ Connections","test":"value < 80","issue":"Thread cache is not efficient.","recommendation":"Increase {thread_cache_size}.","justification":"Thread cache hitrate: %s%, this value should be above 80% | round(value,1)"},{"name":"Is InnoDB disabled?","precondition":"PMA_MYSQL_INT_VERSION < 50600","formula":"have_innodb","test":"value != \"YES\"","issue":"You do not have InnoDB enabled.","recommendation":"InnoDB is usually the better choice for table engines.","justification":"have_innodb is set to 'value'"},{"name":"Max InnoDB log size","precondition":"innodb_buffer_pool_size > 0 && innodb_log_file_size \/ innodb_buffer_pool_size * 100 < 30","formula":"innodb_log_file_size \/ (1024 * 1024)","test":"value > 256","issue":"The InnoDB log file size is inadequately large.","recommendation":"It is usually sufficient to set {innodb_log_file_size} to 25% of the size of {innodb_buffer_pool_size}. A very big {innodb_log_file_size} slows down the recovery time after a database crash considerably. See also <a href=\"https:\/\/www.percona.com\/blog\/2006\/07\/03\/choosing-proper-innodb_log_file_size\/\">this Article<\/a>. You need to shutdown the server, remove the InnoDB log files, set the new value in my.cnf, start the server, then check the error logs if everything went fine. See also <a href=\"https:\/\/mysqldatabaseadministration.blogspot.com\/2007\/01\/increase-innodblogfilesize-proper-way.html\">this blog entry<\/a>","justification":"Your absolute InnoDB log size is %s MiB | round(value,1)"}],"errors":[]}}