Adventures in Archiving with MongoDB
Join the DZone community and get the full member experience.
Join For FreeMost databases have them: a small handful of big tables that grow at a substantially faster rate than any of their peers (think tweets, clicks, check-ins, etc). Over time, the sheer size of these tables cause queries against them to slow down, and increase the size and time taken for backups. In some cases, not all of this data needs to be “live” so that it can be randomly accessed forever, and can be archived once some criteria has been met. I like to think of this as Eventually Irrelevant (if I may coin a term).
This post was authored by Brian Ploetz
Many database products support partitioning features, which allow you to arrange data physically based on some criteria (typically date, range, list, or hash-based partitioning). These partitioning features allow you to drop partitions from the live database when appropriate. While MongoDB supports horizontal partitioning via sharding, it does not currently support the notion of dropping shards. Even if it did, the criteria for what documents to drop from a collection would most likely not be based on your shard key, so it’s unlikely you’d want to drop a shard anyways. You’d want to drop data from all shards. Capped Collections and the forthcoming TTL-based Capped Collections feature are not quite what we’re looking for either. Capped Collections have very strict rules (including not being able to shard them), and for both normal and TTL-based Capped Collections, as old documents are aged out they are simply dropped on the floor. What we need is more control over the dropping process, such that we can guarantee that our data has been copied to an archive database/data warehouse before being dropped from the main database.
I set out to find the most efficient way of manually archiving documents from a large collection given the tools currently available in MongoDB. For the purposes of this exercise, let’s assume we have a large collection of ad clicks which are associated with ads. We want to archive clicks which are associated with ads which expired more than 3 months ago.
MapReduce
MongoDB wants you to do bulk operations using MapReduce. Since all of the examples and documentation revolve around aggregation, I suspected I wouldn’t be able to use MapReduce as the backbone of my archiving process. After some experimenting, I found that my intuition was correct. Upon attempting to insert/remove documents within the map or reduce functions, I would quickly run into exception 10293 internal error: locks are not upgradeable errors:
Sun Sep 25 21:22:24 [conn5] update warehouse.clicks query: { _id: ObjectId('4d6bf191db0b4b2b1fad5b65') } exception 10293 internal error: locks are not upgradeable: { "opid" : 4248173, "active" : false, "waitingForLock" : false, "op" : "update", "ns" : "?", "query" : { "_id" : { "$oid" : "4d6bf191db0b4b2b1fad5b65" } }, "client" : "0.0.0.0:0", "desc" : "conn" } 0msSun Sep 25 21:22:24 [conn5] Assertion: 10293:internal error: locks are not upgradeable: { "opid" : 4248174, "active" : false, "waitingForLock" : false, "op" : "update", "ns" : "?", "query" : { "_id" : { "$oid" : "4d6bf198db0b4b2b20ad5b65" } }, "client" : "0.0.0.0:0", "desc" : "conn" }0x10008de9b 0x1002064f7 0x1002c1ec6 0x1002c4f3d 0x1002c5f7a 0x1000a8181 0x100147df4 0x1004dc68e 0x1004efc93 0x1004dc71b 0x1004dcb71 0x10049a078 0x100158efa 0x100377760 0x100389d0c 0x10034c204 0x10034d877 0x100180cc4 0x100184649 0x1002b9e89
0 mongod 0x000000010008de9b _ZN5mongo11msgassertedEiPKc + 315
1 mongod 0x00000001002064f7 _ZN5mongo10MongoMutex19_writeLockedAlreadyEv + 263
2 mongod 0x00000001002c1ec6 _ZN5mongo14receivedUpdateERNS_7MessageERNS_5CurOpE + 886
3 mongod 0x00000001002c4f3d _ZN5mongo16assembleResponseERNS_7MessageERNS_10DbResponseERKNS_8SockAddrE + 5661
4 mongod 0x00000001002c5f7a _ZN5mongo14DBDirectClient3sayERNS_7MessageE + 106
5 mongod 0x00000001000a8181 _ZN5mongo12DBClientBase6updateERKSsNS_5QueryENS_7BSONObjEbb + 273
6 mongod 0x0000000100147df4 _ZN5mongo12mongo_updateEP9JSContextP8JSObjectjPlS4_ + 660
7 mongod 0x00000001004dc68e js_Invoke + 3864
8 mongod 0x00000001004efc93 js_Interpret + 71932
9 mongod 0x00000001004dc71b js_Invoke + 4005
10 mongod 0x00000001004dcb71 js_InternalInvoke + 404
11 mongod 0x000000010049a078 JS_CallFunction + 86
12 mongod 0x0000000100158efa _ZN5mongo7SMScope6invokeEyRKNS_7BSONObjEib + 666
13 mongod 0x0000000100377760 _ZN5mongo2mr8JSMapper3mapERKNS_7BSONObjE + 96
14 mongod 0x0000000100389d0c _ZN5mongo2mr16MapReduceCommand3runERKSsRNS_7BSONObjERSsRNS_14BSONObjBuilderEb + 1740
15 mongod 0x000000010034c204 _ZN5mongo11execCommandEPNS_7CommandERNS_6ClientEiPKcRNS_7BSONObjERNS_14BSONObjBuilderEb + 628
16 mongod 0x000000010034d877 _ZN5mongo12_runCommandsEPKcRNS_7BSONObjERNS_10BufBuilderERNS_14BSONObjBuilderEbi + 2151
17 mongod 0x0000000100180cc4 _ZN5mongo11runCommandsEPKcRNS_7BSONObjERNS_5CurOpERNS_10BufBuilderERNS_14BSONObjBuilderEbi + 52
18 mongod 0x0000000100184649 _ZN5mongo8runQueryERNS_7MessageERNS_12QueryMessageERNS_5CurOpES1_ + 10585
19 mongod 0x00000001002b9e89 _ZN5mongo13receivedQueryERNS_6ClientERNS_10DbResponseERNS_7MessageE + 569
Upon killing the client which kicked off the MapReduce process, theses errors would continue to spin out of control, and I’d have to bounce the server. While MapReduce’s divide and conquer approach is ideal for what we’re trying to accomplish, unless the ability to obtain a write lock within a MapReduce function is supported, it is not a viable option for now.
db.eval()
While a server side script is certainly a viable option for writing an archiving process, it has a fundamental flaw in that it won’t work for sharded collections, which could be a non-starter for some folks.
An example archiving process using db.eval() would look something like this:
archiveClick = function archiveClick(doc) {
if (expiredAdIds.indexOf(doc.ad_id) != -1) {
archivedb = db.getMongo().getDB("archive");
archivedb.clicks.save(doc);
// before we remove the original document, make sure the archive worked
if (archivedb.getLastError() == null) {
db.clicks.remove({_id: doc._id});
} else {
throw "could not archive document with _id " + doc._id;
}
}
}
archiveClicks = function archiveClicks() {
threeMonthsAgo = new Date();
threeMonthsAgo.setMonth(threeMonthsAgo.getMonth()-3);
expiredAdIds = [];
getExpiredAds = function(ad) {expiredAdIds.push(ad._id.str);}
db.ads.find({end_date: {$lte: threeMonthsAgo}}).forEach(getExpiredAds);
db.system.js.save({_id: "expiredAdIds", value: expiredAdIds});
db.clicks.find().forEach(archiveClick);
db.system.js.remove({_id: "expiredAdIds"});
}
db.system.js.save({_id: "archiveClick", value: archiveClick});
db.system.js.save({_id: "archiveClicks", value: archiveClicks});
db.runCommand({$eval: "archiveClicks()", nolock: true});
Note the nolock: true sent to db.eval(). This ensures the mongod isn’t locked for other operations while this runs.
The Holy Grail: Define Custom archive() Functions On Collections
In order for our archiving process to work for sharded collections, we will need to add new functions to collection objects in the shell to perform the archiving. Frankly, this feels a lot more natural than db.eval().
I wanted to support two modes of archiving: immediate archiving, and a mark & sweep approach which allows you to queue documents to be archived at one point in time, and perform the actual archiving at a later time (off-peak hours).
I’ve made these functions available in my mongodb-archive project on GitHub. Download the archive.js file, and use it like so:
load("archive.js");
threeMonthsAgo = new Date();
threeMonthsAgo.setMonth(threeMonthsAgo.getMonth()-3);
expiredAdIds = [];
getExpiredAds = function(ad) {expiredAdIds.push(ad._id.str);}
db.ads.find({end_date: {$lte: threeMonthsAgo}}).forEach(getExpiredAds);
archiveConnection = new Mongo("localhost:27018");
archiveDB = archiveConnection.getDB("archive");
archiveCollection = archiveDB.getCollection("clicks");
for (var i = 0; i < expiredAdIds.length; i++) {
print("archiving clicks for ad_id: " + expiredAdIds[i]);
db.clicks.archive({"ad_id": expiredAdIds[i]}, archiveCollection);
print("");
}
The mark/sweep variant would look like:
db.clicks.queueForArchive({"ad_id": expiredAdIds[i]});
// ....some time later.....
db.clicks.archiveQueued(archiveCollection);
The biggest factors on the performance of the archiving process are really no different than the things that impact the performance of your MongoDB database overall: working set size, indexes, disk speed, and lock contention. The more indexes there are on the source collection, the longer the remove() will take. The more indexes there are on the archive collection, the longer the save() will take. The more of the data that you wish to archive is resident in memory, the faster the process will be.
On my MacBook Pro (2.3GHz i7, 8GB RAM) with a 5400 RPM SATA drive and a clicks collection containing over 11 million documents, I was able to archive ~500 documents per second with the immediate archiving approach. Example output from mongostat when this was running:
Archive DB:
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 1 0 0 3 0 208m 2.85g 15m 3.8 0 0|0 0|0 1k 1k 2 17:13:02
0 0 45 0 0 46 0 208m 2.85g 15m 3.4 0 0|0 0|0 61k 6k 2 17:13:03
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 8 0 0 9 0 208m 2.85g 15m 0.1 0 0|0 0|0 10k 2k 2 17:13:04
0 0 47 0 0 48 0 208m 2.85g 15m 3.4 0 0|0 0|0 66k 7k 2 17:13:05
0 0 0 0 0 1 1 208m 2.85g 15m 0 0 0|0 0|0 62b 1k 2 17:13:06
0 0 18 0 0 19 0 208m 2.85g 15m 2.6 0 0|0 0|0 24k 3k 2 17:13:07
0 0 79 0 0 80 0 208m 2.85g 16m 1.1 0 0|0 0|0 111k 11k 2 17:13:08
0 0 131 0 0 132 0 208m 2.85g 16m 8.8 0 0|0 0|0 181k 17k 2 17:13:09
0 0 216 0 0 217 0 208m 2.85g 16m 3.8 0 0|0 0|0 291k 27k 2 17:13:10
0 0 425 0 0 426 0 208m 2.85g 17m 10.5 0 0|0 0|0 600k 53k 2 17:13:11
0 0 490 0 0 490 0 208m 2.85g 19m 7.3 0 0|0 0|1 658k 61k 2 17:13:12
0 0 630 0 0 632 0 208m 2.85g 20m 11.3 0 0|0 0|0 844k 78k 2 17:13:13
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 818 0 0 818 0 208m 2.85g 22m 13.6 0 0|0 0|0 1m 101k 2 17:13:14
0 0 686 0 0 688 0 208m 2.85g 21m 9.6 0 0|0 0|0 932k 85k 2 17:13:15
0 0 0 0 0 1 0 208m 2.85g 22m 0 0 0|0 0|0 62b 1k 2 17:13:16
0 0 178 0 0 179 0 208m 2.85g 22m 1.2 0 0|0 0|0 252k 23k 2 17:13:17
0 0 974 0 0 975 0 208m 2.85g 23m 12.3 0 0|0 0|0 1m 121k 2 17:13:18
0 0 920 0 0 921 0 208m 2.85g 26m 10.1 0 0|0 0|0 1m 114k 2 17:13:19
0 0 810 0 0 811 0 208m 2.85g 26m 8.1 0 0|0 0|0 1m 100k 2 17:13:20
0 0 612 0 0 613 0 208m 2.85g 27m 8.1 0 0|0 0|0 798k 76k 2 17:13:21
0 0 0 0 0 1 0 208m 2.85g 27m 0 0 0|0 0|0 62b 1k 2 17:13:22
0 0 0 0 0 1 0 208m 2.85g 27m 0 0 0|0 0|0 62b 1k 2 17:13:23
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 30 0 0 31 0 208m 2.85g 27m 1.1 0 0|0 0|0 46k 5k 2 17:13:24
0 0 852 0 0 853 0 208m 2.85g 27m 14.8 0 0|0 0|0 1m 106k 2 17:13:25
0 0 768 0 0 769 0 208m 2.85g 29m 9.5 0 0|0 0|0 1m 95k 2 17:13:26
0 0 867 0 0 868 0 208m 2.85g 32m 13.4 0 0|0 0|0 1m 107k 2 17:13:27
0 0 705 0 0 706 0 208m 2.85g 31m 10.8 0 0|0 0|0 936k 88k 2 17:13:28
0 0 331 0 0 332 0 208m 2.85g 32m 3.9 0 0|0 0|0 446k 42k 2 17:13:29
0 0 0 0 0 1 0 208m 2.85g 32m 0 0 0|0 0|0 62b 1k 2 17:13:30
0 0 551 0 0 552 0 208m 2.85g 32m 6.1 0 0|0 0|0 739k 69k 2 17:13:31
0 0 728 0 0 729 0 208m 2.85g 35m 12.2 0 0|0 0|0 949k 90k 2 17:13:32
0 0 991 0 0 992 0 208m 2.85g 35m 11.7 0 0|0 0|0 1m 123k 2 17:13:33
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 935 0 0 935 0 208m 2.85g 38m 10.9 0 0|0 0|1 1m 116k 2 17:13:34
0 0 431 0 0 433 0 208m 2.85g 39m 5.9 0 0|0 0|0 563k 54k 2 17:13:35
0 0 0 0 0 1 0 208m 2.85g 39m 0 0 0|0 0|0 62b 1k 2 17:13:36
0 0 518 0 0 519 0 208m 2.85g 40m 5 0 0|0 0|0 693k 65k 2 17:13:37
0 0 904 0 0 905 0 208m 2.85g 40m 8.9 0 0|0 0|0 1m 112k 2 17:13:38
0 0 958 0 0 959 0 208m 2.85g 41m 14.2 0 0|0 0|0 1m 119k 2 17:13:39
0 0 899 0 0 900 0 208m 2.85g 44m 11.1 0 0|0 0|0 1m 111k 2 17:13:40
0 0 401 0 0 402 0 208m 2.85g 42m 5.9 0 0|0 0|0 540k 50k 2 17:13:41
0 0 856 0 0 857 0 208m 2.85g 44m 9.4 0 0|0 0|0 1m 106k 2 17:13:42
0 0 232 0 0 233 0 208m 2.85g 45m 2.9 0 0|0 0|0 311k 29k 1 17:13:43
Source DB:
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 2 0 12 1 13 0 56g 115g 2.82g 52.2 0 0|0 0|1 1k 822k 2 17:13:02
0 0 0 42 0 43 0 56g 115g 2.65g 88.1 0 0|0 0|1 5k 4k 2 17:13:03
0 0 0 14 0 15 1 56g 115g 1.88g 101 0 0|0 0|1 1k 2k 2 17:13:04
0 0 0 33 1 35 0 56g 115g 1.89g 53.8 16.6 0|0 1|0 4k 4k 2 17:13:05
0 0 0 0 0 1 0 56g 115g 1.9g 0 0 0|0 1|0 62b 1k 2 17:13:06
0 0 0 34 0 35 0 56g 115g 1.9g 47.3 0 0|0 0|0 4k 4m 2 17:13:07
0 0 0 91 0 92 0 56g 115g 1.89g 86.8 0 0|0 0|0 12k 8k 2 17:13:08
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 149 0 150 0 56g 115g 1.9g 82.5 0 0|0 0|0 20k 13k 2 17:13:09
0 0 0 287 0 287 0 56g 115g 1.9g 74.3 0 0|0 0|1 38k 25k 2 17:13:10
0 0 0 387 0 389 0 56g 115g 1.91g 64.6 0 0|0 0|0 52k 33k 2 17:13:11
0 0 0 580 0 581 0 56g 115g 1.93g 59 0 0|0 0|0 78k 49k 2 17:13:12
0 0 0 685 0 686 0 56g 115g 1.93g 47.9 0 0|0 0|0 93k 58k 2 17:13:13
0 0 0 729 0 730 0 56g 115g 1.95g 42.6 0 0|0 0|0 99k 61k 2 17:13:14
0 0 0 551 1 552 0 56g 115g 1.97g 34.5 0 0|0 1|0 74k 47k 2 17:13:15
0 0 0 0 0 1 0 56g 115g 1.98g 0 0 0|0 1|0 62b 1k 2 17:13:16
0 0 0 368 0 368 0 56g 115g 1.96g 20 0 0|0 0|1 50k 4m 2 17:13:17
0 0 0 1032 0 1034 0 56g 115g 2g 35.9 0 0|0 0|0 140k 87k 2 17:13:18
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 834 0 835 0 56g 115g 2.01g 42.3 0 0|0 0|0 113k 70k 2 17:13:19
0 0 0 922 0 922 0 56g 115g 2.02g 38.9 0 0|0 0|1 125k 77k 2 17:13:20
0 0 0 338 1 340 0 56g 115g 2.03g 13 0 0|0 1|0 46k 29k 2 17:13:21
0 0 0 0 0 1 0 56g 115g 2.04g 0 0 0|0 1|0 62b 1k 2 17:13:22
0 0 0 0 0 1 0 56g 115g 2.04g 0 0 0|0 1|0 62b 1k 2 17:13:23
0 0 0 185 0 186 0 56g 115g 2.02g 24.7 0 0|0 0|0 25k 4m 2 17:13:24
0 0 0 920 0 920 0 56g 115g 2.02g 29.9 0 0|0 0|1 125k 77k 2 17:13:25
0 0 0 741 0 742 0 56g 115g 2.04g 49 0 0|0 0|1 100k 62k 2 17:13:26
0 0 0 886 0 887 0 56g 115g 2.05g 33 0 0|0 0|1 120k 74k 2 17:13:27
0 0 0 683 0 684 0 56g 115g 2.08g 57.3 0 0|0 0|1 92k 58k 2 17:13:28
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 138 1 140 0 56g 115g 2.06g 14.5 0 0|0 1|0 18k 12k 2 17:13:29
0 0 0 12 0 12 0 56g 115g 2.07g 0.2 0 0|0 0|1 1k 4m 2 17:13:30
0 0 0 761 0 763 0 56g 115g 2.08g 46.5 0 0|0 0|0 103k 64k 2 17:13:31
0 0 0 762 0 762 0 56g 115g 2.09g 47 0 0|0 0|1 103k 64k 2 17:13:32
0 0 0 957 0 959 0 56g 115g 2.1g 35.3 0 0|0 0|0 130k 80k 2 17:13:33
0 0 0 905 0 905 0 56g 115g 2.13g 38.5 0 0|1 0|1 123k 76k 2 17:13:34
0 0 0 239 1 241 0 56g 115g 2.12g 14.9 0 0|0 1|0 32k 21k 2 17:13:35
0 0 0 0 0 1 0 56g 115g 2.13g 0 0 0|0 1|0 62b 1k 2 17:13:36
0 0 0 780 0 781 0 56g 115g 2.13g 42.8 0 0|0 0|0 106k 4m 2 17:13:37
0 0 0 805 0 806 0 56g 115g 2.14g 39.8 0 0|0 0|0 109k 68k 2 17:13:38
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 988 0 989 0 56g 115g 2.16g 34.8 0 0|0 0|0 134k 83k 2 17:13:39
0 0 0 953 0 953 0 56g 115g 2.17g 39.9 0 0|0 0|1 129k 80k 2 17:13:40
0 0 0 375 1 376 0 56g 115g 2.18g 13.9 0 0|1 0|1 51k 1m 2 17:13:41
0 0 0 867 0 869 0 56g 115g 2.19g 41.7 0 0|0 0|0 118k 73k 1 17:13:42
Total:
MongoDB shell version: 2.0.1 connecting to: localhost:27017/prodcopy archiving clicks for ad_id: 4d6c12497b90420373000056 archiving documents... archived 19045 documents in 40701ms.
For the queued approach with this same set up, I was able to queue ~1200 documents per second, and the archiving process performed at about the same ~500 documents per second. Example output from mongostat while this was running:
Archive DB:
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 0 0 1 0 208m 2.85g 18m 0 0 0|0 0|0 62b 1k 2 17:16:44
0 0 0 0 0 1 0 208m 2.85g 18m 0 0 0|0 0|0 62b 1k 2 17:16:45
0 0 0 0 0 1 0 208m 2.85g 18m 0 0 0|0 0|0 62b 1k 2 17:16:46
0 0 0 0 0 1 0 208m 2.85g 18m 0 0 0|0 0|0 62b 1k 2 17:16:47
0 0 0 0 0 1 0 208m 2.85g 18m 0 0 0|0 0|0 62b 1k 2 17:16:48
0 0 0 0 0 1 0 208m 2.85g 18m 0 0 0|0 0|0 62b 1k 2 17:16:49
0 0 248 0 0 249 0 208m 2.85g 17m 6 0 0|0 0|0 283k 31k 2 17:16:50
0 0 780 0 0 781 0 208m 2.85g 18m 8.2 0 0|0 0|0 883k 97k 2 17:16:51
0 0 1256 0 0 1257 0 208m 2.85g 22m 13.7 0 0|0 0|0 1m 155k 2 17:16:52
0 0 1084 0 0 1085 0 208m 2.85g 21m 10 0 0|0 0|0 1m 134k 2 17:16:53
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 1108 0 0 1109 0 208m 2.85g 25m 9.8 0 0|0 0|0 1m 137k 2 17:16:54
0 0 1108 0 0 1109 0 208m 2.85g 24m 9.8 0 0|0 0|0 1m 137k 2 17:16:55
0 0 1110 0 0 1111 0 208m 2.85g 27m 11 0 0|0 0|0 1m 137k 2 17:16:56
0 0 1166 0 0 1167 0 208m 2.85g 31m 11.3 0 0|0 0|0 1m 144k 2 17:16:57
0 0 1246 0 0 1247 0 208m 2.85g 31m 10.6 0 0|0 0|0 1m 154k 2 17:16:58
0 0 1094 0 0 1095 0 208m 2.85g 35m 9.6 0 0|0 0|0 1m 135k 2 17:16:59
0 0 957 0 0 958 0 208m 2.85g 33m 9.6 0 0|0 0|0 1m 119k 2 17:17:00
0 0 721 0 0 722 0 464m 3.35g 38m 8.9 0 0|0 0|0 1m 90k 2 17:17:01
0 0 243 0 0 243 0 464m 3.35g 34m 9.7 0 0|0 0|0 445k 31k 2 17:17:02
0 0 921 0 0 923 0 464m 3.35g 37m 30.1 0 0|0 0|0 1m 114k 2 17:17:03
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 668 0 0 669 0 464m 3.35g 38m 9.6 0 0|0 0|0 962k 83k 2 17:17:04
0 0 995 0 0 995 0 464m 3.35g 41m 21.2 0 0|0 0|1 1m 123k 2 17:17:05
0 0 467 0 0 468 0 464m 3.35g 27m 12 0 0|1 0|1 679k 58k 2 17:17:06
0 0 1 0 0 2 1 464m 3.35g 17m 85.5 0 0|1 0|1 1k 1k 2 17:17:07
0 0 337 0 0 338 0 464m 3.35g 19m 36.2 0 0|0 0|1 499k 42k 2 17:17:08
0 0 917 0 0 919 0 464m 3.35g 21m 20.4 0 0|0 0|0 1m 114k 2 17:17:09
0 0 1022 0 0 1022 0 464m 3.35g 25m 22.7 0 0|0 0|1 1m 126k 2 17:17:10
0 0 970 0 0 971 0 464m 3.35g 24m 27.4 0 0|0 0|1 1m 120k 2 17:17:11
0 0 166 0 0 168 0 464m 3.35g 25m 1.2 0 0|0 0|0 251k 21k 2 17:17:12
0 0 862 0 0 863 0 464m 3.35g 28m 23.1 0 0|0 0|0 1m 107k 2 17:17:13
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 888 0 0 889 0 464m 3.35g 28m 28.8 0 0|0 0|0 1m 110k 2 17:17:14
0 0 540 0 0 541 0 464m 3.35g 30m 12.2 0 0|0 0|0 802k 67k 2 17:17:15
0 0 879 0 0 880 0 464m 3.35g 31m 22.3 0 0|0 0|0 1m 109k 2 17:17:16
0 0 473 0 0 474 0 464m 3.35g 36m 17.2 0 0|0 0|0 1m 59k 1 17:17:17
Source DB:
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 0 0 1 0 56g 115g 1.86g 0 0 0|0 0|0 62b 1k 1 17:16:29
0 0 0 0 0 1 0 56g 115g 1.86g 0 0 0|0 0|0 62b 1k 1 17:16:30
1 1 1 0 1 3 0 56g 115g 1.87g 51.1 0 0|0 0|1 475b 697k 2 17:16:31
0 0 0 0 0 1 0 56g 115g 349m 129 0 0|0 0|1 62b 1k 2 17:16:32
0 0 0 0 0 1 0 56g 115g 367m 101 0 0|0 0|1 62b 1k 2 17:16:33
0 0 0 0 0 1 0 56g 115g 384m 73.4 0 0|0 0|1 62b 1k 2 17:16:34
0 0 0 0 0 1 0 56g 115g 428m 112 0 0|0 0|1 62b 1k 2 17:16:35
0 0 0 0 0 1 0 56g 115g 436m 94.8 0 0|0 0|1 62b 1k 2 17:16:36
0 0 0 0 0 1 0 56g 115g 450m 108 0 0|0 0|1 62b 1k 2 17:16:37
0 0 0 0 0 1 0 56g 115g 461m 110 0 0|0 0|1 62b 1k 2 17:16:38
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 0 0 1 0 56g 115g 480m 95.5 0 0|0 0|1 62b 1k 2 17:16:39
0 0 0 0 0 1 0 56g 115g 494m 73.5 0 0|0 0|1 62b 1k 2 17:16:40
0 0 0 0 0 1 0 56g 115g 531m 120 0 0|0 0|1 62b 1k 2 17:16:41
0 0 0 0 0 1 0 56g 115g 541m 108 0 0|0 0|1 62b 1k 2 17:16:42
0 0 0 0 0 1 0 56g 115g 564m 74.6 0 0|0 0|1 62b 1k 2 17:16:43
0 0 0 0 0 1 0 56g 115g 579m 127 0 0|0 0|1 62b 1k 2 17:16:44
0 0 0 0 0 1 0 56g 115g 605m 100 0 0|0 0|1 62b 1k 2 17:16:45
0 0 0 0 0 1 0 56g 115g 631m 98.5 0 0|0 0|1 62b 1k 2 17:16:46
0 0 0 0 0 1 0 56g 115g 657m 89.1 0 0|0 0|1 62b 1k 2 17:16:47
0 0 0 0 0 1 0 56g 115g 662m 87.8 0 0|0 0|1 62b 1k 2 17:16:48
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 0 0 1 0 56g 115g 680m 93.5 0 0|0 0|1 62b 1k 2 17:16:49
0 1 0 530 1 531 0 56g 115g 767m 81.3 0 0|0 0|1 72k 4m 2 17:16:50
0 0 0 884 0 886 0 56g 115g 721m 48 0 0|0 0|0 120k 74k 2 17:16:51
0 0 0 1079 0 1080 0 56g 115g 698m 33.6 0 0|0 0|0 146k 90k 2 17:16:52
0 0 0 1294 0 1294 0 56g 115g 720m 23.8 0 0|1 0|1 175k 108k 2 17:16:53
0 0 0 1118 1 1120 0 56g 115g 740m 31.9 0 0|0 0|0 152k 4m 2 17:16:54
0 0 0 1111 0 1112 0 56g 115g 718m 33.3 0 0|0 0|0 151k 93k 2 17:16:55
0 0 0 1097 0 1098 0 56g 115g 706m 33.1 0 0|0 0|0 149k 92k 2 17:16:56
0 0 0 1097 0 1098 0 56g 115g 701m 33.2 0 0|0 0|0 149k 92k 2 17:16:57
0 0 0 1091 1 1092 0 56g 115g 696m 35.1 0 0|0 0|0 148k 4m 2 17:16:58
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 1149 0 1150 0 56g 115g 718m 21.3 0 0|0 0|0 156k 96k 2 17:16:59
0 0 0 1091 0 1091 0 56g 115g 707m 31.2 0 0|0 0|1 148k 91k 2 17:17:00
0 0 0 468 0 470 0 56g 115g 714m 9.3 0 0|0 0|0 63k 40k 2 17:17:01
0 0 0 332 0 333 0 56g 115g 694m 27.3 0 0|0 0|0 45k 28k 2 17:17:02
0 0 0 1013 1 1014 0 56g 115g 713m 17.8 0 0|0 0|0 137k 4m 2 17:17:03
0 0 0 551 0 552 0 56g 115g 691m 39.9 0 0|0 0|0 74k 47k 2 17:17:04
0 0 0 1140 0 1141 0 56g 115g 709m 19.7 0 0|0 0|0 155k 95k 2 17:17:05
0 0 0 126 0 127 0 56g 115g 709m 2.1 0 0|0 0|0 17k 11k 2 17:17:06
0 0 0 92 0 92 0 56g 115g 710m 1.6 0 0|0 0|1 12k 8k 2 17:17:07
0 0 0 605 1 607 0 56g 115g 700m 29.1 0 0|0 0|0 82k 4m 2 17:17:08
insert query update delete getmore command flushes mapped vsize res locked % idx miss % qr|qw ar|aw netIn netOut conn time
0 0 0 814 0 814 0 56g 115g 699m 17.3 0 0|0 0|1 110k 68k 2 17:17:09
0 0 0 1036 0 1037 0 56g 115g 708m 18.4 0 0|0 0|0 140k 87k 2 17:17:10
0 0 0 820 0 821 0 56g 115g 715m 14.4 0 0|1 0|1 111k 69k 2 17:17:11
0 0 0 298 0 300 0 56g 115g 683m 24.2 0 0|0 0|0 40k 26k 2 17:17:12
0 0 0 907 1 908 0 56g 115g 699m 20.4 0 0|0 0|0 123k 4m 2 17:17:13
0 0 0 946 0 947 0 56g 115g 707m 16.4 0 0|0 0|0 128k 79k 2 17:17:14
0 0 0 374 0 375 0 56g 115g 677m 36.7 0 0|0 0|0 50k 32k 2 17:17:15
0 0 0 905 1 905 0 56g 115g 689m 16.2 0 0|0 0|0 123k 821k 2 17:17:16
0 0 0 259 0 261 0 56g 115g 690m 5.1 0 0|0 0|0 35k 22k 1 17:17:17
0 0 0 0 0 1 0 56g 115g 690m 0 0 0|0 0|0 62b 1k 1 17:17:18 Total:
MongoDB shell version: 2.0.1 connecting to: localhost:27017/prodcopy archiving clicks for ad_id: 4d6c12497b90420373000062 ensuring sparse index on field "arch" marking documents for archive... queued 22227 documents for archive in 19369ms. archiving queued documents to archive.clicks... archived 22227 queued documents to archive.clicks in 26901ms.
Upgrading the hard drive to an SSD, I was able to almost triple the performance of the archiving step, and archive ~1300 documents per second.
Obviously this is all a giant hack, and it would be nice if MongoDB
supported some kind of partitioning feature so we didn’t have to do any
of this manually. But until then, this is the best I could get given the
tools currently available.
Source: http://blog.brianploetz.com/post/12131083486/adventures-in-archiving-with-mongodb
Opinions expressed by DZone contributors are their own.
Comments