max_open_files table_open_cache
服务器mysql 报警：2017-11-17T04:02:34.400257Z 0 Changed limits: max_open_files: 1024 (requested 5000)
2017-11-17T04:02:34.400333Z 0 Changed limits: table_open_cache: 431 (requested 2000)
2017-11-17T04:02:34.573111Z 0 TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = /application/mysql
datadir = /data/3307/data
port = 3307
server_id = 1
#lower_case_table=1
character_set_server=utf8
#autocommit =off
innodb_flush_log_at_trx_commit=1
skip_name_resolve=ON
relay_log=slave_relay_binlog
innodb_strict_mode=ON
log_bin=mysql-bin
log-bin-index=/data/3307/data/mysql-bin.index
innodb_flush_method=O_DIRECT
lower_case_table_names=1
#binlog_do_db=test
#bind_address=192.168.1.128
plugin_dir=/application/mysql/lib/plugin
ft_min_word_len =1
innodb_data_home_dir=/data/3307/data
innodb_log_group_home_dir=/data/3307/data
#lc-messages-dir=/usr/local/mysql/share/mysql/
#innodb_force_recovery=1
#innodb_undo_directory=/mydata/undo
#innodb_undo_tablespaces=4
log_slave_updates=1
master-info-repository= TABLE
relay-log-info-repository = TABLE
relay-log=/data/3307/data/relay-mysql-bin
sync_master_info= 1
sync_relay_log=1
sync_relay_log_info=1
#gtid-mode=on
#enforce-gtid-consistency=true
#rpl_semi_sync_master_enabled=ON
#rpl_semi_sync_slave_enabled=ON
#auto_increment_offset=2
#auto_increment_increment=2
log_error="/data/3307/data/mysql_master.err"
socket=/tmp/mysql3307.sock
#innodb_force_recovery = 1
socket=/tmp/mysql3307.sock
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust> # join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
mysql> show variables like '%open%'
-> ;
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| have_openssl | DISABLED |
| innodb_open_files | 431 |
| open_files_limit | 1024 |
| table_open_cache | 431 |
| table_open_cache_instances | 16 |
+----------------------------+----------+
max_connections 151
MyISAM和CSV表打开时占用2个文件描述符，Innodb则需要1个文件描述符。一些日志信息（relay
log，binlog，error-log等）也需要文件描述符。table_open_cache对MyISAM有效，对Innodb无效。当运行 flush tables
关闭表的时候，只对MyISAM表有效，即关闭有MISAM表的文件描述符，Innodb表也会关闭，但是文件描述符不会关。
当表都是MyISAM，在极端的情况下，table_open_cache数目的表全部被打开（512张）就会占用掉1024个文件描述符。而open_files_limit是1024的话，就会出现报错的情况(本文例子的情况)。所以如果是有大量的 MyISAM 表，那么就需要特别注意打开文件数是否会超出限制了。
总之，确实设置open_files_limit的时候，先要知道table_open_cache
为多少，再加上inodb表的数目和一些日志的数目。上面的结果和系统的ulimit没有关系（ubuntu默认apt下来的实例），要是不是默认安装的实例呢？请继续看：
在测试中发现，设置OS的文件描述符(/etc/security/limits.conf)值是无效的，即MySQL启动后open_files_limit始终以my.cnf中设置的参数open_files_limit为准。（版本MySQL5.5.29 ubuntu0.12.04.1），而在非Ubuntu中是按照他们（os和mysql）哪个最高用哪个的方法，通过 cat /proc/10415/limits
查看，依然都是数据库配置文件(open_files_limit)中设置的值。怀疑是ubuntu定制mysql的问题（apt下来的mysql）。
而用mysqld_safe开启的实例(非apt-get install
安装)，则按照下面的规则：
https://common.cnblogs.com/images/copycode.gif
默认设置的open_files_limit 比其他相关的参数都大。
一：：ulimit -n ：修改 my.cnf 限制
open_files_limit = ：重启 mysql：show global variables like ;
| open_files_limit ||
二：
注释 open_files_limit
| open_files_limit |三：：ulimit -n ：修改 my.cnf 限制
open_files_limit = ：重启 mysql：show global variables like ;
| open_files_limit |四：
注释 open_files_limit
| open_files_limit |
https://common.cnblogs.com/images/copycode.gif
注意：open_files_limit 大小和
max_connections*5需要比较，那个最大就用那个值来设置open_files_limit
。【比较open_files_limit，max_connections*5和10+max_connections+table_cache_size*2中最大值】
https://common.cnblogs.com/images/copycode.gif
--my.cnf--
open_files_limit = 5000max_connections = 1024mysql> show globalvariables like '%open%';
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| open_files_limit| 5120 | #1024*5 > 5000+-------------------+----------+
https://common.cnblogs.com/images/copycode.gif
http://images.cnblogs.com/OutliningIndicators/ContractedBlock.gifhttp://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gifView Code
vi /etc/security/limits.conf
mysql soft nofile 65536mysql hard nofile 65536
总结：
所以在配置open_files_limit的时候，设置多大合适，需要知道具体的表数目和类型等，具体情况需要自己分析。没有配置的则需要注意配置OS的ulimit（启动前设置）和max_connections的大小。而用apt-get
下来的mysql实例则都是按照open_files_limit和max_connections
来比较设置的，和OS的ulimit无关。
页:
[1]