{"id":540,"date":"2014-03-13T11:54:39","date_gmt":"2014-03-13T03:54:39","guid":{"rendered":"http:\/\/www.gpfeng.com\/?p=540"},"modified":"2014-03-18T14:47:20","modified_gmt":"2014-03-18T06:47:20","slug":"mysql-threadpool%ef%bc%88%e4%bc%98%e5%85%88%e9%98%9f%e5%88%97%ef%bc%89%e4%bb%8b%e7%bb%8d%e5%8f%8a%e6%80%a7%e8%83%bd%e6%b5%8b%e8%af%95","status":"publish","type":"post","link":"http:\/\/www.gpfeng.com\/?p=540","title":{"rendered":"MySQL threadpool\uff08\u4f18\u5148\u961f\u5217\uff09\u4ecb\u7ecd\u53ca\u6027\u80fd\u6d4b\u8bd5"},"content":{"rendered":"

\u80cc\u666f\u4ecb\u7ecd<\/h3>\n

MySQL\u5e38\u7528\uff08\u76ee\u524d\u7ebf\u4e0a\u4f7f\u7528\uff09\u7684\u7ebf\u7a0b\u8c03\u5ea6\u65b9\u5f0f\u662fone-thread-per-connection\uff08\u6bcf\u8fde\u63a5\u4e00\u4e2a\u7ebf\u7a0b\uff09\uff0cserver\u4e3a\u6bcf\u4e00\u4e2a\u8fde\u63a5\u521b\u5efa\u4e00\u4e2a\u7ebf\u7a0b\u6765\u670d\u52a1\uff0c\u8fde\u63a5\u65ad\u5f00\u540e\uff0c\u8fd9\u4e2a\u7ebf\u7a0b\u8fdb\u5165thread_cache\u6216\u8005\u76f4\u63a5\u9000\u51fa\uff08\u53d6\u51b3\u4e8ethread_cache\u8bbe\u7f6e\u53ca\u7cfb\u7edf\u5f53\u524d\u5df2\u7ecfcache\u7684\u7ebf\u7a0b\u6570\u76ee\uff09\uff0cone-thread-per-connection\u8c03\u5ea6\u7684\u597d\u5904\u662f\u5b9e\u73b0\u7b80\u5355\uff0c\u800c\u4e14\u80fd\u591f\u5728\u7cfb\u7edf\u6ca1\u6709\u9047\u5230\u74f6\u9888\u4e4b\u524d\u4fdd\u8bc1\u8f83\u5c0f\u7684\u54cd\u5e94\u65f6\u95f4\uff0c\u6bd4\u8f83\u9002\u5408\u6d3b\u8dc3\u7684\u957f\u8fde\u63a5\u7684\u5e94\u7528\u573a\u666f\uff0c\u800c\u5728\u5927\u91cf\u77ed\u8fde\u63a5\u6216\u8005\u9ad8\u5e76\u53d1\u60c5\u51b5\u4e0b\uff0cone-thread-per-connection\u9700\u8981\u521b\u5efa\/\u8c03\u5ea6\u5927\u91cf\u7684\u7ebf\u7a0b\uff0c\u4ea7\u751f\u8f83\u9ad8\u7684\u7684context-switch\u4ee3\u4ef7\uff0c\u4ece\u800c\u4f7f\u5f97\u7cfb\u7edf\u6027\u80fd\u4e0b\u964d
\n
\n\u4e3a\u4e86\u89e3\u51b3\u8fd9\u4e2a\u95ee\u9898\uff0cOracle\u548cMariaDB\u5206\u522b\u63a8\u51fa\u4e86threadpool\u65b9\u6848\uff0c\u76ee\u524dOracle\u7684threadpool\u5b9e\u73b0\u4e3aplugin\u65b9\u5f0f\uff0c\u5e76\u4e14\u53ea\u6dfb\u52a0\u5230\u5728Enterprise\u7248\u672c\u4e2d\uff0c\u6ca1\u6709\u516c\u5e03\u4ee3\u7801\uff0cMariaDB threadpool\u57285.5\u7248\u672c\u4e2d\u5f15\u5165\uff0c\u6211\u4eec\u4e00\u76f4\u5bc6\u5207\u5173\u6ce8\u793e\u533a\u52a8\u6001\u5e76\u5728\u7b2c\u4e00\u65f6\u95f4\u6d4b\u8bd5\u4e86MariaDB threapool\u6027\u80fd\uff0c\u5e76\u4e14\u53d1\u73b0\u4e86\u4e00\u4e9b\u5176\u4e2d\u7684\u95ee\u9898\uff0c\u6bd4\u5982\uff1a\u8981\u50cf\u53d1\u6325\u7ebf\u7a0b\u6c60\u7684\u4f18\u52bf\uff0c\u9700\u8981\u5c3d\u91cf\u63a7\u5236\u7ebf\u7a0b\u6c60\u4e2d\u7ebf\u7a0b\u6570\u76ee\uff0c\u5426\u5219\u4f1a\u9000\u5316\u6210one-thread-per-connection\uff0c\u800c\u5982\u679c\u4e25\u683c\u63a7\u5236\u7ebf\u7a0b\u6c60\u4e2d\u7ebf\u7a0b\u6570\u636e\uff0c\u53ef\u80fd\u4f1a\u51fa\u73b0\u8c03\u5ea6\u4e0a\u7684\u6b7b\u9501\uff0cpercona\u5728\u79fb\u690dMariaDB threadpool\u7684\u5b9e\u73b0\u540e\u8fdb\u4e00\u6b65\u4f18\u5316\u4e86\u7ebf\u7a0b\u6c60\u6027\u80fd\uff0c\u901a\u8fc7\u5f15\u5165\u4f18\u5148\u961f\u5217\u5f88\u597d\u89e3\u51b3\u4e86\u8fd9\u4e2a\u95ee\u9898\uff0c\u7ecf\u6d4b\u8bd5\u6548\u679c\u660e\u663e\uff0c\u56e0\u6b64\u6211\u4eec\u5c06\u8fd9\u4e2a\u7279\u6027port\u5230\u4e86AliMySQL\u4e2d<\/p>\n

\u5b9e\u73b0\u7b80\u4ecb<\/h3>\n

1. threadpool\u4e2dworker\u7ebf\u7a0b\u5904\u7406\u5355\u4f4d\u4e3a\u4e00\u4e2asql\uff0c\u800c\u4e0d\u662fone-thread-per-connection\u5bf9\u5e94\u7684\u4e00\u4e2a\u8fde\u63a5\uff1b\u5f53worker\u7ebf\u7a0b\u5904\u7406\u5b8cA\u8fde\u63a5\u53d1\u9001\u6765\u7684\u4e00\u4e2asql\u540e\uff0cA\u8fde\u63a5\u6ca1\u6709\u7acb\u523b\u53d1\u9001\u7b2c\u4e8c\u6761sql\uff0cworker\u7ebf\u7a0b\u4f1a\u53bb\u670d\u52a1\u5176\u5b83\u8fde\u63a5\u53d1\u9001\u6765\u7684sql\uff0c\u56e0\u6b64worker\u7ebf\u7a0b\u5de5\u4f5c\u6548\u7387\u66f4\u9ad8\uff0c\u7cfb\u7edf\u9700\u8981\u7684\u7ebf\u7a0b\u6570\u4e5f\u66f4\u5c11<\/p>\n

2. threadpool\u672c\u8d28\u4e0a\u662f\u4e00\u4e2a\u751f\u4ea7\u8005-\u6d88\u8d39\u8005\u6a21\u578b\uff0c\u4e3a\u4e86\u51cf\u5c0f\u7ade\u4e89\uff0cthreadpool\u88ab\u5212\u5206\u4e3aN\u4e2agroup\uff08n\u9ed8\u8ba4\u4e3acpu\u6838\u5fc3\u6570\uff09\uff0c\u8fde\u63a5\u53d1\u9001\u7684sql\u6839\u636e\u8fde\u63a5id\u5206\u914d\u5230\u4e0d\u540c\u7684group\u4e2d\uff0c\u56e0\u6b64\uff0c\u540c\u4e00\u4e2a\u8fde\u63a5\u53d1\u9001\u7684\u6240\u6709sql\u662f\u88ab\u540c\u4e00\u4e2agroup\u4e2d\u7684worker\u7ebf\u7a0b\u5904\u7406\u7684<\/p>\n

3. \u6bcf\u4e2agroup\u90fd\u67092\u4e2a\u4efb\u52a1\u961f\u5217\uff0c\u5373\u4f18\u5148\u961f\u5217\u548c\u666e\u901a\u961f\u5217<\/strong>\uff0c\u5982\u679c\u4e00\u4e2asql\u6240\u5728\u7684\u4e8b\u52a1\u5df2\u7ecf\u5f00\u542f\uff0c\u5219\u5c06\u4efb\u52a1\u653e\u5230\u4f18\u5148\u961f\u5217\u4e2d\uff0c\u5426\u5219\u653e\u5230\u666e\u901a\u961f\u5217\u4e2d\uff0cworker\u7ebf\u7a0b\u4f18\u5148\u4ece\u4f18\u5148\u961f\u5217\u4e2d\u53d6\u4efb\u52a1\u6267\u884c\uff0c\u5f53\u4f18\u5148\u961f\u5217\u4e3a\u7a7a\u5219\u4ece\u666e\u901a\u961f\u5217\u53d6\u4efb\u52a1\u6267\u884c\uff0c\u8fd9\u4e2a\u53ef\u4ee5\u4fdd\u8bc1\u5df2\u7ecf\u5f00\u542f\u7684\u4e8b\u52a1\u4f18\u5148\u5f97\u5230\u6267\u884c\uff0c\u4ece\u800c\u5c3d\u65e9\u91ca\u653e\u5176\u5360\u7528\u7684\u8d44\u6e90\uff08\u4e3b\u8981\u662f\u9501\uff09\uff0c\u53ef\u4ee5\u6709\u6548\u51cf\u5c0f\u54cd\u5e94\u65f6\u95f4\uff0c\u5e76\u4e14\u907f\u514d\u8c03\u5ea6\u4e0a\u7684\u6b7b\u9501\uff08A\u548cB\u88ab\u5206\u5230\u4e0d\u540c\u7684group\u4e2d\uff0cA\u4e8b\u52a1\u5df2\u7ecf\u5f00\u542f\uff0c\u5e76\u4e14\u83b7\u5f97\u4e86\u9501\uff0c\u53ef\u80fd\u65e0\u6cd5\u7acb\u5373\u5f97\u5230\u8c03\u5ea6\u6267\u884c\uff0cB\u4e8b\u52a1\u4f9d\u8d56A\u4e8b\u52a1\u91ca\u653e\u9501\u8d44\u6e90\uff0c\u4f46\u662f\u5148\u4e8eA\u5f97\u5230\u8c03\u5ea6\uff09<\/p>\n

4. \u6bcf\u4e2agroup\u4e2d\u6bcf\u4e2aworker\u7ebf\u7a0b\u5730\u4f4d\u4e00\u6837\uff0c\u5982\u679c\u9047\u5230\u4efb\u52a1\u961f\u5217\u4e3a\u7a7a\u7684\u60c5\u51b5\uff0c\u7ebf\u7a0b\u4f1a\u8c03\u7528epoll_wait\u6279\u91cf\u53d6\u4efb\u52a1<\/p>\n

5. threadpool\u989d\u5916\u521b\u5efa\u4e86\u4e00\u4e2atimer\u7ebf\u7a0b\uff0c\u6bcf\u9694\u4e00\u6bb5\u65f6\u95f4\u68c0\u67e5\u4e00\u904d\u6240\u6709\u7684group\uff0c\u5982\u679c\u53d1\u73b0group\u51fa\u73b0\u5f02\u5e38\uff08\u5835\u585e\/\u8d85\u65f6\/worker\u7ebf\u7a0b\u6570\u76ee\u4e0d\u591f\uff09\uff0c\u53ca\u65f6\u5524\u9192\u7ebf\u7a0b<\/p>\n

\u5173\u4e8eMySQL\u7ebf\u7a0b\u8c03\u5ea6\u63a5\u53e3\u4ee5\u53caMariaDB threadpool\u5b9e\u73b0\u7ec6\u8282\u53ef\u4ee5\u53c2\u8003\u4e4b\u524d\u6211\u5199\u7684\u4e24\u7bc7\u535a\u5ba2\uff1amysql thread sheduler \u6e90\u7801\u5206\u6790<\/a>\uff0cmariadb 5.5 threadpool \u6e90\u7801\u5206\u6790<\/a><\/p>\n

threadpool\u76f8\u5173\u53c2\u6570<\/h3>\n
\r\nroot@(none) 05:33:27>show global variables like '%thread_pool%';\r\n+-------------------------------+--------------+\r\n| Variable_name                 | Value        |\r\n+-------------------------------+--------------+\r\n| thread_pool_high_prio_mode    | transactions |\r\n| thread_pool_high_prio_tickets | 4294967295   |\r\n| thread_pool_idle_timeout      | 60           |\r\n| thread_pool_max_threads       | 100000       |\r\n| thread_pool_oversubscribe     | 3            |\r\n| thread_pool_size              | 24           |\r\n| thread_pool_stall_limit       | 500          |\r\n+-------------------------------+--------------+\r\n7 rows in set (0.00 sec)\r\n<\/pre>\n
thread_pool_high_prio_mode<\/strong>
\n\u6709\u4e09\u4e2a\u53d6\u503c\uff1atransactions \/ statements \/ none
\ntransactions(default): \u4f7f\u7528\u4f18\u5148\u961f\u5217\u548c\u666e\u901a\u961f\u5217\uff0c\u5bf9\u4e8e\u4e8b\u52a1\u5df2\u7ecf\u5f00\u542f\u7684statement\uff0c\u653e\u5230\u4f18\u5148\u961f\u5217\u4e2d\uff0c\u5426\u5219\u653e\u5230\u666e\u901a\u961f\u5217\u4e2d
\nstatements\uff1a\u53ea\u4f7f\u7528\u4f18\u5148\u961f\u5217
\nnone: \u53ea\u662f\u7528\u666e\u901a\u961f\u5217\uff0c\u672c\u8d28\u4e0a\u548cstatements\u76f8\u540c\uff0c\u90fd\u662f\u53ea\u662f\u7528\u4e00\u4e2a\u961f\u5217<\/p>\n
thread_pool_high_prio_tickets<\/strong>
\n\u53d6\u503c0~4294967295\uff0c\u5f53\u5f00\u542f\u4e86\u4f18\u5148\u961f\u5217\u6a21\u5f0f\u540e\uff08thread_pool_high_prio_mode=transactions\uff09\uff0c\u6bcf\u4e2a\u8fde\u63a5\u6700\u591a\u5141\u8bb8thread_pool_high_prio_tickets\u6b21\u88ab\u653e\u5230\u4f18\u5148\u961f\u5217\u4e2d\uff0c\u4e4b\u540e\u653e\u5230\u666e\u901a\u961f\u5217\u4e2d\uff0c\u9ed8\u8ba4\u4e3a4294967295<\/p>\n
thread_pool_idle_timeout<\/strong>
\nworker\u7ebf\u7a0b\u6700\u5927\u7a7a\u95f2\u65f6\u95f4\uff0c\u5355\u4f4d\u4e3a\u79d2\uff0c\u8d85\u8fc7\u9650\u5236\u540e\u4f1a\u9000\u51fa\uff0c\u9ed8\u8ba460<\/p>\n
thread_pool_max_threads<\/strong>
\nthreadpool\u4e2d\u6700\u5927\u7ebf\u7a0b\u6570\u76ee\uff0c\u6240\u6709group\u4e2dworker\u7ebf\u7a0b\u603b\u6570\u8d85\u8fc7\u8be5\u9650\u5236\u540e\u4e0d\u80fd\u7ee7\u7eed\u521b\u5efa\u66f4\u591a\u7ebf\u7a0b\uff0c\u9ed8\u8ba4100000<\/p>\n
thread_pool_oversubscribe<\/strong>
\n\u4e00\u4e2agroup\u4e2d\u7ebf\u7a0b\u6570\u8fc7\u8f7d\u9650\u5236\uff0c\u5f53\u4e00\u4e2agroup\u4e2d\u7ebf\u7a0b\u6570\u8d85\u8fc7\u6b21\u9650\u5236\u540e\uff0c\u7ee7\u7eed\u521b\u5efaworker\u7ebf\u7a0b\u4f1a\u88ab\u5ef6\u8fdf\uff0c\u9ed8\u8ba43<\/p>\n
thread_pool_size<\/strong>
\nthreadpool\u4e2dgroup\u6570\u91cf\uff0c\u9ed8\u8ba4\u4e3acpu\u6838\u5fc3\u6570\uff0cserver\u542f\u52a8\u65f6\u81ea\u52a8\u8ba1\u7b97<\/p>\n
thread_pool_stall_limit<\/strong>
\ntimer\u7ebf\u7a0b\u68c0\u6d4b\u95f4\u9694\uff0c\u5355\u4f4d\u4e3a\u6beb\u79d2\uff0c\u9ed8\u8ba4500<\/p>\n
\u6027\u80fd\u6d4b\u8bd5<\/h3>\n
\u6d4b\u8bd5\u786c\u4ef6\uff1a<\/strong>
\nmysql\u670d\u52a1\u5668\uff1amybxxxxxx.cm3
\nmytest\u538b\u529b\u673a\uff1amyayyyyyy.cm3
\n\u4e24\u53f0\u673a\u5668\u90fd\u4e3a24\u6838\u5fc3cpu\uff0c192G\u5185\u5b58\uff0cbufferpool: 70G<\/p>\n
\u6027\u80fd\u6307\u6807\uff1a<\/strong>
\nQPS\/TPS\/RT(Response time)<\/p>\n
\u5b9e\u9a8c\u5bf9\u7167<\/strong>
\none-thread-per-connection:  \u57fa\u51c6\u6570\u636e
\nthreadpool(high prio off):  \u7ebf\u7a0b\u6c60\u65b9\u6848\uff0c\u4f18\u5148\u961f\u5217\u4e0d\u5f00\u542f
\nthreadpool(high prio on):  \u7ebf\u7a0b\u6c60\u65b9\u6848\uff0c\u5f00\u542f\u4f18\u5148\u961f\u5217<\/p>\n
\u53ea\u8bfb\u573a\u666f<\/h4>\n
tc_read_1_3(\u8bfb\u53d6\u6570\u636e\u5360\u6240\u6709\u6570\u636e1\/3)\uff0ctcbuyer_0000(\u6570\u636e\u91cf120G)\uff0csql\u5e8f\u5217\uff1a<\/p>\n
\r\nset autocommit=0; select...; commit; select...; commit;...\r\n<\/pre>\n
QPS\uff1a
\n\"commit\"<\/a><\/p>\n
RT\uff1a
\n\"commit\"<\/a><\/p>\n
\u7ed3\u679c\u8bf4\u660e<\/h4>\n
threadpool\u65b9\u6848\uff0c\u5f00\u542f\u4f18\u5148\u961f\u5217\u540e\u80fd\u591f\u5728\u9ad8\u5e76\u53d1\u4e0b\u7ef4\u6301\u6700\u9ad8\u6027\u80fd\uff0c\u4e14RT\u66f4\u4f4e\uff0c\u800c\u4e0d\u5f00\u542f\u4f18\u5148\u961f\u5217\uff0c\u5728\u9ad8\u5e76\u53d1\u4e0b\u6027\u80fd\u751a\u81f3\u4f1a\u6bd4one-thread-per-connection\u4f4e\uff0c\u539f\u56e0\uff1a\u672c\u6d4b\u8bd5\u4e2d\uff0csql\u6267\u884c\u5e8f\u5217\u4e3a\uff1a
\nset autocommit=0; select…; commit; select…; commit;…\u5728\u4e0d\u5f00\u542f\u4f18\u5148\u961f\u5217\u7684\u60c5\u51b5\u4e0b\uff0c\u56e0\u4e3a\u7ebf\u7a0b\u6c60\u4ee5sql\u4e3a\u8c03\u5ea6\u5355\u4f4d\uff0c\u5bfc\u81f4\u6d3b\u8dc3\u4e8b\u52a1\u94fe\u8868\u76f8\u5bf9\u4e8eone-thread-per-connection\u66f4\u957f<\/p>\n
thread_pool_stall_limit\u5f71\u54cd\uff1a<\/h4>\n
1. thread_pool_stall_limit = 500\uff08default\uff0cRT 50ms\uff0c\u6d3b\u8dc3\u4e8b\u52a1589\u4e2a\uff0cQPS\uff1a22K<\/strong>\uff09<\/p>\n
\r\n- - --------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ --------tcprstat(us)-------- \r\n- - -  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|  count    avg 95-avg 99-avg|\r\n16:01:45|24.42 28.74 41.41| 58  26  16   0|    0    0|    0     0     0  22156     0|   45074 100.00|    0     0     0  22147|  32 2050    0    0|  27401  51453  48623  50722|\r\n16:01:50|24.71 28.72 41.34| 56  27  17   0|    0    0|    0     0     0  22197     0|   45085 100.00|    0     0     0  22188|  32 2050    0    0|  29130  50076  47418  49414|\r\n16:01:55|24.97 28.71 41.27| 57  27  17   0|    0    0|    0     0     0  22368     0|   45490 100.00|    0     0     0  22363|  28 2050    0    0|  28890  50118  47570  49484|\r\n16:02:01|24.74 28.60 41.17| 57  26  17   0|    0    0|    0     0     0  22139     0|   44947 100.00|    0     0     0  22126|  30 2050    0    0|  29835  49205  46741  48593|\r\n16:02:06|24.44 28.47 41.06| 57  26  17   0|    0    0|    0     0     0  22350     0|   45434 100.00|    0     0     0  22343|  33 2050    0    0|  28861  50235  47547  49553|\r\n<\/pre>\n
\r\n$mysql -uroot -e \"show engine innodb status\\G\"|grep 'TRANSACTION'|grep 'ACTIVE'|wc -l\r\n589\r\n<\/pre>\n
2. thread_pool_stall_limit = 50\uff08RT 30ms\uff0c\u6d3b\u8dc3\u4e8b\u52a1233\u4e2a\uff0cQPS\uff1a43K<\/strong>\uff09<\/p>\n
\r\n- - --------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ --------tcprstat(us)-------- \r\n- - -  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|  count    avg 95-avg 99-avg|\r\n16:05:07|36.72 30.31 39.44| 61  34   5   0|    0    0|    0     0     0  43161     0|   87709 100.00|    0     0     0  43086|  90 2050    0    0|  27352  32342  28501  31226|\r\n16:05:13|37.14 30.51 39.46| 61  34   5   0|    0    0|    0     0     0  43569     0|   88545 100.00|    0     0     0  43489|  91 2050    0    0|  25431  34640  30410  33380|\r\n16:05:18|39.69 31.15 39.61| 62  34   5   0|    0    0|    0     0     0  42948     0|   87307 100.00|    0     0     0  42872|  93 2050    0    0|  26660  33376  29565  32292|\r\n16:05:23|41.88 31.74 39.76| 61  34   5   0|    0    0|    0     0     0  43063     0|   87508 100.00|    0     0     0  42977|  88 2050    0    0|  28459  32175  28363  31055|\r\n16:05:28|44.05 32.36 39.92| 62  33   5   0|    0    0|    0     0     0  43448     0|   88287 100.00|    0     0     0  43375|  89 2050    0    0|  25910  34201  30115  32981|\r\n<\/pre>\n
\r\n$mysql -uroot -e \"show engine innodb status\\G\"|grep 'TRANSACTION'|grep 'ACTIVE'|wc -l\r\n233\r\n<\/pre>\n
3. thread_pool_stall_limit = 30\uff08RT 30ms\uff0c\u6d3b\u8dc3\u4e8b\u52a1127\u4e2a\uff0cQPS\uff1a52K<\/strong>\uff09<\/p>\n
\r\n- - --------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ --------tcprstat(us)-------- \r\n- - -  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|  count    avg 95-avg 99-avg|\r\n16:06:43|57.76 39.25 41.74| 64  33   3   0|    0    0|    0     0     0  52850     0|  107456 100.00|    0     0     0  52740|  95 2050    0    0|  26054  28743  24475  27464|\r\n16:06:48|59.46 39.91 41.94| 66  32   3   0|    0    0|    0     0     0  53107     0|  107873 100.00|    0     0     0  52997|  95 2050    0    0|  22254  32018  27256  30635|\r\n16:06:54|61.02 40.56 42.14| 64  33   3   0|    0    0|    0     0     0  51907     0|  105618 100.00|    0     0     0  51837|  95 2050    0    0|  23849  30579  26223  29298|\r\n16:06:59|59.73 40.97 42.26| 64  33   3   0|    0    0|    0     0     0  52717     0|  107101 100.00|    0     0     0  52571|  94 2050    0    0|  23591  30359  26042  29117|\r\n16:07:04|60.31 41.40 42.39| 64  33   3   0|    0    0|    0     0     0  52412     0|  106468 100.00|    0     0     0  52306|  93 2050    0    0|  26934  28257  24280  27082|\r\n<\/pre>\n
\r\n$mysql -uroot -e \"show engine innodb status\\G\"|grep 'TRANSACTION'|grep 'ACTIVE'|wc -l\r\n127\r\n<\/pre>\n
\u89e3\u91ca\uff1a<\/strong>thread_pool_stall_limit \u662f\u540e\u53f0timer\u7ebf\u7a0b\u68c0\u6d4b\u4efb\u52a1\u662f\u5426\u5835\u585e\u7684\u65f6\u95f4\u95f4\u9694\uff0c\u5728\u5e76\u53d1\u538b\u529b\u8f83\u5927\u65f6\uff0c\u8be5\u53c2\u6570\u8bbe\u7f6e\u8fc7\u5927\u53ef\u80fd\u4f1a\u9020\u6210timer\u7ebf\u7a0b\u65e0\u6cd5\u53ca\u65f6\u5524\u9192\/\u521b\u5efaworker\u7ebf\u7a0b\uff0c\u4ece\u6d4b\u8bd5\u7ed3\u679c\u4e2d\u53ef\u4ee5\u770b\u51fa\u8be5\u53c2\u6570\u8bbe\u7f6e\u4e3a\u6700\u5927\u53ef\u63a5\u53d7\u7684RT<\/strong>\u6bd4\u8f83\u5408\u9002\uff08\u5e94\u7528\u671f\u671bDB\u7684\u6700\u5927\u54cd\u5e94\u65f6\u95f4\uff09<\/p>\n
\u8bfb\u5199\u573a\u666f>\/h4>
\ntc_rw_5_1(\u8bfb\u5199\u538b\u529b\u4e3a5:1)\uff0ctcbuyer_0000(\u6570\u636e\u91cf\u7ea6120G)\uff0csql\u5e8f\u5217\uff1a<\/p>\n
\r\nset autocommit=0; update...;update;commit\/select...;select...;commit\/update...;select...;commit\/...\r\n<\/pre>\n
QPS + TPS
\n\"commit\"<\/a><\/p>\n
RT\uff1a
\n\"commit\"<\/a><\/p>\n
\u7ed3\u679c\u8bf4\u660e<\/h4>\n
\u5f00\u542f\u4f18\u5148\u961f\u5217\u7684threadpool\u5728\u9ad8\u5e76\u53d1\u4e0b\u53ef\u4ee5\u4fdd\u6301\u6700\u9ad8\u6027\u80fd\uff0c\u540c\u65f6rt\u4e5f\u8f83\u5c0f\uff0c\u800cone-thread-per-connection\u4e0e\u4e0d\u5f00\u542f\u4f18\u5148\u961f\u5217\u7684threadpool\u8c03\u5ea6\u65b9\u6848\u5728\u9ad8\u5e76\u53d1\u4e0b\u6027\u80fd\u6025\u5267\u4e0b\u964d\uff0crt\u660e\u663e\u5347\u9ad8<\/p>\n<\/h3>\n
\u6d4b\u8bd5\u7ed3\u8bba<\/h3>\n
\u5f00\u542f\u4f18\u5148\u961f\u5217\u7684threadpool\u901a\u8fc7\u4f18\u5148\u8c03\u5ea6\u5df2\u5f00\u542f\u4e8b\u52a1\u7f29\u77ed\u4e8b\u52a1\u6267\u884c\u65f6\u95f4\uff0c\u5728\u9ad8\u5e76\u53d1\u4e0b\u53ef\u4ee5\u4fdd\u6301\u6700\u9ad8\u6027\u80fd\uff0c\u540c\u65f6\u4fdd\u8bc1\u8f83\u5c0f\u7684rt<\/p>\n
MySQL \u53c2\u6570\u914d\u7f6e<\/h4>\n
\r\n[mysqld_safe]\r\npid-file=\/u01\/my3306\/run\/mysqld.pid\r\nmalloc-lib=\/u01\/mysql\/lib\/libjemalloc.so\r\n\r\n[mysql]\r\nport=3306\r\nprompt=\\\\u@\\\\d \\\\r:\\\\m:\\\\s>\r\ndefault-character-set=gbk\r\nno-auto-rehash\r\n\r\n[client]\r\nport=3306\r\nsocket=\/u01\/my3306\/run\/mysql.sock\r\n\r\n[mysqld]\r\n#dir\r\nbasedir=\/u01\/my3306\r\ndatadir=\/u01\/my3306\/data\r\ntmpdir=\/u01\/my3306\/tmp\r\nlc_messages_dir=\/u01\/mysql\/share\r\n#log-error=\/u01\/my3306\/log\/alert.log\r\nslow_query_log_file=\/u01\/my3306\/log\/slow.log\r\ngeneral_log_file=\/u01\/my3306\/log\/general.log\r\nsocket=\/u01\/my3306\/run\/mysql.sock\r\n\r\n#innodb\r\ninnodb_data_home_dir=\/u01\/my3306\/data\r\ninnodb_log_group_home_dir=\/u01\/my3306\/data\r\ninnodb_data_file_path = ibdata1:4G;ibdata2:16M:autoextend\r\ninnodb_buffer_pool_size=70G\r\ninnodb_buffer_pool_instances=8\r\ninnodb_log_files_in_group=4\r\ninnodb_log_file_size=1G\r\ninnodb_log_buffer_size=200M\r\ninnodb_flush_log_at_trx_commit=1\r\ninnodb_additional_mem_pool_size=20M\r\ninnodb_max_dirty_pages_pct=60\r\ninnodb_io_capacity=1000\r\ninnodb_thread_concurrency=32\r\ninnodb_read_io_threads=8\r\ninnodb_write_io_threads=8\r\ninnodb_open_files=60000\r\ninnodb_file_format=Barracuda\r\ninnodb_file_per_table=1\r\ninnodb_flush_method=O_DIRECT\r\ninnodb_flush_neighbor_pages=0\r\ninnodb_change_buffering=inserts\r\ninnodb_adaptive_flushing=1\r\ninnodb_adaptive_flushing_method=keep_average\r\ninnodb_adaptive_hash_index_partitions=1\r\ninnodb_old_blocks_time=1000\r\ninnodb_fast_checksum=1\r\ninnodb_stats_on_metadata=0\r\ninnodb_lazy_drop_table=0\r\ninnodb_read_ahead=0\r\ninnodb_use_native_aio=0\r\ninnodb_lock_wait_timeout=5\r\ninnodb_rollback_on_timeout=0\r\ninnodb_purge_threads=1\r\ninnodb_strict_mode=1\r\ntransaction-isolation=READ-COMMITTED\r\n\r\n#myisam\r\nkey_buffer=64M\r\nmyisam_sort_buffer_size=64M\r\nconcurrent_insert=2\r\ndelayed_insert_timeout=300\r\n#replication\r\nmaster-info-file=\/u01\/my3306\/log\/master.info\r\nrelay-log=\/u01\/my3306\/log\/relaylog\r\nrelay_log_info_file=\/u01\/my3306\/log\/relay-log.info\r\nrelay-log-index=\/u01\/my3306\/log\/mysqld-relay-bin.index\r\nslave_load_tmpdir=\/u01\/my3306\/tmp\r\nslave_type_conversions=\"ALL_NON_LOSSY\"\r\nslave_net_timeout=4\r\nskip-slave-start\r\nsync_master_info=1000\r\nsync_relay_log_info=1000\r\n\r\n#binlog\r\nlog-bin=\/u01\/my3306\/log\/mysql-bin\r\nserver_id=0\r\nbinlog_cache_size=32K\r\nmax_binlog_cache_size=2G\r\nmax_binlog_size=500M\r\nbinlog-format=ROW\r\nsync_binlog=1000\r\nlog-slave-updates=1\r\nexpire_logs_days=0\r\n\r\n#server\r\ndefault-storage-engine=INNODB\r\ncharacter-set-server=gbk\r\nlower_case_table_names=1\r\nskip-external-locking\r\nopen_files_limit=655360\r\nsafe-user-create\r\nlocal-infile=1\r\n#sqlmod=\"STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE\"\r\nperformance_schema=0\r\nlog_slow_admin_statements=1\r\nlog_slow_verbosity=full\r\nlog_warnings=1\r\nlong_query_time=1\r\nslow_query_log=1\r\ngeneral_log=0\r\nquery_cache_type=0\r\nquery_cache_limit=1M\r\nquery_cache_min_res_unit=1K\r\ntable_definition_cache=65536\r\ntable_cache=65536\r\nthread_stack=512K\r\nthread_cache_size=256\r\nread_rnd_buffer_size=128K\r\nsort_buffer_size=256K\r\njoin_buffer_size=128K\r\nread_buffer_size=128K\r\nport=3306\r\nskip-name-resolve\r\nskip-ssl\r\nmax_connections=18500\r\nmax_user_connections=18000\r\nmax_connect_errors=65536\r\nmax_allowed_packet=128M\r\nconnect_timeout=8\r\nnet_read_timeout=30\r\nnet_write_timeout=60\r\nback_log=1024\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u80cc\u666f\u4ecb\u7ecd MySQL\u5e38\u7528\uff08\u76ee\u524d\u7ebf\u4e0a\u4f7f\u7528\uff09\u7684\u7ebf\u7a0b\u8c03\u5ea6\u65b9\u5f0f\u662fone-thread-per-connection\uff08\u6bcf … \u7ee7\u7eed\u9605\u8bfbMySQL threadpool\uff08\u4f18\u5148\u961f\u5217\uff09\u4ecb\u7ecd\u53ca\u6027\u80fd\u6d4b\u8bd5<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[1],"tags":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3tPZp-8I","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/540"}],"collection":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=540"}],"version-history":[{"count":13,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/540\/revisions"}],"predecessor-version":[{"id":569,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/540\/revisions\/569"}],"wp:attachment":[{"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=540"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=540"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.gpfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=540"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}