MySQL挑战：100k连接 - Percona数据库性能博客


在这篇文章中，我想探索一种与MySQL建立100,000个连接的方法。不只是空闲连接，而是执行查询。
你真的需要MySQL100,000个连接，你可能会问？虽然看起来有点过分，但我在客户部署中看到了很多不同的设置。有些部署了一个应用程序连接池，每个池中有100个应用程序服务器和1,000个连接。有些应用程序使用“如果查询太慢，会重新尝试连接”的技术，这是一种可怕的做法。它可以导致滚雪球效应，并可以在几秒钟内建立数千个与MySQL的连接。
所以现在我想设定一个超出预期的目标，看看我们是否能够实现它。

构建
我将使用以下硬件：

由packet.net提供的裸机服务器，实例大小：c2.medium.x86 
物理内核@ 2.2 GHz 
（1 X AMD EPYC 7401P）
内存：64 GB ECC RAM 
存储：INTEL SSDDC S4500,480GB

这是服务器级SATA SSD。
我将使用其中五个盒子，原因如下。一个用于MySQL服务器的盒子和四个用于客户端连接的盒子。
对于服务器，我将使用带有线程池插件的Percona Server for MySQL 8.0.13-4。该插件将需要支持数千个连接。

初始服务器设置

网络设置（Ansible格式）：

- { 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 }

这些是推荐用于10Gb网络和高并发工作负载的典型设置。
限制systemd的设置：

[Service]
LimitNOFILE=1000000
LimitNPROC=500000

以及my.cnf中MySQL的相关设置：

back_log=3500
max_connections=110000

对于客户端，我将使用sysbench版本0.5而不是1.0.x，原因如下所述。

发出工作负载：

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

1. 设置10,000个连接
这个很容易，因为没有太多的事要做。我们只用一个客户就可以做到这一点。但是您可能在客户端遇到以下错误：

FATAL: error 2004: Can't create TCP/IP socket (24)

这是由打开文件限制引起的，这也是TCP / IP套接字的限制。这可以通过设置：

ulimit -n 100000

我们观察到性能：

[  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

2. 设置25,000个连接
当有25,000个连接，我们在MySQL端遇到错误：

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

如果您尝试查找有关此错误的信息，可能会发现以下文章：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 加上：

thread_handling = pool - of - threads

到my.cnf并重启Percona Server，结果：

[   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％的响应时间已经从3690毫秒改善到979毫秒（由于线程池）。

3. 50,000个连接
这是我们遇到的最大挑战。首先，尝试在sysbench中获取50,000个连接，我们遇到以下错误：

FATAL: error 2003: Can't connect to MySQL server on '139.178.82.47' (99)

Error (99) 是神秘的，它意味着：无法分配请求的地址。
它来自应用程序可以打开的端口限制。默认情况下，我的系统是

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

这表示只有28,231个可用端口--60999减去32768 - 或者您可以与给定IP地址建立或建立TCP连接的限制。您可以在客户端和服务器上使用更广泛的范围扩展它：

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

这将为我们提供61,000个连接，但这非常接近一个IP地址的限制（最大端口为65535）。这里的关键点是，如果我们想要更多的连接，我们需要为MySQL服务器分配更多的IP地址。为了实现100,000个连接，我将在运行MySQL的服务器上使用两个IP地址。
在整理出端口范围后，我们遇到了sysbench的以下问题：

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内存分配（即lua子系统）的问题。Sysbench只能为32,351个连接分配内存。这是一个在sysbench 1.0.x中更严重的问题。

Sysbench 1.0.x限制
Sysbench 1.0.x使用不同的Lua JIT，即使有4000个连接也会遇到内存问题，所以在sysbench 1.0.x中不可能超过4000连接
因此，与Percona Server相比，我们似乎比sysbench更快地达到了极限。为了使用更多连接，我们需要使用多个sysbench客户端，如果32,351连接是sysbench的限制，我们必须使用至少四个sysbench客户端来获得多达100,000个连接。
对于50,000个连接，我将使用2个服务器（每个服务器运行单独的sysbench），每个服务器运行来自sysbench的25,000个线程。
每个sysbench的结果如下所示：

[  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

所以我们有相同的吞吐量（总共16794 * 2 = 33588 tps），但95％的响应时间翻了一番。这是预料之中的，因为与25,000个连接基准测试相比，我们使用的连接数是原来的两倍。

3. 75,000个连接
要实现75,000个连接，我们将使用三个带sysbench的服务器，每个服务器运行25,000个线程。
每个sysbench的结果：

[ 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

4. 100,000个连接
实现75k和100k连接没有任何意义。我们只需启动一个额外的服务器并启动sysbench。对于100,000个连接，我们需要四个服务器用于sysbench，每个服务器显示：

[ 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

因此我们具有相同的吞吐量（总共8065 * 4 = 32260 tps），响应时间为3405毫秒95％。
这是一个非常重要的要点：使用100k连接并使用线程池，95％的响应时间甚至比没有线程池的10k连接更好。线程池允许Percona Server更有效地管理资源并提供更好的响应时间。

结论​​​​​​​
MySQL可以实现100k连接，我相信我们可以更进一步。有三个组件可以实现此目的：

  • Percona Server中的线程池
  • 正确调整网络限制
  • 在服务器盒上使用多个IP地址（每个约60k连接一个IP地址）

完整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