Percona 關於 10 萬條 MySQL 連線的系統調校

shazi7804

5 年前

在 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=500000

MySQL 主要設定檔 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 run

my.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-threads

pool-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 …

分享此文：