Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have a desktop application that sends a POST request to my website every 5 minutes. This post request can contain a variable amount of data about the PC sending the request, but typically it contains about 20-30 variables of information.

I'm storing all of this information in a MySQL table in a format like, RequestID|Field|Value. I want to be able to keep all of it so I can run trend reports over 24 hours, 48 hours, weeks, months, years, etc.

This could amount so something like 10,000 rows of data every day. That's 3 million rows of data a year.

I can imagine 20-30 people using my website and the database storing 100 million rows of data a year.

What is the best way to approach this kind of 'notification' service and scale it?

share|improve this question
1  
What are you currently using as the backend? Is it an EC2 instance, AppEngine, ..? –  Marvin Pinto Jan 19 '12 at 18:34
add comment

2 Answers

up vote 3 down vote accepted

Only send changes.

Since you're running trends, you don't have to send every 5 minutes. Maybe stash the data on the desktop and send when the user is not so busy. Try to keep it from getting too full.

Aggregate the data into some daily averages and then weekly or monthly. You can off-load old detail data into some other data source to file just in case you want to bring it back to run a new trend analysis/formula that requires details.

share|improve this answer
add comment

Think of it like an incremental backup plan of sorts. Store the aggregated data then throw away the underlying raw data.

A table for the YEAR data, A Table for the MONTHS (1 for each month depending on your DB design I suppose), and so on. At each "level", once you have enough data to summarize, do so and throw that "raw" data away.

For each day, 24/48 hrs, month, year, etc. you'll have to decide a cutoff point for keeping the "raw" data from which that "summary level" was made. For example, are 5 month old daily records useful?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.