再开始这个问题之前,我们先的准备一下环境, 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/