Solved

DB updating - realtime or batch from session

Posted on 2014-03-30
3
235 Views
Last Modified: 2014-06-05
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.
0
Comment
Question by:greglwhite
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39964865
Personally, I would definitely store each calculation as it is done. Not only does that give you the full range of calculations of user may try, but it also enables you to do real-time data  analysis.  

The only other problem I see with doing batch processing is that you have to make sure that your session database garbage collector does not run before you have a chance to store the information in your calculations database. Probably fairly simple but if your garbage collector runs frequently, you may not get the benefit that you're looking for.

Of course, that seems to beg the question of whether or not you could do all of your data analysis off of the session database and whether or not you actually need a new database at all.
0
 

Author Comment

by:greglwhite
ID: 39965574
I have multiple load balanced servers so we are using flat session files, not a session database.
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 39965637
So how do you clean out your session files? Whatever process you use to clean out your session files has to run less frequently than your batch update. The other thing is that if the user logs out, that usually deletes the session, which also would make it difficult for you to keep that a transaction log.

I still think it would be better to have the queries tool log in real time because that way you don't have to depend on the timing of another process. As a benefit, you get the real-time analysis.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
A publishing tool, a Version Control System, or a Collaboration Platform! These can be some of the defining words for the two very famous web-hosting Git repositories: Bitbucket and Github. Git is widely used amongst the programmers and developers f…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now