Hello all.
I have a seemingly simple problem, but it seems to have turned out to be quite the challenge. I need to keep track of how many people have connected to a server. This will be done from a flash file, using php and such, but that's really irrelevant. Basically, I need to have a MySQL query when the user connects (easy) and a MySQL query when the user finishes the download (again, easy). The problem comes when you consider the amount of users that will close a window, or (and this part removes the possibility of using onUnload), the users browser crashes.
The only way I can think of is having a page that scans through all active connections and disables one that have been open longer than X (in the database, the connection will remain). Unfortunatly, this is simply not feasible due to the fact that this site will have to scale up to at least 5,000 users at peak, with a possibility of 3 or 4 times that number in the case of Slashdotting etc.
I may be approaching this from the wrong way, and if you can come up with an alternative, please let me know. The ideal situtation will use PHP, MySQL, JavaScript or others, but no server-side installs can be done (no cron jobs, etc).
Thanks a lot for reading (I know, it was long, sorry). Any help is GREATLY appreciated - I've been wracking my brains over this one.
Interesting problem (Flash, PHP, JavaScript, others?)
Moderator: General Moderators
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Indeed the best way to do it *used* to be based upon times since last activity... i.e. update a timestamp in the database on every link click and see who was active within the last 120 seconds or so.
These days we have AJAX so you can get your browser to talk to the server at say, 10 second intervals without the user even having to click links etc... use the same principles with that, simply update timestamps in the DB at 10 second intervals.
You want to use an AJAX library since learning it start-to-finish might take a while:
http://xajax.sourceforge.net/
http://www.modernmethod.com/sajax/

These days we have AJAX so you can get your browser to talk to the server at say, 10 second intervals without the user even having to click links etc... use the same principles with that, simply update timestamps in the DB at 10 second intervals.
You want to use an AJAX library since learning it start-to-finish might take a while:
http://xajax.sourceforge.net/
http://www.modernmethod.com/sajax/
Thanks for the reply!
The problem is not having the browser update constantly, it's more having the database keep track of it. While it IS possible to simply have a timestamp updated on a regular basis, it becomes impractical after around 10 users, as scanning a datbase comparing two values is very expensive. This especially applies when we get into the thousands of users that are expected at peak times.
As to AJAX, I am really trying to make it so that there will be no need for a server side install, although it's starting to look like I have to. Although, with AJAX, the same problem applies. It's still sending information to the server at regular intervals, and the main problem is keeping track of that.
Once again, thanks for the reply. Help is much appreciated.
(Also, thanks for the AJAX links, I was interested in AJAX already, but was having a bit of a hard time finding some good software for it. Those will help a fair bit
)
The problem is not having the browser update constantly, it's more having the database keep track of it. While it IS possible to simply have a timestamp updated on a regular basis, it becomes impractical after around 10 users, as scanning a datbase comparing two values is very expensive. This especially applies when we get into the thousands of users that are expected at peak times.
As to AJAX, I am really trying to make it so that there will be no need for a server side install, although it's starting to look like I have to. Although, with AJAX, the same problem applies. It's still sending information to the server at regular intervals, and the main problem is keeping track of that.
Once again, thanks for the reply. Help is much appreciated.
(Also, thanks for the AJAX links, I was interested in AJAX already, but was having a bit of a hard time finding some good software for it. Those will help a fair bit
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
When you start getting thousands of connections though, to compare the two and then count them, I would need to have several queries (is it possible to do a query by checking two timestamps and seeing if they're within a certain time?), and then one query that returns ALL the rows of the connections (unless there's a MySQL function for this?), then count them with PHP.
Let me know if you can do either of those two things though.
Let me know if you can do either of those two things though.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
AJAX sends a request to the server every 10-30 seconds to run a function such as this:
Then on a request for the web page where you need to display the statistics you run a query such as this:
Use either a datetime field or a unix timestamp on your database 
Simple queries like this add nothing.... our app at work runs far more intensive queries per click and that's with millions of rows of data
You'll acheive massive speed increases with well-placed indexes too.
Code: Select all
function markUserActive($userid)
{
$query = "
update
user_activity
set
lastactive = now()
where
uid = '{$userid}'";
mysql_query($query);
}Code: Select all
$query = "
select
count(*) as active_users
from
user_activity
where
lastactive > (now() - 30)";Simple queries like this add nothing.... our app at work runs far more intensive queries per click and that's with millions of rows of data
You'll acheive massive speed increases with well-placed indexes too.
Oops, didn't phrase that well enough.
Updating to the database is not really the problem. It's getting the information from the database, as when someone wishes to connect to a server, they will have to make sure there is a slot available on the server they had selected. Once I have updated the database, there will be several connections, but I will need to pull out the currently active ones (or very close to active, there is a margin of error that's allowed).
The idea is I will select from the database all servers where, say, `slotsUsed` < `slotsAllowed`. Then select one of the returned servers at random (provided there is any returned).
Again, sorry for not making that clear.
Updating to the database is not really the problem. It's getting the information from the database, as when someone wishes to connect to a server, they will have to make sure there is a slot available on the server they had selected. Once I have updated the database, there will be several connections, but I will need to pull out the currently active ones (or very close to active, there is a margin of error that's allowed).
The idea is I will select from the database all servers where, say, `slotsUsed` < `slotsAllowed`. Then select one of the returned servers at random (provided there is any returned).
Again, sorry for not making that clear.