The company I work for creates applications for the Blackberry platform.
We(ve been working on a proprietary "analytics system" that allows us to embed code within our applications and have the applications report back some stats to our central servers every time they(re run. Currently, the system works ok; however it(s only in beta with 100-200 hits per hour. The "hits" are sent to the servers without a problem. We(ve built a very solid API to handle the acceptance and storage of the hits (in a MySQL DB). We(ve tested the load and we should be able to accommodate hundreds of thousands of hits per hour without a problem. That(s not really a problem.
The problem is showing the stats. We(ve built a display panel similar to Mint(s (haveamint.com), it shows the hits over each hour, the past days, months, weeks, years...etc. The fist version ran straight queries pulling data from the hits table and interpreting it on the fly. That didn(t work for very long. Our current solution is that the hits are "queued" for processing and we have a cron come through every 5 minutes taking the hits and sorting them into "caches" for each hour, day, week, month, year...etc. This works amazing and it(s incredibly scalable; however, it only works for 1 timezone. Since the entire company has access to this, we(re dealing with a few hundred users in various timezones. What I define as "Today" in San Jose is MUCH different than what my colleague in London defines as Today. Since the current solution is only cached to 1 timezone, it(s a nightmare for anyone who(s checking the data outside of our timezone.
Our current plan to fix this is to create caches for every timezone (40 in total); however, that would mean that we(re multiplying the amount of data by 40...that(s terrible to me and given that the caches can be very large, multiplying it just sounds like a bad idea; plus, when we go to process the queue, it(s going to take a lot more CPU time to put them in 40 different caches.
Any one else have a better idea of how to solve this problem?
(Sorry for such a long question..it(s not exactly easy to explain. Thanks all!)
posted date: 2009-04-12 09:16:00