故障案例 | lsof是怎么＂影响＂MySQL计算打开文件句柄数的 mysql数据库sql

欢迎来到 GreatSQL社区分享的MySQL技术文章，如有疑问或想学习的内容，可以在下方评论区留言，看到后会进行解答
lsof中附加不同参数产生的结果也不同，小心“踩坑”。

1、背景：偶然发现数据库连不上，在数据库的err日志中，出现了“Too many open files”错误，都知道这个是mysqld进程触发了句柄限制，导致无法建立新连接。
度娘上面找到了统计句柄数的命令

lsof -n|awk '{print $2}'|sort|uniq -c|sort -nr| head -n 10

发现输出的结果远超了ulimit -n的结果。但是报错现象才持续不长时间，正常情况下不可能产生这么多句柄。
为了研究这个问题的根因，在自己的服务器上面模拟了以下测试。
服务器配置4C8G，MySQL 8.0.22，lsof版本为4.87(为什么特别介绍lsof版本，后续会提到，很重要)
2、测试过程： 1、在服务器上面安装好MySQL，设置下面的参数，重启数据库服务

max_connections=150 open_files_limit=1000

此时，数据库进程的open_files_limit为2160

mysql> show variables like '%open%'; +----------------------------+-------+ | Variable_name| Value | +----------------------------+-------+ | have_openssl| YES| | innodb_open_files| 1000| | mysqlx_port_open_timeout| 0| | open_files_limit| 2160| | table_open_cache| 1000| | table_open_cache_instances | 16| +----------------------------+-------+ 6 rows in set (0.01 sec)

其计算公式为table_open_cache*2+max_connections+10 具体见下面的官网介绍

文章图片

【故障案例 | lsof是怎么"影响"MySQL计算打开文件句柄数的】2、此时数据库刚刚启动，查看进程占用的句柄数

[root@greatdb mysql]# ps -ef| grep mysql root623986440 16:25 pts/500:00:00 mysql -uroot root1013482600 16:42 pts/100:00:00 mysql -uroot root1017710 16:47 ?00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/my.cnf mysql11604 101778 16:47 ?00:00:02 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/logs/mysqld-error.log --ope n-files-limit=1000 --pid-file=/data/mysql/mysqld.pid --socket=/data/mysql/mysql.sock --port=3306root1169685720 16:47 pts/200:00:00 grep --color=auto mysql [root@greatdb mysql]# [root@greatdb mysql]# [root@greatdb mysql]# lsof -n|awk '{print $2}'|sort|uniq -c|sort -nr| head -n 10 5727 11604 1575 1384 525 1550 455 879 371 561 135 1101 130 1001 120 16780 114 1304 88 16894 [root@greatdb mysql]#

可以看到刚刚启动的数据库，用上面的命令统计出来的句柄数5727就已经超过了设置的值2160了，但是数据库仍然可以正常使用和连接。
2、此时进入数据库，修改最大连接限制，用于模拟真实超过句柄的情况

set global max_connections=10000

3、在数据库中创建三个分区表，用于快速占用文件句柄数

create database test; create table test.tb1(id int, c varchar(10)) partition by hash(id) partitions 1024; create table test.tb2(id int, c varchar(10)) partition by hash(id) partitions 1024; create table test.tb3(id int, c varchar(10)) partition by hash(id) partitions 1024;

4、此时再次使用上面的命令查询句柄数

[root@greatdb mysql]# lsof -n|awk '{print $2}'|sort|uniq -c|sort -nr| head -n 10 73485 11604 1575 1384 525 1550 455 879 371 561 135 1101 130 1001 120 16780 114 1304 88 16894 [root@greatdb mysql]#

5、可以看到mysql的进程“占用”句柄数已经达到73485，远远超过了实际允许的值了，开始怀疑这个统计值不对。
6、使用另外的命令lsof -p 11604 |wc -l; ls /proc/11604/fd |wc -l 来统计句柄和打开文件，发现比较正常，在合理范围内

[root@greatdb mysql]# lsof -p 11604 |wc -l; ls /proc/11604/fd |wc -l 1066 1021 [root@greatdb mysql]#

7、最终对比lsof -n | head 和lsof -p 11604 |head 发现了差异，lsof -n 的结果多了一列TID，观察后发现是线程编号，其值和performance_schema.threads中的thread_id能够对应上

[root@greatdb mysql]# lsof -p 11604 | head COMMANDPIDUSERFDTYPEDEVICESIZE/OFFNODE NAME mysqld11604 mysqlcwdDIR253,14096 1310723 /data/mysql/data mysqld11604 mysqlrtdDIR253,140962 / mysqld11604 mysqltxtREG253,1441771255927033 /usr/local/mysql/bin/mysqld mysqld11604 mysqlDELREG0,116526074 /[aio] mysqld11604 mysqlDELREG0,116526073 /[aio] mysqld11604 mysqlDELREG0,116526072 /[aio] mysqld11604 mysqlDELREG0,116526071 /[aio] mysqld11604 mysqlDELREG0,116526070 /[aio] mysqld11604 mysqlDELREG0,116526069 /[aio] [root@greatdb mysql]# [root@greatdb mysql]# [root@greatdb mysql]# lsof -n | head COMMANDPIDTIDUSERFDTYPEDEVICESIZE/OFFNODE NAME systemd1rootcwdDIR253,140962 / systemd1rootrtdDIR253,140962 / systemd1roottxtREG253,11632744136135 /usr/lib/systemd/systemd systemd1rootmemREG253,120064132877 /usr/lib64/libuuid.so.1.3.0 systemd1rootmemREG253,1265576134383 /usr/lib64/libblkid.so.1.1.0 systemd1rootmemREG253,190176132861 /usr/lib64/libz.so.1.2.7 systemd1rootmemREG253,1157424132876 /usr/lib64/liblzma.so.5.2.2 systemd1rootmemREG253,123968133092 /usr/lib64/libcap-ng.so.0.0.0 systemd1rootmemREG253,119896133076 /usr/lib64/libattr.so.1.1.0

8、经过多方咨询及资料查阅，了解到在lsof 4.82版本后，默认会统计出每个线程的句柄，即默认加上了-K参数，这样实现后，以前的命令 lsof -n|awk '{print $2}'|sort|uniq -c|sort -nr| head -n 10 统计出来的数据就约等于实际句柄数 * 线程数，也就是我们前面看到的结果，统计数据数字很大。
9、新lsof版本统计句柄正确的语法是 lsof -n -Ki|awk '{print $2}'|sort|uniq -c|sort -nr| head -n 10，统计出来后数据正常

[root@greatdb mysql]# lsof -Ki -n| awk '{print $2}' | sort | uniq -c | sort -nr | head 1065 11604 130 1001 91 879 84 8643 84 8641 84 8622 84 8603 84 8570 84 8258 84 8156

10、使用循环的方式，批量创建数据库会话连接，发现句柄到设置极限后，还会超45个句柄数，在此之后手动创建连接，会一直等待，mysql-err日志中出现错误“Too many open files”

for x in {1..1139}; do nohup mysql -uroot -e 'select sleep(240)' & done

11、在无法建立新连接的情况下，查询进程占用句柄数

[root@greatdb mysql]# lsof -p 11604 |wc -l 2205

12、至此找到正确统计进程句柄的方法，计算MySQL句柄上限计算方式，并且准确复现异常场景。
3、总结：

lsof 统计句柄数据，与版本有很大关系，如果是4.82之后的版本，统计进程句柄数，需要加上参数-Ki
MySQL启动时占用句柄数84，当table_open_cache设置为1000时，除开连接句柄，最多占用句柄1065个，主要包含共享库so、ibd文件、日志文件、临时文件等，每个链接再单独占用一个句柄
MySQL进程的最大句柄数，是参数open_files_limit + 45，这个是测试出来的，并没有找到具体代码出处
MySQL的句柄限制，是由参数max_connections、table_open_cache共同影响的，并且不受操作系统限制(测试了操作系统ulimit -n 1024的情况下，MySQL 的open_files_limit依然可以为2160)
文件句柄在线程间是共享的，因此打开一个ibd文件，不论有多少session访问，都只会占用一个句柄

参考文章 https://github.com/rapidoid/r...
https://github.com/draios/sys...
https://dev.mysql.com/doc/ref...
Enjoy GreatSQL :)
文章推荐：技术分享 | MGR最佳实践(MGR Best Practice)
https://mp.weixin.qq.com/s/66...
技术分享 | 万里数据库MGR Bug修复之路
https://mp.weixin.qq.com/s/Ia...
Macos系统编译percona及部分函数在Macos系统上运算差异
https://mp.weixin.qq.com/s/jA...
技术分享 | 利用systemd管理MySQL单机多实例
https://mp.weixin.qq.com/s/iJ...
产品 | GreatSQL，打造更好的MGR生态
https://mp.weixin.qq.com/s/By...
产品 | GreatSQL MGR优化参考
https://mp.weixin.qq.com/s/5m...
关于 GreatSQL GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
微信&QQ群：
可扫码添加GreatSQL社区助手微信好友，发送验证信息“加群”加入GreatSQL/MGR交流微信群，亦可直接扫码加入GreatSQL/MGR交流QQ群。