[StefanOnRails]

Hi guys,

I'm new on this forum and I have a question related to MySQL database. Let's say I have a JS script which access via Ajax a PHP file. The file updates and returns the number of online users (it executes 4 querys). Considering JS script calls the PHP one every 1 second (+ Time/Latency - 1,0X s / 1,0X s), can my method cause a crash to the database if too many users are online at a time and so, too many querys are executed?

My JS file:

$(document).ready(function(){
	var logout = false;
	setInterval(checkUsers,1000);
	
	$("a.logout").click(function(){
		logout = true;
	});
	
	function checkUsers(){
		if(logout) return;
		$.get("uo.php",function(data){
			$("p#uo").html("Users online: <b>" + data + "</b>");
		});
	}
	
});

My PHP file:

<?php
// Block non-Ajax access
if(isset($_SERVER['HTTP_X_REQUESTED_WITH']) && $_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest'){
		
	session_start();
	define('INCLUDE_CHECK',true);	
	require_once 'includes/config.php';
	$time = time();
	$timeout = $time - 10;
	$user = $_SESSION['user'];
	
	$ps = $pdo->query("SELECT COUNT(*) FROM `online` WHERE `user` = '". $user ."'");
	$status = (bool) $ps->fetchColumn(0);
	if($status) $pdo->query("UPDATE `online` SET `timestamp` = ". $time ." WHERE `user` = '". $user ."'");
	else $pdo->query("INSERT INTO `online`(`user`,`timestamp`) VALUES('". $user ."',". $time .")");
	
    // Delete records older than 10 seconds
	$pdo->query("DELETE FROM `online` WHERE `timestamp` < ". $timeout .""); 
	
	$ps = $pdo->query("SELECT COUNT(*) FROM `online`");
	$onlineNum = (int) $ps->fetchColumn(0);
	echo $onlineNum;
	
}else header('Location: index.php');

Sorry if my question seems to be a little un-professional, but I'm an amateur programmer and I'm still learning

[DarenR]

you can always use with(nolock) in ms sql that way multiple users can access tables at the same time

[AdaHacker]

StefanOnRails, on 17 August 2012 - 09:41 AM, said:

...can my method cause a crash to the database if too many users are online at a time and so, too many querys are executed?

Short answer: yes.

Long answer: It depends.

A given database server can only handle so much traffic, so anything that generates a lot of queries has the potential to push it over the edge and grind your server to a halt. However, there's not really any way to tell in advance how many is too many - it will depend on the queries, traffic volume, your server, etc. You'd have to just try it and see. Personally, I'd question the need for something like this to be up-to-the-second accurate, but whatever. Either way, four queries per user per second is not inherently a problem and you can probably get away with it on a low-traffic site.

As for the code you posted, I can see a couple of problems right off the bat.

• If you're going to use PDO, don't use string concatenation to insert data into your queries. That's an SQL injection attack waiting to happen. Use prepared statements instead. If you've already validated that $_SESSION['user'] is an integer, then you're lucky - what you have there is actually safe. But it's still a bad habit to get into.
  
• The block where you check whether to do an INSERT or UPDATE is unnecessary. You don't need to do a SELECT first to see if the row exists. Instead, you can just do an INSERT with an ON DUPLICATE KEY clause to accomplish the same thing in one step.

[baavgai]

I don't think I'd bother checking or deleting. The insert is quick and non blocking. Updates take a longer, for the where lookup and row lock. Deletes take time, for row locks and other cleanup.

Just insert. Use an intelligent select. Delete some other time as part of maintenance. One insert, one select.
e.g.

//	$ps = $pdo->query("SELECT COUNT(*) FROM `online` WHERE `user` = '". $user ."'");
// $status = (bool) $ps->fetchColumn(0);
// if($status) $pdo->query("UPDATE `online` SET `timestamp` = ". $time ." WHERE `user` = '". $user ."'");
// else 
$pdo->query("INSERT INTO `online`(`user`,`timestamp`) VALUES('". $user ."',". $time .")");

// do this later, when you feel like it.
// Delete records older than 10 seconds
// $pdo->query("DELETE FROM `online` WHERE `timestamp` < ". $timeout .""); 

$ps = $pdo->query("SELECT COUNT(distinct user)"
	. " FROM `online`"
	. " WHERE `timestamp` < ". $timeout
	);
$onlineNum = (int) $ps->fetchColumn(0);
echo $onlineNum;

I'd also consider making the timestamp generated by the database, so you needn't bother to pass it.

Changes are the most expensive thing you can do in a database and queries the least. With this in mind, I believe the above provides the least impact for what you're doing.

That said, since you're using session...

Do you need to know the very last access time, or will just knowing they're on do? If you just want to know they're on, set the time at session creation. There is a function, session-register-shutdown that will let your run code when a session closes. You could time them out then.

Or...

Create a record for them when the session is created. If they have a session, you know they have a record. Just update it, no checking. I would use a auto gen primary key for this. You could have a history of logins...

More than one way to skin this cat, obviously.

Hope this helps.

[vectorialpx]

Also, you can have a cron that makes that DELETE action every X seconds.
You don't have to make the delete for each user.

[StefanOnRails]

Many thanks to you all! You helped me a lot with your suggestions . This is what I like at this community: no matter how strange my problem seems to be, there's always someone out there, with more experince, who can bring me on the right path!

This post has been edited by StefanOnRails: 18 August 2012 - 11:30 AM