{"id":165519,"date":"2020-10-09T11:59:00","date_gmt":"2020-10-09T03:59:00","guid":{"rendered":"http:\/\/4563.org\/?p=165519"},"modified":"2020-10-09T11:59:00","modified_gmt":"2020-10-09T03:59:00","slug":"%e8%af%b7%e5%a4%a7%e5%ae%b6%e8%af%84%e7%82%b9%e4%b8%80%e4%b8%8b%e8%bf%99%e4%b8%aa%e8%80%81%e7%89%a9%e7%90%86%e6%9c%ba%e5%9c%a8%e8%99%9a%e6%8b%9f%e6%9c%ba%e4%b8%8a%e8%b7%91-mysql-%e4%bc%98%e5%8c%96","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=165519","title":{"rendered":"\u8bf7\u5927\u5bb6\u8bc4\u70b9\u4e00\u4e0b\u8fd9\u4e2a\u8001\u7269\u7406\u673a\u5728\u865a\u62df\u673a\u4e0a\u8dd1 MySQL \u4f18\u5316\u7684\u914d\u7f6e\u53c2\u6570\uff0c\u8c22\u8c22~"},"content":{"rendered":"<div>\n<div>\n<div>\n<h1>                  \u8bf7\u5927\u5bb6\u8bc4\u70b9\u4e00\u4e0b\u8fd9\u4e2a\u8001\u7269\u7406\u673a\u5728\u865a\u62df\u673a\u4e0a\u8dd1 MySQL \u4f18\u5316\u7684\u914d\u7f6e\u53c2\u6570\uff0c\u8c22\u8c22~               <\/h1>\n<p> <\/p>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : pppguest3962 <\/span>  <span><i><\/i> 0<\/span> <\/div>\n<div> <\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<div isfirst=\"1\"> <\/p>\n<p>\u7269\u7406\u673a\u5668\u6027\u80fd\uff1a<br \/> \u673a\u5668\u7684\u7269\u7406 CPU \u662f E5\uff0c\u4e13\u95e8\u6307\u5b9a\u5206\u4e86 2 \u4e2a\u6838\u7ed9\u8fd9\u53f0\u865a\u62df\u673a<br \/> \u4e13\u95e8\u5206\u914d\u4e00\u4e2a\u7269\u7406\u7f51\u53e3\u7ed9\u865a\u62df\u673a\u72ec\u4eab<br \/> \u5185\u5b58:4G \u72ec\u4eab<br \/> \u7cfb\u7edf CentOS 6.8<br \/> MySQL \u7248\u672c\u662f 5.1.73<br \/> \u78c1\u76d8 I\/O \u662f 120mb\/s \u8bfb\u5199\u7684\u6c34\u5e73\u5427<\/p>\n<p>\u865a\u62df\u673a\u53ea\u662f\u62ff\u6765\u8dd1 MySQL \u7684,<br \/> \u5927\u6982 40 \u591a\u5f20\u8868\uff0c\u6700\u5927\u7684\u8868\u6709 4 \u5343\u4e07,\u90fd\u5efa\u6709\u201c\u5408\u7406\u7684\u201d\u7d22\u5f15<\/p>\n<p>\u5185\u7f51\u53ea\u6709\u51e0\u53f0 PC \u673a\u4f7f\u7528\u5b83\u7684\u670d\u52a1,\u4ee5\u524d\u5e76\u53d1\u91cf\u5e73\u65f6\u90fd\u662f 100 \u5de6\u53f3\uff08 MySQL \u9ed8\u8ba4\u662f 151\uff0c\u6240\u4ee5\u4e00\u70b9\u513f\u4e8b\u90fd\u6ca1\u6709)\uff0c<br \/> \u6700\u8fd1\u66f4\u65b0\u4e86\u672c\u5730\u7a0b\u5e8f\uff0c\u73b0\u5728 1 \u79d2\u540c\u65f6\u5e76\u53d1\u6700\u5927\u91cf\u5230\u4e86 2000\uff0c\u6709\u63d0\u793a\uff1aToo many Connection \u7684\u5b57\u6837\uff0c<br \/> \u4e8e\u662f\u6839\u636e\u7f51\u4e0a\u7684\u77e5\u8bc6\u70b9\uff0c\u548c\u6211\u8fd9\u91cc\u7684\u5b9e\u9645\u60c5\u51b5\u6539\u4e86\u4e00\u4e0b CentOS \u7684\u5185\u6838\u53c2\u6570\u548c MySQL \u914d\u7f6e,<\/p>\n<pre><code># sysctl \u914d\u7f6e net.ipv4.tcp_tw_reuse = 1 net.ipv4.tcp_tw_recycle = 1 fs.file-max=65535 net.ipv4.ip_local_port_range = 1024 65535 net.ipv4.tcp_fin_timeout = 30 net.ipv4.tcp_max_tw_buckets = 800 net.ipv4.tcp_max_syn_backlog = 819200 net.ipv4.tcp_keepalive_time = 120 net.ipv4.tcp_keepalive_intvl = 15 net.ipv4.tcp_keepalive_probes = 3 net.core.netdev_max_backlog = 500000 net.core.somaxconn = 65536  # my.ini \u914d\u7f6e max_tmp_tables = 64 max_allowed_packet = 32M max_connect_errors = 8000  table_cache = 614 wait_timeout = 10 interactive_timeout = 10 max_connections=4096 back_log=600 wait_timeout=13 key_buffer_size = 128M query_cache_size = 32M read_buffer_size =16M read_rnd_buffer_size = 16M sort_buffer_size = 16M read_rnd_buffer_size = 16M thread_cache_size = 16 thread_concurrency = 8 open-files-limit = 10240 <\/code><\/pre>\n<p>\u5168\u90e8\u8868\u7684\u5f15\u64ce\u90fd\u662f Myisam\uff0c\u7a0b\u5e8f\u64cd\u4f5c\u6ca1\u6709\u4e8b\u52a1\u7684\u64cd\u4f5c,\u5bf9\u6570\u636e\u5e93\u7684\u64cd\u4f5c\u5c31\u662f INSERT\/SELECT\/UPDATE\uff0c<br \/> \u8bed\u53e5\u8fde mysql \u7684\u51fd\u6570\u8fd0\u7b97\u90fd\u6ca1\u6709\u7528\u5230<\/p>\n<p>\u7a0b\u5e8f\u4e0a\u7684\u6240\u6709\u8fde\u63a5\u90fd\u662f\u5355\u72ec\u7684\u77ed\u8fde\u63a5\uff0c\u7a0b\u5e8f\u91cc\u6ca1\u6709\u957f\u8fde\u63a5<\/p>\n<p>\u4f18\u5316\u7684\u7684\u671f\u671b\u662f\uff0c\u9664\u4e86\u589e\u5927\u63a5\u53d7\u7684\u5e76\u53d1\u91cf\uff0c\u5bf9\u4e8e\u5927\u91cf\u540c\u65f6\u7684 INSERT \uff08\u540c\u65f6 600 \u4e2a\u77ed\u94fe\u63a5\u7684\u8fc7\u6765\uff09\uff0cMySQL \u662f\u5426\u53ef\u4ee5\u5148\u7f13\u51b2\uff0c\u7136\u540e\u6162\u6162\u5199\u5230\u8868\u91cc\uff1f<br \/> \u8001\u673a\u5668\uff0cI\/O \u786e\u5b9e\u4e0d\u600e\u4e48\u6837<\/p>\n<p>\u4e0d\u77e5\u9053\u9002\u5408\u5417\uff1f<\/p>\n<\/p><\/div>\n<div> <b>\u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (<span>7<\/span>)        <\/div>\n<div> <\/div>\n<\/p><\/div>\n<\/p><\/div>\n<ul>\n<li data-pid=\"3652820\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : opengps <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u865a\u62df\u673a\u7684\u786c\u76d8 io \u635f\u5931\u5f88\u4e25\u91cd\u7684\uff0c\u6211\u7684 gps \u8868\u5355\u884c 1k\uff0c\u7269\u7406\u786c\u76d8\u53ef\u4ee5 3000 \uff5e 4000\uff0c\u7269\u7406\u786c\u76d8\u7684\u865a\u62df\u673a\u91cc\u53ea\u80fd 300 \uff5e 400                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"3652821\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : wangritian <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             1.wait_timeout=13 \u5982\u679c\u5e94\u7528\u4ee3\u7801\u7684\u77ed\u8fde\u63a5\u6ca1\u6709\u6b63\u786e\u5173\u95ed\u7684\u8bdd\uff0cmysql \u4f1a\u4fdd\u7559 13 \u79d2\uff0c\u6b64\u65f6\u652f\u6301\u7684\u6700\u5927\u5e76\u53d1\u6570\u662f max_connections\/(wait_timeout+1)\uff0c\u6025\u75c5\u4e71\u6295\u533b\u53ef\u4ee5\u6539\u6210 1 \u8bd5\u8bd5\uff0c\u4f46\u6211\u89c9\u5f97\u4e0d\u5065\u5eb7<br \/>2.\u5e76\u53d1\u91cf\u4e0d\u5c0f\uff0c\u7ee7\u7eed\u7528\u77ed\u8fde\u63a5\uff0c\u4f60\u7684\u673a\u5668\u4f1a\u53d8\u6210\u63e1\u624b\u72c2\u9b54<br \/>3.myisam \u63d2\u5165\u6570\u636e\u4f3c\u4e4e\u662f\u8868\u9501\uff0c\u8bfb\u5199\u5e76\u53d1\u6027\u80fd\u4f1a\u88ab innodb \u540a\u6253\u5427\uff0c\u800c\u4e14\u4f60\u7684\u4e1a\u52a1\u5199\u5165\u91cf\u5927\uff0c\u7d22\u5f15\u591a\u4e86\u53cd\u800c\u4f1a\u964d\u4f4e\u5199\u5165\u6027\u80fd                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"3652822\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : 594duck <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u5199\u591a\u8fd8\u662f\u8bfb\u591a\uff0c\u4e8c\u4e2a\u4f18\u5316\u65b9\u5411\u4e0d\u4e00\u6837<\/p>\n<p>\u77ed\u94fe\u63a5\u4e0d\u597d\uff0c\u7528\u957f\u94fe\u63a5\uff0c\u6211\u4eec\u6e38\u620f\u670d\u52a1\u5668\u90fd\u662f\u7528\u7684\u957f\u8fde\u63a5\uff0c\u4e00\u822c\u4e00\u4e2a\u524d\u7aef\u5f80\u540e\u9762\u5f00 10~30 \u4e2a\u8fde\u63a5\u3002\u957f\u94fe\u63a5\u5728\u6709\u89c4\u5212\u7684\u9ad8\u5cf0\u7684\u65f6\u5019\u66f4\u8282\u7701\u65f6\u95f4                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"3652823\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u4e3b<\/span> <span>\u8cc7\u6df1\u5927\u4f6c : pppguest3962 <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             @opengps \u660e\u767d\uff0c\u6211\u77e5\u9053\u5173\u4e8e\u8fd9\u4e2a\u77e5\u8bc6\u70b9\u7684\u539f\u56e0\u662f\uff0c\u867d\u7136\u540c\u6837\u662f\u635f\u8017\uff0c\u8fd9\u4e2a\u662f RAID \u548c\u968f\u673a\u8bfb\u5199\u7b49\u4e00\u4e32\u95ee\u9898\u9020\u6210\u7684\uff0c\u7528 SSD \u4f1a\u597d\u5f88\u591a\uff0c\u4e0d\u77e5\u9053\u662f\u4e0d\u662f\u6b63\u786e\u7684\u7406\u89e3\u4e86\u3002\u3002\u3002<\/p>\n<p>@wangritian 13 \u8fd9\u4e2a\u503c\u4e5f\u662f\u9010\u6b65\u51cf\u4e0b\u6765\u5230\u8fd9\u4e2a\u6570\u7684\uff0c\u5e76\u53d1\u5199\u7684\u65f6\u5019\uff0c\u6709\u65f6\u5019\u4f1a\u5ef6\u8fdf\u597d\u51e0\u79d2\u624d\u5199\u5b8c\uff0c10 \u79d2\u7684\u8bdd\uff0c\u591a\u6570\u60c5\u51b5\u90fd\u5199\u5b8c\u4e86\uff0c\u6240\u4ee5\u624d\u7528 13 \u7684\u3002\u3002\u3002 <br \/>\u63e1\u624b\u72c2\u9b54\u4e5f\u6ca1\u529e\u6cd5\u4e86\uff0c\u7a0b\u5e8f\u7684\u903b\u8f91\u6ca1\u6709\u529e\u6cd5\u5171\u7528\u4e00\u4e2a\u957f\u8fde\u63a5\u7684\u5bf9\u8c61\u3002\u3002\u3002\u9664\u975e\u91cd\u6784\uff0c\u4f46\u6ca1\u5565\u52a8\u529b\u53bb\u505a\u8fd9\u4e2a\u4e8b\u60c5\u3002\u3002\u3002 <br \/>\u81f3\u4e8e myisam \u8fd8\u662f innodb\uff0c\u6211\u56de\u5934\u628a SQL \u5168\u5bfc\u51fa\u6765\uff0c\u53cd\u6b63\u4e5f\u5c31 10 \u51e0 GB \u7684\u91cf\uff0c\u6539\u6210 innodb \u518d\u5012\u8fdb\u5e93\u91cc\uff0c\u8dd1\u51e0\u5929\u770b\u770b\u3002\u3002\u3002<\/p>\n<p>@594duck \u5199\u5f97\u591a\u5462\uff0c90%\u662f\u5199\uff0c10%\u662f\u8bfb\uff0cINSERT\/SELECT\/UPDATE \u4e4b\u6bd4\u5e94\u8be5\u662f 80%\/10%\/10%<br \/>\u8fd9\u4e2a mysql \u5c31\u50cf\u662f\u6570\u636e\u4ed3\u5e93\uff0c\u8bf4\u5b83\u51b7\u6570\u636e\u5462\uff0c\u5168\u662f\u51b7\u6570\u636e\uff0c\u8981\u7528\u7684\u65f6\u5019\u5462\uff0c\u5168\u662f\u70ed\u6570\u636e\uff0c\u5509                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"3652824\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : 594duck <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             @pppguest3962 <\/p>\n<p>\u5199\u7684\u591a\u7684\u8bdd\u5982\u679c\u4e00\u76f4\u662f\u589e\u52a0\u5199\u4e0d\u505a\u4efb\u4f55\u5220\u6539\uff0c\u662f\u53ef\u4ee5\u8003\u8651\u4e3b\u5e93\u4e0d\u8981\u7d22\u5f15\u7684\uff0c\u4e8c\u662f\u5173\u6ce8 IO WAIT\uff0c\u4e09\u662f\u4e00\u5b9a\u8981\u8003\u8651\u8bfb\u5199\u5206\u79bb\uff0c\u6bd5\u7adf\u4f60\u5230\u65f6\u5019\u8981\u8bfb\u8d77\u6765\u4e5f\u51f6\u3002\u53e6\u5916\u8981\u8003\u8651\u5206\u8868\uff08\u51cf\u5c11\u6570\u636e\u88ab\u62bd\u53d6\u548c\u4f7f\u7528\u7684\u65f6\u5019\u538b\u529b\uff09\u3002<\/p>\n<p>\u4e0d\u8981\u7d22\u5f15 \u53ef\u4ee5\u52a0\u5feb\u5199\uff0cZabbix \u5c31\u662f\u4e3a\u5199\u4f18\u5316\u7684\uff08\u6240\u4ee5\u8fd9\u9b3c\u4e1c\u897f\u8bfb\u8d77\u6765\u8981\u75af\u6389\uff09                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"3652825\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : aru <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             \u53ef\u4ee5\u5c1d\u8bd5 innodb\uff0c\u7ed9 mysql \u66f4\u591a\u7684\u5185\u5b58<br \/>\u6362 ssd \u5e94\u8be5\u5c31\u7acb\u7aff\u89c1\u5f71\u4e86\uff0c\u4e0d\u8fc7\u6d89\u53ca\u5230\u91c7\u8d2d\u4e0d\u597d\u8bf4                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li data-pid=\"3652826\" data-uid=\"2\">\n<div>\n<div>\n<div> <span>\u8cc7\u6df1\u5927\u4f6c : wangritian <\/span>  <\/div>\n<div> <i title=\"\u5f15\u7528\"><\/i>  <span>          <\/span> <\/div>\n<\/p><\/div>\n<div>                                                             @pppguest3962 wait_timeout \u5e94\u8be5\u662f\u6307\u4e00\u4e2a\u8fde\u63a5\u7684\u4efb\u52a1\u5b8c\u6210\u4e14\u672a\u4e3b\u52a8\u5173\u95ed\u65f6\uff0cmysql \u7b49\u5f85\u7684\u8d85\u65f6\u65f6\u95f4\uff0c\u4e0d\u662f\u4e00\u53e5 sql \u6700\u5927\u7684\u6267\u884c\u65f6\u95f4\uff0c\u6240\u4ee5\u8ddf insert \u672c\u8eab\u7684\u65f6\u95f4\u65e0\u5173\uff0c\u53ef\u4ee5\u8bbe\u7f6e\u4e3a 1 \u3002\u6211\u89c9\u5f97\u5e94\u8be5\u5148\u4ece\u5199\u5165\u6027\u80fd\u4f18\u5316\u4e86\uff0c\u7136\u540e\u624d\u662f\u53c2\u6570\u548c\u957f\u8fde\u63a5\u3002\u77ed\u8fde\u63a5\u4e0d\u597d\u6539\u7684\u8bdd\uff0c\u5efa\u8bae\u627e\u4e00\u4e2a\u961f\u5217\u4ea7\u54c1\u4e22\u8fdb\u53bb\uff0c\u7136\u540e\u7528 go\/py \u5199\u4e2a\u6d88\u8d39\u8005\u6279\u91cf\u5165\u5e93\u3002\u5f15\u64ce\u8fd8\u662f\u5f3a\u70c8\u5efa\u8bae innodb\uff0c\u53ea\u6709\u8d85\u5927\u91cf\u8bfb\u3001\u6781\u5c11\u5199\u5165\u7684\u9879\u76ee\u9002\u5408 myisam                                                            <\/div>\n<\/p><\/div>\n<\/li>\n<li>\n","protected":false},"excerpt":{"rendered":"<p>\u8bf7\u5927\u5bb6\u8bc4\u70b9\u4e00\u4e0b\u8fd9\u4e2a\u8001\u7269\u7406\u673a\u5728\u865a\u62df\u673a&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/165519"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=165519"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/165519\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=165519"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=165519"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=165519"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}