Best practice to handle lots of connection to multiple databases

[StarpTech]

Hi, what is the best approach to connect with multiple databases? e.g if you want to build a multi-tenancy architecture which is database-based.

Should I use ODatabase to initialiaze a connection new ODatabase({ name: <tenant>, ... }) Shoud I use db.use(<tenant>) or what is the recommend way? Can give me an example of how to use the connection pool with multiple databses?

[StarpTech]

@luigidellaquila @maggiolo00 anybody?

[wolf4ood]

hi @StarpTech

with new ODatabase({ name: <tenant>, ... }) you should open and close every time the socket. That could be not efficient. You could leave the db open but you should handle your own multiple pool

I think the best way is to use db.use(<tenant>) and configure it with connection pool. like this.

var OrientJS = require('orientjs'),
var server = OrientJS({ host: 'localhost', port: 2424,  pool : { max : 10} }),
var DB = server.use({ name: 'GratefulDeadConcerts', username: 'admin', password: 'admin' });

so the connections will be shared between databases.

[janjilek]

Is the connection pool stable now? Few month ago, it was not working properly.

[StarpTech]

Hi @maggiolo00 thanks. Is this the same approach? You dont have to forget that I have to change my db in every request depending on the tenant.

1 - Initialize a pool per tenant

createPool(<tenant>) {

        if(this.pools[<tenant>]) {
            return this.pools[tenant];
        }

        const pool = new OrientDB.Pool({
            host: this.options.host,
            port: this.options.host.port,
            username: this.options.username,
            password: this.options.password,
            name: <tenant>,
            max: 5,
            debug: console.log.bind(console)
        });

        this.pools[<tenant>] = pool;

        return pool;

    }

2 - Tenant request is incoming...

a) acquire new pool or get existing one
b) get DB

a) Make request
b) After request release the db.

DB Pool per tenant would guarantee that tenants does not block each other.

[StarpTech]

@janjilek I did some tests untill now I could not found any issues.

[wolf4ood]

hi @StarpTech

that would work, but if you have a lot of tenant you will open too many connections 5 for each tenant.

[StarpTech]

@maggiolo00 yes that correct so we can say if you have many tenants you can create one pool per tenant but if you have lots of tenants it is better to create one pool for all tenants?

[StarpTech]

@maggiolo00 Do you know any numbers how many connection can orientdb handle?

[StarpTech]

@maggiolo00 in you solution how I can release the db? It would great to document this.

[wolf4ood]

hi @StarpTech

yes i think the best way is you have a pool for all tenants. But OrientDB.Pool you cannot now. Let me think about it for handling a lot of tenant.

How many tenant you will have more/less

By default it should be 1000

http://orientdb.com/docs/2.1/Configuration.html#network-maxconcurrentsessions

[StarpTech]

@maggiolo00 around 20 but high traffic. What do you mean with "But OrientDB.Pool cannot now" ?

Can you give me an example how to aquire and release a db with your approach?

[tglman]

In orientdb we limit the number of connection to 1000, this is configurable with this https://orientdb.com/docs/last/Configuration.html#network-maxconcurrentsessions configuration, this can be increased but be aware over this level probably you will start to see some overhead on context switching and as well you could go to hit the file descriptor limit of your OS.

regards.

[tglman]

In orientdb we limit the number of connection to 1000, this is configurable with this https://orientdb.com/docs/last/Configuration.html#network-maxconcurrentsessions configuration, this can be increased but be aware over this level probably you will start to see some overhead on context switching and as well you could go to hit the file descriptor limit of your OS.

regards.

[wolf4ood]

hi @StarpTech

it means that the pool is binded to one databases. You could create a pool for each database Let me check if i we can use some solution JWT.

[StarpTech]

Hi @maggiolo00 create a pool per database was my first approach. I can't switch my database with .use() when I use the pool?

[wolf4ood]

@StarpTech i meant you could use the new OrientDB.Pool but it is for 1 db only. You should create in this case 1 Pool for db. Let me see if i can set up an example project for the multiple database with JWT.

[StarpTech]

Im waiting for your solution thanks @maggiolo00

[wolf4ood]

hi @StarpTech

here an example of a route with express. You could use ODatabase for each database in multitenant

var express = require('express');
var router = express.Router();
var ODatabase = require('orientjs').ODatabase;

var multitenant = {}

/* GET users listing. */
router.get('/:db/:query', function(req, res) {
    var db = multitenant[req.params.db] || initDb(req.params.db);
    db.query(req.params.query)
    .then(function(data){
        res.send(data);
    })
    .catch(function(err){
    res.status(500).send(err);
    });
});



function initDb(name){
    var db = new ODatabase({
        host: 'localhost', 
        port: 2424, 
        username : 'admin', 
        password : 'admin', 
        name : name
        );
    multitenant[name] = db;
    return db;
}
module.exports = router;

Then if you want to use multiple connection for each db you can activate the connection pool and the JTW in order to scale with multiple connection stateless.

var db = new ODatabase({
        host: 'localhost', 
        port: 2424, 
        username : 'admin', 
        password : 'admin', 
        name : name,
        useToken : true,
        pool : {
            max : 10
        }});

[wolf4ood]

in 2.1.x you should activate also the JWT server side

in orientdb-server-config.xml

<handler class="com.orientechnologies.orient.server.token.OrientTokenHandler">
            <parameters>
                <parameter value="true" name="enabled"/>
                <parameter value="aGVsbG8gd29ybGQgdGhpcyBpcyBteSBzZWNyZXQgc2VjcmV0" name="oAuth2Key"/>
                <parameter value="3600000" name="sessionLength"/>
                <parameter value="HmacSHA256" name="encryptionAlgorithm"/>
            </parameters>
        </handler>

[StarpTech]

@maggiolo00 thanks! I will try it.

[StarpTech]

@maggiolo00 Can I use the db directly or I have to call aquire when I use the pool?

[StarpTech]

Hi @maggiolo00 I can't find any acquire in ODatabase. OrientJs.Pool based on ODatabase and provide aquire and releaseDb interface. Perhaps you were wrong?

[wolf4ood]

hi @StarpTech you can use directly the db.

Yes the OrientJs.Pool is a pool of ODatabase instances that you can manually acquire/release

But you can also use ODatabase with the n socket connection. So you can use 1 db with n connections. But that will scale only with token. There is no need for acquire with this configuration 1 db instance + pool of socket.

[StarpTech]

@maggiolo00 thanks. Do you know how this scales (1DB + n socket connections) ?

[seeden]

I am using 1 db with n connections in my livia-orientdb driver instead of OrientJs.Pool

[StarpTech]

@seeden Do you use it in production with high traffic?

[seeden]

What is high traffic? It was working fine on page with 10 000 daily visitors. I had a problem with orientdb queries (all CPUs on 100%)

[StarpTech]

@seeden thanks with high traffic I mean no development load. @maggiolo00 We could add this to the documentation also the fact about the OrientTokenHandler I cannot find in the docs.

[seeden]

@StarpTech I am using it in production few months. I created same driver like mongoose because I was not sure if I will stay with orientdb

[StarpTech]

@seeden Im interesting which queries did you fire. But this is little bit off topic.