How to Stop MySQL Sleep() Processes?

[jsnyder2k]

I've built a site that accesses a mysql database through a class I have constructed. Here is how it is constructed (I've omitted a lot of the functions, but hopefully you get the idea...)

class MySQLDB
{
    function MySQLDB(){
      /* Make connection to database */
      $this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
      mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
	}
   function somefunction($u) {
	   $q = "SELECT something FROM table WHERE this = '$u'";
		$r = mysql_query($q, $this->connection);
		$row = mysql_fetch_row($r);
		return $row[0];
   }
};

$database = new MySQLDB

Everything works really well and fast and I can run queries buy using $database->somefunction($u); from any other page in the site.

The problem is every once in a while I get the sleep() processes that will cause the site to hang until the sleeps are timed out (which seems to be 60 seconds). If a user submits a new query while a sleep() is in process, it will create another sleep() that goes for another 60 seconds.

I read that you need to close your connections with the mysql_close(); to stop connections from timing out, but when I did this in my code it throws errors when it tried to run another query.

So, should I not be using the class? should I be using a mysql_close() anywhere? Or is there something else going on here?

Thanks in advance!

Jim

[dagon]

do you mean a process in the sleep state or are you using the mysql sleep() function ?

[Crowly]

should I be using a mysql_close() anywhere?

PHP will close any non-persistent mysql connections when the script exits. Dont think mysql_close() will solve anything in this case. But if you are going to put it anywhere, put it where you dont need to be connected to the database anymore.

[jsnyder2k]

I don't have any sleep() queries anywhere in my code. I don't want them to show up in the process list at all. They just show up and hang the site. I don't know why.

[dagon]

but what is sleeping? it has to be a particular query(s)

[jsnyder2k]

That's my struggle, dragon...I need to figure out which query it is, but it seems to happen randomly throughout the site. My main question for this thread is 'should I be closing the connection', but since it is closed automatically when it's not used, that can't be the problem. I do have some ajax httprequests throughout the site, so i'm wondering if that might have something to do with it if a request goes unused? If I figure it out, I'll let you guys know.

[dagon]

it does sound like you actully know if its a mysql sleeping process vs a number of other potential issues.

[jsnyder2k]

I added a "kill_sleep" function to my code that is called when the major pages are loaded and the major ajax calls. It will kill sleep processes that are active for 5 seconds or more.

   function kill_sleep() {
	   define ( 'MAX_SLEEP_TIME', 5 );
		$result = mysql_query ( "SHOW PROCESSLIST", $this->connection);
		while ( $proc = mysql_fetch_assoc ( $result ) ) {
        	if ($proc ["Command"] == "Sleep" && $proc ["Time"] > MAX_SLEEP_TIME) {
                @mysql_query ( "KILL " . $proc ["Id"], $connect );
                $display = "KILL " . $proc ["Id"] . "<br />";
			}
		}
   }

Everything is working fast and I have not had a sleep() query hanging the site since I put it in. I'll keep testing it. Let me know if you think this is a bad idea.

[dagon]

sounds very dangerous to me, you don't know what process your killing it could have all sorts of consequences.

[jsnyder2k]

It turns out the sleep queries were caused by session_start(). I'm using sessions to manage user data throughout the site. Our host is Network Solutions and they put the session data in a temporary directory somewhere. I've also learned that this temp directory may be accessible by anyone on that server.

I moved where my files were being saved by using session_save_path() right before calling session_start();

session_save_path("/data/#/#/#/#/#/user/#/htdocs/#/session");
session_start();

(of course, the #'s are filled in with where your values)

I have not had a sleep query and the site runs many times faster. I hope this helps anyone out there who may be having similar problems.

[dagon]

jsnyder2k;10964796 wrote:

It turns out the sleep queries were caused by session_start().

odd

jsnyder2k;10964796 wrote:

I'm using sessions to manage user data throughout the site. Our host is Network Solutions and they put the session data in a temporary directory somewhere.

thats how they work

jsnyder2k;10964796 wrote:

I've also learned that this temp directory may be accessible by anyone on that server.

yup, thats how every shared host does it, but can you guess the session file name? i think they likely hood of some one on a shared host being hacked by another user of the host by grabbing the session data is extremely slim

jsnyder2k;10964796 wrote:

I moved where my files were being saved by using session_save_path() right before calling session_start();

session_save_path("/data/#/#/#/#/#/user/#/htdocs/#/session");
session_start();

(of course, the #'s are filled in with where your values)

I have not had a sleep query and the site runs many times faster. I hope this helps anyone out there who may be having similar problems.

thanks but odd that helped, unless the host was putting them on a particularly slow drive, the location is more or less irrelevant; and what does it have to do with mysql at all.

[jsnyder2k]

Thanks Dragon! It is odd, but the site is amazingly fast right now and users have noticed the difference.

Best,

Jim

[bradgrafelman]

dagon wrote:

can you guess the session file name? i think they likely hood of some one on a shared host being hacked by another user of the host by grabbing the session data is extremely slim

There's really no guessing needed; [man]glob/man the temp directory for session files and capture them all into your own directory for browsing.

Then again, if you're on shared hosting then you must not really expect much security in the first place, right? 😉

[dagon]

Can you imagine actully doing that on any shared host in order to maybe hack someone? Can't say i'm worried for any client on a shared host.