26.Mysql之打开文件详解
1.前言
当我们连接用账号密码连接数据库时出现如下报错时:
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
这时我们可以查看Mysql的错误日志看到也会有如下报错：
[ERROR] /usr/sbin/mysqld: Can't open file: './java/tt_fte.frm' (errno: 24)
这时一看到这里就觉的要调整open_files_limit参数了(一般它默认的是最小1024)
2.系统的open_files_limit参数到底是什么？
os中的open_files_limit参数
[root@node01 fd]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 3796 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 3796 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited
说明：
设置可以同时打开的最大文件数，默认是1024，当使用ulimit -n + number 时表示可以设置调整os同时打开文件的最大数量
然后可以通过ulimit -u + number 设置最大可用进程数
其中 "open files (-n) 1024 "是Linux操作系统对一个进程打开的文件句柄数量的限制(也包含打开的SOCKET数量,可影响MySQL的并发连接数目)
这个值可用ulimit 命令来修改,但ulimit命令修改的数值只对当前登录用户的目前使用环境有效,系统重启或者用户退出后就会失效.
具体详解见:https://www.cnblogs.com/kongzhongqijing/p/5784293.html
3.如果我们想要查看一个进程打开的文件数
如果我们想要查看一个进程打开的文件数可以通过查看/proc/进程id/fd 下面的软连接个数，该个数就是当前进程打开文件数量，也可以直接通过命令ls -l /proc/进程号/fd | wc 查看
[root@node01 fd]# ll /proc/27373/fd |wc 55 601 4375
4.查看Mysql打开文件限制
mysql> show variables like '%open%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | have_openssl | YES | | innodb_open_files | 10240 | | open_files_limit | 300000 | | table_open_cache | 20480 | | table_open_cache_instances | 32 | | tdsqlsys_opening | ON | +----------------------------+--------+
这里我发现腾讯的分布式数据库下os的open_files_limit=600000，而Mysql的参数open_files_limit=300000
- open_files_limit :操作系统允许mysqld打开的文件数量，服务器运行时该变量为系统实际允许打开的值，和启动服务器指定的参数可能不一致。如果该值为0，表示不允许MySQL修改打开的文件数量
- 有效的open_files_limit值是基于系统启动指定的open_files_limit，max_connections和table_open_cache计算得到，服务器将会获取三个指标中最大的值，如果三者指标都没有指定，服务器将获得os允许的最大值
1) 10 + max_connections + (table_open_cache * 2) 2) max_connections * 5 3) 启动时设定的open_files_limit，如果没有指定默认为5000
- innodb_open_files：指定MySQL可同时打开
.ibd文件的最大个数，最小为10，默认300。此选项只针对InnoDB表打开的.ibd文件描述符，独立于open_files_limit。 - table_open_cache：所有线程打开表的数目。它的作用就是缓存表文件描述符，降低打开关闭表的频率， 如果这个参数设置得过小，就不得不关闭一些已打开的表以便为缓存新表，从而出现频繁的打开关闭MyISAM表文件的情况，而INNODB表的打开不受这个参数控制，而是放到其数据字典当中，即在ibd文件中。当Opened_tables状态值较大，且不经常使用FLUSH TABLES 关闭并重新打开表，就需要增加该值。
- table_open_cache_instances：表缓存实例数，为通过减小会话间争用提高扩展性，表缓存会分区为table_open_cache/table_open_cache_instances大小的较小的缓存实例。DML语句会话只需要锁定所在缓存实例，这样多个会话访问表缓存时就可提升性能（DDL语句仍会锁定整个缓存）。默认该值为1，当16核以上可设置为8或16。
- table_definition_cache:缓存表定义(.frm)文件的数量。如果表较多，可以增大该值加快打开表。与一般表缓存不同，表定义缓存不占用文件描述符，占用空间也小。最小为400，上线为2000，默认为：
400 + (table_open_cache / 2)。如果打开表数量高于table_definition_cache，则会通过LRU机制搜索表空间LRU文件列表并刷新列表。对于InnoDB，打开文件的限制为max(table_definition_cache, innodb_open_files)。
5.查看Mysql文件打开的状态
root@localhost 15:17: [employees]> show global status like '%open%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Com_ha_open | 0 | | Com_show_open_tables | 0 | | Innodb_num_open_files | 26 | | Open_files | 17 | | Open_streams | 0 | | Open_table_definitions | 109 | | Open_tables | 104 | | Opened_files | 159 | | Opened_table_definitions | 109 | | Opened_tables | 111 | | Slave_open_temp_tables | 0 | | Table_open_cache_hits | 9 | | Table_open_cache_misses | 111 | | Table_open_cache_overflows | 0 | +----------------------------+-------+
- Open_table_definitions：代表当前缓存了多少.frm文件。
- Opened_table_definitions：代表自MySQL启动后，缓存了.frm文件的数量。 需要注意的是.frm文件是MySQL用于存放表结构的文件，
对应myisam和innodb存储引擎都必须有的，可以通过show open tables 查看 这2个变量的值。 - Open_tables：代表当前打开的表个数
- Opened_tables：代表自MySQL启动后，打开过的表个数，如该值过大，可能是table_open_cache设置太小。
- Open_files：打开文件的个数。服务器层打开的一般文件，不包含sockets 或 pipes类型文件，也不包含内部函数打开的文件。
- Opened_files：通过使用my_open()系统函数打开的文件数。
- Table_open_cache_hits：打开表缓存查找的命中数。
- Table_open_cache_misses：打开表缓存查找的未命中数。
- Table_open_cache_overflows：打开表缓存溢出数。
6.Mysql如何打开和关闭表
由于MySQL是多线程的，因此可能存在多个会话同时根据指定表进行查询。为解决同一个表在不同会话状态不一致，该表会由每个会话独立的打开，这样MySQL会消耗内存但会提高性能。
table_open_cache同时会跟max_connections相关。如200个并发连接线程，指定的表缓存至少为200*N，N为每个连接关联的最大表数量。
以下几种情况MySQL会关闭未使用的表并将其从表缓存中删除：
- 表缓存已满，线程将要打开不在缓存中的表。
- 缓存中的表多于table_open_cache且缓存中的表不被任何线程使用。
- 当发生表刷新操作(flush tables)
当表缓存满后，服务器将执行以下操作：
- 当前不使用的表将被释放，先释放最近最少使用的表
- 当新表需要被打开，但是缓存已满且无表可以被释放，服务器将会根据需要临时扩展缓存，当临时扩展缓存中的表从使用变为未使用状态，表将被关闭，扩充的临时缓存将被释放
7.文件打开常见的问题
数据库报错： [ERROR] /opt/mysql/bin/mysqld: Can't open file: './tpcc/sbtest98.frm' (errno: 24 - Too many open files) 查看os最大允许打开数 shell> ulimit -n 65535 查看数据库打开设定最大打开文件数 mysql> show global variables like 'open_files_limit'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 500 | +------------------+-------+ 1 row in set (0.00 sec) 查看当前数据库已经打开的文件描述符 shell>ll /proc/24012/fd | wc 501 5507 41673 调整open_files_limit设定并重启生效。
