Buffered warning: Changed limits: max_connections: 214 (requested 800)

Question

Everytime I restart MySQL I have this warning:

[Warning] Buffered warning: Changed limits: max_connections: 214 (requested 800)

So I have to change max_connections variable:

set global max_connections = 800;

But /etc/my.cf has max_connections set:

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections = 800

I also modified mysqld.service:

# Start main service
ExecStart=/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf

I've also updated max open files of Centos from 1024 to 2000:

ulimit -Sa | grep "open files"
open files                      (-n) 2000

But the problem persists.

Mysql log start:

150924 13:15:04 mysqld_safe mysqld from pid file 

/var/run/mysqld/mysqld.pid ended
150924 13:15:04 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-09-24 13:15:04 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-09-24 13:15:04 0 [Note] /usr/sbin/mysqld (mysqld 5.6.25) starting as process 29997 ...
2015-09-24 13:15:04 29997 [Warning] Buffered warning: Changed limits: max_open_files: 1024 (requested 5000)

2015-09-24 13:15:04 29997 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 800)

2015-09-24 13:15:04 29997 [Warning] Buffered warning: Changed limits: table_open_cache: 400 (requested 2000)

2015-09-24 13:15:04 29997 [Note] Plugin 'FEDERATED' is disabled.
2015-09-24 13:15:04 29997 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-09-24 13:15:04 29997 [Note] InnoDB: The InnoDB memory heap is disabled
2015-09-24 13:15:04 29997 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-09-24 13:15:04 29997 [Note] InnoDB: Memory barrier is not used
2015-09-24 13:15:04 29997 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-09-24 13:15:04 29997 [Note] InnoDB: Using Linux native AIO
2015-09-24 13:15:04 29997 [Note] InnoDB: Using CPU crc32 instructions
2015-09-24 13:15:04 29997 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-09-24 13:15:04 29997 [Note] InnoDB: Completed initialization of buffer pool
2015-09-24 13:15:04 29997 [Note] InnoDB: Highest supported file format is Barracuda.
2015-09-24 13:15:04 29997 [Note] InnoDB: 128 rollback segment(s) are active.
2015-09-24 13:15:04 29997 [Note] InnoDB: Waiting for purge to start
2015-09-24 13:15:04 29997 [Note] InnoDB: 5.6.25 started; log sequence number 24337684937
2015-09-24 13:15:04 29997 [Note] Server hostname (bind-address): '*'; port: 3306
2015-09-24 13:15:04 29997 [Note] IPv6 is available.
2015-09-24 13:15:04 29997 [Note]   - '::' resolves to '::';
2015-09-24 13:15:04 29997 [Note] Server socket created on IP: '::'.
2015-09-24 13:15:04 29997 [Note] Event Scheduler: Loaded 0 events
2015-09-24 13:15:04 29997 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.25'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

I tried all Google solutions, but the result is always the same...

MySQL is running on CentOS Linux release 7.1.1503 (Core).

Thanks!

It looks OK for me. take a look [here][1] [1]: http://dba.stackexchange.com/questions/12061/mysql-auto-adjusting-max-connections-values — Bernd Buffen, Sep 24 '15 at 11:44

MNB · Accepted Answer · 2017-09-13T15:21:25.263

Having just spent an hour or two facing the exact same problem on CentOS 7 with MySQL 5.6.26, here's my solution. In addition to raising the max open files (for the mysql user), which it looks like you've already done, you need to add "LimitNOFILE=65535" (or similar depending on how high a limit you want to set) to your mysql.service definition file.

So the complete solution for me was as follows

append these two lines to /etc/security/limits.conf

mysql hard nofile 65535
mysql soft nofile 65535

append this line to /usr/lib/systemd/system/mysqld.service (in the [service] section)

LimitNOFILE=65535

then finally reboot and check that those error messages have disappeared from your mysql error log.

EDIT: Thanks to @SieGeL below for reminding me to add, if you update the ".service" file directly your edits will be lost on upgrade. To keep edits after an upgrade use a systemd override by creating an additional conf file in /etc/systemd/system/mysqld.service.d

There is no such folder in my kubuntu installation. There is not 'system' folder in '/usr/lib/systemd/' — makkasi, Nov 22 '16 at 08:07
I did ======= sudo nano /lib/systemd/system/mysql.service ======= sudo systemctl daemon-reload =========== sudo service mysql restart=========== And it worked. I did the first step as well. Regards — makkasi, Nov 22 '16 at 08:15

score 13 · Answer 2 · answered May 23 '17 at 05:59

I would not recommend to edit the original systemd file as it will be overwritten during updates.

To modify the limits, do the following:

mkdir /etc/systemd/system/mysqld.service.d

Inside that directory, create new file limits.conf and add the following to that file:

[Service]
  LimitNOFILE = 65535

finally reload systemd with:

systemctl daemon-reload

and restart mysqld to enable the change:

systemctl restart mysqld

Now validate that the change was successful by using the following query:

mysql> show variables like '%file%';

You should find a line like this:

| open_files_limit                      | 65535

That's it, this way your changes survive MySQL updates.

Agreed, I'll edit my answer as this is what I did end up doing. Thanks for taking the time to point it out. — MNB, Sep 13 '17 at 15:09
For Ubuntu 16.04 it should be `mkdir /etc/systemd/system/mysql.service.d` and `systemctl restart mysql`. — rob006, Jul 09 '18 at 12:33

score 0 · Answer 3 · answered Aug 12 '20 at 20:33

for me (mariadb 1:10.3.23-0+deb10u1 on Debian Buster) reason for that Changed limits warnings was none of OS limits (it seems that Debian setup increases file limits automatically depending on open_file_limit variable in my.cnf) ...

I was having:

open_files_limit        = 185000
table_open_cache        = 185000
table_definition_cache  = 185000
innodb_open_files       = 185000

in my.cnf, but upon startup they changed:

MariaDB [(none)]> select @@open_files_limit, @@table_open_cache, @@table_definition_cache, @@innodb_open_files;
+--------------------+--------------------+--------------------------+---------------------+
| @@open_files_limit | @@table_open_cache | @@table_definition_cache | @@innodb_open_files |
+--------------------+--------------------+--------------------------+---------------------+
|             185000 |              92460 |                   185000 |              185000 |
+--------------------+--------------------+--------------------------+---------------------+

What caught my eye is that table_open_cache was about half of what I set it to...

Interestingly enough, I could enter mysql client and set it there with set global table_open_cache = 185000 without problems.

Turns out MariaDB was overriding my config with something it though was more reasonable.

There was a clue in the /var/log/syslog:

[Warning] Changed limits: max_open_files: 185000  max_connections: 50 (was 50)  table_cache: 92460 (was 185000)

My solution was simply to set all of variables to more than double (400000), so my table_open_cache would be at least at 185000 where I wanted it.

Buffered warning: Changed limits: max_connections: 214 (requested 800)

3 Answers3