Site icon Mr. 沙先生

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

在 Percona 看到的文章「MySQL Challenge: 100k Connections」,是在描述當 MySQL 遇到 10 萬個連線時所要進行的系統調校,測試過程一些細節頗具參考價值,很值得閱讀。

先講在前面,這篇是 Percona 為了測試高併發 (high concurrent workloads) 所寫,絣除電商雙 11 的情境,在一般情況下 Database 是不應該出現高併發的狀況,這代表兩個問題正在發生:

在這幾年的經驗後,當資料庫出現上述其中一種情況發生,都會導致資料庫的連線、效能如滾雪球般瞬間耗盡。

假設上方的陳述不成立,實際就是有這麼大的需求,系統就是必須吃下這麼高量的連線數,那麼可以參考這篇的作法,主要都是針對 Linux 系統的一些限制做開放

測試環境

硬體使用的是 packet.net 的 Bare metal 伺服器,規格是 c2.medium.x86

資料庫版本為 Percona Server for MySQL 8.0.13-4 加上 thread pool plugin.

系統設定優化

首先是 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 回應時間大約 979mspool-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 萬個以上的併發時,需要考慮幾個因素:

最終,引用 DBA 同事說的話:Sysbench 壓的過 10 萬條連線,比不過 100 條爛 SQL 盲搜 XDDD …

Exit mobile version