MySQL Bugs: #71821: Manual doesn't explain that max_connections is "autosized" when ulimit -n is low

Bug #71821	Manual doesn't explain that max_connections is "autosized" when ulimit -n is low
Submitted:	24 Feb 2014 17:58	Modified:	14 Mar 2014 14:19
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	autosized, max_connections, ulimit

Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_max_connections) says nothing explicitly about the case when server finds out that ulimit -n setting (and/or open_files_limit setting, if explicit) is too low to satisfy the request.

It seems in this case changes based on the same principles as for open_files_limit (see http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_open_files_limi...) happens:

"The effective open_files_limit value is based on the value specified at system startup (if any) and the values of max_connections and table_open_cache, using these formulas:

1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) open_files_limit value specified at startup, 5000 if none

The server attempts to obtain the number of file descriptors using the maximum of those three values. If that many descriptors cannot be obtained, the server attempts to obtain as many as the system will permit."

Unfortunately, manual does not say that max_connections is also autosized and does not explain how it calculates the effective value for it.

How to repeat:
Make sure ulimit -n returns 1024, then start MySQL server with all defaults, but max_connections set to 1000. Check the effective value:

mysql> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               214 |
+-------------------+
1 row in set (0,00 sec)

and find the following warnings in the error log:

2014-02-24 16:32:09 13734 [Warning] Buffered warning: Changed limits: max_open_f
iles: 1024 (requested 5010)

2014-02-24 16:32:09 13734 [Warning] Buffered warning: Changed limits: max_connec
tions: 214 (requested 1000)

2014-02-24 16:32:09 13734 [Warning] Buffered warning: Changed limits: table_cach
e: 400 (requested 2000)

Now, try to find out in the manual any explanation for the warning about max_connections and the effective value 214.

Suggested fix:
Explain all the details on autosizing for max_connections (and table_open_cache, for that matter, but for it http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cach... at least says it's "autosized")

Hello Valeriy,

Thank you for the bug report.

Thanks,
Umesh

I agree that the manual should say which variables are auto-reduced if the required open_files_limit needed for them can't be obtained.

We're not keen on giving the exact rules because those could change.

James Day, MySQL Senior Principal Support Engineer, Oracle

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

I'll mention that the server reduces the value of max_connections if the required number of file descriptors is unavailable.

Specific details on autosizing can change so they aren't given.