Hi Everyone,

Logwriter here! How are you?

Usually when you are a Developer/DBA, you probably have a tied control of what queries your application does against the database and you kinda know which indexes will be necessary to support the queries.

But I bet that a lot of us working in the enterprise companies have the Developer/DBA role splitted because of the organization. So, you’re a DBA or you’re a Developer.

When that is the case and you’re the DBA, doesn’t matter how many times you’ve attended to SCRUM meetings with the Developer team, they will create a new feature that is querying data without an index. If this situation has never happened to you:

• You are lucky guy, but there is always a first time for everything in life
• Feel free to stop reading here if you don’t believe that it will never happen with you

It’s a wonderful day, you’re working in your comfy chair and all of a sudden you get a ticket about performance issues in the application forwarded by the application team to you. Of course my friend, the application team will always blame the database (they are supposed to do that, is their mission in life).

Users are complaining about slowness in the app when accessing/filtering cities per population sizes.

How you will find out what is happening?

One approach could be turning on database profiling:

> db.setProfilingLevel(1,10)
{ "was" : 0, "slowms" : 100, "ok" : 1 }

Right after the execution of the above command, you have a new collection in your database known as “system.profile”.

> show collections
system.profile
zips

Any operation that exceeds 10ms will be recorded in the system.profile collection.

note1: let’s assume that 10ms is the ceiling of acceptable latency for my app. Just because I don’t have a humongous database here.

I’m a lucky guy and the issue just happened and was recorded in the system.profile collection. So, analyzing the output:

> db.system.profile.find().pretty()
{
 "op" : "getmore",
 "ns" : "postal.zips",
 "query" : {
 "getMore" : NumberLong("25579730777"),
 "collection" : "zips"
 },
 "originatingCommand" : {
 "find" : "zips",
 "filter" : {
 "pop" : {
 "$gt" : 12000
 }
 }
 },
 "cursorid" : 25579730777,
 "keysExamined" : 0,
 "docsExamined" : 29178,
 "cursorExhausted" : true,
 "numYield" : 228,
 "locks" : {
 "Global" : {
 "acquireCount" : {
 "r" : NumberLong(458)
 }
 },
 "Database" : {
 "acquireCount" : {
 "r" : NumberLong(229)
 }
 },
 "Collection" : {
 "acquireCount" : {
 "r" : NumberLong(229)
 }
 }
 },
 "nreturned" : 6751,
 "responseLength" : 681209,
 "protocol" : "op_command",
 "millis" : 12,
 "planSummary" : "COLLSCAN",
 "execStats" : {
 "stage" : "COLLSCAN",
 "filter" : {
 "pop" : {
 "$gt" : 12000
 }
 },
 "nReturned" : 6852,
 "executionTimeMillisEstimate" : 11,
 "works" : 29469,
 "advanced" : 6852,
 "needTime" : 22616,
 "needYield" : 0,
 "saveState" : 231,
 "restoreState" : 231,
 "isEOF" : 1,
 "invalidates" : 0,
 "direction" : "forward",
 "docsExamined" : 29467
 },
 "ts" : ISODate("2017-04-12T00:39:53.808Z"),
 "client" : "127.0.0.1",
 "appName" : "MongoDB Shell",
 "allUsers" : [ ],
 "user" : ""
}
> 

I highlighted the fields that I believe that are important for this troubleshooting.

Q: Which collection has been reported with an operation that exceeds 
the 10ms threshold?
A: zips.
"ns": "postal.zips"

Q: Which operation has exceeded the 10ms threshold? 
A: getmore.
"op": "getmore"

Q: Which command has been executed?
A: find command, using a filter where the field pop should be greater
than 12000.
"originatingCommand" : {
 "find" : "zips",
 "filter" : {
 "pop" : {
 "$gt" : 12000
 }

Q: How long was the execution of this command?
A: 12ms.
"millis" : 12

Q: Was this query supported by an index?
A: No, it wasn't. The query planner has pointed that this query is a
collection scan. 
"planSummary" : "COLLSCAN"

Q: How many documents were examined and how many were returned?
A: All documents in the collection (29,467) were examined and 
6,852 were returned.
"docsExamined" : 29467
"nReturned" : 6852

Since we have what we were looking for let’s disable the database profiling:

> db.setProfilingLevel(0)
{ "was" : 1, "slowms" : 10, "ok" : 1 }
> db.system.profile.drop()
true
>

OK, there is one treat here: COLLSCAN. How do we avoid a collection scan? Creating an index to support the query.

The application is executing a query filtering on the field “pop”, so let’s create an index to support this query:

> db.zips.createIndex( { "pop": 1 } )
{
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 1,
 "numIndexesAfter" : 2,
 "ok" : 1
}
>

Index has been created, but how can I check if the execution time has been improved by the index?

Let’s create an explainable object and try the same query as the application:

> var execplan = db.zips.explain("executionStats")
> execplan.find( { "pop": { $gt: 12000 } } )
{
 "queryPlanner" : {
 "plannerVersion" : 1,
 "namespace" : "postal.zips",
 "indexFilterSet" : false,
 "parsedQuery" : {
 "pop" : {
 "$gt" : 12000
 }
 },
 "winningPlan" : {
 "stage" : "FETCH",
 "inputStage" : {
 "stage" : "IXSCAN",
 "keyPattern" : {
 "pop" : 1
 },
 "indexName" : "pop_1",
 "isMultiKey" : false,
 "multiKeyPaths" : {
 "pop" : [ ]
 },
 "isUnique" : false,
 "isSparse" : false,
 "isPartial" : false,
 "indexVersion" : 2,
 "direction" : "forward",
 "indexBounds" : {
 "pop" : [
 "(12000.0, inf.0]"
 ]
 }
 }
 },
 "rejectedPlans" : [ ]
 },
 "executionStats" : {
 "executionSuccess" : true,
 "nReturned" : 6852,
 "executionTimeMillis" : 8,
 "totalKeysExamined" : 6852,
 "totalDocsExamined" : 6852,
 "executionStages" : {
 "stage" : "FETCH",
 "nReturned" : 6852,
 "executionTimeMillisEstimate" : 10,
 "works" : 6853,
 "advanced" : 6852,
 "needTime" : 0,
 "needYield" : 0,
 "saveState" : 53,
 "restoreState" : 53,
 "isEOF" : 1,
 "invalidates" : 0,
 "docsExamined" : 6852,
 "alreadyHasObj" : 0,
 "inputStage" : {
 "stage" : "IXSCAN",
 "nReturned" : 6852,
 "executionTimeMillisEstimate" : 10,
 "works" : 6853,
 "advanced" : 6852,
 "needTime" : 0,
 "needYield" : 0,
 "saveState" : 53,
 "restoreState" : 53,
 "isEOF" : 1,
 "invalidates" : 0,
 "keyPattern" : {
 "pop" : 1
 },
 "indexName" : "pop_1",
 "isMultiKey" : false,
 "multiKeyPaths" : {
 "pop" : [ ]
 },
 "isUnique" : false,
 "isSparse" : false,
 "isPartial" : false,
 "indexVersion" : 2,
 "direction" : "forward",
 "indexBounds" : {
 "pop" : [
 "(12000.0, inf.0]"
 ]
 },
 "keysExamined" : 6852,
 "seeks" : 1,
 "dupsTested" : 0,
 "dupsDropped" : 0,
 "seenInvalidated" : 0
 }
 }
 },
 "serverInfo" : {
 "host" : "brbrain",
 "port" : 27017,
 "version" : "3.4.0-rc3",
 "gitVersion" : "7d68067e5a6272bb463acc4e7a6c6a144148039c"
 },
 "ok" : 1
}
> 

Not going on too much detail here, but no more COLLSCANs, now the query is supported by the index ( “stage” : “IXSCAN”).

The number of examined and returned documents are the same, indicating a returned per examined documents rate of 1 which is an excellent thing to have. (“nReturned” : 6852, “totalDocsExamined” : 6852).

The execution time came down to 8ms (“executionTimeMillis” : 8).

Database profiling is an powerful tool that can be used to identify which indexes can be created in your collection to improve execution times.

It’s a simple example, but I believe that you got the idea, right? 🙂

Thanks for reading it and let me know if you have any questions.

See you next post!

Logwriter

Hey All,

Logwriter here! How are you?

Evaluating storage efficiency isn’t an easy task. In my opinion, the hardest part is to choose a dataset that best represents a real system.

For example, I wouldn’t say that measuring storage savings from YCSB (Yahoo Cloud Serving Benchmark tool) dataset is the best thing to do, unless your data set looks like an YCSB data set.

The Data Set

For my testing I’ve decided to use a public data set. I found a public data set from Reddit. It’s a data set with public comments from May and June 2016. It is a single collection with 131,079,747 documents. Figure 1 shows a sample document and its fields.

The data was ingested to MongoDB using the command: mongoimport

MongoDB Cluster Topology

I have a MongoDB cluster made of a single Replica Set. My Replica Set has 3 members: 1 Primary, 1 Secondary and 1 Arbiter.

It means that the Primary has a copy of the database and he handles requests of reads/writes to it.

Secondary has also a copy of the database that is sent by the primary (replication).

Arbiter is a member that helps to elect a new primary in case of the failure of the current primary.

WiredTiger (WT) Compression

WiredTiger (WT) is the default storage engine for MongoDB 3.2. It’s completely different compared to MMAPv1. It delivers document level concurrency and it stores data in a more efficient way then MMAPv1 because it supports compression.

You can choose between two different compression libraries: snappy or zlib.  The default is the snappy lib.

Snappy is a data compression/decompression library written by Google. Further information about it here.

Zlib is a data compression/decompression library written by Jean-loup Gailly and Mark Adler. More info about it here.

All-Flash FAS (AFF) Storage Efficiency

ONTAP 9.0 data-reduction technologies, including inline compression, inline deduplication, and inline data compaction, can provide significant space savings. Savings can be further increased by using NetApp Snapshot® and NetApp FlexClone® technologies.

ONTAP 9.0 has introduced a new storage efficiency technique known as “inline data compaction”. It gets more than 1 logical block and if possible, stores them in a single physical 4KB block.

Let’s say your NetApp AFF running ONTAP 9.0 gets the following write requests:

How these write requests will be handled by inline compression and inline compaction? Take a look on Figure 4.

In this example, after it breaks the requests in WAFL blocks (4KB blocks) it would need 11 blocks to store the data. Applying inline adaptive compression, the number of required blocks to store the data goes down to 8 blocks, and finally after inline data compaction the data is stored in 4 physical disk blocks.

Which Storage Efficiency Technology I Should Use on My Environment?

I hate to answer a question like that with: “It depends.”, but unfortunately this is the real answer. Let me walk you through the process that has made me to conclude that “it depends”.

I’ve done 24 different tests using the Reddit public data set.

MongoDB has a parameter to control the on-disk page max size. The larger the value, the better will be your read performance in a sequential workload. The smaller the value, the better will be your read performance for a random workload. By default, leaf_page_max is set to 32K.

If we turn off all the efficiencies (in MongoDB and ONTAP) the Reddit data set has used 171GB of space.

Keeping leaf_page_max at the default, turning Compression on at MongoDB and turning efficiency off at ONTAP, the Reddit data set has used 88GB.

WiredTiger compression is an on-disk feature, so it maintain compressed data on disk, but it doesn’t maintain compression on memory. So, you spend CPU time to compress the data before to send it to disk and when you need to read the data on disk to populate your cache you need to decompress the block and then make it available on cache.

ONTAP has been built to provide storage efficiency without impact the performance of your database. So, if you want to let ONTAP working on saving space for you, we need to change the leaf_page_max from 32K to 4K.

 Changing that setting, the Reddit data set has used 130GB. It is a little bit less saving than MongoDB, but your application will experience a consistent and predictable low latency.

Please, let me know if you have any questions and see you next post!

Thanks for reading it!

Logwriter

Hey All,

Logwriter here! How are you?

This post became longer than I was expecting, so I’ve decided to change the agenda of this series a little bit. At part two, you will see:

• WiredTiger and Its Write Path
• NetApp Snap Creator Framework
• Creating Your MongoDB Profile and Configuration on Your Snap Creator Server

That said, let’s start it… I hope you enjoy it !

NetApp Snap Creator is the tool you want to use for backup, restore and clone operations with your MongoDB database.

But before to jump into how to use Snap Creator, let’s see how WiredTiger handles write operations.

WiredTiger and Its Write Path

WiredTiger uses a MultiVersion Concurrency Control (MVCC) to handle write transactions. At the beginning of a write operation, WiredTiger provides a point in time version (aka snapshot) of the data that resides on disk. The snapshot is a representation of that data in memory.

When is time to write to disk, WiredTiger writes all the snapshot’s dirty pages into the datafiles in a consistent way. This process is called checkpoint.

By default checkpoints are made every 60 seconds. It can be modified changing the parameter syncPeriodSecs at /etc/mongod.conf.

But checkpoints aren’t enough to guarantee the durability of the data. Between checkpoints MongoDB protects your data using journaling.

A journal record is a representation of the operations that are happening with your data. For example, a document update might result in changes to an index, so WiredTiger creates a single journal record that contains the update operation and the necessary changes to the index.

By default, MongoDB syncs WiredTiger buffers with disks in the following events:

• Every 50 milliseconds;
• If a write operation occurs with write concern of j: true, WiredTiger syncs the journal files immediately;

The journal sync interval can be modified using the option commitIntervalMs, the minimum value is 1 and maximum value is 500.

You’re probably asking yourself “Why must I know about WiredTiger snapshot, checkpoint and journaling?”

You should know about these things to understand what will be in your backup as soon as you create one.

Through the Write Concern(j: true or j:false), MongoDB let’s the application determines the data’s level of importance, or better to say the durability of the data.

So, if your application is sending write operations with j: true, when you take a snapshot for sure all the operations acknowledge by mongod will be in your backup. But if your application is sending write operations with j:false, it isn’t guarantee that all operations acknowledge by mongod will be in your snapshot, because you don’t know if you’re taking a snapshot before or after the WiredTiger buffers being flushed to the journal files.

Note: You might be thinking about db.fsyncLock(), but according to MongoDB it is disruptive to your database. It will make mongod lock everything for write operations and it might affect reads. The connection that has started the lock must keep open to send the unlock, otherwise a hard shutdown (process kill) has to be done on mongod to unlock your instance.

NetApp Snap Creator Framework

Snap Creator is an unified data protection platform for multiple applications, databases and operating systems. It helps NetApp’s customers to address the following challenges:

• Achieve application-consistent data protection
• Standardize and simplify backup, restore and disaster recovery tasks in any environment
• Become cloud ready by reducing the complexity of unique business processes and the development and management of scripts, helping you to make your backup, restore, and disaster recovery tasks more agile.

Let’s take a look on how Snap Creator works. It’s made of two main components: Snap Creator Server (scServer) and Snap Creator Agents (scAgent). Figure 2 gives you a better view how it looks like:

You need a VM or bare metal server to run your scServer. This VM or server needs to be able to reach out to your ONTAP cluster because it will be responsible to send the API calls to backup/restore or clone your application.

The scAgent goes in your application’s server. So, in our case here, the scAgent is installed on my MongoDB primary and secondary servers.

Creating your MongoDB Profile and Configuration on Your Snap Creator Server

To connect to your Snap Creator Server you just need a browser and then you point to your scServer IP on port 8443. The first time you login you will see the screen shown by figure 3.

After click on “OK”, you will be redirected to the profile creation screen shown here by figure 4.

At this point I’m going to create a profile called “production”. Here I’m using the profile as the representation of my MongoDB production environment. After click on “OK” you will redirected to the configuration wizard as shown by figure 5.

You need to give a name to your config. The name of my config is PRD_Real_Time_Analytics_DB.

Snap Creator doesn’t have a specific plug-in for MongoDB, so at this screen you will select “None” as your plug-in type.

After that, you need to inform the name or IP address where your scAgent is installed. In our case here, I’ve installed my scAgent on my MongoDB replica members and I’m pointing to the name of my primary server in the configuration.

Now is time to let your scServer knows how to talk with your ONTAP cluster. At this point you need to inform which protocol do you want to use to connect to your ONTAP cluster/SVM. Here I’m using HTTP on port 80.

Now your scServer knows which protocol will be used to talk to ONTAP, but it still needs the connectivity and authentication information to send commands to it. Here you will inform your SVM/Cluster name or IP address and also username and password to connect to it.

scServer now connects to your SVM/Cluster and it will show a list of volumes (left panel) for that particular object. You need to select the volumes that contains your database and click on the “right arrow” button to move them to the right side as shown by figure 11. Then click on “save”.

After that, Snap Creator shows you the list of volumes that you’ve selected and your credentials for your configuration. Then, click “next”.

Now is time to setup your backup and retention policies. For this example, we are setting up a daily backup policy with retention of 2 days.

Here is the most critical step. As your database is spread across volumes (in my case I have 8 volumes), to backup it properly you need to take a consistency group snapshot. So, at this screen, don’t forget to click on “Consistency Group” check box before to click on “next”.

The next screen is about replication and remote backup, SnapMirror and SnapVault respectively. I’m not using any of these two options on my environment, so just click on “next”.

If you have OnCommand Unified Manager in your environment and wants to let your scServer to send notifications for it, here is where you provide all the connection information to let that happen.

Then a Summary screen and the opportunity to review all your settings before to click on “Finish”.

After click on “Finish” your configuration is created and you are ready to create your first MongoDB backup.

To create your first backup, select your configuration on the left panel. Click on “Actions” button and then “Backup”.

On the next part of this series I will cover restore and cloning operations.

Thanks for reading it and let me know your opinion about this post leaving a comment.

A special thanks for Kevin Adistambha (Technical Service Engineer @ MongoDB). He had answered my questions about fsyncLock() and fsyncUnlock in the mongodb users forum.

See you soon,

Logwriter

References:

WiredTiger Internals

WiredTiger Snapshots and Checkpoints

NetApp Snap Creator Data Sheet