DB updating - realtime or batch from session
Posted on 2014-03-30
I have a moderately busy website with about 110,000 page views a day and it is growing at the rate of about 35% per year. About half of these are using one of a dozen different calculators on the site. I want to collect the calculation data into a mysql database, based on the session id of the user (anonymous data, but attributed to a single session). There appear to be a couple of ways to do this.
1) Have the calculation script write to the DB in real time, user hits submit, render the results page and save the data. The downside to this seems to be that if a user then goes to three or four calculations, I end up appending more data to the record, or having multiple records per session.
2) Put all the data to a session file which is happening anyway, then a few times a day have a process run and collect all the session files since the last execution and grab the parts from that file that are pertinent and write to the DB. I can run this process a few times a day, When server loads are lighter, but I lose the ability to have more realtime reporting of the details (this calculator has been used xxx times in the last 24 hours) which might be an interesting thing to do, and I only get one transaction per user per calculator, which is ok, but I get the last one, which may or may not be the "best" one.
This gets me to my questions: Is one or the other of these obviously better or am I missing another option?
Is there any special things I should be doing for the mysql tables so it can handle 50,000 records a day, I will probably have about 5 different tables, with the session id, timestamp, calculator id, the 5-6 inputs to the calculator, and the results.