作者: 郑全 时间: 2018-7-9 15:52
该问题参加官方文档：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