{"id":14021,"date":"2025-12-05T10:25:52","date_gmt":"2025-12-05T02:25:52","guid":{"rendered":"https:\/\/www.killtest.com.tw\/?p=14021"},"modified":"2025-12-05T10:25:52","modified_gmt":"2025-12-05T02:25:52","slug":"mysql%e8%a8%98%e6%86%b6%e9%ab%94%e4%bd%bf%e7%94%a8%e9%81%8e%e5%a4%9a%ef%bc%8c%e5%8f%af%e8%83%bd%e4%bb%80%e9%ba%bc%e5%8e%9f%e5%9b%a0%ef%bc%9f","status":"publish","type":"post","link":"https:\/\/www.killtest.com.tw\/?p=14021","title":{"rendered":"MySQL\u8a18\u61b6\u9ad4\u4f7f\u7528\u904e\u591a\uff0c\u53ef\u80fd\u4ec0\u9ebc\u539f\u56e0\uff1f"},"content":{"rendered":"\n<p>\u8f49\u81ea MYSQL\u6578\u64da\u5eab\u806f\u76df<br>\u53bb\u5e74\uff0c\u5728\u3010DBA\u9a5b\u7ad9\u3011\u4e2d\uff0c\u6536\u96c6\u5230\u4e00\u4f4d\u661f\u53cb\u53bb\u67d0\u4e92\u806f\u7db2\u5927\u5ee0\u9762\u8a66\u7684\u771f\u984c\uff0c\u5176\u4e2d\u6709\u9019\u6a23\u4e00\u9053\u9762\u8a66\u984c\uff1a\u7dda\u4e0a\u6709\u6c92\u6709\u9047\u5230MySQL\u4f3a\u670d\u5668\u56e0\u70ba\u5167\u5b58\u4f7f\u7528\u904e\u5927\u800cOOM\u7684\u60c5\u6cc1\uff0c\u5982\u679c\u6709\u8aaa\u4e00\u4e0b\u539f\u56e0\uff08\u9762\u8a66\u5b98\u63d0\u793a\uff1aMySQL\u904b\u884c\u6642\u9593\u5f88\u9577\uff0c\u8a18\u61b6\u9ad4\u4f54\u7528\u6703\u8d8a\u4f86\u8d8a\u5927\uff09\u7576\u6642\u7d66\u4e86\u4e00\u500b\u89e3\u6790\uff0c\u6700\u8fd1\u770b\u4e86\u8ffd\u98a8\u8001\u5e2b\u7684\u5c08\u6b04\u300a\u4e00\u7dda<a href=\"https:\/\/www.killtest.net\/MongoDB-Certified-DBA-Associate\/C100DBA.asp\">DBA<\/a>\u8cc7\u6599\u5eab\u5be6\u6230\u7d93\u5178\u6848\u4f8b30\u8b1b\u300b\u7b2c5\u7bc0\uff1aMySQL\u5167\u5b58\u5c45\u9ad8\u4e0d\u4e0b\u5982\u4f55\u7834\u5c40\uff1f<br>\u611f\u89ba\u7576\u6642\u5c0d\u65bc\u9019\u500b\u9762\u8a66\u984c\u7684\u89e3\u6790\u9084\u662f\u4e0d\u592a\u5b8c\u6574\u7684\uff0c\u9019\u7bc7\u6587\u7ae0\u5c31\u4f86\u91cd\u65b0\u5beb\u4e00\u4e0b\u89e3\u6790\u3002 1 MySQL\u670d\u52d9\u54ea\u4e9b\u90e8\u5206\u6703\u4f7f\u7528\u8a18\u61b6\u9ad4\u4e3b\u8981\u6709Server\u5c64\u548cInnoDB\u5c64\u3002 <br>1.1 Server\u5c64\u5305\u62ec\uff1aJoin buffer\uff1bSort buffer\uff1bQuery Cache\uff08\u5982\u679c\u958b\u555f\uff09\uff1b\u57f7\u884c\u7dd2\u200b\u200b\u548c\u9023\u7dda\u3002<br>1.2 InnoDB\u5c64\u4e3b\u8981\u5305\u62ec\uff1aInnoDB Buffer Pool\u3002\u800c\u5728Linux\u4e2d\uff0c\u5c0f\u65bc128k\u7684\u5167\u5b58\uff0c\u5206\u7247\u4e4b\u5f8c\u518d\u91cb\u653e\uff0c\u4e26\u4e0d\u6703\u99ac\u4e0a\u6b78\u9084\u7d66OS\u3002<br>\u5927\u65bc128k\u7684\u5167\u5b58\uff0c\u4f7f\u7528\u5f8c\u91cb\u653e\uff0c\u6703\u99ac\u4e0a\u6b78\u9084\u7d66OS\u3002<br>InnoDB Buffer Pool\u5728MySQL\u555f\u52d5\u6642\u7533\u8acb\u7684\u662f\u865b\u64ec\u5167\u5b58\uff0c\u96a8\u8457\u8cc7\u6599\u91cf\u52a0\u8f09\u5230\u5167\u5b58\u4e2dbuffer pool\u6162\u6162\u7533\u8acb\u5be6\u9ad4\u5167\u5b58\uff0c\u76f4\u5230\u9054\u5230buffer\u8a2d\u5b9a\u4e0a\u9650\u3002<br>\u5982\u679c\u6c92\u9054\u5230\u4e0a\u9650\uff0c\u6b64\u6642\u8cc7\u6599\u91cf\u5c0f\u65bc\u8a2d\u5b9a\u7684buffer\u5927\u5c0f\uff0c\u6548\u80fd\u7121\u7591\u662f\u6700\u597d\u7684\uff0c\u5982\u679c\u9054\u5230\u4e0a\u9650\u4e86\uff0c\u5247\u6703\u9032\u884clru\u6dd8\u6c70\u3002<br>\u53e6\u5916\uff0c\u5982\u679c\u8abf\u4f4ebuffer pool\u5927\u5c0f\uff0c\u4f60\u80fd\u767c\u73fefree -g\u7acb\u523b\u770b\u5230free\u5217\u7acb\u523b\u589e\u5927\uff0c\u9019\u5c31\u662f\u76f4\u63a5\u91cb\u653e\u7d66\u4f5c\u696d\u7cfb\u7d71\u3002<br>\u6240\u4ee5\uff0c\u5982\u679cInnoDB Buffer Pool\u8a2d\u5b9a\u5408\u7406\uff0cMySQL\u4f7f\u7528\u8a18\u61b6\u9ad4\u53c8\u9060\u9060\u8d85\u8d8aInnoDB Buffer Pool\u8a2d\u5b9a\u7684\u503c\uff0c\u90a3\u5c31\u4e0d\u662fBuffer Pool\u7684\u554f\u984c\u4e86\u3002<br>2 \u5206\u6790MySQL\u8a18\u61b6\u9ad4\u4f7f\u7528\u60c5\u6cc1\u53ef\u4ee5\u900f\u904e\u555f\u7528 performance_schema \u4e2d\u8207\u8a18\u61b6\u9ad4\u76f8\u95dc\u7684\u76e3\u63a7\u4f86\u67e5\u770b MySQL \u7684\u8a18\u61b6\u9ad4\u4f7f\u7528\u91cf\u3002<br>\u958b\u555f\u65b9\u5f0f\u5982\u4e0b\uff1a<br><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><\/li>\n<\/ul>\n\n\n\n<p>update\u00a0performance_schema.setup_instruments\u00a0set\u00a0enabled\u00a0=\u00a0&#8216;yes&#8217;\u00a0where\u00a0name\u00a0like\u00a0&#8216;memory\/%&#8217;;<br><br>\u6839\u64da\u4e3b\u6a5f\u5206\u7d44\u7684\u8a18\u61b6\u9ad4\u4f7f\u7528\uff1a<br><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><\/li>\n<\/ul>\n\n\n\n<p>select\u00a0host,current_count_used\/1024\/1024\u00a0as used_MB ,current_allocated\/1024\/1024\u00a0as allocated_MB,current_avg_alloc\/1024\/1024\u00a0as avg_alloc_MB,current_max_alloc\/1024\/1024\u00a0as max_alloc_MB,total_allocated\/1024\/1024\u00a0as total_all_MB from sys.x$memory_by_host_by_current_bytes limit\u00a05;<br><br>\u6839\u64da\u7dda\u7a0b\u548c\u4e8b\u4ef6\u5206\u7d44\u7684\u8a18\u61b6\u9ad4\u4f7f\u7528\uff1a<br><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><\/li>\n<\/ul>\n\n\n\n<p>select\u00a0* from performance_schema.memory_summary_by_thread_by_event_name\u00a0limit\u00a05;<br><br>\u67d0\u689dSQL\u4f54\u7528\u7684\u8a18\u61b6\u9ad4\u4f7f\u7528\u91cf\uff1a<br><br>select\u00a0t.processlist_id,concat(t.PROCESSLIST_USER,&#8217;@&#8217;,t.PROCESSLIST_host)\u00a0as\u00a0account,m.event_name, m.CURRENT_NUMBER_OF_BYTES_USED\/1024\/1024\/1024\u00a0as\u00a0mem_used, t.PROCESSLIST_INFO\u00a0from\u00a0performance_schema.memory_summary_by_thread_by_event_name m, performance_schema.threads t\u00a0where\u00a0m.thread_id = t.thread_id\u00a0and\u00a0processlist_id\u00a0is\u00a0not\u00a0null\u00a0order\u00a0by\u00a0CURRENT_NUMBER_OF_BYTES_USED desc limit\u00a05;<br><br><strong>3 MySQL\u5185\u5b58\u4f7f\u7528\u8fc7\u591a\u4e0d\u91ca\u653e\u7684\u5e38\u89c1\u539f\u56e0<\/strong><\/p>\n\n\n\n<p>\u5185\u5b58\u6cc4\u6f0f\u548c\u788e\u7247\uff1a\u5728 MySQL \u957f\u65f6\u95f4\u8fd0\u884c\u65f6\uff0c\u5185\u5b58\u4f7f\u7528\u53ef\u80fd\u4f1a\u9010\u6e10\u589e\u52a0\uff0c\u7279\u522b\u662f\u5728 Server \u5c42\u3002\u4f8b\u5982\uff0cperformance_schema \u4e2d\u7684 table_handles \u8868\u4f1a\u5360\u7528\u5927\u91cf\u5185\u5b58\uff08\u7ea6 9GB\uff09\uff0c\u53c2\u8003Bug\uff1a<br><a href=\"https:\/\/bugs.mysql.com\/bug.php?id=95504\">https:\/\/bugs.mysql.com\/bug.php?id=95504<\/a><\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><\/blockquote>\n\n\n\n<p>\u914d\u7f6e\u95ee\u9898\uff1aInnoDB Buffer Pool \u3001join_buffer_size\u3001sort_buffer_size\u3001query cache\u914d\u7f6e\u4e0d\u5408\u7406\u3002<\/p>\n\n\n\n<p>\u5185\u5b58\u5206\u914d\u5668\uff1aMySQL\u9ed8\u8ba4\u4f7f\u7528\u7684\u662fglibc \u5185\u5b58\u5206\u914d\u5668\uff0cglibc\u5185\u5b58\u5206\u914d\u5668\u7684\u6027\u80fd\u76f8\u8f83\u4e8ejemalloc\uff0c\u5185\u5b58\u5206\u914d\u548c\u56de\u6536\u65b9\u9762\u4f1a\u5f31\u4e00\u4e9b\uff0c\u56e0\u6b64\uff0c\u63a8\u8350\u7684\u662f\u66f4\u6362\u5185\u5b58\u5206\u914d\u5668\u4e3ajemalloc\u65b9\u5f0f\u3002<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u8f49\u81ea MYSQL\u6578\u64da\u5eab\u806f\u76df\u53bb\u5e74\uff0c\u5728\u3010DBA\u9a5b\u7ad9\u3011\u4e2d\uff0c\u6536\u96c6\u5230\u4e00\u4f4d\u661f\u53cb\u53bb\u67d0\u4e92\u806f\u7db2\u5927\u5ee0\u9762\u8a66\u7684\u771f\u984c\uff0c\u5176\u4e2d\u6709\u9019\u6a23\u4e00\u9053\u9762\u8a66\u984c\uff1a\u7dda\u4e0a\u6709\u6c92\u6709\u9047\u5230MySQL\u4f3a\u670d\u5668\u56e0\u70ba\u5167\u5b58\u4f7f\u7528\u904e\u5927\u800cOOM\u7684\u60c5\u6cc1\uff0c\u5982\u679c\u6709\u8aaa\u4e00\u4e0b\u539f\u56e0\uff08\u9762\u8a66\u5b98\u63d0\u793a\uff1aMySQL\u904b\u884c\u6642\u9593\u5f88\u9577\uff0c\u8a18\u61b6\u9ad4\u4f54\u7528\u6703\u8d8a\u4f86\u8d8a\u5927\uff09\u7576\u6642\u7d66\u4e86\u4e00\u500b\u89e3\u6790\uff0c\u6700\u8fd1\u770b\u4e86\u8ffd\u98a8\u8001\u5e2b\u7684\u5c08\u6b04\u300a\u4e00\u7ddaDBA\u8cc7\u6599\u5eab\u5be6\u6230\u7d93\u5178\u6848\u4f8b30\u8b1b\u300b\u7b2c5\u7bc0\uff1aMySQL\u5167\u5b58\u5c45\u9ad8\u4e0d\u4e0b\u5982\u4f55\u7834\u5c40\uff1f\u611f\u89ba\u7576\u6642\u5c0d\u65bc\u9019\u500b\u9762\u8a66\u984c\u7684\u89e3\u6790\u9084\u662f\u4e0d\u592a\u5b8c\u6574\u7684\uff0c\u9019\u7bc7\u6587\u7ae0\u5c31\u4f86\u91cd\u65b0\u5beb\u4e00\u4e0b\u89e3\u6790\u3002 1 MySQL\u670d\u52d9\u54ea\u4e9b\u90e8\u5206\u6703\u4f7f\u7528\u8a18\u61b6\u9ad4\u4e3b\u8981\u6709Server\u5c64\u548cInnoDB\u5c64\u3002 1.1 Server\u5c64\u5305\u62ec\uff1aJoin buffer\uff1bSort buffer\uff1bQuery Cache\uff08\u5982\u679c\u958b\u555f\uff09\uff1b\u57f7\u884c\u7dd2\u200b\u200b\u548c\u9023\u7dda\u30021.2 InnoDB\u5c64\u4e3b\u8981\u5305\u62ec\uff1aInnoDB Buffer Pool\u3002\u800c\u5728Linux\u4e2d\uff0c\u5c0f\u65bc128k\u7684\u5167\u5b58\uff0c\u5206\u7247\u4e4b\u5f8c\u518d\u91cb\u653e\uff0c\u4e26\u4e0d\u6703\u99ac\u4e0a\u6b78\u9084\u7d66OS\u3002\u5927\u65bc128k\u7684\u5167\u5b58\uff0c\u4f7f\u7528\u5f8c\u91cb\u653e\uff0c\u6703\u99ac\u4e0a\u6b78\u9084\u7d66OS\u3002InnoDB Buffer Pool\u5728MySQL\u555f\u52d5\u6642\u7533\u8acb\u7684\u662f\u865b\u64ec\u5167\u5b58\uff0c\u96a8\u8457\u8cc7\u6599\u91cf\u52a0\u8f09\u5230\u5167\u5b58\u4e2dbuffer pool\u6162\u6162\u7533\u8acb\u5be6\u9ad4\u5167\u5b58\uff0c\u76f4\u5230\u9054\u5230buffer\u8a2d\u5b9a\u4e0a\u9650\u3002\u5982\u679c\u6c92\u9054\u5230\u4e0a\u9650\uff0c\u6b64\u6642\u8cc7\u6599\u91cf\u5c0f\u65bc\u8a2d\u5b9a\u7684buffer\u5927\u5c0f\uff0c\u6548\u80fd\u7121\u7591\u662f\u6700\u597d\u7684\uff0c\u5982\u679c\u9054\u5230\u4e0a\u9650\u4e86\uff0c\u5247\u6703\u9032\u884clru\u6dd8\u6c70\u3002\u53e6\u5916\uff0c\u5982\u679c\u8abf\u4f4ebuffer pool\u5927\u5c0f\uff0c\u4f60\u80fd\u767c\u73fefree -g\u7acb\u523b\u770b\u5230free\u5217\u7acb\u523b\u589e\u5927\uff0c\u9019\u5c31\u662f\u76f4\u63a5\u91cb\u653e\u7d66\u4f5c\u696d\u7cfb\u7d71\u3002\u6240\u4ee5\uff0c\u5982\u679cInnoDB Buffer Pool\u8a2d\u5b9a\u5408\u7406\uff0cMySQL\u4f7f\u7528\u8a18\u61b6\u9ad4\u53c8\u9060\u9060\u8d85\u8d8aInnoDB Buffer Pool\u8a2d\u5b9a\u7684\u503c\uff0c\u90a3\u5c31\u4e0d\u662fBuffer Pool\u7684\u554f\u984c\u4e86\u30022 \u5206\u6790MySQL\u8a18\u61b6\u9ad4\u4f7f\u7528\u60c5\u6cc1\u53ef\u4ee5\u900f\u904e\u555f\u7528 performance_schema \u4e2d\u8207\u8a18\u61b6\u9ad4\u76f8\u95dc\u7684\u76e3\u63a7\u4f86\u67e5\u770b MySQL \u7684\u8a18\u61b6\u9ad4\u4f7f\u7528\u91cf\u3002\u958b\u555f\u65b9\u5f0f\u5982\u4e0b\uff1a update\u00a0performance_schema.setup_instruments\u00a0set\u00a0enabled\u00a0=\u00a0&#8216;yes&#8217;\u00a0where\u00a0name\u00a0like\u00a0&#8216;memory\/%&#8217;; \u6839\u64da\u4e3b\u6a5f\u5206\u7d44\u7684\u8a18\u61b6\u9ad4\u4f7f\u7528\uff1a select\u00a0host,current_count_used\/1024\/1024\u00a0as used_MB ,current_allocated\/1024\/1024\u00a0as allocated_MB,current_avg_alloc\/1024\/1024\u00a0as avg_alloc_MB,current_max_alloc\/1024\/1024\u00a0as max_alloc_MB,total_allocated\/1024\/1024\u00a0as total_all_MB from sys.x$memory_by_host_by_current_bytes limit\u00a05; \u6839\u64da\u7dda\u7a0b\u548c\u4e8b\u4ef6\u5206\u7d44\u7684\u8a18\u61b6\u9ad4\u4f7f\u7528\uff1a select\u00a0* from performance_schema.memory_summary_by_thread_by_event_name\u00a0limit\u00a05; \u67d0\u689dSQL\u4f54\u7528\u7684\u8a18\u61b6\u9ad4\u4f7f\u7528\u91cf\uff1a select\u00a0t.processlist_id,concat(t.PROCESSLIST_USER,&#8217;@&#8217;,t.PROCESSLIST_host)\u00a0as\u00a0account,m.event_name, m.CURRENT_NUMBER_OF_BYTES_USED\/1024\/1024\/1024\u00a0as\u00a0mem_used, t.PROCESSLIST_INFO\u00a0from\u00a0performance_schema.memory_summary_by_thread_by_event_name m, performance_schema.threads t\u00a0where\u00a0m.thread_id = t.thread_id\u00a0and\u00a0processlist_id\u00a0is\u00a0not\u00a0null\u00a0order\u00a0by\u00a0CURRENT_NUMBER_OF_BYTES_USED desc limit\u00a05; 3 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[23],"class_list":["post-14021","post","type-post","status-publish","format-standard","hentry","category-killtest","tag-dba"],"_links":{"self":[{"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=\/wp\/v2\/posts\/14021","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=14021"}],"version-history":[{"count":1,"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=\/wp\/v2\/posts\/14021\/revisions"}],"predecessor-version":[{"id":14022,"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=\/wp\/v2\/posts\/14021\/revisions\/14022"}],"wp:attachment":[{"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=14021"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=14021"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.killtest.com.tw\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=14021"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}