Open fboucquez opened 3 years ago
gimre-xymcity
commented
3 years ago a) Is there any reason NOT to add 'transaction.signerPublicKey' to that index? (or recipient?)
b) is there a reason to create index on BOTH id AND amount?
(having such a specific index doesn't make sense in general, so if that is really wanted, it probably should be done as some kind of extension to configuration settings - preferably provided from the outside)
fboucquez
commented
3 years ago Great questions @gimre-xymcity .
I've created the issue from the discussion in discord, to not forget about it. I haven't looked at this particular index in detail yet.
We could add a feature to bootstrap where the user can provide their own mongo index scripts to be executed next to the default ones. Then we can define, if the index is a common case, we can put it to the shared js script, if not, we can advice the user to use the custom preset feature
xembook
commented
2 years ago @fboucquez
if not, we can advice the user to use the custom preset feature
Could you please tell me how I set the createIndex using the custom preset function? Currently, COMSA is generating over 3000 Mosaic and it is possible to intentionally generate a full scan of mongoDB from some REST APIs. If transaction.mosaics.id index is not a common case, I would like to advice the node user to use the custom preset feature.
ryo-opening-line
commented
2 years ago hi, @fboucquez
It would be very helpful if we can add indexes to mongodb by a custom preset.
I observed that some very slow queries issued on our mainnet node(sym-main-01.opening-line.jp) today. I would like to solve this performance problem. So if you could give me some advice I would appreciate it.
Our mongodb profiler shows the following slow queries which took over 1,000 msec.
(1) transactions#transaction.mosaics.id
command: "find" : "transactions", "filter" : { "transaction.mosaics.id" : NumberLong("7585053463727868751") },
time: 10,776 msec : 2022-02-12T03:16:26.734Z 11,637 msec : 2022-02-12T07:08:15.316Z 10,906 msec : 2022-02-12T08:23:59.891Z
(2) transactionStatements#statement.receipts.targetAddress
command: "find" : "transactionStatements", "filter" : { "statement.receipts.targetAddress" : BinData(0,"aIc8NFD5/UQs/i5L6i3fYLWJuJjxZBNs") },
time: 4,097 msec : 2022-02-12T04:10:15.974Z 2,640 msec : 2022-02-12T06:24:07.945Z
(3) transactionStatements#statement.receipts.senderAddress
command: "find" : "transactionStatements", "filter" : { "statement.receipts.senderAddress" : BinData(0,"aIc8NFD5/UQs/i5L6i3fYLWJuJjxZBNs") },
time: 2,677 msec : 2022-02-12T04:10:18.914Z 2,648 msec : 2022-02-12T04:11:45.400Z 2,710 msec : 2022-02-12T05:57:19.293Z 2,648 msec : 2022-02-12T06:05:53.910Z 2,676 msec : 2022-02-12T06:09:13.138Z 2,686 msec : 2022-02-12T06:22:42.269Z 2,634 msec : 2022-02-12T06:24:10.786Z
For reference, the following is the profiler log.
> db.system.profile.find({}, {'millis' : 1, 'command' : 1, 'ts' : 1}).forEach(printjson);
{
"command" : {
"find" : "transactions",
"filter" : {
"transaction.mosaics.id" : NumberLong("7585053463727868751")
},
"sort" : {
"_id" : 1
},
"projection" : {
"meta.addresses" : 0
},
"limit" : 20,
"lsid" : {
"id" : UUID("d02bc5b3-383e-4ef5-b91d-9af8fade97e4")
},
"$db" : "catapult"
},
"millis" : 10776,
"ts" : ISODate("2022-02-12T03:16:26.734Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.targetAddress" : BinData(0,"aIc8NFD5/UQs/i5L6i3fYLWJuJjxZBNs")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("31c9acaa-2898-4f42-9931-77ece9165eb8")
},
"$db" : "catapult"
},
"millis" : 4097,
"ts" : ISODate("2022-02-12T04:10:15.974Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aIc8NFD5/UQs/i5L6i3fYLWJuJjxZBNs")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("31c9acaa-2898-4f42-9931-77ece9165eb8")
},
"$db" : "catapult"
},
"millis" : 2677,
"ts" : ISODate("2022-02-12T04:10:18.914Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aAaen50YU4qlUd4uJZvDncf9RnIKQJ3A")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("ef56d108-df80-4036-8cd3-c33850eca4d9")
},
"$db" : "catapult"
},
"millis" : 2648,
"ts" : ISODate("2022-02-12T04:11:45.400Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aNnLOuXq5Es1DeFRHutQPb9WQs5PlVn1")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("7887bed6-a09a-43be-8f6a-a399818677d3")
},
"$db" : "catapult"
},
"millis" : 2710,
"ts" : ISODate("2022-02-12T05:57:19.293Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aBqMqLsou0y5ZLkdxOIcFDRqNH2iPDb6")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("018b28ae-3c4c-4929-8e36-c4d33281709a")
},
"$db" : "catapult"
},
"millis" : 2648,
"ts" : ISODate("2022-02-12T06:05:53.910Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aJCHKk0tMcRrDAwpXcYuXeEO1rlNjOC+")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("ef56d108-df80-4036-8cd3-c33850eca4d9")
},
"$db" : "catapult"
},
"millis" : 2676,
"ts" : ISODate("2022-02-12T06:09:13.138Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aGE/UJGE3YTDwSSKYf0HaaL18a3+8MAN")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("524d4c8e-9a60-4691-943a-a41e417b6ae9")
},
"$db" : "catapult"
},
"millis" : 2686,
"ts" : ISODate("2022-02-12T06:22:42.269Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.targetAddress" : BinData(0,"aJEKUq9Bq6zcIhp7CpuJz/thBccH4UBP")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("40f52919-7dd6-446f-87f6-7ffc742ce0c7")
},
"$db" : "catapult"
},
"millis" : 2640,
"ts" : ISODate("2022-02-12T06:24:07.945Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aJEKUq9Bq6zcIhp7CpuJz/thBccH4UBP")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("40f52919-7dd6-446f-87f6-7ffc742ce0c7")
},
"$db" : "catapult"
},
"millis" : 2634,
"ts" : ISODate("2022-02-12T06:24:10.786Z")
}
{
"command" : {
"find" : "transactions",
"filter" : {
"transaction.mosaics.id" : NumberLong("8574712413345528100")
},
"sort" : {
"_id" : 1
},
"projection" : {
"meta.addresses" : 0
},
"limit" : 20,
"lsid" : {
"id" : UUID("018b28ae-3c4c-4929-8e36-c4d33281709a")
},
"$db" : "catapult"
},
"millis" : 11637,
"ts" : ISODate("2022-02-12T07:08:15.316Z")
}
{
"command" : {
"find" : "transactions",
"filter" : {
"transaction.mosaics.id" : NumberLong("6480386948338954679")
},
"sort" : {
"_id" : 1
},
"projection" : {
"meta.addresses" : 0
},
"limit" : 20,
"lsid" : {
"id" : UUID("011611cc-5548-47d6-a238-2c8b290d5cea")
},
"$db" : "catapult"
},
"millis" : 10906,
"ts" : ISODate("2022-02-12T08:23:59.891Z")
}
{
"command" : {
"find" : "transactions",
"filter" : {
"transaction.mosaics.id" : NumberLong("6480386948338954679")
},
"sort" : {
"_id" : 1
},
"projection" : {
"meta.addresses" : 0
},
"limit" : 20,
"lsid" : {
"id" : UUID("3ae7809b-fb43-4853-b0c1-04f833db5c62")
},
"$db" : "catapult"
},
"millis" : 11292,
"ts" : ISODate("2022-02-12T09:15:49.365Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aPJGpcobgFHsrJNuUp/tge/T4a8yiHSM")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("6469e38a-3bad-4b25-b694-3a0cb08fafa3")
},
"$db" : "catapult"
},
"millis" : 3065,
"ts" : ISODate("2022-02-12T09:17:46.636Z")
}
{
"command" : {
"find" : "transactionStatements",
"filter" : {
"statement.receipts.senderAddress" : BinData(0,"aMFBIJKQ8m5iHruJIDlPjMAv3+6Ozm6m")
},
"sort" : {
"_id" : -1
},
"projection" : {
},
"limit" : 15,
"lsid" : {
"id" : UUID("3ae7809b-fb43-4853-b0c1-04f833db5c62")
},
"$db" : "catapult"
},
"millis" : 2679,
"ts" : ISODate("2022-02-12T09:27:30.091Z")
}If you need full profile log, please let me know.
Add
To: https://github.com/symbol/symbol-bootstrap/blob/dev/config/docker/mongo/mongoDbPrepare.js
Also, test how long it takes for mainnet/testnet to reindex existing data without re-syncing.
Chat: https://discordapp.com/channels/856325968096133191/879529537975762975/896019016764784691