Code for Concinnity

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.