Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 Dec 4 '11 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 Dec 4 '11 at 0:02
    
@NickChammas: MyISAM, I thought about InnoDB but InnoDB has amazingly slow SELECT COUNT(*) –  genesis Dec 4 '11 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 Dec 4 '11 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 Dec 4 '11 at 2:12

1 Answer 1

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.
share|improve this answer
    
Is it necessary to use memchached? Couldn't I use flat files & my own caching layer? –  genesis Dec 4 '11 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 Dec 4 '11 at 12:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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