Update mongoDbPrepare.js for improving the response time #304

[xembook]

add 3 indexs transaction.mosaics.id statement.receipts.targetAddress statement.receipts.senderAddress

[sonarqubecloud[bot]]

Kudos, SonarCloud Quality Gate passed!   

0 Bugs
0 Vulnerabilities
0 Security Hotspots
0 Code Smells

No Coverage information
No Duplication information

[fboucquez]

Hi xem, fyi,

I have released 1.1.5 with another (hot) fix. I haven't tried this one yet as I'm still on holiday.

I'm keen to test the new indexes on already synced (or syncing) mongo db. I want to validate if it is possible without data reset, how much space it takes and how long it takes.

[fboucquez]

Hi @xembook.

I have revisited the PR. The indexes look good, the only problem is they are not applied when restarting the database. This improvement requires a database reset, the user would need to re-sync from scratch.

I'm still looking for a way to run a migration that adds the new indexes to an existing database without a reset on boot.

[xembook]

I appreciate your efforts. can we use db.collection.reIndex ? https://www.mongodb.com/docs/manual/reference/method/db.collection.reIndex/

[fboucquez]

hi @cryptoBeliever , have you tested it? I'm happy to merge as-is knowing that it only applies to new or reset nodes

[cryptoBeliever]

@fboucquez I'm very sorry for the late answer.

I synced on the mainnet two nodes:

1. With new indexes
2. Without new indexes

After full sync db size (check using show dbs when connecting to mongo):

1. With new indexes: Total db size: 8.444GB, index size: 1033293824 = 985MB
2. Without new indexes: Total db size: 8.351GB, index size: 896,12890625=896MB

Total db size increase 1.1% Total index size increase 9.9%

Other stats (db.stats())

1. With indexes

{ "db" : "catapult", "collections" : 20, "views" : 0, "objects" : 13270829, "avgObjSize" : 968.6406171008608, "dataSize" : 12854663992, "storageSize" : 8033198080, "indexes" : 76, "indexSize" : 1033293824, "totalSize" : 9066491904, "scaleFactor" : 1, "fsUsedSize" : 116211380224, "fsTotalSize" : 166382714880, "ok" : 1 }

2. Without indexes

{ "db" : "catapult", "collections" : 20, "views" : 0, "objects" : 13270769, "avgObjSize" : 968.6434641428842, "dataSize" : 12854643656, "storageSize" : 8026615808, "indexes" : 73, "indexSize" : 939659264, "totalSize" : 8966275072, "scaleFactor" : 1, "fsUsedSize" : 116209524736, "fsTotalSize" : 166382714880, "ok" : 1 }

Query speed

Example query: /statements/transaction?targetAddress=NCESRRSDSXQW7LTYWMHZOCXAESNNBNNVXHPB6WY

Without indexes (5 tries):

Total: 7,221517s Total: 6,299108s Total: 6,580552s Total: 6,359186s Total: 5,294720s

With indexes (5 tries)

Total: 0,091067s Total: 0,071982s Total: 0,076726s Total: 0,081498s Total: 0,074835s

Also after update bootstrap and resetData indexes were added :heavy_check_mark:
From my side good to merge :heavy_check_mark: