MongoDB Shell: A Primer

1 . MongoDB Shell: A Primer A brief guide to features of the MongoDB shell Rick Golba Percona Solutions Engineer June 8, 2017 1 © 2017 Percona 

2 .Agenda ▪ Basics of the Shell ▪ Limit and Skip ▪ Sorting ▪ Aggregation Pipeline ▪ Explain Plans ▪ Write Concern ▪ Validation Options ▪ Read Preference 2 © 2017 Percona 

3 .The MongoDB Shell ▪ JavaScript interface to MongoDB ▪ Part of the standard installation of MongoDB ▪ Used to query and update data, and perform administrative operations ▪ Stored by default in /usr/bin • Either access the shell from /usr/bin or add the path to the PATH variable $ PATH=$PATH:/usr/bin 3 © 2017 Percona 

4 .Common MongoDB Shell Options ▪ --port <port> - allows you to specify the port where mongod or mongos is listening; 27017 by default ▪ --host <hostname> - allows you to specify the host where mongod or mongos is running; needed when accessing a remote host ▪ --username <username> - allows you to specify the username to authenticate if the MongoDB database uses authentication ▪ --password <password> - allows you to specify the password for the provided username. If omitted, the shell prompts for the password. ▪ --help - provides information on all of the shell options for MongoDB 4 © 2017 Percona 

5 .MongoDB Limit and Skip ▪ You can limit the number of documents returned by a query > db.<collection>.find().limit(NUMBER) • For queries that would return a large number of documents, but only a sample set is needed ▪ You can also skip a specified number of documents returned by a query > db.<collection>.find().limit(NUMBER).skip(NUMBER) • If a value for limit is not specified, all documents will be initially returned, and then the specified number of documents are skipped 5 © 2017 Percona 

6 .MongoDB Sorting ▪ MongoDB queries do not returned data in a sorted order unless specified > db.<collection>.find().sort({<key1>:1,<key2>:1...}) • 1 specifies an ascending sort, which is the default • -1 specifies a descending sort • Be careful of multiple sort levels slowing down the query process • Queries can be sped up through proper indexing 6 © 2017 Percona 

7 .MongoDB Aggregation Pipeline > db.orders.aggregate([ {$match: {status: "active"}}, {$group: {_id: "$cust_id":, total: {$sum: "$amount"}}} ] ) 7 © 2017 Percona 

8 .MongoDB Aggregation Pipeline - $match > db.orders.aggregate([ {$match: {status: "active"}}, {$group: {_id: "$cust_id":, total: {$sum: "$amount"}}} ] ) 8 © 2017 Percona 

9 .MongoDB Aggregation Pipeline - $group > db.orders.aggregate([ {$match: {status: "active"}}, {$group: {_id: "$cust_id":, total: {$sum: "$amount"}}} ] ) 9 © 2017 Percona 

10 .MongoDB Explain Plan > db.<collection>.explain().<method> ▪ Returns information on the query plan for • aggregate() • count() • distinct() • find() • group() • remove() • update() ▪ Optional <method> allows you to control verbosity of the plan 10 © 2017 Percona 

11 .MongoDB Explain Plan Inclusions > db.orders.explain().aggregate([ {$match: {status:"active"}}, {$group: {_id:"$ cust_id",total: {$sum:"$amount"}}}]) ▪ Passes information through a series of stages to completion • COLLSCAN for collection scan (all documents) • IXSCAN for scan of index keys (subset of documents) • FETCH for retrieval of data • SHARD_MERGE for merging results from multiple shards ▪ Shows information on rejected plans, if available 11 © 2017 Percona 

12 .MongoDB Write Concern ▪ By default, MongoDB only requires write acknowledgement from the master in a replica set ▪ You can override this and specify the number of replicas that must acknowledge prior to the write being processed to the database • This can either be a specified number of nodes or a majority of nodes ▪ To specify write concern 2 on an insert > db.movies.insert( { title: "Logan", year : 2017, director: "James Mangold" }, { writeConcern: { w: 2 } }) ▪ Use {writeConcern: {w: "majority"}} for majority option 12 © 2017 Percona 

13 .Write Concern Examples Write Concern 1 Write Concern 2 (or majority) 13 © 2017 Percona 

14 .validate Command ▪ Checks the structures within a namespace for correctness by scanning the collection’s data and indexes ▪ Returns information regarding the on-disk representation of the collection ▪ Can be slow, particularly on larger data sets • While running, it holds an exclusive lock on the collection • This blocks all reads and writes until the validate command finishes ▪ Output differs based on storage engine > db.collection.validate(<true>) • true specifies a complete validation and will require more time 14 © 2017 Percona 

15 .validate Output Summary ▪ validate.ns - full namespace name of the collection ▪ validate.nrecords - number of documents in the collection ▪ validate.nIndexes - number of indexes on the collection ▪ validate.keysPerIndex - number of keys in each index ▪ validate.valid - returns true if all aspects of the collection are valid ▪ validate.errors - if valid returns false, this field contains a message describing the validation error ▪ validate.ok - displays 1 for successful completion of command, 0 if it fails 15 © 2017 Percona 

16 .MongoDB Read Preference ▪ By default, an application sends its read requests to the primary member of a replica set ▪ You can direct the read request to another member of the replica set • This may return stale data due to write concerns and asynchronous replication ▪ Setting read preference mode is specific to the driver being used 16 © 2017 Percona 

17 .MongoDB Read Preference Options ▪ primary - default, operations read from the current replica set primary ▪ primaryPreferred - operations read from the primary but if it is unavailable, operations can read from secondary members ▪ secondary - operations read from the secondary members of the replica set ▪ secondaryPreferred - operations read from secondary members but if no secondary members are available, operations read from the primary ▪ nearest - operations read from member of the replica set with the least network latency regardless of type ▪ maxStalenessSeconds can be specified for all but primary to determine a time threshold after which the member is not used for reads. Must be >90. 17 © 2017 Percona 

18 .Percona Live Europe Call for Papers & Registration are Open! Championing Open Source Databases ▪ MySQL, MongoDB, Open Source Databases ▪ Time Series Databases, PostgreSQL, RocksDB ▪ Developers, Business/Case Studies, Operations ▪ September 25-27th, 2017 ▪ Radisson Blu Royal Hotel, Dublin, Ireland Submit Your Proposal by July 17th! www.percona.com/live/e17 18 © 2017 Percona 

19 . DATABASE PERFORMANCE Database Performance Matters MATTERS