Unanswered: Performance anxiety: many rows, constantly changing
Preface: I wouldn't be surprised if the answer to my question already exists out in the ether, so if you know of a resource that answers my question, please feel free to link to it and don't feel obligated to reinvent the wheel unless you want to.
I run a website with several thousand users daily (say 10k), generating about 30k pageviews daily. I'm setting up a "users online" table that I'd also like to use to track visits (I use Analytics for serious analysis, but this will be useful for tracking members' habits, etc.). The site is also inherently 'social', otherwise I wouldn't be creating this otherwise silly addon.
Because of the way 'users online' tables work, 30k pageviews = at least 30k db calls daily. Because I want the data in this table to be persistent, I don't plan on deleting rows.
On each pageview, one of two things will happen: If appropriate, I'll modify a visitor's already-existent row (if a user revisits before a set timeout, I'll modify their row to set a new timeout timestamp). Otherwise, a new row will be created for each visitor.
So for sake of argument, I'll generate 10k new rows daily, with 20k updates to these rows each day.
Is this anywhere near the level of traffic where this should be of concern for a MySQL table? (Also, based on growth patterns, I would not be surprised to see these numbers multiplied by 10 by this time next year. Would 100k new daily rows with 200k daily updates be seen as inherently problematic?)
The reason I ask is that I have an alternative solution, which involves 2 tables: an active table and an inactive (storage) table. Only the active table would be written and updated. The inactive table would just receive the old, inactive rows when deleted from the active table. Are there any benefits to this alternative approach, or is it all in my head?
Thanks for reading this!
Last edited by nineinchnailgun; 10-16-08 at 16:49.