3

My server having 2GB of RAM and max_connections variable is set to 100. The website is an active website and having plenty of traffic, and I expect more than 100 users simultaneously using the website and all of these simultaneous users will must communicate with Database.

Since some days I/Users have started getting the error "Too many connections", many people suggest to increase the max_connections value to something higher. I just want to confirm from some expert that keeping in view the RAM (2GB) and the simultaneous users, is this wise to increase value of max_connections to 200 or 300 or any larger number? Should I increase the RAM if I increase the value or 2GB RAM can handle larger value?

Edit - - Flow of my web pages

//Start
$mysql_conn = mysql_connect("localhost","USER_NAME","PASSWORD") or die( mysql_error()."  cannot connect...");
mysql_select_db("DATABASE",$mysql_conn);
//[ALL THE LOGIC OF PAGE]
@mysql_close($mysql_conn);
//End

Regards,

Gulfam
  • 558
  • 6
  • 27
  • did you use persist connection and close it at the end of use? – Haim Evgi Jul 17 '14 at 10:16
  • it depends on how you handle the connections to your DB. increasing db connection limit is not always the right solution. – mohamnag Jul 17 '14 at 10:17
  • What do you use the database for? – ljacqu Jul 17 '14 at 10:18
  • 100 connections open is pretty high, you should be able to handle thousands of parallel user accesses with that. – mohamnag Jul 17 '14 at 10:18
  • @HaimEvgi I am not using persist connections Editing question for the flow of my web pages – Gulfam Jul 17 '14 at 10:20
  • are you using a try finally block to close the connection? if not it is possible that your code does not always reach the point that connection is closed (like when an exception is thrown in the middle) – mohamnag Jul 17 '14 at 10:26
  • @mohamnag, Thats what I think too that 100 is a fair enough figure but why am I getting the "Too Many Connections" error then? Any hint? Any clue? – Gulfam Jul 17 '14 at 10:26
  • @ljacqu, Sorry could not understand the question. – Gulfam Jul 17 '14 at 10:26
  • @mohamnag I guess this should not the issue because mysql_close is not a must. What do you say? And I am not using try finally block in my code – Gulfam Jul 17 '14 at 10:27
  • `show processlist;` might help pin point whats happening – Rob Jul 17 '14 at 10:28
  • I would definitely use a try finally and you can also try to see the processes (open connections) to see how old they are: http://stackoverflow.com/questions/1620662/mysql-see-all-open-connections-to-a-given-database – mohamnag Jul 17 '14 at 10:29
  • @Rob, I tried using "show processlist" and "SHOW STATUS WHERE `variable_name` = 'Threads_connected';" but result of these queries is always normal (under 100) – Gulfam Jul 17 '14 at 10:31
  • my Aborted_connects value is 2245. Can this be an issue? Also the variable Max_used_connections is having value 101. Is this value means that at some point connections of MySQL reached 101 while limit is 100 (max_connections)? – Gulfam Jul 17 '14 at 10:34
  • what is the value for time column when you do 'show processlist'? – mohamnag Jul 17 '14 at 10:42
  • Increasing memory may help a little bit but it's better to check for speeding up your DB. For concurrent connections it's always good to use cache. So, mysql performance tips and APC would help you to solve the problem permanently. If you have the same problem after those then upgrading your server configuration is a solution. – artuc Jul 17 '14 at 10:43
  • @mohamnag, User, Host, DB, Command, Time, State, Info - - - - - - - - - - - - - - - - - - - - - - - - USER_NAME, localhost, DATA_BASE, Sleep, 543, , USER_NAME, localhost, DATA_BASE, Query, 0, , SHOW FULL PROCESSLIST – Gulfam Jul 17 '14 at 11:07
  • @artuc, Though I can google but its always better to get a link for something better. So if you can provide me link for some Article for what you wrote? – Gulfam Jul 17 '14 at 11:08

1 Answers1

3

It was an hard disk issue i.e. Hard disk was full.

Gulfam
  • 558
  • 6
  • 27