Replace MySql Table with NoSQL DB
12 Jun 2011Backgroud:
I am not nosql fans. I don't have the habit to replace whole mysql db with nosql db. But if a mysql table can be replaced with nosql db and expensive queries are generated againest this table, I will give a try of nosql DB.
In our product, we have a unique user login log table to know how many users logined in a specify date. Here is the table structure:
unique_login_logs: _attributes: {phpName: UniqueLoginLog} id: user_id: { type: integer } date: { type: date, index: true } created_at: _uniques: unique_date_type: [user_id, date]
The expensive query looks like:
select date, count(`user_id`) from unique_login_logs group by date order by date desc;'
And the other related operations will be save an entry into this table or check the user entry for a specify date is in the table or not. Nothing more.
Choose NoSQL DB from wiki page:
... Key-value cache in RAM Citrusleaf database memcached Oracle Coherence Redis Tuple space Velocity Key-value stores implementing the Paxos algorithm Keyspace Key-value stores on disk BigTable CDB Citrusleaf database Dynomite Keyspace membase Memcachedb Redis Tokyo Cabinet TreapDB Tuple space MongoDB ...
For my feature request, I need a disk stored key-value system. I just spend 1 minute to take a look the MongoDB features. It meet my feature requirements. That's enough. If you know more about the other nosql DBs, of course you should choose the one which suit yours best.
Try MongoDB
Step 1: Install MongoDB(http://www.mongodb.org/display/DOCS/Quickstart+Unix);
Step 2: Simple demo testing:
create a new db:
use mydb;
Save an entry:
db.uniqueLoginLog.save({date: '2011-06-09', user_id: 101}); db.uniqueLoginLog.save({date: '2011-06-09', user_id: 111}); db.uniqueLoginLog.save({date: '2011-06-09', user_id: 102}); .. db.uniqueLoginLog.save({date: '2011-06-11', user_id: 100}); db.uniqueLoginLog.save({date: '2011-06-11', user_id: 101}); db.uniqueLoginLog.save({date: '2011-06-11', user_id: 102});
Group entries by date:
db.uniqueLoginLog.group( {key: { date:true }, reduce: function(obj,prev) { prev.count ++; }, initial: { count: 0 } });
The output looks like:
[ { "date" : "2011-01-01", "count" : 3 }, { "date" : "2011-01-02", "count" : 3 }, { "date" : "2011-01-03", "count" : 1 }, { "date" : "2011-01-04", "count" : 1 } ]
With the output, I can do the order in client.
Install PHP Mongo Extension
You can install Mongo PHP extension with pecl command line or install it manually
> pecl search mongo Retrieving data...0% Matched packages, channel pecl.php.net: ======================================= Package Stable/(Latest) Local mongo 1.1.4 (stable) MongoDB database driver > pecl install mongo
Get stored items with php function
PHP code:
$m = new Mongo(); $db = $m->mydb; $collection = $db->uniqueLoginLog; $obj = array('date'=> '2011-06-09', 'user_id'=> 101); $collection->insert($obj); $keys = array("date" => 1); $initial = array("count" => 0); $reduce = "function(obj,prev) { prev.count ++; }"; $g = $collection->group($keys, $initial, $reduce); var_dump($g);
Output looks like:
array(4) { ["retval"]=> array(3) { [0]=> array(2) { ["date"]=> string(10) "2011-06-09" ["count"]=> float(17) } [1]=> array(2) { ["date"]=> string(10) "2011-06-11" ["count"]=> float(10) } [2]=> array(2) { ["date"]=> string(10) "2011-06-10" ["count"]=> float(7) } } ["count"]=> float(34) ["keys"]=> int(3) ["ok"]=> float(1) }
Deploy to production?
Until now, the demo is still a toy. If you want to deploy it to production, there are more work need to to be done. Take a look at the admin zone
For my feature implemention, I will consider Replication next step.
Summary:
This is not a toturial about how to use MongoDB. It is just a example that can use nosql db in your production.
Reference: