CODEWITHSUNDEEP
mongodbnosql
bbbonthemoon
bbbonthemoon

Reputation: 1778

MongoDB extremely slow comparing to MySQL with 10M records

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"
    }
]

Upvotes: 2

Views: 3123

Answers (4)

Tilo
Tilo

Reputation: 33732

  • do you have all indexes set up correctly? including compound indexes?

  • and are the collections actually being indexed?

    e.g. Collectionname.indexes ; Collectionname.create_indexes

  • can you shard your data or use multiple slaves to spread the load?

  • how much RAM do you have in the system?

Upvotes: 0

xeraa
xeraa

Reputation: 10859

If you are querying on two elements like in your example SELECT var2, var4, var5, var6 from table where var7=xxx and var6=yyy, use a compound index on var7 and var6.

If you have a fixed structure and use the same schema as in a relational database, I'd doubt that you will be able to gain much. But you might be able to make it worse ;-)

Upvotes: 1

vsuhov
vsuhov

Reputation: 85

Well considering that many huge web project are stick with the mysql like Facebook and so on. SO you shouldn't do that as long as the new DB has been tested regarding to your needs. What i soggiest you to do is get the latest backup of your DB and move back to mysql and then adapt the memcached system to your DB, it does handle the big amount of traffic.

But of course you have not specified the type of your project whether its a Web or some application. MongoDB much slower then mysql.
Give us move details and we would be able to help your and give some more info.

Upvotes: 0

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230336

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.

Upvotes: 5

Related Questions