1

I am developing an application to provide daily dynamic information like prices, availability, etc for around 50,000 objects. I need to store data for about the next 200 days. That would mean a total of 10 million rows. The prices will be batch updated and new data will be added once daily. Let me say about 10,000 existing rows get updated and 50,000 rows are inserted daily. What is the best backend framework that I can use.

Can MySQL be scalable with limited hardware capability. Or is NoSQL database the way to go? If yes, then which NoSQL database will be best suited for fast fetching and updating the data.

Community
  • 1
  • 1
DanMatlin
  • 1,212
  • 7
  • 19
  • 37
  • Would your 50,000 daily inserts happen all at once each day, or bit-by-bit throughout the day? Also, how much information would be stored for each object? Is the list of different pieces of information you want to store for each object likely to change (e.g. you decided to add an RRP after a few months)? – Alex Jun 09 '14 at 16:44
  • Also, please be more specific about the hardware you have available, and the limitations you mention. – Alex Jun 09 '14 at 16:48
  • @Alex The inserts would happen all at once each day. About a 100 text fields will be stored for each object. But the ones most likely to change are only price and availability. – DanMatlin Jun 09 '14 at 16:48
  • If price and availability are the only fields you expect to change on a regular basis, I'd recommend storing revisions of the other fields separately (if you need to store a revision history of them), rather than duplicating 40,000+ rows of data each day. Assuming that approach, I would say MySQL would cope fine on decent hardware. I don't know enough about NoSQL databases to tell you how they'd perform though. – Alex Jun 09 '14 at 16:58

2 Answers2

2

I would recommend you to use Cassandra, as you need to write more than read, and Cassandra is optimized for high throughput while write. It provide scalability, no single point failure and high throughput. And you can update records as well.
Cassandra also supports batch operation for DML (data manipulation language) i.e. write, update and delete. And batch operation of Cassandra provides atomicity as well.

kkmishra
  • 699
  • 4
  • 10
2

This type of volume is well within the capabilities/capacities of traditional RDBMS. I would say that if you are familiar with MySQL you will be safe to stick with it. A lot depends also, on what kind of queries you want to run. With a properly structured, denormalized setup, you can run ad hoc queries in an RDBMS, whereas with document stores, you need to think quite carefully about structure up front -- embedding versus referencing, see: MongoDB relationships: embed or reference?. MongoDB has added a very nice aggregation framework, which goes a long way towards being able to query data as you would in an RDBMS, but in many other NoSQL systems, queries are essentially map-reduce jobs and joins are either painful or impossible.

It sounds like your data is structured around dates/days. One thing you can do that will yield dramatic speed improvements on queries is partitioning by date ranges. I have worked on dbs over 100m rows in MySQL where historical data had to be kept for auditing purposes but where most of the read/write was on current data, and partitioning led to truly dramatic read query improvements.

You might be interested by this link which shows what some very high volume sites are using: What databases do the World Wide Web's biggest sites run on? Anecdotally, I know that Facebook had trillions of rows in MySQL across various clusters before they started hitting real bottlenecks, but it is no suprise that Cassandra ultimately came out of Facebook engineering, given the truly colossal data volumes they now handle.

Cassandra, Riak, CouchDB, MongoDB, etc all arose to solve very real problems, but these come with tradeoffs, both in terms of the CAP theorem, and in terms of ad hoc queries being more difficult than in RDBMS. Having said that, MongoDB and Cassandra (which I have most experience with) are easy to set up and fun to work with, so if you want to give them a go, I'm sure you will have no problems, but I would say your usage requirements are well within the capabilities of MySQL. Just my 2c.

Community
  • 1
  • 1
John Powell
  • 12,253
  • 6
  • 59
  • 67