1

I'm working on software for social network and I plan it to scale a lot. Actually, I'm updating timestamp column every time user clicks, to be sure they're marked as online. When they do not click for 30 seconds, they're marked as offline. So simply, my queries are

UPDATE Users SET timestamp = UNIX_TIMESTAMP WHERE uid = 1; -- UNIX_TIEMSTAMP is current unix timestamp given by PHP's time()
SELECT Name FROM Users WHERE timestamp > (UNIX_TIMESTAMP - 30); -- (UNIX_TIMESTAMP - 30) is given directly by PHP (time() - 30)

In this answer, answerer said that

Will you be updating this table continuously? I doubt that would scale

is there a better way to update user's online status that would scale? Or do you think this is fine?

Improve this question
9
  • I think it would be better, though more complicated, to store the last time a user was active in your caching layer and only periodically write that time to the database. For starters this should get you by.
    – Nick Chammas
    Commented Dec 4, 2011 at 0:02
  • How many users do you expect and what kind of activity do you expect? What storage engine are you using for Users?
    – Nick Chammas
    Commented Dec 4, 2011 at 0:02
  • @NickChammas: MyISAM, I thought about InnoDB but InnoDB has amazingly slow SELECT COUNT(*)
    – genesis
    Commented Dec 4, 2011 at 0:35
  • @NickChammas: You mean to store ids in a flat file & updated once a minute for all users (UPDATE Users SET timestamp = time() WHERE uid IN (ids, here)) ? I currently do not expect activity on my own network, maybe after a few years, but I'd might want to sell it & I want to be sure it will be able to scale. If you have any tips, share them, please
    – genesis
    Commented Dec 4, 2011 at 0:36
  • Why do you care about the performance of COUNT(*)? What you want to handle is high concurrent activity against the Users table, and for that you need InnoDB.
    – Nick Chammas
    Commented Dec 4, 2011 at 2:12

1 Answer 1

Reset to default
2

The fastest and easiest to scale query is the query you don't run.

  • Move your session state to a cache tier like memcached or Redis.
  • Update the cached value on every user interaction, not the database value.
  • Persist to the database via a background task that periodically scans the cache for updates.
Improve this answer
2
  • Is it necessary to use memchached? Couldn't I use flat files & my own caching layer?
    – genesis
    Commented Dec 4, 2011 at 12:10
  • Why develop a caching solution when one already exists, that is well tested and proven to scale. I don't see how you can compare your flat file approach with a scale out solution like memcached.
    – Mark Storey-Smith
    Commented Dec 4, 2011 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.