i have been doing a lot of data mining at my new job. we collect TONS of data, and it's my job to visualize that sea of information in real time with statistic/heuristic analysis.

we're currently using mysql on a dedicated virtual server. i'm commonly getting the sum of 100,000+ rows in a single query. and let me say, that some of my more complex multi-table queries are taking 14sec+ :P i'm constantly trying to optimize my sql routines to increase performance, but there's only so much i can do.

my boss keeps asking me if we need to move our data to the cloud. and honestly, i'm not sure. i have read a lot about "how great it is", "how fast it is", etc... but it's hard to tell what's marketing and what's legit.

i have also been thinking about caching data. i have done a bit of this in the past and can see a real benefit from it. but i'm not really sure how applicable it is in my situation. we're getting live data all the time, and give the users the ability to mine that data in a verity of ways. (e.g. hourly, daily, weekly, monthly, quarterly, yearly, all time, or a user defined custom time range) if you looking at a data-set from say last month, that data is finalized. there will be no new data added to it ever. but for this month, new transactions are happening every moment, and i'm not sure if i can even cache that type of thing... sigh

i'm not opposed to moving away from sql either. it's just the model we're currently using. but as our company grows (thus the volume of data read+write) i want to continue to offer a very fast service for both the businesses using it as well as their users.

so can i get some feedback (and suggestions) from real world users? cloud sql? or using/creating some caching engine?


1 Answer 1


You've asked a question which often results in real projects (and I freely admit to being a consultant in this area).

I'm just going to give you some things to think about:

  • If you are getting your results from the cloud, you have to get your data there. This may involve additional lags.
  • How real-time is real-time?
  • Do you have programming expertise in the group/company your work for? For complex data manipulation tasks, Hadoop (and its derivatives) is a cloud-based technology that works well.
  • Once you've found something of interest, do you have a way to "productionize" it, so updating the results does not waste analysts' time.
  • The cloud (such as AWS) can provide very scalable hardware. However, hardware is pretty cheap. Part of the question is the balance between expenses versus capital investment.
  • Are you tied to MySQL? Other databases have better functionality for complex queries on large data sets.
  • getting data too the cloud wouldn't be a big deal. right now we do a tiny bit of processing then just save it. porting over our current data would be trivial and updating our api to save it to a different location/different way would probably be pretty simple. how real time is up to the users. when a transaction happens we have the data, when the business decides to use that data is up to them. i guess i am the expert, lol. googling hadoop now. our data is mined on the fly, i have some pre-written graphing algos i pass a json blob to. no i'm not tied to mysql, LAMP is just our current stack.
    – xero
    Jan 15, 2013 at 20:27
  • I would also consider making a data warehouse for older data. You would have one snapshot of fairly recent data that is live and near real time. Then as data gets older, it gets moved out to a data warehouse that is "offline". You can run queries against it, but the business knows they will be slow. Similar in concept to keeping your working set of data in RAM and other data on disk.
    – ryan1234
    Jan 15, 2013 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.