Zombie MYSQL connections

erect

erect

New member
Jun 3, 2007
3,796
154
0
Esoterica
twitter.com
Got an interesting problem I've never dealt with before, soliciting advice openly.

So I've got a mysql database sitting on an Amazon RDS server. About a week ago, this fucker started to buld up sleeping processes ... to the point it crashed the database. Website was displaying something to the tune of "too many database connections"

So here was my "fix"

PHP: 
$q = mysql_query("$elect id from information_$chema.proce$$list where user='database_username' and Command = 'Sleep'") ;
for($i=0; $i<mysql_numrows($q); $i++) {
  mysql_query("KILL ".mysql_result($q,$i,'id').";") ;
}
Forgive the $ above, vbulletin wouldn't let me submit that sql query.

So originally, a cron hits that script every few minutes and it loops to kill all sleeping processes.

Here's a screenshot of my db connections logs over at amazon

attachment.php



The big hump from a week ago shows when I implemented this "fix" the first time (2nd big hump I moved the fix to run every minute) ... you can see it's steadily rising after the 2nd crash.

I have no confidence I've "solved" the problem and am looking for input as to why I've all of a sudden got a lot of zombie connections showing up. Rebooting the instance doesn't drop the connections back to zero anymore. Any suggestions?
 


The better solution is to solve the root of the problem. Your php scripts aren't closing the mysql connection.

Found this: (Too many mysql processes!! - Programming Discussion - Web Hosting Talk)
If you connect with mysql_pconnect() connection is persistent and will not close when script exits so you have to do it with mysql_close().

If you connect with mysql_connect() the connection will die on its own when script exits so there is no need to mysql_close().
Click to expand...

I'm not sure how much of that qualifies for your scenario, but my gut instinct also says you have sleep calls since you aren't closing the mysql properly. Try seeing if the script gets to the full end and closes by having it ping back to your or output to the debug window (I have no idea how that works in php).

Edit: Also, you're screenshot is broken.​
 
CCarter said:
The better solution is to solve the root of the problem. Your php scripts aren't closing the mysql connection.​
Click to expand...

Absolutely ... I know my hack didn't fix the root, which is why I'm here. I'd love to hit the problem at the source. My issue is, I don't know what that is.

CCarter said:
Found this: (Too many mysql processes!! - Programming Discussion - Web Hosting Talk)


I'm not sure how much of that qualifies for your scenario, but my gut instinct also says you have sleep calls since you aren't closing the mysql properly. Try seeing if the script gets to the full end and closes by having it ping back to your or output to the debug window (I have no idea how that works in php).​
Click to expand...

Found mysql_pconnect zero times in the folders for this site.

This site has been alive for years, probably billions of queries hitting this RDS instance with no problems. Out of the blue last week, massive traffic jam.

CCarter said:
Edit: Also, you're screenshot is broken.​
Click to expand...

RDS Management Console | Awesome Screenshot

There it is hosted by some place other than WF.

You can see that before this spike, the site was sitting on zero active connections almost all day every day ... and nothing has changed scripting wise (except that something obviously did change). FYI: the cpu graph is almost a mirror of this one. None of the others show any difference except the "Binary Log Disk Usage" which dropped from 1MB to practically nothing.
 
If you connect with mysql_connect() the connection will die on its own when script exits so there is no need to mysql_close().
Click to expand...
Possibly by the docs that quote is correct. In practice, it's far from it. Try multicurling some pages that are mysql_query() heavy. You'll find out quickly that those connections aren't closed immediately ... perhaps when the page closes, but when you're layering queries on top of external scraping on top of more queries, the way php handles closing mysql connections leaves a lot to be desired.

Personally, I use

PHP: 
function run_query($sql) {  
  $db_user    = ""; // MySQL username
  $db_pass    = ""; // MySQL password
  $db_host    = "localhost"; // MySQL host, mostly localhost
  $db_db      = ""; // MySQL database
  
  $connect = mysql_connect($db_host,$db_user,$db_pass);   
  if($connect == TRUE) {
      if(mysql_select_db($db_db) != TRUE) { exit("<span style='color: red'>DB table error</body></html>"); }
  } else {
    exit("<span style='color: red'>DB login error</body></html>");
  }
  $query = mysql_query($sql) ;
  mysql_close($connect) ;
  return $query ;
}
Anytime I have a site that's got a super high mysql load ... basically any high volume automation. So instead of doing mysql_query("SELECT username, ......."), I just use run_query("SELECT username, .......") and php makes a connection then closes it for every query that is ran.

Unfortunately, that's not this problem.
 
It doesn't sound like the problem is your code, it's got to be Amazon and the way it's handling it at a server level. I really thing it was an update to php, mysql, or the server at some level that's causing extra sleep connections to happen.

On second thought, maybe it's because the run_query is starting up and stopping very rapidly, if you are doing more than one mysql call. Since each instance it starts and stops? In theory if Amazon makes their servers faster, and your script is going at lightening speed, some stop connections could hang cause it's too fast for the database cause of the start and stop?

Try setting the start at the beginning of the script and the stop at the end instead of within the function, and see if the starting and stopping is causing some of the connections to hang.​
 
Hahaha. Same code you have been running as long as I known you.

Without further analysis it's hard to say. Even if you don't "close the connection" after script execution PHP should close it automatically. At least that's how it's always worked. Have you updated to a new version of PHP recently?

If you want me to take a look hit me up (I have some free time).
 
Doing a connection per database query is incredibly inefficient.

Concurrency limitations are not because of when you close the connection, it's because, well, you are trying to be too concurrent (using too many database resources).
 
Tough to tell from here, but there's a chance a hacker or five decided to direct their attention towards you. If you haven't changed anything, and this problem magically popped up, then something happened.

Have you by chance checked your server logs, to see if anyone is hammering the hell out of any services? There's a chance they're flooding your server with requests while searching for a vulnerability.
 
Maybe you have too much data for it to all be in memory, slowing it down, so queries take longer, resulting in more simultaneous connections.

Look at the general upward trend, ignoring spikes. Do you have sensible indexes on your data?
 
mattseh said:
Concurrency limitations are not because of when you close the connection
Click to expand...

In this case, apparently it was.

Forgot to update this thread. Here's what happened (best guess) ... loop through 3k records with multiple queries ran in each loop and the mysql server decided it was going to crap out.

When it crapped out, apache decided to keep the page active ... which means that using mysql_query() kept a connection open all the time. Another day, another connection. After a few connections built up, even mild volumes of traffic would fill up the available connections.

My solution, after lots of trial and error, was teh run_query() function I was talking about above. this means that the 3k record loop is still happening & for each of the queries, run_query() opens and closes it really quick as opposed to keeping the db connection open for the duration of the page loading/completion. Wer'e talking probably 30k db queries (now with a unique connection & closing for each) in about 30 seconds.

You know, it sucks when you know the answer to the problem but can't connect teh dots to draw the right conclusion and use it.

thank you much for all the suggestions, it did help .. teh cron is cranking through teh page fast again and actually 100% finishing.
 
erect said:
In this case, apparently it was.

Forgot to update this thread. Here's what happened (best guess) ... loop through 3k records with multiple queries ran in each loop and the mysql server decided it was going to crap out.

When it crapped out, apache decided to keep the page active ... which means that using mysql_query() kept a connection open all the time. Another day, another connection. After a few connections built up, even mild volumes of traffic would fill up the available connections.

My solution, after lots of trial and error, was teh run_query() function I was talking about above. this means that the 3k record loop is still happening & for each of the queries, run_query() opens and closes it really quick as opposed to keeping the db connection open for the duration of the page loading/completion. Wer'e talking probably 30k db queries (now with a unique connection & closing for each) in about 30 seconds.

You know, it sucks when you know the answer to the problem but can't connect teh dots to draw the right conclusion and use it.

thank you much for all the suggestions, it did help .. teh cron is cranking through teh page fast again and actually 100% finishing.
Click to expand...

If you're doing a query for each iteration in a for loop, you can generally run that as one query, with much better performance.
 
I have to ask, why are you using the mysql API and not the mysqli API? mysql is depreciated, and might cause bugs, like this, in newer versions of mysql server. You shouldn't use persistent connections either. It's not even an option in the new mysqli API.
 
You must log in or register to reply here.
Top Bottom
Back