I've got fairly high loaded project, running on MySQL with around 10M records, getting capped with approx 500 requests per second. The data is pretty unique, and cache hit rate is around 3% only. Every row got about 10 fields, 2 of which indexed. 99% of my queries use the two index fields for requests.
I decided to try a NoSQL, and MongoDB was no brainer. Moving data was pretty easy, with simple custom made script. Database schema remained exactly the same, I replicated the same two indexes fields, which were still accountable for 90% of requests. Then I decided to give it a try and was quite shocked: MongoDB was very, VERY slow answering to the queries. Response rate varied from 5 to 10 requests per second, comparing to 500 with mysql.
Any ideas why is this happening? Is it normal? Should I expect MongoDb to outperform Mysql in this particular case(10M records, lots of unique requests with low cache hit rate)? I'm feeling like I'm missing a point.
UPDATE with some specs
Server I was testing with is quad core xeon with 4GB ram
MySQL Table is(field names renamed):
CREATE TABLE `table` (
`recordid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`var1` varchar(200) DEFAULT NULL,
`var2` char(32) DEFAULT NULL,
`var3` bigint(20) unsigned DEFAULT NULL,
`var4` smallint(5) unsigned DEFAULT NULL,
`var5` datetime DEFAULT NULL,
`var6` int(10) unsigned NOT NULL,
`var7` int(10) unsigned NOT NULL,
`var8` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`recordid`),
UNIQUE KEY `recordid_UNIQUE` (`recordid`),
KEY `keyvar7` (`var7`),
KEY `keyvar6` (`var6`)
Typical query is:
SELECT var2, var4, var5, var6 from table where var7=xxx and var6=yyy
I hand verified that MongoDB properly replicated the same indexes, by comparing queries using indexed and non-indexed fields.
UPDATE2 MongoDB .getIndexes() reply
> db.table.getIndexes();
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "table.table",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"var6" : 1
},
"ns" : "table.table",
"name" : "var6_1"
},
{
"v" : 1,
"key" : {
"var7" : 1
},
"ns" : "table.table",
"name" : "var7_1"
}
]
解决方案
MongoDB is not a magic query accelerator. Your site won't sustain 10x load just because you switch to mongo.
Judging from your numbers, I suspect that there was resource saturation taking place. MySQL can certainly do a lot more than 500 QPS.
Do you know what was your bottleneck? I'd wager that you have much less RAM than needed, data has to be fetched from disk and disk gets saturated. At this point, no DB tech will help you, unless you get more iron (or delete some data).
As for the poorer performance of mongo, it's hard to tell without the specifics.