mongoDB Atlas optimisation - Part 1

If you are on mongoDB Atlas you might have familiar with alert like this(if alerts are on for this category)

Official documentation says,

“Scanned objects/returned” is similar, except it’s about the number of documents scanned versus the number returned. A large number is a sign that you may need an index on the fields you are querying on. This metric is available for MongoDB 2.6 and newer.

How to over come this?

Within this alert, you can find a link to the Profiler and if you are lucky there is a high chance you can identify the query which triggers above alert here itself. But as you can see in the screenshot below of the Profiler ui,

By default, only operations that took longer than 100ms will be shown.

So if you have a query triggering above alert but not taking 100ms to complete that operation will not be available in the Profile UI. Let's see how we can overcome this.

Since this Profiler tab actually analyzes the MongoDB log rather than the actual database profiler we can lower the threshold(default 100ms) via mongo shell. This will leave the database profiler disabled but lower the threshold of what's logged to the MongoDB log from 100 milliseconds.

1. Let's create a new Database user, which we can use to connect via mongo shell (Can reuse if you have an admin user, but recommended approach would be to create a temporary user considering security)

2. Whitelist your IP Address (if you have enabled access from any IP Address no need to follow this step)

3. Then using the mongo shell you can connect.

mongo "mongodb+srv://<URL>/<DB>" --username <USER>

Note: Connection command can be easily locate if you goto CONNECT>Connect with the mongo shell for your DB cluster

After connecting,

1. db.getProfilingStatus() should give you the current profiling status.
2. To lower the threshold you can execute,

  db.setProfilingLevel(0, { slowms: 50 }) 

This will lower the threshold from 100ms to 50ms but will keep profiling disabled as denoted by 0.
You can keep this setting for 5-6 hours during the peak usage, so profiler UI should be able to gather as much as information. With this information you can identify indexing updates you can do to get rid of above alert.

Note: we can run this multiple rounds lowering the slowms eg:- 25, 15, 10 to further analyse

Once you are done with all improvements make sure to set it back at default 100ms as this can add more load to the system.

Google Add-on framework support for long-running process or polling support

Assume we have a GSuite Add-on,

a. Invoking a long-running process

b. Invoking an async process which we need to check status periodically

Both the above won't be directly supported within the Apps Script framework for GSuite Add-ons, given (a) script framework imposed limitations. 

Eg:- Script execution timeout, Trigger timeout

(b) limitation of embedding <script> tags within Add-on UI

These limitations are imposed to keep Add-ons responsive as well as secure by the framework. But if you are publishing your Apps Script project as a web app(not from the manifest as an Add-on) there are built-in APIs to poll as well as we can include our own custom scripts. Discussion related to this can be found here. 

Back to the topic, 

Overcome the script execution timeout,

If your AppsScript project is on the legacy Rhino runtime based framework upgrade it to use the latest V8 based framework.

With this you will gain approximately +30 seconds of more execution time, which now will be 60 seconds opposed to the 30 seconds earlier.

But if this is still not enough for your requirement, and you require to poll on the status periodically there is not soo sleek workaround to get over it.

pre-requisites:
You need to host a separate app(on a different service provider) to do the heavy lifting, the actual task completion can go beyond 2 seconds and also polling logic can be defined here itself.

As an example, I have the following snippet running to mock the actual long-running task which takes 5 minutes to respond

Then I have the following page hosted which is used by the AppsScript code, and this is invoking the above service(mock long running task) and respond back after 5 minutes.

Note: Make sure to replace <NGROK_URL> with the URL to the above service, at the same time, you can combine these two and house above timeout function within here itself.

To invoke this long-running service I'm to use `setOpenLink` class resides within the `CardService` provided by the AppsScript F/W.


There are a couple of things which we need to focus here during the creation of the `htmlUrl`
eg:- getRedirectURI(), generateNewStateToken, asyncCallback

getRedirectURI() is a simple function within the AppsScript which returns the redirect URI which we have to call once our long-running task is done.

function getRedirectURI() {
    return "https://script.google.com/macros/d/" + ScriptApp.getScriptId() + "/usercallback";
}

generateNewStateToken, returns a state token which can be used along with our callback.

function generateNewStateToken(callbackName, user_info) {
  return ScriptApp.newStateToken()
    .withMethod(callbackName)
    .withArgument("user_info", JSON.stringify(user_info))
    .withTimeout(3600)
    .createToken();
}

asyncCallback, is the implementation of our callback function, here success is a html file created within the AppsScript Project which indicates our long running process is completed.

function asyncCallback(data) {
    Logger.log("Inside asyncCallback: " + JSON.stringify(data));
    return HtmlService.createHtmlOutputFromFile("success");
}

Outcome: When you open the Add-on you will get the link button, which invokes the long-running task which is hosted on a different service.

This should open a new browser-tab/popup based on your `setOpenAs` config and once the long-running process is completed this should automatically get updated and closed. Based on your `setOnClose` configuration Add-on UI might refresh too.

Note: This is the not soo sleek part which I have mentioned earlier in this post.

Github link to the full AppsScript source will be published soon.