mysql报错日志中老是报 table_open_cache不够的警告

郑全

问题现象：

     在默认配置情况下，我们在使用mysql用户打开mysql数据库时，发现报警日志中，老是有下面这个信息：


2018-07-09T04:48:54.866872Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2018-07-09T04:48:54.867076Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)

即使启动时，修改了 table_open_cache 参数的值，依然还会报这个。

问题原因：
    主要是文件打开数不够导致的
     ulimit -a

解决办法：

    我们可以在进入mysql用户后，修改这个打开文件数的限制

    $ ulimit -n 65536
    再去启动数据库
    ./mysqld_safe --socket=/data/db/mysql.sock --basedir=/mysql/mysql-5.6.40 --datadir=/data/db --log-error=/data/db/log_error.log &

    永久的办法：
      编辑  /etc/security/limits.conf :

      mysql hard nofile 65536

     再打开数据库时 ，就不再见这烦人的提示了。

郑全

该问题参加官方文档：Doc ID 1501924.1

MySQL Server: table_open_cache Value is Not the Same as the Value Set In my.cnf on Linux (Doc ID 1501924.1)
To Bottom



In this Document

Symptoms

Cause

Solution

Solution 1: Start mysqld_safe as root

Solution 2: Manually Change the Open Files Limit

/etc/security/limits.conf

systemd

References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
MySQL Server - Version 4.0 and later
Generic Linux
Symptoms
With table_open_cache configured to a value larger than 400, the value is reduced to 400 after MySQL has been started.
table_open_cache was called table_cache in MySQL 5.0 and earlier.
For example using the following setting in the MySQL configuration file:
[mysqld]
table_open_cache = 4096

In MySQL you get:
mysql> SHOW GLOBAL VARIABLES LIKE 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 400   |
+------------------+-------+
1 row in set (0.02 sec)

The MySQL error log will contain something like the following warning after starting the server:
121026  9:36:00 [Warning] Changed limits: max_open_files: 1024  max_connections: 214  table_cache: 400

The issue typically happens when MySQL is started directly using a non-root user. You can confirm whether this is the case from the command Linux command line using ps command:
mysql     3503  0.3  0.0 108248  1452 pts/3    S    08:55   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql
mysql     3622  2.1  0.7 454640 29152 pts/3    Sl   08:55   0:00  \_ /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --log-error=/var/log/mysql.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

Cause
The issue is that the allowed number of open files for the mysql user is limited to 1024 at the operating system level. MySQL will in that case scale back table_open_cache to reduce the possibility of running out of file descriptors.

Solution
To avoid this issue, choose on of the following solutions:
Start mysqld_safe as root and have mysqld_safe change the user to mysql.
Change the limit for the number of open files allowed for the mysql user manually.
Solution 1: Start mysqld_safe as root
This is generally the preferred solution as it allow mysqld_safe to adjust the open files limit as needed before starting mysqld. mysqld_safe can change the user used to run mysqld and will by default use the mysql user. If you want mysqld to execute using a different user, you can use the --user option with mysqld_safe to explicitly specify the user to use.
Solution 2: Manually Change the Open Files Limit
If you are not able to start mysqld_safe as root, you can ask the Linux system administrator to increase the open files limit.
For a temporary change, this can be done with the ulimit command:
shell# ulimit -n 32000

Note that the change must be done before changing to the user who will be running MySQL.
For a permanent change, you will need to update a configuration file. Which one depends on your system. Two examples are described in the following.

/etc/security/limits.conf
You will have to update the /etc/security/limits.conf file with a line similar to:

mysql hard nofile 32000

where:
mysql is the user for which the limit applies.
hard is whether it is the hard or soft limit.

The difference between the hard and soft limits is that the soft limit can be changed as required by the user as long as it no larger than the hard limit which is the maximum setting allowed.
nofile is the limit to change (number of files).
32000 is the value of the limit.

systemd
On systems with systemd (for example Oracle Linux/RHEL 7), you will need to edit systemd service file. One way to do this without touching the file installed by the RPM is to use the following steps:
Create the file /etc/systemd/system/mysqld.service (or add to if you already have this file) with a content similar to:
# Load the defaults
.include /etc/systemd/system/multi-user.target.wants/mysqld.service

# Custom Settings
[Service]
LimitNOFILE = <descriptors>
replacing <descriptors> with the number of descriptors required.

The .include mechanism will first load the content of the /etc/systemd/system/multi-user.target.wants/mysqld.service (which is usually symbolic link to /usr/lib/systemd/system/mysqld.service). This ensures that any update there are to the service settings in connection with upgrades are applied unless explicitly overwritten. Then LimitNOFILE is set to the new value required for this system.
Reload the configuration:
systemctl daemon-reload

Restart MySQL. See Managing MySQL Server with systemd in the Reference Manual for details.
References
NOTE:1322541.1 - MySQL Server Variable: table_open_cache (Formerly Called table_cache)
NOTE:1385400.1 - MySQL Server: What is the Maximum Value for max_connections on Linux?
NOTE:1023817.1 - What can be done about a max_open_files warning?
https://dev.mysql.com/doc/refman/5.6/en/mysqld-safe.html
https://dev.mysql.com/doc/refman/5.6/en/mysqld.html
https://dev.mysql.com/doc/refman ... on_mysqld_safe_user
https://dev.mysql.com/doc/refman ... -using-systemd.html