Changed limits: max_connections: 214

I am using MySQL version 5.7.15 , ubuntu0.16.04.1. When I start the MySQL, I am getting below log,

2016-11-06T13:44:56.838233Z 0 [Warning] option 'table_open_cache': unsigned value 33554432 adjusted to 524288
2016-11-06T13:44:56.839061Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 1049386)
2016-11-06T13:44:56.839078Z 0 [Warning] Changed limits: max_connections: 214 (requested 800)
2016-11-06T13:44:56.839082Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 524288)

$ ulimit -Sa | grep “open files”

open files (-n) 1024

In google, It is specified like open_files_limit value need to be increased to have more max_connections.

Will it be enough to set below values on the /etc/mysql/mysql.conf.d/mysqld.cnf and restarting the MySQl server ?

open_files_limit = 2048

max_connections = 800

table_open_cache = 600

Is there any other way to change the values without restarting the MySQL server ?

Is setting the open_files_limit on the MySQL configuration file enough ?

First, you have to make an OS change to increase open files (-n) 1024. MySQL’s value only reflects that.

max_connections = 214 is a generous number. Let’s discuss whether you really need more.

24 connections from one server is potentially excessive. If there are that many, they may be stumbling over each other. The end result is increased latency without increased throughput. I have seen cases like this; the best remedy is to throttle things earlier, not later.

The units of table_open_cache is tables, not bytes. And it is a cache, so it is usually OK to have a smallish number. (There are STATUS values that indicate whether it is ‘too small’.)

Bottom line: Decrease the number of client threads until you can restart mysqld.

You can always check if a variable is dynamic or not with on http://dev.mysql.com/doc/refman/5.7/en/dynamic-system-variables.html

Or looking at the parameter directly:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_open_files_limit

If it is dynamic you can:

set global <variable_name> = <value>;

Just don’t forget to change in your my.cnf also otherwise next restart will override it.

If it is not then you have to restart MySQL to make it happen. Oracle is working on getting more and more variable dynamic and you can see the trend going through 5.5 -> 5.6 -> 5.7. Unfortunately though there’s always going to be parameters which require restart.