{"id":35657,"date":"2025-05-05T17:20:50","date_gmt":"2025-05-05T09:20:50","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/35657.html"},"modified":"2025-05-05T17:20:50","modified_gmt":"2025-05-05T09:20:50","slug":"%e4%bb%8e%e5%ae%9e%e5%88%97%e4%b8%ad%e5%ad%a6%e4%b9%a0linux-shell10-%ef%bc%9a-%e5%a6%82%e4%bd%95%e6%a0%b9%e6%8d%ae%e6%9c%8d%e5%8a%a1%e5%99%a8%e7%9a%84%e5%86%85%e5%ad%98%ef%bc%8ccpu-%e4%bb%a5%e5%8f%8a","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/35657.html","title":{"rendered":"\u4ece\u5b9e\u5217\u4e2d\u5b66\u4e60linux shell10 \uff1a \u5982\u4f55\u6839\u636e\u670d\u52a1\u5668\u7684\u5185\u5b58\uff0ccpu \u4ee5\u53ca ssd\u786c\u76d8 \u6765\u786e\u5b9amysql \u7684\u6700\u5927\u5e76\u53d1\u6570"},"content":{"rendered":"

\u4ee5\u4e0b\u662f\u6839\u636e\u670d\u52a1\u5668\u786c\u4ef6\u8d44\u6e90\u667a\u80fd\u63a8\u8350MySQL\u6700\u5927\u5e76\u53d1\u8fde\u63a5\u6570<\/h3>\n

\u5305\u542b\u8be6\u7ec6\u7684\u8ba1\u7b97\u903b\u8f91\u548c\u5b9e\u65f6\u8d44\u6e90\u68c0\u6d4b： \u4e14\u8bb0：\u8be5\u811a\u672c\u8981\u653e\u5728 \u5b89\u88c5mysql\u7684\u670d\u52a1\u5668\u4e0a \u8fd0\u884c<\/p>\n

\u7b2c\u4e00\u6b65：\u5b9e\u73b0\u811a\u672c<\/h3>\n

#!\/bin\/bash<\/span><\/p>\n

# \u8ba1\u7b97MySQL\u6700\u5927\u8fde\u63a5\u6570\u63a8\u8350\u503c<\/span>
\n# \u516c\u5f0f\u8bf4\u660e：\u53d6CPU\u8ba1\u7b97\u503c\u4e0e\u5185\u5b58\u8ba1\u7b97\u503c\u7684\u8f83\u5c0f\u503c，SSD\u4f18\u5316\u7cfb\u6570\u63d0\u534730%<\/span><\/p>\n

# \u914d\u7f6e\u53c2\u6570（\u53ef\u6839\u636e\u9700\u8981\u8c03\u6574）<\/span>
\nCONN_PER_CORE_HDD<\/span>=<\/span>100<\/span> # HDD\u78c1\u76d8\u6bcf\u6838\u5fc3\u57fa\u7840\u8fde\u63a5\u6570<\/span>
\nCONN_PER_CORE_SSD<\/span>=<\/span>130<\/span> # SSD\u78c1\u76d8\u6bcf\u6838\u5fc3\u57fa\u7840\u8fde\u63a5\u6570<\/span>
\nMEM_PER_CONN_MB<\/span>=<\/span>4<\/span> # \u6bcf\u4e2a\u8fde\u63a5\u5185\u5b58\u6d88\u8017\u4f30\u503c（MB）<\/span>
\nMYSQL_MEM_RATIO<\/span>=<\/span>0.7<\/span> # \u5206\u914d\u7ed9MySQL\u7684\u5185\u5b58\u6bd4\u4f8b<\/span>
\nRESERVED_MEM_MB<\/span>=<\/span>2048<\/span> # \u7cfb\u7edf\u4fdd\u7559\u5185\u5b58（MB）<\/span><\/p>\n

# \u83b7\u53d6\u786c\u4ef6\u4fe1\u606f<\/span>
\nCPU_CORES<\/span>=<\/span>$(<\/span>nproc)<\/span><\/span>
\nTOTAL_MEM<\/span>=<\/span>$(<\/span>free<\/span> -m |<\/span> awk<\/span> '\/Mem:\/{print $2<\/span>}'<\/span>)<\/span><\/span>
\nAVAIL_MEM<\/span>=<\/span>$(<\/span>awk<\/span> "BEGIN {<\/span>print int((<\/span>$TOTAL_MEM – $RESERVED_MEM_MB)<\/span> * $MYSQL_MEM_RATIO)<\/span><\/span>}<\/span>")<\/p>\n

# \u68c0\u6d4bSSD
\nDISK=$(<\/span>df<\/span> \/ |<\/span> tail<\/span> -1 |<\/span> awk<\/span> '{print $1<\/span>}'<\/span> |<\/span> sed<\/span> 's\/[0-9]\/\/g'<\/span>)<\/span><\/span>
\nDISK=${DISK#<\/span>\/<\/span>dev\/<\/span>}<\/span>
\nIS_SSD=0
\nif [[ -e "<\/span>\/sys\/block\/${DISK}<\/span>\/queue\/rotational" ]]; then
\n [[ $(<\/span>cat<\/span> "\/sys\/block\/${DISK}<\/span>\/queue\/rotational"<\/span>)<\/span><\/span> -eq 0 ]] && IS_SSD=1
\nfi<\/p>\n

# \u8ba1\u7b97\u903b\u8f91
\nif [ $IS_SSD<\/span> -eq 1 ]; then
\n CPU_BASED=$((<\/span> CPU_CORES *<\/span> CONN_PER_CORE_SSD ))<\/span><\/span>
\nelse
\n CPU_BASED=$((<\/span> CPU_CORES *<\/span> CONN_PER_CORE_HDD ))<\/span><\/span>
\nfi<\/p>\n

MEM_BASED=$((<\/span> AVAIL_MEM \/<\/span> MEM_PER_CONN_MB ))<\/span><\/span>
\nSUGGESTED_CONN=$((<\/span> CPU_BASED <<\/span> MEM_BASED ?<\/span> CPU_BASED :<\/span> MEM_BASED ))<\/span><\/span><\/p>\n

# \u5e94\u7528\u8fb9\u754c\u9650\u5236
\nMAX_LIMIT=16384 # \u6700\u5927\u5141\u8bb8\u8fde\u63a5\u6570
\nMIN_LIMIT=200 # \u6700\u5c0f\u63a8\u8350\u8fde\u63a5\u6570
\nif [ $SUGGESTED_CONN<\/span> -gt $MAX_LIMIT<\/span> ]; then
\n FINAL_CONN=$MAX_LIMIT<\/span>
\nelif [ $SUGGESTED_CONN<\/span> -lt $MIN_LIMIT<\/span> ]; then
\n FINAL_CONN=$MIN_LIMIT<\/span>
\nelse
\n FINAL_CONN=$SUGGESTED_CONN<\/span>
\nfi<\/p>\n

# \u8f93\u51fa\u7ed3\u679c
\necho "<\/span>MySQL\u6700\u5927\u5e76\u53d1\u8fde\u63a5\u6570\u63a8\u8350\u503c: $FINAL_CONN<\/span>"
\necho "<\/span>—————-[<\/span>\u786c\u4ef6\u68c0\u6d4b]<\/span>—————–"
\necho "<\/span>CPU\u6838\u5fc3\u6570 :<\/span> $CPU_CORES<\/span>"
\necho "<\/span>\u7269\u7406\u5185\u5b58\u603b\u91cf :<\/span> ${TOTAL_MEM}<\/span>MB"
\necho "<\/span>\u53ef\u7528\u5185\u5b58\u5206\u914d :<\/span> ${AVAIL_MEM}<\/span>MB (<\/span>\u9884\u7559${RESERVED_MEM_MB}<\/span>MB)<\/span>"
\necho "<\/span>\u5b58\u50a8\u4ecb\u8d28\u7c7b\u578b :<\/span> $(<\/span>[<\/span> $IS_SSD -eq 1<\/span> ]<\/span> &&<\/span> echo<\/span> 'SSD'<\/span> ||<\/span> echo<\/span> 'HDD'<\/span>)<\/span><\/span>"
\necho "<\/span>—————-[<\/span>\u8ba1\u7b97\u903b\u8f91]<\/span>—————–"
\necho "<\/span>CPU\u8ba1\u7b97\u57fa\u6570 :<\/span> $(<\/span>[<\/span> $IS_SSD -eq 1<\/span> ]<\/span> &&<\/span> echo<\/span> ${<\/span>CONN_PER_CORE_SSD}<\/span>\/core ||<\/span> echo<\/span> ${<\/span>CONN_PER_CORE_HDD}<\/span>\/core)<\/span><\/span>"
\necho "<\/span>\u57fa\u4e8eCPU\u7684\u8ba1\u7b97 :<\/span> ${CPU_BASED}<\/span> connections"
\necho "<\/span>\u57fa\u4e8e\u5185\u5b58\u7684\u8ba1\u7b97 :<\/span> ${MEM_BASED}<\/span> connections (<\/span>${MEM_PER_CONN_MB}<\/span>MB\/conn)<\/span>"
\necho "<\/span>\u8fb9\u754c\u503c\u7ea6\u675f :<\/span> [<\/span>$MIN_LIMIT<\/span>, $MAX_LIMIT<\/span>]<\/span>"
\necho "<\/span>——————————————-"
\necho "<\/span>\u64cd\u4f5c\u5efa\u8bae："
\necho "<\/span>1<\/span>. \u4fee\u6539\/etc\/my.cnf\u6dfb\u52a0:"
\necho "<\/span> [<\/span>mysqld]<\/span>"
\necho "<\/span> max_connections =<\/span> $FINAL_CONN<\/span>"
\necho "<\/span> thread_cache_size =<\/span> $((<\/span> FINAL_CONN \/<\/span> 2<\/span> ))<\/span><\/span>"
\necho "<\/span>2<\/span>. \u6267\u884c: systemctl restart mysqld"
\necho "<\/span>3<\/span>. \u76d1\u63a7\u547d\u4ee4: show status like 'Threads_connected'<\/span>;<\/span>"<\/p>\n

\u7b2c\u4e8c\u6b65：\u6267\u884c\u65b9\u5f0f<\/h3>\n

chmod<\/span> +x mysql_max_conn.sh
\n.\/mysql_max_conn.sh<\/p>\n

\u7b2c\u4e09\u6b65：\u8bbe\u8ba1\u903b\u8f91\u8bf4\u660e<\/h3>\n
  • \n

    \u591a\u7ef4\u5ea6\u8d44\u6e90\u8bc4\u4f30<\/p>\n