MySQL too many connections

Posted by Webnet on Server Fault See other posts from Server Fault or by Webnet
Published on 2010-06-18T01:21:55Z Indexed on 2010/06/18 1:24 UTC
Read the original article Hit count: 318

Filed under:

optimization

On my server I have 7 databases. Our server has 512 MB of RAM which I'm getting upgraded this evening to 2GB and has a 2.4 single processor.

I've gotten an error about the connection limit exceeded. With increasing my RAM, is it ok to increase the number of connections? Currently it's set to 200 but a single page may connect to 3-4 databases considering JOINs and things.

We've setup so many databases for mere organization. We have a total of about 250-300 tables in all of the databases.

Any advice would be appreciated :)

MySql too many connections

Posted by MichaelMcCabe on Stack Overflow See other posts from Stack Overflow or by MichaelMcCabe
Published on 2010-06-18T12:03:40Z Indexed on 2010/06/18 12:13 UTC
Read the original article Hit count: 317

Filed under:

java

|

mysql

|

tomcat

I hate to bring up a question which is widely asked on the web, but I cant seem to solve it.

I started a project a while back and after a month of testing, I hit a "Too many connections" error. I looked into it, and "Solved" it by increasing the max_connections. This then worked.

Since then more and more people started to use it, and it hit again. When I am the only user on the site, i type "show processlist" and it comes up with about 50 connections which are still open (saying "Sleep" in the command). Now, I dont know enough to speculate why these are open, but in my code I tripple checked and every connection I open, I close.

ie.

public int getSiteIdFromName(String name, String company)throws DataAccessException,java.sql.SQLException{

Connection conn = this.getSession().connection();
Statement smt = conn.createStatement();
ResultSet rs=null;
String query="SELECT id FROM site WHERE name='"+name+"' and company_id='"+company+"'";

rs=smt.executeQuery(query);
rs.next();

int id=rs.getInt("id");

rs.close();
smt.close();
conn.close();
return id;
}

Every time I do something else on the site, another load of connections are opened and not closed. Is there something wrong with my code? and if not, what could be the problem?

Developer IT