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.

How to find wireless network frequency

There are situations where we need to find out our wireless network frequency.
Eg:- before purchasing network devices we need to verify whether we are in a 2.4 or 5 GHz network

Usage of iwlist command on a linux box,

iwlist <WIRELESS INTERFACE> scanning | grep -C3 <WIRELES_NETWORK_NAME>

Eg:

 iwlist wlp4s0 scanning | grep -C3 VM6292804
                    Frequency:2.412 GHz (Channel 1)
                    Quality=47/70  Signal level=-63 dBm 
                    Encryption key:on
                    ESSID:"VM6292804"
                    Bit Rates:1 Mb/s; 2 Mb/s; 5.5 Mb/s; 11 Mb/s; 6 Mb/s
                              9 Mb/s; 12 Mb/s; 18 Mb/s
                    Bit Rates:24 Mb/s; 36 Mb/s; 48 Mb/s; 54 Mb/s

Running different MySQL Servers inside a single host

There are many circumstances where running different MySQL server versions required. Eg:- testing

Spinning MySQL docker instance is the easiest way I have found among multiple methods.

sudo docker run --name mysql56 -p 127.0.0.1:3316:3306 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.6

This command will download and spin a MySQL 5.6 container. At the same time it will map the default 3306 port in to the 3316 of the host machine.

How to hide your application name using nginx proxy_pass directive

For the showcase purpose I will be using the store application which resides inside WSO2 API Manager 2.1.0

Following are API Manager specific configs which needs to be done.

1. Set proxyPort attribute for connector configs resides in <AM_HOME>/repository/conf/tomcat/catalina-server.xml file.

        <Connector protocol="org.apache.coyote.http11.Http11NioProtocol"
                   port="9763"
                   redirectPort="9443"
                   proxyPort="80"
                   bindOnInit="false"
                   maxHttpHeaderSize="8192"
         />

        <Connector protocol="org.apache.coyote.http11.Http11NioProtocol"
                   port="9443"
                   proxyPort="443"
                   bindOnInit="false"
                   sslProtocol="TLS"
                   maxHttpHeaderSize="8192"
          />

Note that I have removed some attributes for brevity.

2. Update reverseProxy configuration resides inside <AM_HOME>/repository/deployment/server/jaggeryapps/store/site/conf/site.json

    "reverseProxy" : {
        "enabled" : true, 
        "host" : "localhost",
        "context":"",
    }

After above changes start/restart the AM node.


Now the Nginx configuration,

Make sure to generate and store SSL certificate and the key within /etc/nginx/ssl directory.

For the explanation purpose I will be having two server blocks, which can be consolidated to a one.

server{
    listen 80;
    server_name localhost;
    location / {
            proxy_pass http://localhost:9763/store/;
            proxy_redirect off;
            proxy_set_header Host $host;
            proxy_set_header X-Real-IP      $remote_addr;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            include /etc/nginx/proxy_params;
            proxy_cookie_path ~*^/.* /;
    }
}
server{
    listen 443 ssl;
    ssl_certificate /etc/nginx/ssl/nginx.crt;
    ssl_certificate_key /etc/nginx/ssl/nginx.key;
    server_name localhost;
    location / {
            proxy_pass https://localhost:9443/store/;
            proxy_redirect off;
            proxy_set_header Host $host;
            proxy_set_header X-Real-IP      $remote_addr;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            include /etc/nginx/proxy_params;
            proxy_cookie_path ~*^/.* /;
    }
}

After reloading the newly added config browse https://localhost or https://localhost,

Download a part of YouTube video as mp3

I will be using ffmpeg and youtube-dl installed linux distro (Ubuntu 16.04 LTS)

ffmpeg -ss 00:00:00.0000 -i $(youtube-dl -f 22 --get-url <YOUTUBE_LINK>) -t 00:00:00.0000 -acodec libmp3lame -qscale:a 0 sample.mp3

Fill <YOUTUBE_LINK> with the link to YouTube video.

Find further explanation of above command here,

• -ss : start of the stream 00:00:00.0000 stands for hour:minute:second.millisecond
• -i   : provide the input, here we will be using an output from youtube-dl.
• -t   : length of the stream

Further information about bitrate selection (-qscale:a 0) can be found here.

• libmp3lame is the audio encoding used for this purpose.
• sample.mp3 is the output file of above command.

Troubleshooting Firebird Database

Note:  I will be using the employee database(/opt/firebird/examples/empbuild/employee.fdb) which is available by-default with the Firebird 2.5.7  in this post.

How to connect to a Firebird Database and test (using command-line)

1. Go to /opt/firebird/bin directory.
2. Run ./isql (Command-line tool for Firebird)

udara@Razorx1:/opt/firebird/bin$ ./isql
Use CONNECT or CREATE DATABASE to specify a database
SQL>

3. Run connect "/opt/firebird/examples/empbuild/employee.fdb"

SQL> connect "/opt/firebird/examples/empbuild/employee.fdb"
CON>

4. Provide the user/password to connect, note the semicolon (;) at the end.

CON> user 'SYSDBA' password 'admin';
Database:  "/opt/firebird/examples/empbuild/employee.fdb", User: SYSDBA
SQL>

5. Run the intended query,

SQL> select * from employee;

 EMP_NO FIRST_NAME      LAST_NAME            PHONE_EXT                 HIRE_DATE DEPT_NO JOB_CODE JOB_GRADE JOB_COUNTRY                    SALARY FULL_NAME                            
======= =============== ==================== ========= ========================= ======= ======== ========= =============== ===================== =====================================
      2 Robert          Nelson               250       1988-12-28 00:00:00.0000  600     VP               2 USA                         105900.00 Nelson, Robert                       
      4 Bruce           Young                233       1988-12-28 00:00:00.0000  621     Eng              2 USA                          97500.00 Young, Bruce                         
      5 Kim             Lambert              22        1989-02-06 00:00:00.0000  130     Eng              2 USA                         102750.00 Lambert, Kim                         
      8 Leslie          Johnson              410       1989-04-05 00:00:00.0000  180     Mktg             3 USA                          64635.00 Johnson, Leslie                      
      9 Phil            Forest               229       1989-04-17 00:00:00.0000  622     Mngr             3 USA                          75060.00 Forest, Phil                         
     11 K. J.           Weston               34        1990-01-17 00:00:00.0000  130     SRep             4 USA                          86292.94 Weston, K. J.                        
     12 Terri           Lee                  256       1990-05-01 00:00:00.0000  000     Admin            4 USA                          53793.00 Lee, Terri   

How to enable trace configuration

1. Open /opt/firebird/fbtrace.conf in your favorite text editor and update relevant configuration.

How to trace the database

1. Go to /opt/firebird/bin directory.
2. Run ./fbtracemgr with proper parameter set.

udara@Razorx1:/opt/firebird/bin$ ./fbtracemgr -SE service_mgr -START -NAME fire -CONFIG ../fbtrace.conf -USER SYSDBA -PASS admin

Sample trace output for the select * from employee;query.

2017-02-22T11:58:23.2200 (1980:0x7f85df7ae678) TRACE_INIT
    SESSION_4 fire
   

2017-02-22T11:58:23.2210 (1980:0x7f85df7ae678) PREPARE_STATEMENT
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445
        (TRA_264, READ_COMMITTED | NO_REC_VERSION | WAIT | READ_WRITE)

Statement 128:
-------------------------------------------------------------------------------
select * from employee
      0 ms

2017-02-22T11:58:23.2210 (1980:0x7f85df7ae678) FREE_STATEMENT
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445

Statement 127:
-------------------------------------------------------------------------------
select * from employee

2017-02-22T11:58:23.2220 (1980:0x7f85df7ae678) EXECUTE_STATEMENT_START
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445
        (TRA_263, CONCURRENCY | WAIT | READ_WRITE)

Statement 128:
-------------------------------------------------------------------------------
select * from employee

2017-02-22T11:58:23.2260 (1980:0x7f85df7ae678) CLOSE_CURSOR
    /opt/firebird/examples/empbuild/employee.fdb (ATT_63, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
    /opt/firebird/bin/isql:7445

Statement 128:
-------------------------------------------------------------------------------
select * from employee

Firebird trace utility

Firebird Trace utility.
Usage: fbtracemgr <action> [<parameters>]

Actions:
  -STA[RT]                              Start trace session
  -STO[P]                               Stop trace session
  -SU[SPEND]                            Suspend trace session
  -R[ESUME]                             Resume trace session
  -L[IST]                               List existing trace sessions

Action parameters:
  -N[AME]    <string>                   Session name
  -I[D]      <number>                   Session ID
  -C[ONFIG]  <string>                   Trace configuration file name

Connection parameters:
  -SE[RVICE]  <string>                  Service name
  -U[SER]     <string>                  User name
  -P[ASSWORD] <string>                  Password
  -FE[TCH]    <string>                  Fetch password from file
  -T[RUSTED]  <string>                  Force trusted authentication

Examples:
  fbtracemgr -SE remote_host:service_mgr -USER SYSDBA -PASS masterkey -LIST
  fbtracemgr -SE service_mgr -START -NAME my_trace -CONFIG my_cfg.txt
  fbtracemgr -SE service_mgr -SUSPEND -ID 2
  fbtracemgr -SE service_mgr -RESUME -ID 2
  fbtracemgr -SE service_mgr -STOP -ID 4