[SERVER-75647] POC: Index build at end for resharding Created: 04/Apr/23  Updated: 02/May/23  Resolved: 10/Apr/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Task Priority: Major - P3
Reporter: Matthew Russotto Assignee: Matthew Russotto
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File pipeline_garaudy.js     HTML File runtest    
Issue Links:
Related
related to SERVER-74722 Investigate performance for initial b... Closed
Sprint: Repl 2023-04-17
Participants:

 Description   

As part of the faster addShard project, which uses resharding to redistributed the data, we are considering building indexes at the end of the process rather than as we insert the data. This is a POC to determine if we get significant performance gains from that.



 Comments   
Comment by Matthew Russotto [ 25/Apr/23 ]

Correct, but note _id was ObjectId and so matched natural order.

Comment by Geert Bosch [ 21/Apr/23 ]

This was still doing scan+fetch on _id for the cloning phase, right?

Comment by Matthew Russotto [ 10/Apr/23 ]

The 1G document (~1T data) run completed, barely. Setup was the same as the 100M document run except I changed mongo_ebs_size to 1500 (GB).

Existing code: 3221m (53 hr 40 min). The machines were only provisioned for 48 hours so only the luck of imprecision let this complete. The machines were de-provisioned before I could get full logs.

Building all indexes except _id at the end: 908m (15 hr 8 min), on the slower shard 34480 secs (9 hr 34 min 40 sec) spent cloning, 20012 secs (5 hr 33 min 32 sec) spent building indexes.

Building indexes at the end is dramatically faster; nearly linear, in fact.

Comment by Matthew Russotto [ 04/Apr/23 ]

Preliminary results with 100M documents generated by https://github.com/pkdone/mongo-mangler using the pipeline_garaudy.js document. There were 10 secondary indexes plus the original shard index which is a hashed index on _id (_id is an ObjectID), plus a hashed index on 'cardnumber' which was used as the new resharding key.

The 10 secondary indexes were

{pre}
{"name.last": 1, "name.first": 1, "name.middle":1}'
'{"age" : 1}'
'{"race" : 1}'
'{"marital_status": 1}'
'{"legal_status": 1}'
'{"dependent_count": 1}'
'{"income_category": 1}'
'{"home_ownership": 1}'
'{"employment_status": 1}'
'{"employment_industry": 1}'{pre}

Configuration was a 2-shard cluster, r6g.2xlarge shards with 3 nodes each, 1 r6g.xlarge mongos and 1 r6g.xlarge config server.

Existing code (build all indexes while inserting data): 6620 seconds, 6575 spent in cloning

Build all indexes except _id and new shard index at end: 5103 seconds, 3383 spent cloning, 1718 spent building indexes

Build all indexes except _id at end: 4820 seconds, 2891 spent cloning, 1927 spent building indexes

Looks like this is definitely worth it; even having the one extra index at insert time makes a difference. These documents were about 1K apiece so 100M documents is very roughly 100G; I'll do a run with 1G documents (1T data) and the existing and "all indexes" cases.

Generated at Thu Feb 08 06:30:42 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.