[SERVER-2058] introduce a metric that shows collection scans taking place Created: 03/Nov/10  Updated: 06/Dec/22  Resolved: 26/May/20

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

Type: New Feature Priority: Major - P3
Reporter: Kenny Gorman Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 0
Labels: metrics, neweng, stats
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
duplicates SERVER-40755 Expose statistics which indicate how ... Closed
Assigned Teams:
Query
Participants:

 Description   

If an application scans by index, then serverStatus().indexCounters.btree.accesses shows the counts and activity against the index. However, if your application is performing scans not using an index (perhaps sub-optimally) there is no metric that shows anything. If a metric was exposed then it would be possible to spot poor plans/scans as well as do ratios of scans vs index accesses. Just a typical pattern for DBA's to see what the DB is doing at any given time. Perhaps the metric could be named: serverStatus().collections.scans. This is also good for an application where scans are needed (like the reads are most of the table) to show the DB activity during these periods. It's also a good measure of overall workload of the DB (scans/sec) and helps to differentiate different I/O patterns/performance. For instance how it changes when faults > 0, and also how it changes platform to platform (aka: I can scan more blocks with platform X).



 Comments   
Comment by David Storch [ 26/May/20 ]

This was implemented in development release 4.3.1 under SERVER-40755. The number of tailable and non-tailable collection scans are now reported in the metrics.queryExecutor.collectionScans section of serverStatus output:

MongoDB Enterprise > db.serverStatus().metrics.queryExecutor.collectionScans
{ "nonTailable" : NumberLong(0), "total" : NumberLong(0) }

These stats can also be retrieved on a per-collection basis using the queryExecStats option to the $collStats aggregation stage:

MongoDB Enterprise > db.c.aggregate([{$collStats: {queryExecStats: {}}}])
{ "ns" : "test.c", "host" : "storchbox:27017", "localTime" : ISODate("2020-05-26T19:44:04.850Z"), "queryExecStats" : { "collectionScans" : { "total" : NumberLong(0), "nonTailable" : NumberLong(0) } } }

I'm closing this ticket as a duplicate of SERVER-40755.

Comment by Asya Kamsky [ 07/Dec/17 ]

planSummary: COLLSCAN is now shown for every logged operation that doesn't use an index.

Comment by Kenny Gorman [ 29/Nov/11 ]

Any news on this? This is pretty key when one wants to quantify the workload against a DB.

For example, if you have few actual queries that return lots of data then someone looking at the DB wouldn't have a nice direct measure of the workload the DB is performing. You could look at the network bytes outbound, but it's indirect.

A direct measure would also be important when doing explain plans. For instance, let's say you are trying to decide about going to a covered index or not. you are doing explain plans and comparing the two. There is no way to quantify one being better than the other. If buffers read (a measure of the number of blocks touched) was show in explain() then one could see the material difference between a index only query and having to fetch all the data blocks.

To go further, let's say you want to quantify making changes to keep data more dense. Let's say you want to try to keep documents with

{owner: myid}

in the same data block to reduce I/O. How can one quantify the benefits/differences between compact data vs non-compact data (as seen with .showDiskLoc()) w/o being able to see the number of blocks visited per query?

This is also useful when querying fragmented databases. One could run a explain() on the primary and slave and see that the slave visits less blocks and thus a candidate for failover. Plus if one was re-orging slaves to be more compact they could quantify the benefits of switching to the slave.

Generated at Thu Feb 08 02:58:50 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.