Sharding slows down queries by 70-120%!

[Joerek van Gaalen]

I'm in the middle of testing sharding on MongoDB as my subset of a collections is growing rapidly and looking for a way to speed up the queries. Like the idea of sharding to be able to use multiple threads on a single query to speed it up. Now I found out that when you enable sharding, or actually add a shardkey to a collection, the query response times increased significantly by 70% to 120%, only when the query is done on the mongos instead of mongod. Got consistent results, so I set up a test where maybe you can help me out why it's slower and what I did wrong, or if it's a nasty bug/problem with Mongo sharding. I used version 2.4.3

1. Start an empty configserver
2. Start an empty mongod
3. Start mongos
4. Connect to mongos
5. use testdb
6. add data: for(var i = 0; i < 5000000; i++) { db.testcol.insert({field1: i}); }
7. add index: db.testcol.ensureIndex({field1:1})
8. Test query speed a couple of times: db.testcol.find({field1:{$gte: 0}}).explain(); On my testsystem I got a consistent 4690ms response time
9. enable sharding: sh.enableSharding("testdb.testcol");
10. Test query speed: still 4690ms
11. shard collection: sh.shardCollection("testdb.testcol", {field1:1})

12. Test query speed again a couple of times: Now I'm getting a consistent 10760 ms response time!!!! What happened here? I get exactly the same resultset, but now with a significant higher response time. And everything is localhost.

13. Now directly connect to mongod

14. use testdb
15. test with the same query: db.testcol.find({field1:{$gte: 0}}).explain(); You will see 4690ms response times. So after sharding a collection response times through mongos will be significantly higher. You need 2 machines to get equal response times as before sharding. That shouldn't be the idea of sharding right, or am I missing a point here, other than just spreading data instead of increasing performance?

It seems that it differs VERY much comparing version 2.2.4. Just tried version 2.2.4 and got much better results on the shard (6740 ms over mongos, 4070ms over mongod, still a difference through mongos, but less). Besides that, 2.2.4 showed better response times than 2.4.3 for this extremely simple query / usecase

[Asya Kamsky]

Could you please include the explain() output for both direct and sharded/through mongos?   I assume you ran both of them a few of times to get a consistent measure?

The explain() through mongos shows the explain from the individual shard - I'm curious to see how that compares to direct on-the-shard explain().

Asya

[Joerek van Gaalen]

Thanks for the reply. Ofcourse I tried them a multiple times to ensure the query is coming from memory, so got very consistent results. Here are the explains after a few warmups:

This is through mongos:

mongos> db.testcol2.find({field1: {$gte: 0}},{field1:1,_id:0}).explain();

{

        "clusteredType" : "ParallelSort",

        "shards" : {

                "192.168.11.250:27020" : [

                        {

                                "cursor" : "BtreeCursor field1_1",

                                "isMultiKey" : false,

                                "n" : 5000000,

                                "nscannedObjects" : 5000000,

                                "nscanned" : 5000000,

                                "nscannedObjectsAllPlans" : 5000000,

                                "nscannedAllPlans" : 5000000,

                                "scanAndOrder" : false,

                                "indexOnly" : true,

                                "nYields" : 10,

                                "nChunkSkips" : 0,

                                "millis" : 10706,

                                "indexBounds" : {

                                        "field1" : [

                                                [

                                                        0,

                                                        1.7976931348623157e+308

                                                ]

                                        ]

                                },

                                "server" : "jvangaalen-PC:27020"

                        }

                ]

        },

        "cursor" : "BtreeCursor field1_1",

        "n" : 5000000,

        "nChunkSkips" : 0,

        "nYields" : 10,

        "nscanned" : 5000000,

        "nscannedAllPlans" : 5000000,

        "nscannedObjects" : 5000000,

        "nscannedObjectsAllPlans" : 5000000,

        "millisShardTotal" : 10706,

        "millisShardAvg" : 10706,

        "numQueries" : 1,

        "numShards" : 1,

        "indexBounds" : {

                "field1" : [

                        [

                                0,

                                1.7976931348623157e+308

                        ]

                ]

        },

        "millis" : 10708

}

Directly on mongod (same machine, same set):

> db.testcol2.find({field1: {$gte: 0}},{field1:1,_id:0}).explain();

{

        "cursor" : "BtreeCursor field1_1",

        "isMultiKey" : false,

        "n" : 5000000,

        "nscannedObjects" : 0,

        "nscanned" : 5000000,

        "nscannedObjectsAllPlans" : 0,

        "nscannedAllPlans" : 5000000,

        "scanAndOrder" : false,

        "indexOnly" : true,

        "nYields" : 6,

        "nChunkSkips" : 0,

        "millis" : 4629,

        "indexBounds" : {

                "field1" : [

                        [

                                0,

                                1.7976931348623157e+308

                        ]

                ]

        },

        "server" : "jvangaalen-PC:27020"

}

I guess the unexpected thing here is, that is is an indexonly query, but still has all documents as nscanneddocuments on the shard. I would expect 0 here. I've read somewhere that on a shard it has the check the document to ensure it is in the proper chunk. Anyway, another very unexpected result is when I do an indexonly false query on the mongod directly, the response times got even significantly better, even when it has to scan every document:

> db.testcol2.find({field1: {$gte: 0}},{field1:1,_id:1}).explain();

{

        "cursor" : "BtreeCursor field1_1",

        "isMultiKey" : false,

        "n" : 5000000,

        "nscannedObjects" : 5000000,

        "nscanned" : 5000000,

        "nscannedObjectsAllPlans" : 5000000,

        "nscannedAllPlans" : 5000000,

        "scanAndOrder" : false,

        "indexOnly" : false,

        "nYields" : 3,

        "nChunkSkips" : 0,

        "millis" : 3669,

        "indexBounds" : {

                "field1" : [

                        [

                                0,

                                1.7976931348623157e+308

                        ]

                ]

        },

        "server" : "jvangaalen-PC:27020"

}

Response time down from 4629 to 3669. More than 20% difference, as I would expect the opposite,

Perhaps this enlighten things

[Joerek van Gaalen]

I've also done some 'profiling' on CPU usage during the queries and it's obvious to see that during the query from mongos it uses up to twice as much cpu compared to running the query directly on the shard

Op zondag 19 mei 2013 19:50:55 UTC+2 schreef Joerek van Gaalen het volgende:

[Joerek van Gaalen]

No one with some kind of explanation? I think this is critical as MongoDB says about itself it is a high performance database, and this case proof it has some low hanging fruit in terms of performance improvements...

[sam]

Well first of all it doesn't look like you configured your config server correctly.  Below it looks like you just started it, must have an entry for the shard.  Second, mongo is built to use multiple shards to help scale queries.  In the below example you have 1 shard, you need to create another shard and add it to the cluster and then you can take advantage of mongo.  You also need to pick a shard key that is going to be relevant to your data and be able to use it when you query.  The truse power comes from using the shard key in your query(this way mongos know where to execute the query automatically)  without that each shard will perform the query. 

[Joerek van Gaalen]

Sam,

Thanks for your reply. I've tried it on my database with real data and then I found out that querying on data that exists on only one shard was significantly slower (70%+) compared to the original setup: no sharding. That why I setup the example og 5 million records and query everything (just to get response times up and make differences comparable). Of course I know for sharding it makes no sense to have just one, this is just an easy showcase that it just makes the query slower. We want to shard our data to make queries go faster, because it can compute on different machines at the same time (big data). Currently we are running against a single threaded aggregation framework on one system which isn't optimal. So I'm starting to look into sharding. If I shard the data over 2 machines, the response times are cut in half. But then I end up with the response times I came from. That shouldn't be the idea. I need 4 equal machines to double the speed.

If you look at the explains, you can also see it is examining each document (nscanneddocuments: 5000000), which isn't the case on a direct query. Perhaps that is causing the much higher response times.

So what are you're suggestions of my wrong mongo configuration?

Op dinsdag 21 mei 2013 21:30:12 UTC+2 schreef sam het volgende: