What scalability problems have you encountered using a NoSQL data store?

NoSQL refers to non-relational data stores that break with the history of relational databases and ACID guarantees. Popular open source NoSQL data stores include:

• http://incubator.apache.org/cassandra/">Cassandra</a> (tabular, written in Java, used by Cisco, WebEx, Digg, Facebook, IBM, Mahalo, Rackspace, Reddit and Twitter)
• http://couchdb.apache.org/">CouchDB</a> (document, written in Erlang, used by BBC and Engine Yard)
• http://github.com/cliffmoon/dynomite">Dynomite</a> (key-value, written in Erlang, used by Powerset)
• http://hadoop.apache.org/hbase/">HBase</a> (key-value, written in Java, used by Bing)
• http://www.hypertable.org/">Hypertable</a> (tabular, written in C++, used by Baidu)
• http://sourceforge.net/projects/kai/">Kai</a> (key-value, written in Erlang)
• http://memcachedb.org/">MemcacheDB</a> (key-value, written in C, used by Reddit)
• http://www.mongodb.org/">MongoDB</a> (document, written in C++, used by Electronic Arts, Github, NY Times and Sourceforge)
• http://neo4j.org/">Neo4j</a> (graph, written in Java, used by some Swedish universities)
• http://project-voldemort.com/">Project Voldemort (key-value, written in Java, used by LinkedIn)
• http://code.google.com/p/redis/">Redis</a> (key-value, written in C, used by Craigslist, Engine Yard and Github)
• http://riak.basho.com/">Riak</a> (key-value, written in Erlang, used by Comcast and Mochi Media)
• http://github.com/tuulos/ringo">Ringo</a> (key-value, written in Erlang, used by Nokia)
• http://code.google.com/p/scalaris/">Scalaris</a> (key-value, written in Erlang, used by OnScale)
• http://code.google.com/p/terrastore/">Terrastore</a> (document, written in Java)
• http://code.google.com/p/thrudb/">ThruDB</a> (document, written in C++, used by JunkDepot.com)
• http://1978th.net/tokyocabinet/">Tokyo Cabinet/Tokyo Tyrant (key-value, written in C, used by Mixi.jp (Japanese social networking site))

I'd like to know about specific problems you - the SO reader - have solved using data stores and what NoSQL data store you used.

Questions:

• What scalability problems have you used NoSQL data stores to solve?
• What NoSQL data store did you use?
• What database did you use prior to switching to a NoSQL data store?

I'm looking for first-hand experiences, so please do not answer unless you have that.

My current project actually.

Storing 18,000 objects in a normalised structure: 90,000 rows across 8 different tables. Took 1 minute to retrieve and map them to our Java object model, that's with everything correctly indexed etc.

Storing them as key/value pairs using a lightweight text representation: 1 table, 18,000 rows, 3 seconds to retrieve them all and reconstruct the Java objects.

In business terms: first option was not feasible. Second option means our app works.

Technology details: running on MySQL for both SQL and NoSQL! Sticking with MySQL for good transaction support, performance, and proven track record for not corrupting data, scaling fairly well, support for clustering etc.

Our data model in MySQL is now just key fields (integers) and the big "value" field: just a big TEXT field basically.

We did not go with any of the new players (CouchDB, Cassandra, MongoDB, etc) because although they each offer great features/performance in their own right, there were always drawbacks for our circumstances (e.g. missing/immature Java support).

Extra benefit of (ab)using MySQL - the bits of our model that do work relationally can be easily linked to our key/value store data.

Update: here's an example of how we represented text content, not our actual business domain (we don't work with "products") as my boss'd shoot me, but conveys the idea, including the recursive aspect (one entity, here a product, "containing" others). Hopefully it's clear how in a normalised structure this could be quite a few tables, e.g. joining a product to its range of flavours, which other products are contained, etc

Name=An Example Product
Type=CategoryAProduct
Colour=Blue
Size=Large
Flavours={nice,lovely,unpleasant,foul}
Contains=[
Name=Product2
Type=CategoryBProduct
Size=medium
Flavours={yuck}
------
Name=Product3
Type=CategoryCProduct
Size=Small
Flavours={sublime}
]

I've switched a small subproject from MySQL to CouchDB, to be able to handle the load. The result was amazing.

About 2 years ago, we've released a self written software on http://www.ubuntuusers.de/ (which is probably the biggest German Linux community website). The site is written in Python and we've added a WSGI middleware which was able to catch all exceptions and send them to another small MySQL powered website. This small website used a hash to determine different bugs and stored the number of occurrences and the last occurrence as well.

Unfortunately, shortly after the release, the traceback-logger website wasn't responding anymore. We had some locking issues with the production db of our main site which was throwing exceptions nearly every request, as well as several other bugs, which we haven't explored during the testing stage. The server cluster of our main site, called the traceback-logger submit page several k times per second. And that was a way too much for the small server which hosted the traceback logger (it was already an old server, which was only used for development purposes).

At this time CouchDB was rather popular, and so I decided to try it out and write a small traceback-logger with it. The new logger only consisted of a single python file, which provided a bug list with sorting and filter options and a submit page. And in the background I've started a CouchDB process. The new software responded extremely quickly to all requests and we were able to view the massive amount of automatic bug reports.

One interesting thing is, that the solution before, was running on an old dedicated server, where the new CouchDB based site on the other hand was only running on a shared xen instance with very limited resources. And I haven't even used the strength of key-values stores to scale horizontally. The ability of CouchDB / Erlang OTP to handle concurrent requests without locking anything was already enough to serve the needs.

Now, the quickly written CouchDB-traceback logger is still running and is a helpful way to explore bugs on the main website. Anyway, about once a month the database becomes too big and the CouchDB process gets killed. But then, the compact-db command of CouchDB reduces the size from several GBs to some KBs again and the database is up and running again (maybe i should consider adding a cronjob there... 0o).

In a summary, CouchDB was surely the best choice (or at least a better choice than MySQL) for this subproject and it does its job well.

Todd Hoff's highscalability.com has a lot of great coverage of NoSQL, including some case studies.

The commercial Vertica columnar DBMS might suit your purposes (even though it supports SQL): it's very fast compared with traditional relational DBMSs for analytics queries. See Stonebraker, et al.'s recent CACM paper contrasting Vertica with map-reduce.

Update: And Twitter's selected Cassandra over several others, including HBase, Voldemort, MongoDB, MemcacheDB, Redis, and HyperTable.

Update 2: Rick Cattell has just published a comparison of several NoSQL systems in High Performance Data Stores. And highscalability.com's take on Rick's paper is here.

We moved part of our data from mysql to mongodb, not so much for scalability but more because it is a better fit for files and non-tabular data.

In production we currently store:

• 25 thousand files (60GB)
• 130 million other "documents" (350GB)

with a daily turnover of around 10GB.

The database is deployed in a "paired" configuration on two nodes (6x450GB sas raid10) with apache/wsgi/python clients using the mongodb python api (pymongo). The disk setup is probably overkill but thats what we use for mysql.

Apart from some issues with pymongo threadpools and the blocking nature of the mongodb server it has been a good experience.

I apologize for going against your bold text, since I don't have any first-hand experience, but this set of blog posts is a good example of solving a problem with CouchDB.

CouchDB: A Case Study

Essentially, the textme application used CouchDB to deal with their exploding data problem. They found that SQL was too slow to deal with large amounts of archival data, and moved it over to CouchDB. It's an excellent read, and he discusses the entire process of figuring out what problems CouchDB could solve and how they ended up solving them.

We've moved some of our data we used to store in Postgresql and Memcached into Redis. Key value stores are much better suited for storing hierarchical object data. You can store blob data much faster and with much less development time and effort than using an ORM to map your blob to a RDBMS.

I have an open source c# redis client that lets you store and retrieve any POCO objects with 1 line:

var customers = redis.Lists["customers"]; //Implements IList<Customer>
customers.Add(new Customer { Name = "Mr Customer" });

Key value stores are also much easier to 'scale-out' as you can add a new server and then partition your load evenly to include the new server. Importantly, there is no central server that will limit your scalability. (though you will still need a strategy for consistent hashing to distribute your requests).

I consider Redis to be a 'managed text file' on steroids that provides fast, concurrent and atomic access for multiple clients, so anything I used to use a text file or embedded database for I now use Redis. e.g. To get a real-time combined rolling error log for all our services (which has notoriously been a hard task for us), is now accomplished with only a couple of lines by just pre-pending the error to a Redis server side list and then trimming the list so only the last 1000 are kept, e.g:

var errors = redis.List["combined:errors"];
errors.Insert(0, new Error { Name = ex.GetType().Name, Message = ex.Message, StackTrace = ex.StackTrace});
redis.TrimList(errors, 1000);

I have no first-hand experiences., but I found this blog entry quite interesting.

I find the effort to map software domain objects (e.g. aSalesOrder, aCustomer...) to two-dimensional relational database (rows and columns) takes a lot of code to save/update and then again to instantiate a domain object instance from multiple tables. Not to mention the performance hit of having all those joins, all those disk reads... just to view/manipulate a domain object such as a sales order or customer record.

We have switched to Object Database Management Systems (ODBMS). They are beyond the capabilities of the noSQL systems listed. The GemStone/S (for Smalltalk) is such an example. There are other ODBMS solutions that have drivers for many languages. A key developer benefit, your class hierarchy is automatically your database schema, subclasses and all. Just use your object oriented language to make objects persistent to the database. ODBMS systems provide an ACID level transaction integrity, so it would also work in financial systems.

I switched from MySQL(InnoDB) to cassandra for a M2M system, which basically stores time-series of sensors for each device. Each data is indexed by (device_id,date) and (device_id,type_of_sensor,date). The MySQL version contained 20 millions of rows.

MySQL:

• Setup in master-master synchronization. Few problem appeared around loss of synchronization. It was stressful and especially in the beginning could take hours to fix.
• Insertion time wasn't a problem but querying required more and more memory as the data grew. The problem is the indexes are considered as a whole. In my case, I was only using a very thin parts of the indexes that were necessary to load in memory (only few percent of the devices were frequently monitored and it was on the most recent data).
• It was hard to backup. Rsync isn't able to do fast backups on big InnoDB table files.
• It quickly became clear that it wasn't possible to update the heavy tables schema, because it took way too much time (hours).
• Importing data took hours (even when indexing was done in the end). The best rescue plan was to always keep a few copies of the database (data file + logs).
• Moving from one hosting company to an other was really a big deal. Replication had to be handled very carefully.

Cassandra:

• Even easier to install than MySQL.
• Requires a lot of RAM. A 2GB instance couldn't make it run in the first versions, now it can work on a 1GB instance but it's not idea (way too many data flushes). Giving it 8GB was enough in our case.
• Once you understand how you organize your data, storing is easy. Requesting is a little bit more complex. But once you get around it, it is really fast (you can't really do mistake unless you really want to).
• If previous step was done right, it is and stays super-fast.
• It almost seems like data is organized to be backuped. Every new data is added as new files. I personally, but it's not a good thing, flush data every night and before every shutdown (usually for upgrade) so that restoring takes less time, because we have less logs to read. It doesn't create much files are they are compacted.
• Importing data is fast as hell. And the more hosts you have the faster. Exporting and importing gigabytes of data isn't a problem anymore.
• Not having a schema is a very interesting thing because you can make you data evolve to follow your needs. Which might mean having different versions of your data at the same time on the same column family.
• Adding a host was easy (not fast though) but I haven't done it on a multi-datacenter setup.

Note: I have also used http://www.elasticsearch.org/">elasticsearch</a> (document oriented based on lucene) and I think it should be considered as a NoSQL database. It is distributed, reliable and often fast (some complex queries can perform quite badly).

I don't. I would like to use a simple and free key-value store that I can call in process but such thing doesn't exist afaik on the Windows platform. Now I use Sqlite but I would like to use something like Tokyo Cabinet. BerkeleyDB has license "issues".

However if you want to use the Windows OS your choice of NoSQL databases is limited. And there isn't always a C# provider

I did try MongoDB and it was 40 times faster than Sqlite, so maybe I should use it. But I still hope for a simple in process solution.

I used redis to store logging messages across machines. It was very easy to implement, and very useful. Redis really rocks

We replaced a postgres database with a CouchDB document database because not having a fixed schema was a strong advantage to us. Each document has a variable number of indexes used to access that document.

I have used Couchbase in the past and we encountered rebalancing problems and host of other issues. Currently I'm using Redis in several production projects. I'm using redislabs.com which is a managed service for Redis that takes care of scaling your Redis clusters. I've published a video on object persistence on my blog at http://thomasjaeger.wordpress.com that shows how to use Redis in a provider model and how to store your C# objects into Redis. Take a look.

I would encourage anyone reading this to try Couchbase once more now that 3.0 is out the door. There are over 200 new features for starters. The performance, availability, scalability and easy management features of Couchbase Server makes for an extremely flexible, highly available database. The management UI is built-in and the APIs automatically discover the cluster nodes so there is no need for a load balancer from the application to the DB. While we don't have a managed service at this time you can run couchbase on things like AWS, RedHat Gears, Cloudera, Rackspace, Docker Containers like CloudSoft, and much more. Regarding rebalancing it depends on what specifically you're referring to but Couchbase doesn't automatically rebalance after a node failure, as designed, but an administrator could setup auto failover for the first node failure and using our APIs you can also gain access to the replica vbuckets for reading prior to making them active or using the RestAPI you can enforce a failover by a monitoring tool. This is a special case but is possible to be done.

We tend not to rebalance in pretty much any mode unless the node is completely offline and never coming back or a new node is ready to be balanced in automatically. Here are a couple of guides to help anyone interested in seeing what one of the most highly performing NoSQL databases is all about.

1. Couchbase Server 3.0
2. Administration Guide
3. REST API
4. Developer Guides

Lastly, I would also encourage you to check out N1QL for distributed querying:

1. N1QL Tutorial
2. N1QL Guide

Thanks for reading and let me or others know if you need more help!

Austin

I have used Vertica in the past.It relies on columnar compression & expedites disk reads and lowers storage needs to make the most of your hardware. Faster data loads and higher concurrency lets you serve analytics data to more users with minimum latency.

Earlier, we were querying Oracle database having billions of records & the performance was very sub-optimal. The queries took 8 to 12s to run, even after optimizing with SSD. Hence, we felt the need to use a faster read optimized, analytics oriented database. With Vertica Clusters behind the lean service layer, we could run APIs with sub-second performance.

Vertica stores data in projections in a format that optimizes query execution. Similar to materialized views, projections store result sets on disk OR SSD rather than compute them each time they are used in a query.Projections provide the following benefits:

1. Compress and encode data to reduce storage space.
2. Simplify distribution across the database cluster.
3. Provide high availability and recovery.

Vertica optimizes the database by distributing data across cluster using Segmentation.

1. Segmentation places a portion of data on a node.
2. It evenly distributes data on all nodes. Thus, each node performs a piece of the querying process.
3. The query runs on the cluster and every node receives the query plan.
4. The results of the queries are aggregated and used to create the output.

For more, please refer to Vertica documentation @ https://www.vertica.com/knowledgebase/