Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I started to encounter some issues on my production site, when having a web page that needs to load a very big ResultSet (currently coming from relational database, MySQL) it takes forever, and problem these result sets are only getting bigger and bigger.

I started to seek after a better solution, and what I came across with is the idea of saving the data in a NoSQL database. (I'm already using Mongo, but Mongo is inefficient due to high amount of DML in my environment.) So when searching the web I thought about the following 2 options:

  1. CouchDB
  2. CouchBase

When looking at both of the above, I can tell say that both are JSON document based (ok, that's a good start), but when getting into some technical background I do look for better caching (I don't wan't to kill my server's I/O) then MongoDB's master-master replication ability (I saw that CouchDB can replicate easily based on source->destination / destination->source).

Can someone provide me some of your input, and if you had tried the above solutions I will be happy to hear about your experience.

share|improve this question
5  
Before you decide to go NoSQL, what is the nature of your "very big ResultSet"? Is it a document, is it data? How much data? –  JNK Feb 13 '13 at 13:23
3  
How many records are in your very big result set? If you ran the query locally on your database server how long does it take to run? The problem could be an inefficient query, fragmented data, or even network latency. Replacing your RDBMS because of a slow-performing web page seems rather drastic. –  datagod Feb 13 '13 at 16:05
    
You can look to this page to see the differences between Couchbase and CouchDB: couchbase.com/couchbase-vs-couchdb They are 2 different products, both Document/JSON databases but with very different approach regarding scalability. Couchbase has a builtin cache -based on memcache- that could be very helpful for your application. –  Tug Grall Feb 19 '13 at 6:24

2 Answers 2

up vote 11 down vote accepted

IMO you are making what is probably a pretty common mistake when it comes to web pages which is to assume that the answer to performance problems due to initial result size on MySQL is to jump to NoSQL solutions often with little understanding of what the tradeoffs are or how to use them appropriately and effectively.

I would be surprised if a well-tuned db was actually the problem here for a web app, if the size of the result set is the issue. The simple fact is that a result set can only be retrieved from disk so fast (assuming you aren't using a main memory db where everything is forced in RAM), and then you actually have to spend time processing the result set to get your web page. You need to start by fully profiling everything before assuming it's the db.

Your most basic tradeoff in NoSQL is data entry flexibility and easy scaling vs integrity guarantees and data processing on output. The only way to do data processing in NoSQL over a result set of any size is to essentially do it on input and this has significant impacts over the life of a product if a NoSQL solution is used at the expense of a traditional RDBMS. On the other hand, were appropriate these offer adjuncts to the RDBMS which can be helpful for both pre- and post-processing. In short there are reasons to choose NoSQL but size really isn't one of them.

Now, you mention here that this is a "web page" that is loading a "very big result set." Now, I do crazy things with web apps sometimes, and I suspect that if you are really loading a very large result set directly into a web page you have plenty of issues other than db performance.

In LedgerSMB for example, I know of cases where we pull over a thousand invoice rows to generate a single web page for some users (we use PostgreSQL). For us PostgreSQL hums along quite well, even when aggregating across tends of thousands of records pulled out of multi-million-record tables. Our time spent (profiled) per page load at that level is approx 15 sec of db time to up to 5 min of web app time to generate the web page. (This is acceptable for the reason that it does globally optimize the workflow for this customer, keep in mind the web page may have up to 20k input elements, and the data has to be significantly transformed between where the db server sends it and where the web page is built). This may not match your use case specifically but it may give you an idea of the fact that the database doesn't have to be the bottleneck there, and probably isn't if you are doing a lot with a web page.

Here are some aspects of troubleshooting and options you have if the db is in fact the problem.

  1. Profile your whole application. How much time is actually spent on db stuff? How much is spent processing the page for display?

  2. Profile your db queries. What can be done to make them more efficient?

Do this before concluding that a different db will fix your problem.

Now if it turns out that you really have pushed this to the max, then you need to look at your choices. These include:

  1. PostgreSQL (yes, a relational db). One thing this has going for it are much more generally optimized table/index structures (InnoDB specializes in pkey lookups, meaning that other searches are slower).

  2. VoltDB (another Relational DB, but this one is main memory for high-speed oltp and very fast)

  3. You can build a caching layer with a NoSQL db which works alongside your rdbms. This is where you might be able to use MongoDB or CouchDB.

share|improve this answer
1  
And of course check and see if you really need to return the entire data set. Poor practices such as using select * when you should specify only the fields you need (and if you have a join, there is a 100% chance there is a field you don't need.) or returning a million records to a web site at once when no one is going to look through them all are things that can cause problems and are easy fixes. –  HLGEM Feb 20 '13 at 18:18

Couchbase includes a built-in cache as a part of the database instance (that's based on memcached technology), which makes it a great choice for a distributed caching tier, key value use cases or document-driven use cases. Large customers like Orbitz use Couchbase as a distributed caching tier. This page may give you addition information you are looking for. http://www.couchbase.com/memcached

http://www.couchbase.com/presentations/couchbase-at-orbitz

CouchDB is a single server document database. It may make for a good peer-to-peer DB but doesn't scale very well and is disk based.

This page tries to capture the differences between couchbase and couchdb.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.