说明
建议系统可以通过分析服务器状态变量对服务器变量的设置提出建议。
请注意本系统所提出的建议建立在简单计算以及通用场合中，可能并不能满足您系统的实际需求。
在修改任何设置之前，请确定您确实知道在修改什么设置 (通过阅读文档) 并知道如何撤销改变。错误的设置可能导致与预期完全相反的结果。
最好的系统调整方法是一次只修改一个设置，观察并测试您的数据库，如果没有显著的效果就撤销改变。
{"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?filter=long_query_time&lang=zh_CN\">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?filter=slow_query_log&lang=zh_CN\">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":"\u884c\u6392\u5e8f","formula":"Sort_rows \/ Uptime","test":"value * 60 >= 1","issue":"\u5927\u91cf\u884c\u88ab\u6392\u5e8f\u3002","recommendation":"\u5927\u91cf\u884c\u6392\u5e8f\u4e0d\u662f\u4ec0\u4e48\u9519\u8bef\uff0c\u4f46\u60a8\u5e94\u786e\u4fdd\u9700\u8981\u5927\u91cf\u6392\u5e8f\u7684\u67e5\u8be2\u5728 ORDER BY \u4e2d\u4f7f\u7528\u7d22\u5f15\u5b57\u6bb5\uff0c\u8fd9\u5c06\u4f7f\u6392\u5e8f\u52a0\u5feb\u3002","justification":"\u884c\u6392\u5e8f\u7387: 2.42 \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":"\u8fd9\u610f\u5473\u7740\u8054\u5408\u67e5\u8be2\u4f7f\u7528\u5168\u8868\u626b\u63cf\u3002\u7ed9\u8054\u5408\u6761\u4ef6\u6240\u7528\u7684\u5b57\u6bb5\u589e\u52a0\u7d22\u5f15\u5c06\u6781\u5927\u63d0\u9ad8\u67e5\u8be2\u901f\u5ea6\u3002","justification":"\u8868\u8054\u5408\u7387: 6.39 \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: 3.85 \u6bcf\u79d2\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: 2.38 \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: 66.61 \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?filter=max_heap_table_size&lang=zh_CN\">max_heap_table_size<\/a> \u548c <a href=\"server_variables.php?filter=tmp_table_size&lang=zh_CN\">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: 17.75 \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?filter=key_buffer_size&lang=zh_CN\">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%\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?filter=table_open_cache&lang=zh_CN\">table_open_cache<\/a> \u907f\u514d\u591a\u6b21\u6253\u5f00\u3002","justification":"\u5df2\u6253\u5f00\u8868\u7684\u6bd4\u7387: 15.92 \u6bcf\u5c0f\u65f6\uff0c\u8be5\u503c\u5e94\u4f4e\u4e8e 10 \u6bcf\u5c0f\u65f6","id":"Rate of table open"},{"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?filter=query_cache_size&lang=zh_CN\">query_cache_size<\/a> \u4e3a 2 \u4f4d\u6570\u7684 MB \u503c\u548c\u8bbe\u7f6e <a href=\"server_variables.php?filter=query_cache_type&lang=zh_CN\">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"}],"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 2 \u5929 10 \u5c0f\u65f6\uff0c41 \u5206 11 \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: 1898152","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?filter=long_query_time&lang=zh_CN\">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?filter=long_query_time&lang=zh_CN\">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.4.11-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.4.11-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":"\u60a8\u5e94\u5347\u7ea7\u5230 MySQL 5.5 \u7684\u7a33\u5b9a\u7248\u672c\u3002","justification":"\u5f53\u524d\u7248\u672c\uff1a10.4.11-MariaDB","id":"Minor Version"},{"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":"\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: 0 \u5b57\u8282","id":"MySQL Architecture"},{"name":"\u67e5\u8be2\u7f13\u5b58\u6a21\u5f0f","precondition":"!fired('Query cache disabled')","formula":"Questions \/ Uptime","test":"value > 100","issue":"\u6700\u4f73\u7f13\u5b58\u6a21\u5f0f\u3002","recommendation":"\u60a8\u6b63\u5728\u4e00\u53f0\u5177\u6709\u76f8\u5f53\u9ad8\u6d41\u91cf\u7684\u6570\u636e\u5e93\u4e2d\u4f7f\u7528 MySQL \u67e5\u8be2\u7f13\u5b58\u3002\u9664\u975e\u60a8\u6709\u591a\u53f0\u4ece\u670d\u52a1\u5668\uff0c\u4f7f\u7528 <a href=\".\/url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.5%2Fen%2Fha-memcached.html\" target=\"_blank\" rel=\"noopener noreferrer\">memcached<\/a> \u4ee3\u66ff MySQL \u67e5\u8be2\u7f13\u5b58\u5c06\u66f4\u597d\u3002","justification":"\u67e5\u8be2\u7f13\u5b58\u5df2\u542f\u7528\u4e14\u670d\u52a1\u5668\u6bcf\u79d2\u6536\u5230 9 \u4e2a\u67e5\u8be2\u3002\u8be5\u89c4\u5219\u5728\u6bcf\u79d2\u8d85\u8fc7 100 \u4e2a\u67e5\u8be2\u65f6\u88ab\u89e6\u53d1\u3002","id":"Query caching method"},{"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":"\u6839\u636e\u7cfb\u7edf\u5185\u5b58\u9650\u5236\uff0c\u8003\u8651\u589e\u52a0 <a href=\"server_variables.php?filter=sort_buffer_size&lang=zh_CN\">sort_buffer_size<\/a> \u548c\/\u6216 <a href=\"server_variables.php?filter=read_rnd_buffer_size&lang=zh_CN\">read_rnd_buffer_size<\/a>\u3002","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":"\u6839\u636e\u7cfb\u7edf\u5185\u5b58\u9650\u5236\uff0c\u8003\u8651\u589e\u52a0 <a href=\"server_variables.php?filter=sort_buffer_size&lang=zh_CN\">sort_buffer_size<\/a> \u548c\/\u6216 <a href=\"server_variables.php?filter=read_rnd_buffer_size&lang=zh_CN\">read_rnd_buffer_size<\/a>\u3002","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":"\u4e0d\u540c\u7684 tmp_table_size \u548c 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.00 MB\uff0cmax_heap_table_size: 16.00 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?filter=max_heap_table_size&lang=zh_CN\">max_heap_table_size<\/a> \u548c <a href=\"server_variables.php?filter=tmp_table_size&lang=zh_CN\">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":"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?filter=key_buffer_size&lang=zh_CN\">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":"\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?filter=open_files_limit&lang=zh_CN\">open_files_limit<\/a>\uff0c\u5e76\u5728\u4fee\u6539 <a href=\"server_variables.php?filter=open_files_limit&lang=zh_CN\">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 1%\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?filter=open_files_limit&lang=zh_CN\">open_files_limit<\/a>\uff0c\u5e76\u5728\u4fee\u6539 <a href=\"server_variables.php?filter=open_files_limit&lang=zh_CN\">open_files_limit<\/a> \u91cd\u542f\u540e\u68c0\u67e5\u9519\u8bef\u65e5\u5fd7\u3002","justification":"\u5df2\u6253\u5f00\u6587\u4ef6\u7684\u6bd4\u7387\uff1a22.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\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?filter=thread_cache_size&lang=zh_CN\">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"},{"name":"\u7ebf\u7a0b\u7f13\u5b58\u547d\u4e2d\u7387 %","precondition":"thread_cache_size > 0","formula":"100 - Threads_created \/ Connections","test":"value < 80","issue":"\u7ebf\u7a0b\u7f13\u5b58\u6548\u7387\u4f4e\u3002","recommendation":"\u589e\u52a0 <a href=\"server_variables.php?filter=thread_cache_size&lang=zh_CN\">thread_cache_size<\/a>\u3002","justification":"\u7ebf\u7a0b\u7f13\u5b58\u547d\u4e2d\u7387: 100%\uff0c\u8be5\u503c\u5e94\u9ad8\u4e8e 80%","id":"Thread cache hit 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 \u5927\u4e8e 2 \u79d2\u3002","recommendation":"\u8bbe\u7f6e <a href=\"server_variables.php?filter=slow_launch_time&lang=zh_CN\">slow_launch_time<\/a> \u4e3a 1 \u79d2\u6216 2 \u79d2\u4ee5\u6b63\u786e\u8ba1\u7b97\u542f\u52a8\u6162\u7684\u7ebf\u7a0b\u6570\u3002","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?filter=max_connections&lang=zh_CN\">max_connections<\/a>\uff0c\u6216\u51cf\u5c11 <a href=\"server_variables.php?filter=wait_timeout&lang=zh_CN\">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 5%\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_log_files_in_group)\/ 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?filter=innodb_log_file_size&lang=zh_CN\">innodb_log_file_size<\/a> \u8bbe\u4e3a <a href=\"server_variables.php?filter=innodb_buffer_pool_size&lang=zh_CN\">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 63%\uff0c\u8be5\u503c\u4e0d\u5e94\u4f4e\u4e8e 20%","id":"InnoDB log 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?filter=concurrent_insert&lang=zh_CN\">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":"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":"Percentage of index reads from memory","precondition":"Key_read_requests > 0","formula":"100 - (Key_reads \/ Key_read_requests * 100)","test":"value < 95","issue":"The % of indexes that use the MyISAM key buffer is low.","recommendation":"You may need to increase {key_buffer_size}.","justification":"Index reads from memory: %s%, this value should be above 95% | 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)"},{"name":"InnoDB buffer pool size","precondition":"system_memory > 0","formula":"innodb_buffer_pool_size \/ system_memory * 100","test":"value < 60","issue":"Your InnoDB buffer pool is fairly small.","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=\"https:\/\/www.percona.com\/blog\/2007\/11\/03\/choosing-innodb_buffer_pool_size\/\">this article<\/a>","justification":"You are currently using %s% of your memory for the InnoDB buffer pool. This rule fires if you are assigning less than 60%, however this might be perfectly adequate for your system if you don't have much InnoDB tables or other services running on the same machine. | value"},{"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"}],"errors":[]}}
