2

I need to store more metrics for my web app. User behavior and other conditions needs to be tracked over time and compared.

Some records have a timestamp associated with it, some don't. So an on-demand query for metrics might not be suitable all the time. I think what is needed is a snapshot (daily?) of certain analytics queries (via a cronjob?) that I write and then stored somewhere (DB? file?).

Right now I'm worried about the size of these snapshots if I were to save them in the DB. How will it affect my app's performance?

Admins should be able to view these analytics on a web dashboard. Imagine storing the state of 100,000+ rows of data every day....and then querying them pretty often to do analysis.

Are there other good approaches to storing and viewing metrics? I'm using the LAMP stack for my app. Also using Google Analytics and other 3rd party metric tracking tools but it's not good enough to track specialized statistics for my app.

samxli
  • 1,536
  • 5
  • 17
  • 28
  • 1
    Can you be more specific about the nature of what you need to store, and how much data there actually is? How it affects performance depends on the size of the footprint. – Calvin Froedge Jun 18 '11 at 02:25
  • For example: We might want to track the daily number of users that change their profile settings. I can put a timestamp on when they LAST updated their profile in the user table, but that doesn't show me anything historical. This is just a simple example. A more complex one might include: a SQL join on different tables and then needing those type of query results, over time. – samxli Jun 18 '11 at 02:37
  • 1
    Perhaps this question / answers may help you http://stackoverflow.com/questions/2672178/database-designing-an-events-table. – Alix Axel Jun 18 '11 at 02:48
  • Will a 3rd party OLAP solution solve this? It was suggested to me by a friend. Will look into it for more details. – samxli Jun 19 '11 at 17:11

1 Answers1

2

If it is that intensive i would set up a separate DB (possibly on an entirely different server) for the snapshots. If admins are going to view them in a web interface i would probably stick with a DB though. That way you can just run snapshot of base analytic info, then you can build a separate interface/app to query that data further and present it.

As Calvin said in his comment, it would be good have some examples of the data youre recording though as well as how much of it you think there is going to be on the daily. That info might change my thoughts on it.

prodigitalson
  • 60,050
  • 10
  • 100
  • 114
  • Let's say I have a user table. And the user has a column that stores their current cash balance. That cash balance will fluctuate over time. But I would like to track the changes over time FOR ALL users. – samxli Jun 19 '11 at 16:50
  • As you suggested, OLAP cubes might be a solution. Perhaps I'm a bit biaised, but have a look to http://www.icCube.com You're welcome to post an architecture/design question in our forum. – Marc Polizzi Jul 07 '11 at 09:47
  • Going with a simple MySQL solution for now and building out a star schema on a separate DB and running daily scripts to load the metrics data. – samxli Jul 23 '11 at 04:12