Couldn't agree more.
Printable View
Couldn't agree more.
This isn't a good approach because a SELECT COUNT(*) against this table will lock it, and any login/logout action will want to lock a row to write to it, so it'll have to wait for any SELECT to finish. So if you have 100 logins/logouts per second and 50 of those are log-ins, with each request taking 8ms due to lock contention, you'll have 100*8 = 800ms + 50*8ms = 1200ms of work to do every second, but you only have 1000ms of time to do it. So this will fail at that low level of activity. This is also assuming that the SELECT COUNT(*) isn't slow, and it will be if this table has tens of thousands of rows, much slower than 8ms and the locking issue will actually be much worse.
A better approach is to use an atomic key/value store to keep track of how many users are online. This can also be used to track exactly which users are online, and their usernames so you don't have to perform the same SELECT against the users table for each user, and instead simply write whatever's in the cache. As others have pointed out, a simple on/off doesn't work in an online application where a user is "online" if they recently did something (because they aren't required to touch your logout page before leaving). In this case, you can either back an activity-based timestamp in a user table with a K/V query with expiration on write, which is the straightforward and easy solution, or you can do something a bit more complex with a means to fire triggers on expiration. Memcached and Redis don't currently support this so it'd have to be custom (it could be done pretty trivially in Node.js), but it would scale much better at the cost of complexity. In the simple solution, a good solution would be to have an activity table that keeps track of the last time each user performed an action (mutable actions, not GETs as these may be cached). A simple tuple table with an fkey to the user table on id (thus an index on id) and a timestamp field will have very little write-lock contention and a simple use of Memcache or Redis to cache a user count where activityTime > some timestamp can be stored for 5-30 seconds and replaced each time it expires, meaning the amortization of the read lock will be trivial, especially since it can be done against a slave.
I should point out that if you just want the number of records in a query, you should use COUNT(*). The code presented here will return a cursor, which uses resources, and requires an implicit ordering, which makes the operation much slower.
This is a great post... but I'm worried not very many people will be able to.. understand it.Quote:
Originally Posted by jMerliN
Let's see if I can trivialize it; please do correct any mistakes I may make jMerliN.
WhiteWizz: Your script will cause the MySQL table to lock up - even with a very low amount of activity. This is bad. Really bad. No one should use your script because of this.
jMerliN offers two alternative approaches.
The simpler one is as follows: Have an activity table that links users to their last active time. Whenever the user does something on your website that signifies they are online, record the current timestamp in that activity table. Then you can just run a query every 5-30 seconds(or whatever interval you want it to update at) to get the count of rows that were active recently.
The more complex one: I don't fully understand this, partially because jMerliN didn't appear to go into much detail, but I'll do my best to explain it. You fire a trigger when the user's active session expires, which would, I assume, update the current active users list. Due to limitations in key/value stores, jMerliN recommended building this in Node.js.
Do note that the more complex solution is more scalable than the simpler solution.
Obviously, jMerliN goes into more technical detail about both of the above solution in his original post. He justifiably recommended the use of a key/value storage such as Memcache or Redis rather than using a relational database such as MySQL. jMerliN, I'm not sure if you were recommending or not the use of MySQL for either solution in your post, so if you were, please do correct me.
As an example, I've made the following schema:
I filled this with 10000 fake users, with names of 'bob0000' to 'bob9999'. As a stress-test, I wrote this Node.JS script.Code:CREATE TABLE `ragezone_test`.`users` (
`id` INT NOT NULL ,
`name` VARCHAR(45) NOT NULL ,
`active` TINYINT NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`) )
COMMENT = 'Your run-of-the-mill user table, not normalized, as an example.';
It will attempt to perform 400 log-ins and 400 log-outs and 400 SELECT COUNT's to simulate a count after a successful login to show the user how many people are logged-in. Once all requests are done, it determines how long this entire process took.
After a few runs, I find that the average time taken for this (1200 requests total), is roughly 2400ms. That means each request is taking roughly 2ms, and the maximum throughput we can handle here is 500 requests/second. Not terrible. Now I'll try a few things:
1. I'll change it from a COUNT(*) to a SELECT *
2. I'll remove the COUNT(*) to get an idea of the cost of the writes alone
In the first case, I find the cost is now 17000ms, which is SEVEN times the cost in time, it uses 4x as much CPU, and 2x as much memory. Needless to say, use COUNT(*) if you just want a count. In the second case, it takes just around 850ms, which is a little more than 1ms per request (800 vs 1200, since we cut out the COUNTs). Twice as fast, and we can easily perform over 800 requests a second, pretty sexy improvement.
So now we have some baseline ideas on how fast this is using a purely-MySQL driven approach. I'll go ahead and implement the simple alternative I gave for a reference. In the first suggestion, I'm creating a table called 'active_users'. Here's the table creation:
Then I fill this table with 10000 entries, one for each user, with the default value of 0. I'll run my original example on this, with a modified query to COUNT(*) where active_time is greater than a timestamp (new code). The results are as follows:Code:CREATE TABLE `ragezone_test`.`active_users` (
`user_id` INT NOT NULL ,
`active_time` TIMESTAMP NOT NULL DEFAULT 0 ,
PRIMARY KEY (`user_id`) ,
INDEX `active_users_idx` (`user_id` ASC) ,
CONSTRAINT `active_users`
FOREIGN KEY (`user_id` )
REFERENCES `ragezone_test`.`users` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE);
1. Identical to the previous but using `active_users` and a modified COUNT(*): 2400ms
2. Now using no COUNT(*): 850ms
So similar numbers. Now I'm going to re-write this slightly to do a continuous stress test. It'll try to push as many requests as possible and count how many requests we've seen, and every 1000ms displaying the current throughput to stdout. Same code, just continuous, and instead of dumping everything in a for loop, I'll issue a repeat request in the callback of each do_logout, do_login, and do_usercount. Each will increment a global that will keep track of how many requests we've handled.
Sample output (using full COUNT):
Now, I'm going to mimick the use of a K/V store. Because I don't want to use Memcache or Redis here, I'm going to implement a very naive method of expiring keys. I will only use this to expire the actual count, and on each expiry I will re-compute the count. This is doable via PHP with Memcache by simply checking if the count key exists, if not, perform the COUNT query, otherwise, just use the existing value.Code:Throughput: 515 r/s
Throughput: 522 r/s
Throughput: 492 r/s
Throughput: 522 r/s
Throughput: 509 r/s
Throughput: 516 r/s
Output using expiring count, recomputed every 2 seconds:
It should be pointed out this solution uses almost zero CPU compared with the COUNT version. This also scales very well. Here's the same test run with 1 million rows (and a 5 second expiry, which is far more real-time than most sites need, a 5-minute update would be sufficient for most):Code:Throughput: 902 r/s, currently active: 4905
Throughput: 956 r/s, currently active: 4890
Throughput: 958 r/s, currently active: 4890
Throughput: 933 r/s, currently active: 4931
Throughput: 972 r/s, currently active: 4931
Throughput: 972 r/s, currently active: 4956
Throughput: 986 r/s, currently active: 4956
Throughput: 959 r/s, currently active: 4960
Throughput: 919 r/s, currently active: 4960
Throughput: 960 r/s, currently active: 4963
But it's pretty clear what happens every 5 seconds here when the COUNT is updated. The disparity between the 'currently active' is because of how I'm selecting records to retire. Since the probability space is so much larger, the log-in code runs pretty much unchecked until it stabilizes at the maximum number of active users (~450 * 30, since I'm expiring sessions after 30 seconds). And with COUNT:Code:Throughput: 990 r/s, currently active: 13269
Throughput: 949 r/s, currently active: 13269
Throughput: 964 r/s, currently active: 13269
Throughput: 655 r/s, currently active: 13258
Throughput: 962 r/s, currently active: 13258
Throughput: 960 r/s, currently active: 13258
Throughput: 1013 r/s, currently active: 13258
Throughput: 946 r/s, currently active: 13258
Throughput: 661 r/s, currently active: 13283
I won't write an example using no COUNTs because it's quite a bit more complicated, and might be best solved using a fibonacci heap or something similar. You basically need a priority queue with very high performance for removing smallest nodes (expired) and very good performance for re-balancing the heap after an update (increment). That's more work than I'm willing to put into an example, for now. I'll probably end up writing this in one of my projects, in which case I'll post the code here. The benefit of this solution, while complicated, is that it doesn't need a backing table, which means it can achieve thousands to tens of thousands of requests per second, and scales extremely well. Not really necessary for most websites, I would point out.Code:Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Also, final code.
Edit: Added the below quick implementation of the second idea as an example.
Here's a crude implementation using a HashMap with 10000 simulated users. No backing table, but it doesn't scale as well as it could because of thrashing in the backing array for the hash map and because the cleaning code is O(n^2) or O(n^3) (because it's obviously O(n) but it also does deletes), where n is the number of active users. Better structures could eliminate almost all of these overheads.
The currently active figure isn't even useful here. With how fast this operates, this model isn't even capable of representing real usage. For one, logouts would be very rare, but they would also only occur to a person who's active. For two, activity isn't registered only as a log-in, and while "login" is a misnomer here, the approximation is close to correct, but it's unlikely a completely random selection of users would be performing action, rather it would be some fraction of users repeating lots of activity (thus massive updates to already existing timestamps), and some other fraction with a pattern of action followed by no action until the timeout occurs (insert then delete before re-insert). There's also some fraction of users who don't ever really log-in, so with 10000 registered users, a site might only have 500 active users. This is capable of such a high throughput in a good case that this simulation immediately balances at half of the user base logged-in and generally has a very full users map, which makes this implementation scale particularly horrendously under this synthetic test. In a realistic case, it would be significantly better as the expected number of active users is low, and this scales poorly with the number of active users (but excellent with the number of total users in the system). A realistic model would leave almost all of the 'delete' operations to the cleanup and have few active users, so the thrashing that occurs in this model due to the logout wouldn't exist, and the throughput should be probably an order of magnitude better than it is in this case. The most important thing when implementing a high-performance anything is that it fit the scenario well, and this doesn't fit my test well at all.Code:Throughput: 114298 r/s, currently active: 5024
Throughput: 113380 r/s, currently active: 5035
Throughput: 113776 r/s, currently active: 5022
Throughput: 114394 r/s, currently active: 5006
Throughput: 113686 r/s, currently active: 4983
Throughput: 113830 r/s, currently active: 4969
Throughput: 116638 r/s, currently active: 5048
Throughput: 113740 r/s, currently active: 5000
Throughput: 113572 r/s, currently active: 4982
Indeed, if the logout is disabled, the throughput skyrockets to over 500K r/s at 10000 users due to a lack of thrashing (I'm somewhat limited by the implementation of the hash map in Javascript, and some other data structure would likely be significantly better even at my test). With 100K users, it gets 450K r/s, and it can't even function at 1 million users because the gc sweep is too expensive.
I usually do it like this if it helps anyone
you can see i just use a unix timestamp on the field `lastactivetime` but only check active players as my banned players get `active` 0 and `status` 3 so i only need check active cause new account wont account either just easier this way for mePHP Code:
$online = time() - 180; // 3 mins since actively doing something on site
$usersonline = mysql_result($this->DB->query("SELECT COUNT(*) FROM `users_sys` WHERE `active` = '1' AND `lastactivetime`> $online"),0);
then i just echo this on the page where online statistics
Optimizing access to cross-request variables can be challenging with a stateless language like PHP. It doesn't support atomicity or anything similarly without an external database of some sort. I'd say a typical SQL database for keeping track of the activity times, then storing the number of active users in memcached for a set period of time is probably the best approach.
Honestly, for a pserver, I would go with node.js as jMerliN suggested unless you have good prior experience in other languages. For an enterprise, such as an official MMORPG publisher, I'd say node.js is too new and it would be difficult to find developers experienced with it. Although, I could see more lightweight languages like Javascript replacing PHP/ASP.NET in some years.
You should really try out PDO or MySQLi, faster and much more safer. MySQL is going to be deprecated soon in PHP6.
I was trying to figure out a way to do this without a database. You can just count the active sessions. The only problem is that the folder where the sessions are stored is protected. This can be bypassed by using a custom folder to save the sessions in.
I came across this solution on Stackoverflow.
PHP Code:<?php
// you must set your own accessible session path atop every page.
session_save_path("/home/some/other/location/"); # SECURITY VIOLATION!!!
session_start();
function session_count() {
$filter = "sess_";
$files = scandir(session_save_path());
$count = 0;
foreach ($files as $file) {
if(strpos($file,$filter)===0) {
$count += 1;
}
}
return $count;
}
echo session_count();
?>
The database way is probably better but I figured I would toss this in here anyways.
I imagine this would scale horrendously.Quote:
Originally Posted by leenster
could u explain me, how u can believe javascript is able to replace asp.net. i agree, php is easy to pwn due the lack of securiy etc, but asp.net provides a strong oop model with a huge delievered .net lib collection. also asp.net is very fast after compiling and it allows huge projects without much programming time to spend on. basics are might harder, but complex stuff getting easier, i.e. look on the entity framework. now compare this high language model with javascript. doest provide real oop. server sided like node.js it wont rule just cuz it will be slow.Quote:
Originally Posted by mootie
Node.js is simple, and performance is becoming less and less important especially for smaller websites. Any website getting less than 1000 requests per second could run easily on node.js(in almost all cases) from what I've seen. The other issue with ASP.NET is scalability, considering how easy it is to scale a node.js application in comparison. Also note that LinkedIn serves over a billion requests per month with node.js, which is a pretty strong number.Quote:
Originally Posted by VibeTribe
i agree node.js is pretty simple, such as php, node.js is even safer than php which is a good point. for smaller websites node.js could really become a leading tool within next years. im not facing smaller websites at all, however. to say node.js could replace asp.net u drop into a clanger. asp.net has no issue with scalability nor performance, its all about how u go for it.Quote:
Originally Posted by xLethal
believe me im familar with the asp.net system and its optimal for web development. sure are some cons when im looking on the freedom to edit html and bring in variables or reading out them from controls. however, this is just a hidden lie, cuz asp.net provides own possibilities to do anything (its completly different and hard to compare with other languages, its a high system not a script system). many ppl call it the wrong way microsoft goes, cuz its a system which is build by itself and i felt like i was in a programming jail when i skipped from php to asp. now when i understand the system, life cycle, behaviors, web control inflection to html controls, c# at all, etc. i see the high pros of this system. to build something small on a correct way takes longer, but to expand the "small" (no matter how big it really is) never takes insane long if u do it right. without three tier logic u wont success, but with it u will overview your code so easily and maintain it faster than any language can provide you.