Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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     |
+----------------------+-------+
share|improve this question

migrated from stackoverflow.com Feb 1 '12 at 17:09

This question came from our site for professional and enthusiast programmers.

1  
Try setting the ulimit open files higher, like 4096. ulimit -n 4096 –  Michael Feb 1 '12 at 15:57
    
Has the behaviour changed? I had 5.0.77 version and I did not notice this issue. It is only when I upgraded to 5.5.20 I have the values changed! –  shantanuo Feb 1 '12 at 16:08
    
Just going to ask the obvious. Have you confirmed that the upgrade didn't change your my.cnf? Also, what OS? Was this installed by a package manager such as yum? If so, which package? –  Derek Downey Feb 1 '12 at 19:57
add comment

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer
add comment

This appears to be some bug in the new mysqld. I had the same problem. Setting max_connections to 700 worked, but if I set it to 900, it was reset to 214 during startup. If this was a purposeful change, then it would have lowered it to 700 or something similar, the value 214 doesn't make sense.
Anyway, upping the descriptor limit with ulimit -n fixes the problem.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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