[Warning] Buffered Warning:changed limits:max_connections:214 (requested 2000)

1. Version

1) Operating system

Cat/etc/issue
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
Kernel \ r \m

Cat/proc/version
Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) ( GCC) #1 SMP Wed Oct 04:27:16 UTC 2014

2 MySQL database version

MySQL--version
MySQL Ver 14.14 distrib 5.6.26, for linux-glibc2.5 (x86_64) using Editline Wrapper

2. Description of the problem

2.1 Finding problems

Today, in my test environment library, I found the following alarm in the error log:

Mysqld_safe--DEFAULTS-FILE=/ETC/MY.CNF &

2016-01-08 09:37:38 28555 [Warning] buffered warning:could not increase number of max_open_files to more than 1024 (reque st:10240)

2016-01-08 09:37:38 28555 [Warning] buffered warning:changed limits:max_connections:214 (requested 2000)

2016-01-08 09:37:38 28555 [Warning] Buffered warning:changed-limits:table_open_cache:400 (requested 2000)

# The value specified in the open_files_limit=10240,max_connections specified in the #在my. cnf file is 2000,table_open_cache unspecified
2.2 Analysis problems

Let's analyze why the three alarms appear:

2.2.1 About the Max_open_files alarm

In fact, as long as you know MySQL about Open_files_limit setting rules (Open_files_limit values are not necessarily the values you specify in the config file), then the question is clear. For more information on open_files_limit settings, see my other blog (http://blog.csdn.net/shaochenshuo/article/details/51966076). This blog only points out the problem. The problem here is that our OS level Open files is limited to 1024

[Root@localhost ~]# ulimit-sa|grep "open files"
open files                      (-N) 1024
[root@localhost ~]# Ulimit-ha|grep] Op En files "
open files                      (-N) 4096

So here's the open_files_limit value of the operating system open files limit 1024

2.2.2 About the max_connections alarm

This is actually a bug in MySQL 5.6, which details see

https://bugs.mysql.com/bug.php?id=71821

Or

http://blog.csdn.net/shaochenshuo/article/details/50484475

MySQL automatically adjusts the value of the max_connections when it is started when MySQL discovers that the operating system's open files or the Open_files_limit values in the MySQL profile are set relatively small. But the official MySQL document, and the bug above, did not say what the adjustment was based on.

2.2.3 about the Table_open_cache alarm

The reason for this alarm appears to be the same as 2.2.2 (requested 2000 because the default value for Table_open_cache is 2000 because we did not specify Table_open_cache in the cnf file, so we took the default value, But also found that the operating system's open files limit is too small, so this parameter automatically adjusted to 400)

3. The solution

Adjust the open files restrictions for the operating system

Vi/etc/security/limits.conf
At the end add
* Soft Nofile 655350
* Hard Nofile 655350
* Hard Nproc 655350
* Soft Nproc 655350
Or
MySQL Hard nofile 655350
MySQL Soft nofile 655350
MySQL Hard Nproc 655350
MySQL Soft nproc 655350

# #参考

http://blog.csdn.net/shaochenshuo/article/details/51966076

http://blog.csdn.net/shaochenshuo/article/details/50484475