Slow batch insert with MongoDB sharding and how I debugged it
Now this title sounds fairly technical and seems to belong in a bug ticket rather than a general blog post. But I want to write about it anyway because it took me a couple of hours to find out and it highlights how immature MongoDB in general is. In the end I give a solution that solves the issue so you can still get performance with sharding+batch insert.
So here we go:
A couple of weeks ago I was hanging out at the #mongodb IRC channel and troubleshooting performance issue with a guy. So he had a beefy 32 GB server with 8 cores but it was taking 20 seconds for him to insert 20000 documents as simple as this:
{ t: "2013-06-23", n: 7, a: 1, v: 0, j: "1234" }
So I wrote a quick script (included below) to try it on my MacBook Pro with SSD, and I was able to get results like this:
20000 documents inserted Took 580ms 34482.75862068966 doc/s
So something must be wrong with his configuration / code, I thought, and I kept telling him to just run my code on his machine.
Turns out it’s due to MongoDB sharding failing with batch inserts
So it turns out performance dropped drastically also for me after I enabled sharding:
20000 documents inserted Took 15701ms 1273.8042162919558 doc/s
The test set up
Here’s what I used to test:
- 3 config servers
- 8 shards, sharding on
{ _id: "hashed" }
- All on localhost
Here is the setup script I used to create 8 shards on my localhost. (By the way, setting up sharding is painful)
The test script
The test script is as simple as possible — just a normal batch insert:
// Quite a lot of orchestration var count0 = db.foos.find().count(); var t0 = Date.now(); var docs = []; for(var i = 0; i < 20000; i++) { docs.push({ t: "2013-06-23", n: 7 * i, a: 1, v: 0, j: "1234" }); } // And actually just these couple of lines are the real action db.foos.insert(docs); db.getLastError(); var t1 = Date.now(); var count1 = db.foos.find().count(); var took = t1 - t0; var count = count1 - count0; var throughput = count / took * 1000; print(count + " documents inserted"); print("Took " + took + "ms"); print(throughput + " doc/s");
How I systematically discovered the problem
By passing the -v option to mongod and doing something like tail -f mongolab/**/*.log
, I saw tons of logs like this:
==> mongolab/sh5/mongo.log <== Sun Aug 18 10:12:23.136 [conn2] run command admin.$cmd { getLastError: 1 } Sun Aug 18 10:12:23.136 [conn2] command admin.$cmd command: { getLastError: 1 } ntoreturn:1 keyUpdates:0 reslen:67 0ms==> mongolab/sh6/mongo.log <== Sun Aug 18 10:12:23.136 [conn2] run command admin.$cmd { getLastError: 1 } Sun Aug 18 10:12:23.136 [conn2] command admin.$cmd command: { getLastError: 1 } ntoreturn:1 keyUpdates:0 reslen:67 0ms
==> mongolab/sh7/mongo.log <== Sun Aug 18 10:12:23.137 [conn2] run command admin.$cmd { getLastError: 1 } Sun Aug 18 10:12:23.137 [conn2] command admin.$cmd command: { getLastError: 1 } ntoreturn:1 keyUpdates:0 reslen:67 0ms
...
So mongos
is splitting up the batch insert into individual inserts and donig it one by one, with a getLastError() accompanying each of them!
How to prove that it’s related to batch insert
I changed my test script to do sequential insert, and it worked out fine (note that this is still slower than non-sharded batch insert):
20000 documents inserted Took 1746ms 11454.75372279496 doc/s
The moral of the lesson is that if you shard — you should benchmark very carefully if you do batch inserts.
So can I shard and still get good batch insert performance?
I figured out a way to still get good batch insert performance by using a numeric (instead of hashed) shard key:
sh.shardCollection("shard_test.foos", {rnd: 1})
- Pre-chunk the collection on
rnd
- On each document, generate the rnd key
db.foos.insert({ rnd: _rand(), t: ...
- Before inserting the documents, sort the doucment array so that mongos will only send N batch inserts, if you have N shards
(The code to do all this is available in GitHub, to avoid flooding this post with code snippets)
So instead of letting mongos calculate and sort the hash keys before sending the inserts, I have to do this all by myself. This is fairly basic and I am totally shocked that it could have been solved just like that.
The last step (sorting) is also required. Apparently mongos is not smart enough to sort the batch insert to optimize its own operation.
Benchmark Summary
Time in ms (lower is better). No-Shard No-Shard (with rnd) Shard { id: "hashed" } Shard { rnd: 1 } Batch insert 640 740 21038 1004 Normal 1404 1468 1573 1790 (sequential) insert
Note that even with the rnd insert is still slower than the non-sharded version. Granted I sharded all on a single machine but this about shows the general non-zero overhead of sharding.