MySQL Configuration – How to Auto Adjust Max Connections Values

MySQL

I have set max connections to around 2000 in my.cnf file.

max_connections=2048

I have upgraded to 5.5.20 and now I see the following line in the error log.

120201 19:40:24 [Warning] Changed limits: max_open_files: 1024  max_connections: 214  table_cache: 400

Why does mysql changed the max connections value to 214 ?

# mysqladmin variables | grep max_connections
| max_connections | 214

Soft and hard open files restricted by OS is 1024

# ulimit -Sa | grep "open files"
open files                      (-n) 1024

# ulimit -Ha | grep "open files"
open files                      (-n) 1024

The number of actually used max connections:

# mysql -e"show status like '%used_connections%'"
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 95     |
+----------------------+-------+

Best Answer

It seems OK to me. You cannot have max_connections larger than that, because you open_files_limit is too low. For every connection MySQL opens several files. Take a look here.

So, you will have to increase open files limit on your OS, and afterwards, you can put higher max_connections value.

Related Question