Too many connections to DB preventing some queries from executing.

Question

I have an issue where users on my web site have recently begun to receive the error message:

Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I know that the connection strings in the code are correct, as users are able to query the database view pages that retrieve info from and submit user-generated info to the database. This leads me to believe that the error is a result of too many connections open at one time. The code is php, and each php page has itw own mysql_open and mysql_close commands, so I'm wondering if there are too many connections open at one time. I don't know if that's likely, given that there are only 20 people that log into the site on any given day. My question is - how can I tell how many connections are open at any given time, and can I somehow keep track of the # of connections open throughout a random day? I'm relatively new to this, and I'm using phpmyadmin to manage the database. If it is in fact too many connections, what's the best approach to deal with it?

And are there any other possible causes that I'm not yet considering? Basically, there's no predictable way to determine whether or not a user's data will be submitted or whether an error will be returned, which is disconcerting from the user's perspective, to say the least.

This question is closest to my problem but does not provide a solution to fix my specific issue, hence my posting a new question.

Thanks in advance for the ideas.

Answer 1

Create a pool of connections with a "connection pool", that might help manage how many connections you allow. Also there is a command in MYSQL that should tell you how many connections there are.

Here is one command that might work-- show processlist;

Then you will see the lingering processes. Each has an ID, and you can kill them by process ID right in the MySQL Monitor by typing:

kill 2309344;

Hope this helps a bit.

This is an issue when you think you have closed a connection but there are pages from programmers....well the programmer simply forgot to close his/her connections!

Answer 2

show status; from within a mysql client will show you the stats. in particular you want to look at max_used_connections i think.

Answer 3

You should make sure you actually close the connections or better yet make sure that you aren't opening too many connection on a single request. If you have a include tag in a page and both the including and the included page will do a mysql_open() you will at one point have two open connections simultaneously. You should try to avoid that and reuse the connection provided by mysql_open(). Make sure to actually close it too :-).

Answer 4

Tangential comment: if you show mysql errors to web users, you make it very easy to exploit any security holes you might have.