Since I haven’t looked at the source code and don’t know the specific implementation of mongo’s bottom layer, won’t mongo delete the corresponding index record after deleting the document? or Is the B-tree not self-balancing? Otherwise, why would there be slow checks for 30,000 pieces of data?
Hello @xiongyan_zhong ,
Welcome to The MongoDB Community Forums! 
There could be several reason to queries responding slow, most common reasons are resource crunch and in-efficient indexing. Please take a look at Analyze Slow Queries to make sure you are following the best practices for faster query processing. To learn more about your use case, can you please share below details?
- Please run your query with explain in
executionStatsmode (e.g. `db.collection.explain(‘executionStats’).aggregate(…)) and share the output. - Also share db.collection.stats() output.
Regards,
Tarun
Hi, @Tarun_Gaur
explain:
mongos> db.Roles.find({"_id" : ObjectId("5a6c9920991a709c50833b63")}).explain()
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SINGLE_SHARD",
"shards" : [
{
"shardName" : "mongo1",
"connectionString" : "xxx",
"serverInfo" : {
"host" : "xxx",
"port" : 27017,
"version" : "3.2.18",
"gitVersion" : "4c1bae566c0c00f996a2feb16febf84936ecaf6f"
},
"plannerVersion" : 1,
"namespace" : "db101.Roles",
"indexFilterSet" : false,
"parsedQuery" : {
"_id" : {
"$eq" : ObjectId("5a6c9920991a709c50833b63")
}
},
"winningPlan" : {
"stage" : "SHARDING_FILTER",
"inputStage" : {
"stage" : "IDHACK"
}
},
"rejectedPlans" : [ ]
}
]
}
},
"ok" : 1
}
stats:
mongos> db.Roles.stats()
{
"sharded" : true,
"capped" : false,
"ns" : "db101.Roles",
"count" : 41063,
"size" : 17369891292,
"storageSize" : 114935259136,
"totalIndexSize" : 194359296,
"indexSizes" : {
"_id_" : 73748480,
"_id_hashed" : 37478400,
"attr.lv_1" : 8916992,
"attr.lf_1" : 974848,
"attr.nin_1" : 42893312,
"attr.nmi_1" : 30347264
},
"avgObjSize" : 423005.90049436234,
"nindexes" : 6,
"nchunks" : 13679,
"shards" : {
"mongo1" : {
"ns" : "db101.Roles",
"count" : 12803,
"size" : 5369869548,
"avgObjSize" : 419422,
"storageSize" : 36037365760,
"capped" : false,
"wiredTiger" : {
"metadata" : {
"formatVersion" : 1
},
"creationString" : "access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=1),block_allocation=best,block_compressor=snappy,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,encryption
[details="Summary"]
[spoiler]This text will be hidden[/spoiler]
[/details]
=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=4KB,key_format=q,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=32KB,leaf_value_max=64MB,log=(enabled=true),lsm=(auto_throttle=true,bloom=true,bloom_bit_count=16,bloom_config=,bloom_hash_count=8,bloom_oldest=false,chunk_count_limit=0,chunk_max=5GB,chunk_size=10MB,merge_max=15,merge_min=0),memory_page_max=10m,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=false,prefix_compression_min=4,source=,split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,type=file,value_format=u",
"type" : "file",
"uri" : "statistics:table:db101/collection/13330-7644487786444373533",
"LSM" : {
...
},
"block-manager" : {
"allocations requiring file extension" : 707593,
"blocks allocated" : 3041856,
"blocks freed" : 2774511,
"checkpoint size" : 1913065472,
"file allocation unit size" : 4096,
"file bytes available for reuse" : 34124185600,
"file magic number" : 120897,
"file major version number" : 1,
"file size in bytes" : 36037365760,
"minor version number" : 0
},
"btree" : {
"btree checkpoint generation" : 1586318,
"column-store fixed-size leaf pages" : 0,
"column-store internal pages" : 0,
"column-store variable-size RLE encoded values" : 0,
"column-store variable-size deleted values" : 0,
"column-store variable-size leaf pages" : 0,
"fixed-record size" : 0,
"maximum internal page key size" : 368,
"maximum internal page size" : 4096,
"maximum leaf page key size" : 2867,
"maximum leaf page size" : 32768,
"maximum leaf page value size" : 67108864,
"maximum tree depth" : 4,
"number of key/value pairs" : 0,
"overflow pages" : 0,
"pages rewritten by compaction" : 0,
"row-store internal pages" : 0,
"row-store leaf pages" : 0
},
"cache" : {
"bytes currently in the cache" : 122620208,
"bytes read into cache" : NumberLong("1799809388338"),
"bytes written from cache" : NumberLong("1356585890194"),
"checkpoint blocked page eviction" : 5,
"data source pages selected for eviction unable to be evicted" : 272026,
"hazard pointer blocked page eviction" : 1960,
"in-memory page passed criteria to be split" : 33477,
"in-memory page splits" : 16611,
"internal pages evicted" : 423515,
"internal pages split during eviction" : 70,
"leaf pages split during eviction" : 19359,
"modified pages evicted" : 1823107,
"overflow pages read into cache" : 0,
"overflow values cached in memory" : 0,
"page split during eviction deepened the tree" : 1,
"page written requiring lookaside records" : 0,
"pages read into cache" : 3120642,
"pages read into cache requiring lookaside entries" : 0,
"pages requested from the cache" : 16970641,
"pages written from cache" : 2873107,
"pages written requiring in-memory restoration" : 30,
"tracked dirty bytes in the cache" : 4909962,
"unmodified pages evicted" : 1994134
},
"cache_walk" : {
"Average difference between current eviction generation when the page was last considered" : 0,
"Average on-disk page image size seen" : 0,
"Clean pages currently in cache" : 0,
"Current eviction generation" : 0,
"Dirty pages currently in cache" : 0,
"Entries in the root page" : 0,
"Internal pages currently in cache" : 0,
"Leaf pages currently in cache" : 0,
"Maximum difference between current eviction generation when the page was last considered" : 0,
"Maximum page size seen" : 0,
"Minimum on-disk page image size seen" : 0,
"On-disk page image sizes smaller than a single allocation unit" : 0,
"Pages created in memory and never written" : 0,
"Pages currently queued for eviction" : 0,
"Pages that could not be queued for eviction" : 0,
"Refs skipped during cache traversal" : 0,
"Size of the root page" : 0,
"Total number of pages currently in cache" : 0
},
"compression" : {
"compressed pages read" : 2703425,
"compressed pages written" : 1760856,
"page written failed to compress" : 0,
"page written was too small to compress" : 1112245,
"raw compression call failed, additional data available" : 0,
"raw compression call failed, no additional data available" : 0,
"raw compression call succeeded" : 0
},
"cursor" : {
"bulk-loaded cursor-insert calls" : 0,
"create calls" : 208437,
"cursor-insert key and value bytes inserted" : NumberLong("1361073168445"),
"cursor-remove key bytes removed" : 2867883,
"cursor-update value bytes updated" : 0,
"insert calls" : 1765355,
"next calls" : 11847,
"prev calls" : 1,
"remove calls" : 737555,
"reset calls" : 9858337,
"restarted searches" : 25836,
"search calls" : 6567165,
"search near calls" : 759,
"truncate calls" : 0,
"update calls" : 0
},
"reconciliation" : {
"dictionary matches" : 0,
"fast-path pages deleted" : 0,
"internal page key bytes discarded using suffix compression" : 745691,
"internal page multi-block writes" : 86808,
"internal-page overflow keys" : 0,
"leaf page key bytes discarded using prefix compression" : 0,
"leaf page multi-block writes" : 20356,
"leaf-page overflow keys" : 0,
"maximum blocks required for a page" : 2,
"overflow values written" : 0,
"page checksum matches" : 1472652,
"page reconciliation calls" : 2821143,
"page reconciliation calls for eviction" : 170333,
"pages deleted" : 745720
},
"session" : {
"object compaction" : 0,
"open cursor count" : 165
},
"transaction" : {
"update conflicts" : 412
}
},
"nindexes" : 6,
"totalIndexSize" : 56500224,
"indexSizes" : {
"_id_" : 27062272,
"_id_hashed" : 9093120,
"base.level_1" : 2260992,
"base.nickname_1" : 8638464,
"base.number_id_1" : 9129984,
"base.login_flag_1" : 315392
},
"ok" : 1
},
MongoDB v3.2 reached end of life in September 2018, I would recommend you to update to MongoDB v4.2 at-least which will reach end of life in April 2023. The latest releases includes many improvements including performance improvements.
Cloud you please provide me with more information to check if I can replicate the issue and figure out the reason for the same?
-
Please share output of your query with explain in
executionStatsmode (e.g. `db.collection.explain(‘executionStats’).aggregate(…)), this will help us understand the execution plan being used by the queryPlanner and other relevant parameters. -
When you say slow are you talking about
db.Roles.find({"_id" : ObjectId("5a6c9920991a709c50833b63")})query or other queries? How much slowness are you seeing? Have you experienced it just now may be after changing something or are you facing it from the start? -
Full output of your db.collection.stats(), as it appears that the output you posted is truncated.
-
Topology of your cluster and the hardware configuration of a shard?
-
what is the Shard key for the collection in question?
-
Also please provide full output of sh.status().