MYSQL 8 一个实例打开的表被那些参数和资源限制分析

最新推荐文章于 2024-07-20 03:42:33 发布

原创最新推荐文章于 2024-07-20 03:42:33 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #linux #java #mysql #python

本文介绍了在MySQL8.0环境下进行性能测试的过程，包括调整sysbench参数以生成大规模数据，遇到的max_prepared_stmt_count限制问题，以及解决方法。通过增加内存和调整系统参数如table_open_cache和文件句柄限制，尝试提高并发线程数。然而，过高的并发导致服务器资源紧张，最终在优化过程中发现与table_open_cache、内存管理和并发度相关的因素对数据库性能的影响。

再开始这个问题之前，我们先的准备一下环境， mysql 8.027 8G 内存 SSD 磁盘 4核心CPU 。同时通过sysbench来对系统进行测试数据的填充。

首先安装sysbench 并通过下面的命令来对mysql test 数据库产生 10000万张表。

sudo sysbench /usr/share/sysbench/oltp_common.lua --mysql-host=192.168.198.101 --mysql-port=3306 --mysql-user=admin --mysql-password=Huayang3 --mysql-db=test --tables=10000--table_size=1 prepare

在产生这些表后，就需要通过sysbench对其进行压测，在压测的过程中发现报错max_prepared_stmt_count 提出测试的过程中，测试中的无法进行线程的初始化。

sudo sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=192.168.198.101 --mysql-port=3306 --mysql-user=admin --mysql-password=Huayang3 --mysql-db=test --tables=100 --table_size=1 --threads=200 --time=1200 --report-interval=3 run

其中我们需要初始化 200个线程，而默认的 max_prepared_stmt_count

的值是 16382.

也就是说我们要产生200个并发，默认的 max_prepared_stmt_count 16382的值无法满足我们。这个值是限制程序对数据库产生的prepare 语句的数量，同时也有方式通过一次性增加prepare 语句让数据库系统内存消耗出现问题。这里我们调整值为 1000000 ，一百万测试可以正常进行

我们将测试的线程更改为500 ,测试程序提示,太多连接数，无法承接，看来是有触发了之前有一篇文字中出现的问题，最大linux 句柄不修改，导致mysql无法修改max_connection的问题。这里我们修改linux的句柄。

/etc/security/limits.conf

修改完毕后，我们再次测试，这次直接将线程数改为3000

sudo sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=192.168.198.101 --mysql-port=3306 --mysql-user=admin --mysql-password=Huayang3 --mysql-db=test --tables=500 --table_size=1 --threads=3000 --time=1000 --report-interval=3 run

测试开始20秒后，MYSQL8.027 直接被系统KILL 了。

系统整体的出先问题的原因在于vm 已经占用了10G ，而内存才8G mysql 占用的内存太多，导致OOM

添加内存到16G，将线程添加到3000 内存吃不消了，降到2000,测试勉强可以进行。但sysbench 报初始化线程超时，经过查询，这里和table_open_cache 有关，默认的cache 是 400 这里将cache 调整到 1000

在次测试，数据库直接又被KILL vm的分配已经超过16G 了

在此调整系统的参数 table_open_cache 到5000, 测试当中 100个表 1000个并发的情况下，我们的系统基本上已经处于无响应的状态了。

到这里暂时先总结一下，一个INSTANCE 可以打开表的数量与什么有关

1 与应用程序的并发度有关，与并发度有关的有

1 table_open_cache 这里table_open_cache 与并发当中打开多少表的数量有关，实际上每个表在访问中，不会频繁的被打开，句柄是放到table_open_cache 当中. 系统设置的table open cache 越大，可以并发中同时打开的表就越多。

需要注意的是，如果一个语句中包含多个表的访问，则一个语句就需要更多的tbale_open_cache.

2 系统的内存，在mysql中打开每个连接都是需要内存的支持的，在刨除 innodb_buffer_pool + 20% 后，就是我们数据库本身可以支持的连接内存，除以每个session 可能最大的内存使用量，计算后就是我们最保守的最大可以支持的连接数。

SELECT ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@tmp_table_size
+ 2*@@net_buffer_length
) / (1024 * 1024) AS MEMORY_PER_CON_MB;
3  打开的表的数量与LINUX 中文件的句柄有关

echo  6553560 > /proc/sys/fs/file-max
上面是临时生效方法，重启机器后会失效；
永久生效方法：
修改 /etc/sysctl.conf, 加入
fs.file-max = 6553560 重启生效
对单独的线程的控制打开文件的句柄数，上面的设置是针对整体的LINUX 系统打开句柄的数量

echo "* soft nofile 65535"  >> /etc/security/limits.conf
echo "* hard nofile 65535"  >> /etc/security/limits.conf

另有一些开大 open_table_cache 后产生的性能问题反馈，可以从下面的文字里有相关的介绍可以从下面的网址找到

https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/