0

I wrote an app using Sails.js with mongoDb(sails-mongo).

Firstly, I decided to write all to a single document... And database slowed on 5GB of data.. "Slowed" means that basic find query executed in 30-50s..

Than I rewrite all in an multiple collections and add indexing.. example of my models:

Markets.js

  module.exports = {
      attributes: {
        name: {
          type: 'string',
          index: true
        },
        pairs: {
         collection: 'Exchanges',
         via: 'source',
        }
      }
    };

and Exchanges.js

module.exports = {

  attributes: {
    s1: {
      type: "string"
    },
    source:{
      model: "Maklers",
      index: true
    },
    s2: {
      type: "string"
    },
    p: {
      type: 'float'
    },
    v1: {
      type: 'float'
    },
    v2: {
      type: 'float'
    },
    vb: {
      type: 'float'
    }
  }
};

and example of slow query 

Markets.findOne({
          name: info,
          sort: 'createdAt DESC',
          limit: 1,
          createdAt: {
            '<=': aft
          }
        }).populateAll().exec(function(err, items) {
          callback(err, items);
        });

result of db.stats

> db.stats()
{
    "db" : "stats222",
    "collections" : 8,
    "objects" : 36620661,
    "avgObjSize" : 238.26556139988844,
    "dataSize" : 8725442352,
    "storageSize" : 10033258480,
    "numExtents" : 63,
    "indexes" : 13,
    "indexSize" : 2940024192,
    "fileSize" : 14958985216,
    "nsSizeMB" : 16,
    "extentFreeList" : {
        "num" : 0,
        "totalSize" : 0
    },
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 22
    },
    "ok" : 1
}

What you can advice me? It`s about 2000 of records every minute..

How to increase perfomance? Change db config? Change indexes? Change DB? Change models/collections config?

I using 2-core server with 2GB of Virtual Memory.. Sorry for bad english..

Vasiliy Belov
  • 11
  • 3
  • What are you actually trying to do? It looks like you're mixing up types of queries; e.g. using a limit in a findOne method can't do anything... –  May 25 '16 at 14:27
  • Actualy Limit .. is part of older query where was just Find.. now it`s removed.. – Vasiliy Belov May 25 '16 at 15:23
  • Part of it just might be the limitations of the server your database resides on; ideally you want enough memory to hold your entire database + indexes in memory, so that you're not getting page faults, etc. Part of it will be invoking the .populateAll() method; this is *really* expensive, especially if you have a lot of records returned from the initial .find() step. It could add *thousands* of extra queries and iterations onto your query... –  May 26 '16 at 08:29

1 Answers1

5

There is a drawback in the 0.12 version of Waterline when using mongodb. By default waterline is not case sensitive, and mongodb is!

Your queries are slow, because when searching strings, it is being used a REGEX to find any case, so your indexes are useless. But you can change it, by disabling the case sensitiveness with the wlnex attribute:

someMongodbServer: {
    adapter: 'sails-mongo',
    host: 'mongodb',
    port: 27017,
    user: 'username',
    password: 'password',
    database: 'databaseCoolName',
    wlNext: {
      caseSensitive: true
    }   
},

You can confirm this error by checking on the mongodb logs. And see what are the slow queries.

Gustavo Garcia
  • 1,905
  • 1
  • 15
  • 27
  • Is there any way to have this setting per model instead? We observed the same issue in our sailsjs app, but would like to keep this behaviour for everything else and enforce only case sensitive for a couple models. – schystz Aug 31 '18 at 03:29
  • The default in Sails.js 1.0 is not using case sensitive anymore. This answer is only for 0.12 – Gustavo Garcia Sep 01 '18 at 23:51