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 ?

  • for increase number of connections You can use SET max_connections = 300; in mysql command line. It will work till restart. 800 - it is too much and show bad application design. – a_vlad Nov 15 '16 at 6:54
  • Saying "800 - it is too much and show bad application design" without knowing the context might be a bit premature. There are absolutely valid reasons why you might need even thousands of connections being opened to the database. Just think about persistent connections from PHP or Perl web applications where each process keeps a connection open. Having 24 process for 20 servers you immediately have close to 500 connections. – Károly Nagy Nov 15 '16 at 11:08
  • Saying because if Your are not Google (and even if) - normal practice pooling connections if need, and mysql can of course handle hundreds connections ... but work good if it less than 300 – a_vlad Nov 15 '16 at 11:28
up vote 0 down vote accepted

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.