在 Percona 看到的文章「MySQL Challenge: 100k Connections」，是在描述當 MySQL 遇到 10 萬個連線時所要進行的系統調校，測試過程一些細節頗具參考價值，很值得閱讀。
先講在前面，這篇是 Percona 為了測試高併發 (high concurrent workloads) 所寫，絣除電商雙 11 的情境，在一般情況下 Database 是不應該出現高併發的狀況，這代表兩個問題正在發生：
- SQL 語法下法有問題，導致執行過久耗盡資料庫效能而囤積
- 索引 (index) 沒有建立，導致 SQL 花費過多時間執行
- 資料庫的效能已不足以承載 SQL 的執行量
- 同時有太多 SQL 正在執行
在這幾年的經驗後，當資料庫出現上述其中一種情況發生，都會導致資料庫的連線、效能如滾雪球般瞬間耗盡。
假設上方的陳述不成立，實際就是有這麼大的需求，系統就是必須吃下這麼高量的連線數，那麼可以參考這篇的作法，主要都是針對 Linux 系統的一些限制做開放
測試環境
硬體使用的是 packet.net 的 Bare metal 伺服器，規格是 c2.medium.x86
- Physical Cores @ 2.2 GHz (1 X AMD EPYC 7401P)
- Memory: 64 GB of ECC RAM
- Storage : INTEL® SSD DC S4500, 480GB
資料庫版本為 Percona Server for MySQL 8.0.13-4 加上 thread pool plugin.
- 使用 1 台 MySQL 伺服器、4 台使用者端壓測
系統設定優化
首先是 sysctl network 的優化設定 (Ansible)，可以參考 net/ipv4 的參數解釋
- { name: 'net.core.somaxconn', value: 32768 }
- { name: 'net.core.rmem_max', value: 134217728 }
- { name: 'net.core.wmem_max', value: 134217728 }
- { name: 'net.ipv4.tcp_rmem', value: '4096 87380 134217728' }
- { name: 'net.ipv4.tcp_wmem', value: '4096 87380 134217728' }
- { name: 'net.core.netdev_max_backlog', value: 300000 }
- { name: 'net.ipv4.tcp_moderate_rcvbuf', value: 1 }
- { name: 'net.ipv4.tcp_no_metrics_save', value: 1 }
- { name: 'net.ipv4.tcp_congestion_control', value: 'htcp' }
- { name: 'net.ipv4.tcp_mtu_probing', value: 1 }
- { name: 'net.ipv4.tcp_timestamps', value: 0 }
- { name: 'net.ipv4.tcp_sack', value: 0 }
- { name: 'net.ipv4.tcp_syncookies', value: 1 }
- { name: 'net.ipv4.tcp_max_syn_backlog', value: 4096 }
- { name: 'net.ipv4.tcp_mem', value: '50576 64768 98152' }
- { name: 'net.ipv4.ip_local_port_range', value: '4000 65000' }
- { name: 'net.ipv4.netdev_max_backlog', value: 2500 }
- { name: 'net.ipv4.tcp_tw_reuse', value: 1 }
- { name: 'net.ipv4.tcp_fin_timeout', value: 5 }MySQL Systemd 設定最大檔案數開放
[Service]
LimitNOFILE=1000000
LimitNPROC=500000MySQL 主要設定檔 my.cnf
back_log=3500
max_connections=110000記得執行 sysctl -p 和重啟 MySQL 服務，讓設定生效
壓測 Sysbench
找一台還算夠力的設備來壓測，這個情境是使用 sysbench 0.5 版本測試，而非最新的 1.0.x，是因為 Sysbench 1.0.x 的限制，由 Lua 改為 LuaJIT (詳細可參考 sysbench 1.0: teaching an old dog new tricks，當到 4000 以前就會因為記憶體問題而遇到瓶頸，因此 Sysbench 1.0.x 不可能超過 4000 條連線測試。
執行的範本如下：
sysbench --test=sysbench/tests/db/select.lua --mysql-host=139.178.82.47 --mysql-user=sbtest --mysql-password=sbtest --oltp-tables-count=10 --report-interval=1 --num-threads=10000 --max-time=300 --max-requests=0 --oltp-table-size=10000000 --rand-type=uniform --rand-init=on runmy.cnf 設定：
[mysqld]
datadir {{ mysqldir }}
ssl=0
skip-log-bin
log-error=error.log
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character_set_server=latin1
collation_server=latin1_swedish_ci
skip-character-set-client-handshake
innodb_undo_log_truncate=off
# general
table_open_cache = 200000
table_open_cache_instances=64
back_log=3500
max_connections=110000
# files
innodb_file_per_table
innodb_log_file_size=15G
innodb_log_files_in_group=2
innodb_open_files=4000
# buffers
innodb_buffer_pool_size= 40G
innodb_buffer_pool_instances=8
innodb_log_buffer_size=64M
# tune
innodb_doublewrite= 1
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit= 0
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_lru_scan_depth=2048
innodb_page_cleaners=4
join_buffer_size=256K
sort_buffer_size=256K
innodb_use_native_aio=1
innodb_stats_persistent = 1
#innodb_spin_wait_delay=96
innodb_adaptive_flushing = 1
innodb_flush_neighbors = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity=1500
innodb_io_capacity_max=2500
innodb_purge_threads=4
innodb_adaptive_hash_index=0
max_prepared_stmt_count=1000000
innodb_monitor_enable = '%'
performance_schema = ON當壓測超過 10 萬條連線時，陸續遇到的幾個問題：
第一階段：1 萬條連線
FATAL: error 2004: Can’t create TCP/IP socket (24)
壓測的設備就自己先踩到限制了，必須開放 ulmiit
ulimit -n 100000此時 MySQL 回應時間大約 3681ms。
第二階段：2.2 萬條連線
Can’t create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
MySQL 端吐出的錯誤訊息
這時候用到了 MariaDB 5.5 新增的 Thread Pool 模式：pool-of-threads
thread_handling=pool-of-threadspool-of-threads 用於高併發的情境，讓 Thread Pool 可以自動擴展及回收
此時 MySQL 回應時間大約 979ms，pool-of-threads 大幅增加高併發的效能處理
第三階段：5 萬條連線
FATAL: error 2003: Can’t connect to MySQL server on ‘139.178.82.47’ (99)
這邊踩到了 MySQL 本機動態 Port 的數量限制，設定 ip_local_port_range 參數
echo 4000 65000 > /proc/sys/net/ipv4/ip_local_port_range每個 IP 最大限制的 Port 數量為 65535，如果要超過 65535 則需要第二個 IP
而這邊壓測的設備也快到極限，使用 2 台設備壓測，一台 25,000 條連線
此時 MySQL 回應時間大約 1800ms。
第四階段：7.5 萬條連線
使用 3 台設備壓測，一台 25,000 條連線
此時 MySQL 回應時間大約 2601ms。
第五階段：10 萬條連線
使用 4 台設備壓測，一台 25,000 條連線
此時 MySQL 回應時間大約 3405ms。
結論
基本上測到這邊已經沒有再遇到錯誤，但回應時間開始累進，剩下就是效能上的議題，而當 MySQL 需要超過 10 萬個以上的併發時，需要考慮幾個因素：
- 使用支援 pool-of-threads (Thread Pool) 的 MySQL 版本
- 系統網路環境調校
- 多個 IP (每個 IP 大約 60k 個連線)
最終，引用 DBA 同事說的話：Sysbench 壓的過 10 萬條連線，比不過 100 條爛 SQL 盲搜 XDDD …
