?
Solved

DB updating - realtime or batch from session

Posted on 2014-03-30
3
Medium Priority
?
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
mankowitz earned 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s a strangely common occurrence that when you send someone their login details for a system, they can’t get in. This article will help you understand why it happens, and what you can do about it.
In this article, we’ll look at how to deploy ProxySQL.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to selectively show certain fields based on user input using rules to gather relevant information and data from your forms. The rules feature provides you with an opportunity…

777 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