Skip to main content ↑ ↓
Topic: Cache-based "who is online" (Read 6945 times) previous topic - next topic
0 Members and 1 Guest are viewing this topic.

Cache-based "who is online"

While receiving notifications from a very-busy website, I noticed that about 20/25% of the slow query notices (more than 5 seconds) I'm receiving is related to smf_log_online (the rest are from mods and few from known buggers).

Now, that table is not very important, in the end is just an array that is pulled, pushed and "cleaned" at each page load, so I was thinking to implement an alternative way to log people on-line relying only on the cache.

The slowest query commands are mainly REPLACE, UPDATE and DELETE.
I think that even using a php array, and having let's say 400 items in that array, loop them shouldn't take 5 seconds, ever (and using the session as index of the array, the only case a loop would be required is DELETE).
The only thing that at the moment comes to my mind as potentially problematic is the size of that array, with 150 items the mysql table is about 50 KiB, so it should be about 150 KiB when 450 users are on-line together. I suppose an equivalent php array could be about the same size, maybe a bit more, but I hope not 10 times more (this is related to memcached limit of 1MB per element I found a while ago, that is anyway configurable, so it should no be a big problem).

I'm not sure this is worth a "core feature", so for the moment I may create an addon (I suppose it will need to edit some code all around the places though), but I'm posting it here to hear from you what you think about that. :)
Bugs creator.
Features destroyer.
Template killer.

Re: Cache-based "who is online"

Reply #1 –

just an idea (not fully related but it may help Fixing the issue): Have you tried changing the table engine to MEMORY?
Thorsten "TE" Eurich
------------------------

Re: Cache-based "who is online"

Reply #2 –

No, not yet.
I thought about it quickly while writing the post, and I did some (basic) research now.

http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
According to the page, it may or may not fix my current issue:
Quote from: Performance CharacteristicsDespite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions.
Since that entire db is already InnoDB, then only way to know is try, but considering an average of 200/300 hundred people online about at the same time (I think the interval to update the online list is currently set at 1 minute or so) it Ç’uld result in quite a bit of table locks... :-\
Anyway I'll give it a try.:D

The other point is that MEMORY would work only for MySQL. For sure postgre doesn't have anything equivalent (at least I didn't find anything), while SQLite has virtual tables that may be the same thing... dunno. But we do not currently support them in any way.
Bugs creator.
Features destroyer.
Template killer.

Re: Cache-based "who is online"

Reply #3 –

Tonight I got some nice stats: 3200 slow queries related to smf_log_online on a total of about 4000 in about 2/3 hours... xD
I have to do something... :(
Bugs creator.
Features destroyer.
Template killer.

Re: Cache-based "who is online"

Reply #4 –

Wow ... thats quite a few.  Why the 1min setting, is that to try and keep the list down to size due to it being so active?

Re: Cache-based "who is online"

Reply #5 –

TBH I'm not entirely sure...
Bugs creator.
Features destroyer.
Template killer.

Re: Cache-based "who is online"

Reply #6 –

You're saying 1 minute is the timeout for them being online? That would mean that every minute 300 unique users are accessing the site. That is a massive site. I'm betting the query cache is enabled. So, you're updating that as well. Did you try the MEMORY engine?

Re: Cache-based "who is online"

Reply #7 –

Mainly are crawlers (soso IIRC).
There are spikes of activity every week at a certain time.
At the beginning they were "visiting" with a certain useraget (sosospider or something like that), so I deny'ed it from htaccess, the week after that they started coming from a single IP with a different UA (IE6 somehting), fine, let's deny the IP, they changed IP, let' deny the second too. After that, "someone" (I think it's still the same) started hitting hard like that from a bunch of IPs, so I added some code to SMF so that if a guest generates a slow query it is banned for (I think) a double amount of time. And still they are able to push thousands of slow queries in a matter of hours... :-S

BTW it's WCRPG, so the site is rather big anyway. :P
Bugs creator.
Features destroyer.
Template killer.

Re: Cache-based "who is online"

Reply #8 –

TBH I forgot about altering the table until a couple of days ago I got about 6000 emails in a matter of minutes... lol
Well, now is memory, though it didn't solve that much, I just got 482 slow queries and about 95% of them were related to the log_online table...
Bugs creator.
Features destroyer.
Template killer.

Re: Cache-based "who is online"

Reply #9 –

https://gist.github.com/joshuaadickerson/9880707
Been playing with this. I hate it. Right now it does way too much in writeLog(). Problem is that we are accessing the membergroups with a join. I've said this before, that table is used way too often that it should be standard for every page load as its own request.