mySQL FIFO and Thoughts on Data Posterity

I am working on a project that will track the actions of users and store them in a database in order to build an activity stream not dissimilar to Facebook’s news feed. Right away I can see that I am very quickly going to accumulate more data than i can possibly manage using conventional methods so I am left with a few obstacles to overcome if I am to implement something useful while not sacrificing performance or posterity. The idea is a user wants to see their own activity stream or possibly another user’s stream. The first decision that must be made is how far should the user be able to go back to see activities in the past? Should they be capped at the last 25 activities, or should there be pagination available to see older activities? For posterity my instinct is to never delete any activities ever however anyone can see how this could become unmanageable especially if I were to track activities as mundane as page views. Additionally, this would be the only way to generate statistical reports for individual users such as how many total page views they have, or what content categories do they frequent the most.

Another thought that occured to me was I could investigate the possibility of using one of the many touted ‘noSQL’ solutions such as mongo and the ilk. From what i’ve read they would be good solutions for storing large amounts of data that do not need to be fetched very often. I can see activities that fall off the end of a FIFO queue going into one of these archival data stores and only the latest activities remaining in the mysql database. If i were to go this route, the follow up concern would be performance of maintaining this FIFO queue in a mysql table when recording activities. I came across a great article on implementing a FIFO queue in mysql which i’ve considered experimenting with. The situation I’d like to avoid is having to do multiple sql queries for every activity insertion. This article seems to have a solution for doing it all in one shot which sounds promising however I would need to modify it slightly to handle a modulus that is associated to a two column index since my table would track multiple datasets not a single one (multiple users).

In exploring solutions I would love to hear some suggestions on optimal ways of handling these problems. What’s best practice for managing user activity history using either standalone mySQL or mySQL + an additional data store?