We were enjoying mysql database performance until we had large data in the database. The problem started with millions of data in the tables and increasing rapidly on daily bases.It was taking much more time to retrieve data from those tables.We already had proper indexing on the columns but still it was taking some time(20-25 sec) to run a query. After doing research on google, we found an alternative solution to the RDBMS. Welcome to the word of NoSQL !!!
We looked at the various implementation of NoSQL and found solutions like Membase, Redis, CouchDB, MongoDB and SimpleDB. Since we were already using Amazon infrstructure like EC2 and S3 so we gave a shot to amazon SimpleDB. We are very pleased with the performance of simpledb as query time is below five seconds now.
Here are the instructions to use SimpleDB with Ruby on rails. We have used the simple_db gem to connect to Amazon SimpleDB web service.
How to use simple db with ruby on rails:
1. Install simple_record gem by: gem install simple_record
2. Create connection with simple db and execute sql query.
SimpleRecord.establish_connection('access_key_id','secret_access_key', :connection_mode=>:per_thread)
Company.find(:all,:conditions => ["name = ?", name], :order => "id desc")
3. close simple db connection.It is better to use close_connection in Application controller:
after_filter :close_sdb_connection class ApplicationController < ActionController::Base #TO CLOSE SIMPLE DB CONNNECTION AFTER EVERY REQUEST def close_sdb_connection SimpleRecord.close_connection end
There were few more requirements from client-side to generats reports from the SimpleDB. we told the client about the limitation of SimpleDB but client insist us to use SimpleDB for reporting system. We started working on the reporting and encounter some problems and limitations of SimpleDB.
Here are some major limitations :
1. It doesn’t support group by clause
2. It doesn’t support join of tables
3. It doesn’t support as (alias)
4. It can not have more than 20 comparisons in where clause.
NOTE: We cannot use full fledge ‘in’ query in the simpleDB as every value in array counted as a comparison clause.
Finally we had to revert back to mysql for reporting system.So, the moral of the story is that never use simple db blindly.
Think carefully before implementing, is it suitable or fulfill your requirement.
Here are some points to consider before implementing simple db:
** SimpleDB shifts work out of the database and onto programmers which is why the SimpleDB programming model sucks: it requires a lot more programming to do simple things. I’ll argue however that this is the kind of sulkiness programmers like. Programmers like problems they can solve with more programming. We don’t even care how twisted and inelegant the code is because we can make it work. As long as we can make it work we are happy.
References:
Amazon SimpleDB
SimpleRecord Gem
Top 10 Reasons to Avoid the SimpleDB Hype
