在 MySQL 服务器上建立 100,000 个可执行查询连接的方法

搜索在 MySQL 服务器上建立 100,000 个连接的方法。我们要建立的是一个可以执行查询的连接，而不是10万个空闲连接。

你可能会问，我的MySQL服务器真的需要10万个连接吗？我见过很多不同的部署方案，比如使用连接池，每个应用程序在连接池中放入1000个连接，部署100个这样的应用服务器。还有一些使用“查询速度慢，重新连接并重试”技术的非常糟糕的做法。这会产生滚雪球效应，可能会导致在几秒钟内建立数千个连接。

所以我决定定一个“小目标”，看看我能不能实现。

准备阶段

我们先来看看硬件。服务器由packet.net（云服务提供商）提供。配置如下：

实例大小：c2.medium.x86 物理核心 @ 2.2 GHz (1 X AMD EPYC 7401P) 内存：64 GB ECC RAM 存储：INTEL® SSD DC S4500，480 GB 5 台此类服务器，1台将用作MySQL服务器，其余4台将用作客户端。 MySQL服务器使用Percona Server的MySQL 8.0.13-4和线程池插件。该插件必须支持数千个连接。

初始化服务器配置

网络设置：

- { 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 }
复制代码

系统限制设置：

[Service]
LimitNOFILE=1000000
LimitNPROC=500000
复制代码

相应的MySQL配置（my.cnf文件）：

back_log=3500
max_connections=110000
复制代码

客户端使用的是sysben.0，而不是1.5版本。 X。具体原因我们稍后会解释。

执行命令：sysbench --test=sysbench/tests/db/select.lua --mysql-host=139.178.82.47 --mysql-user=sbtest--mysql-password=sbtest --oltp-tables-= 10 --report-interval=1 --number-threads=10000 --max-time=300 --max-requests=0 --oltp-table-size=10000000 --rand-type=uniform --rand- init =on run

第一步，10000个连接

这一步很简单，我们不需要做太多的调整来实现。此步骤只需要一台机器作为客户端，但客户端可能会出现以下错误：

FATAL: error 2004: Unable to create TCP/IP socket (24)

这是由于数量限制打开文件的数量，此限制限制了客户端上可以调整的 TCP/IP 套接字数量：

ulimit -n100000

此时，让我们观察性能：

[  26s] threads: 10000, tps: 0.00, reads: 33367.48, writes: 0.00, response time: 3681.42ms (95%), errors: 0.00, reconnects:  0.00
[  27s] threads: 10000, tps: 0.00, reads: 33289.74, writes: 0.00, response time: 3690.25ms (95%), errors: 0.00, reconnects:  0.00
复制代码

第二步，25,000 秒。

这一步会导致MySQL服务器错误：

无法创建新线程（错误#11）；如果您没有用完可用内存，您可以查阅手册以了解可能与操作系统相关的错误

关于此问题的解决方案可以在此链接中找到：

https://www.percona .com /blog/ 2013/02/04/cant_create_thread_errno_11/

但是，这种方法不适用于我们当前的情况，因为我们已经将所有限制调整为最高：

cat /proc/`pidof mysqld`/limits
Limit                     Soft Limit Hard Limit           Units
Max cpu time              unlimited  unlimited            seconds
Max file size             unlimited  unlimited            bytes
Max data size             unlimited  unlimited            bytes
Max stack size            8388608    unlimited            bytes
Max core file size        0          unlimited            bytes
Max resident set          unlimited  unlimited            bytes
Max processes             500000     500000               processes
Max open files            1000000    1000000              files
Max locked memory         16777216   16777216             bytes
Max address space         unlimited  unlimited            bytes
Max file locks            unlimited  unlimited            locks
Max pending signals       255051     255051               signals
Max msgqueue size         819200     819200               bytes
Max nice priority         0          0
Max realtime priority     0          0
Max realtime timeout      unlimited unlimited            us
复制代码

这就是为什么我们选择具有首先是线程池：https://www.percona.com/doc/percona-server/8.0/performance/threadpool.html

将以下行选项添加到 my.cnf 文件中，然后重新启动服务

thread_handling=pool-of -threads

检查结果

[   7s] threads: 25000, tps: 0.00, reads: 33332.57, writes: 0.00, response time: 974.56ms (95%), errors: 0.00, reconnects:  0.00
[   8s] threads: 25000, tps: 0.00, reads: 33187.01, writes: 0.00, response time: 979.24ms (95%), errors: 0.00, reconnects:  0.00
复制代码

吞吐量相同，但 95% 响应从 3690ms 下降到 979ms。

第三步，5万个连接

此时我们遇到了最大的挑战。 sysbench第一次尝试建立5w连接时，sysbench报错：

FATAL: error 2003: Unable to connect to MySQL server at '139.178.82.47' (99)

Error (99) 错误比较神秘，这意味着它不能分配指定的地址。此问题是由于程序可以打开的端口数量受到限制造成的。我们系统的默认配置是：

cat /proc/sys/net/ipv4/ip_local_port_range : 32768 60999

这意味着我们只有 28,231 个可用端口（60,999 减去 CP，T can 的最大数量）或 CP- 连接你可以最大化。建立到指定的IP地址。您可以在服务器和客户端上扩展此范围：

echo 4000 65000 > /proc/sys/net/ipv4/ip_local_port_range

这样我们就可以建立 61,000 个连接，这接近一个 IP 可用端口。最大限制为 (65535)。这里关键的是，如果我们想要达到10万个连接，我们需要给MySQL服务器分配多个IP地址，所以我给MySQL服务器分配了两个IP地址。

解决了端口计数问题后，又遇到了新的问题：

sysbench 0.5:  multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 50000
FATAL: pthread_create() for thread #32352 failed. errno = 12 (Cannot allocate memory)
复制代码

这个问题是由于sysbench的内存分配问题引起的。 Sysbench 只能分配内存来创建 32,351 个连接。这个问题在1.0.x版本中更加严重。

Sysbench 1.0.x 的限制

Sysbench 1.0.x 版本使用不同的 Lua 编译器，这使得我们无法创建超过 4000 个连接。所以看来Sysbench比Percona Server更早达到极限，所以我们需要更多的客户端。如果每个客户端最多有 32,351 个连接，那么我们至少需要 4 个客户端才能达到 100,000 个连接的目标。

为了实现5万个连接，我们使用两台机器作为客户端，每台机器开启25000个线程。结果如下：

[  29s] threads: 25000, tps: 0.00, reads: 16794.09, writes: 0.00, response time: 1799.63ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 25000, tps: 0.00, reads: 16491.03, writes: 0.00, response time: 1800.70ms (95%), errors: 0.00, reconnects:  0.00
复制代码

演练与上一步类似（总tps为16794*2 = 33588），但性能有所下降，并且95%的响应时间增加了一倍。这是可以预料的，因为与上一步相比，我们的连接数量增加了一倍。

第四步，75,000个连接

在这一步中，我们添加另一台服务器作为客户端，每个客户端也运行25,000个线程。结果如下：

[ 157s] threads: 25000, tps: 0.00, reads: 11633.87, writes: 0.00, response time: 2651.76ms (95%), errors: 0.00, reconnects:  0.00
[ 158s] threads: 25000, tps: 0.00, reads: 10783.09, writes: 0.00, response time: 2601.44ms (95%), errors: 0.00, reconnects:  0.00
复制代码

第五步，10万个连接

终于到了。这一步也不难。您只需要打开另一个客户端并运行 25,000 个线程。结果如下：

[ 101s] threads: 25000, tps: 0.00, reads: 8033.83, writes: 0.00, response time: 3320.21ms (95%), errors: 0.00, reconnects:  0.00
[ 102s] threads: 25000, tps: 0.00, reads: 8065.02, writes: 0.00, response time: 3405.77ms (95%), errors: 0.00, reconnects:  0.00
复制代码

吞吐量依然保持在32260（8065*4）的水平，95%响应时间为3405ms。

这里有一个很重要的事情，我想大家都发现了：有线程的情况下10万个连接的响应速度甚至比没有线程池的情况下10000个连接的响应速度要好。 线程池让Percona Server能够更高效地管理资源并提供更好的响应速度。

结论

10w 个连接是可以实现的，并且可能还有更多，实现此目标需要三个重要组成部分：

1. Percona Server 的线程池
2. MyQ❝e 服务器的正确 IP 服务器设置 S (每个IP限制65535个连接）

附录

最后粘贴完整的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

作者：Programming for Google
链接：https://juejin.im /post/5c513c88c81c858c858c858c88来源：掘金
版权归作者所有。商业转载请联系作者获取授权。非商业转载请注明出处。