mysql-dynamic-cluster

Galera cluster with implementation of dynamic choose mysql server for queries, caching, hashing it and metrics

Features

1. Pool checks by 2 points:
   1. Pool status - if pool is valid and ready for work checked by validators
   2. Pool score - prioritizing pools by load checked by load factors. Less load on the top
2. Hashing services for query - set to the table pool by service if query was successful with this service. Another query with the same service will be trying process in the pool from table
3. Filter pools by criteria:
   1. Pool status
4. Sort pools by criteria:
   1. Cluster hashing
   2. Pool score
5. Caching query by Redis
6. Realtime metrics for cluster and each database node
7. Console logger and AMQP logger

Technologies

• mysql2
• amqp-logger
• pm2.io metrics
• ioredis
• jest
• typescript

Install

Download project from npm

$ npm i @voicenter-team/mysql-dynamic-cluster

How to use

Configure cluster

This is just main settings what necessary to configure cluster. More detail about user settings here

const cfg = {
    clusterName: 'demo',
    // Configuration for each pool. At least 2 pools are recommended
    hosts: [
        {
            host: "192.168.0.1",
            name: "demo1",
            /**
             * You can reconfigure global parameters for current pool
             */
            queryTimeout: 5000,
            user: "user_current",
            password: "password_current",
            database: "db_name_current"
        },
        {
            /**
             * ID is automatically generated, but if you set the id at least for one pool 
             * then other pools will be generated with a higher id 
             * started from the highest manually set id
             */
            id: 10,
            host: "192.168.0.2"
        }
    ],
    // Configuration for all pools
    defaultPoolSettings: {
        user: "user",
        password: "password",
        database: "db_name"
    },
    logs:{
       level: "info",
        output: "console"
    }
}

Example

cfg - configuration for cluster

const galeraCluster = require('@voicenter-team/mysql-dynamic-cluster');
const cluster = galeraCluster.createPoolCluster(cfg);

async function test() {
    await cluster.connect();

    try {
        const res = await cluster.query(`SHOW GLOBAL STATUS;`);
        console.log(res[0]);
    } catch (e) {
        console.log(e.message);
    }

    await cluster.disconnect();
}

test();

How to run metrics

For metrics, we use pm2 and recommend to have some knowledge about it. You can find documentation here Just for testing library you can skip configuration file part and use existed one

Create configuration for pm2

Create copy of ecosystem.config.js or create new one using it as template.

Run using pm2

$ pm2 start .\ecosystem.config.js

Open metrics

It will show all logs and metrics on the console filtered by running projects in realtime

$ pm2 monit

To see metrics and all information about project just for one time

$ pm2 info [id]

You can check id in the first column using

$ pm2 ls

Connect to pm2 GUI

Create bucket and name it here. Then you can get private and public keys from your dashboard by clicking to the connect button

$ pm2 link [private] [public]

After running the project using pm2, dashboard will automatically update

Params

Exported library params

Functions

createPoolCluster

Creating the cluster and initialize with user settings Params:

1. Cluster configuration to configure library

galeraCluster.createPoolCluster({
   hosts: [
      {
         host: "192.168.0.1",
      }
   ],
   defaultPoolSettings: { 
     user: "admin",
     password: "password_global",
     database: "global_db"
   }
})

connect

Connecting to all database pools passed in user settings

cluster.connect()

disconnect

Disconnecting from all database pools

cluster.disconnect()

on

Connecting to events Params:

1. Name of event
2. Callback when event is emitted

cluster.on('connected', () => {
    console.log("Some stuff after an event emitted")
})

query

Request to the database. Cluster automatically select best database node (pool) for quick result by filter and sort it. Pools configured in user settings Params:

1. Query string - request to the database
2. Query values - values used in query string. Can be one of this structure: string | any[] | { [paramName: string]: any }. Similar to mysql2 query values
3. Query options - configuration only for this request

cluster.query(`SELECT SLEEP(?)`, [100], { redis: true })

Configs

User settings

Settings to configure library

User pool settings - globalPoolSettings Configuration for all pools true object

Global pool settings - redis Redis object created using ioredis library false Redis object

Redis

Service metrics

Cluster hashing

AMQP settings

AMQP settings

Redis settings

Redis settings

Pool settings

General pool settings which inherited by user pool settings and global pool settings

[
    { key: 'wsrep_ready', operator: '=', value: 'ON' },
    { key: 'wsrep_local_state_comment', operator: '=', value: 'Synced' },
    { key: 'Threads_running', operator: '<', value: 50 }
]

[
    { key: 'Connections', multiplier: 2 },
    { key: 'wsrep_local_recv_queue_avg', multiplier: 10 }
]

Global pool settings

Global pool settings is extended version of pool settings using to configure all pools Used in user settings

User pool settings

User pool settings is extended version of pool settings using to configure each pool individually Used in user settings

Redis settings

Configuration for Redis. Cashing the query Used in user settings

AMQP settings

Settings to configure amqp logger. Logging to the console in object format and send to the AMQP server, for example RabbitMQ. All parameters are not required Used in user settings

AMQP connection channel - configuration for AMQP channel

AMQP channel

[
    {
        connection: {
            host: "127.0.0.1",
            port: 5672,
            ssl: false,
            username: "guest",
            password: "guest",
            vhost: "/",
            heartbeat: 5
        },
        channel: {
            directives: "ae",
            exchange_name: "MDC",
            exchange_type: "fanout",
            exchange_durable: true,
            topic: "",
            options: {}
        }
    }
]

AMQP pattern

AMQP pattern

AMQP connection

Configuration for AMQP connection. All parameters are required Used in AMQP settings

AMQP channel

Configuration for AMQP channel. All parameters are required Used in AMQP settings

AMQP pattern

Configuration for AMQP pattern. All parameters are not required Used in AMQP settings

Cluster hashing settings

Configuration for cluster hashing. Cluster hashing set pool with current service on the top if exist in the hashing table. Service set to the table if query was success with this service. All parameters are not required Used in user settings

Service metrics settings

Configuration for service metrics to get correct data about services. Table must contain columns:

• ServiceID
• ServiceName

All parameters are not required Used in user settings

Query options

Reconfigure for current one query only. All parameters are not required. Default parameters are set using pool settings, cluster settings and redis settings Used in each query

Connect to events

connected

The cluster will emit connected event when cluster is completely created.

cluster.on('connected', () => {
    console.log("Cluster completely created");
})

disconnected

The cluster will emit disconnected event when cluster is completely disconnected.

cluster.on('disconnected', () => {
    console.log("Cluster completely disconnected");
})

hashing_created

The cluster will emit hashing_created event when hashing in cluster is completely created and connected.

cluster.on('hashing_created', () => {
    console.log("Cluster hashing completely created");
})

acquire

The pool will emit an acquire event when a connection is acquired from the pool. This is called after all acquiring activity has been performed on the connection, right before the connection is handed to the callback of the acquiring code.

cluster.on('acquire', (connection, poolId) => {
    console.log('Connection %d acquired', connection.threadId, poolId);
})

connection

The pool will emit a connection event when a new connection is made within the pool. If you need to set session variables on the connection before it gets used, you can listen to the connection event.

cluster.on('connection', (connection, poolId) => {
    console.log('New connection made', connection.threadId, poolId);
})

release

The pool will emit a release event when a connection is released back to the pool. This is called after all release activity has been performed on the connection, so the connection will be listed as free at the time of the event.

cluster.on('release', (connection, poolId) => {
    console.log('Connection %d released', connection.threadId, poolId);
})

pool_connected

The pool will emit pool_connected event when pool is completely connected.

cluster.on('pool_connected', (poolId) => {
    console.log("Pool completely created", poolId);
})

pool_disconnected

The pool will emit pool_disconnected event when pool is completely disconnected.

cluster.on('pool_disconnected', (poolId) => {
    console.log("Pool completely disconnected", poolId);
})

Demo

Demo file index.js for how to use the library in demo folder. Build the project to run it

Build

Clone repository

$ git clone https://github.com/VoicenterTeam/mysql-dynamic-cluster.git

Install dependencies

$ npm install

Build the project

$ npm run build

Create .env

Create copy of .env.example and name it .env. Set correct values

Run

To test that all work correctly run the demo file with script:

$ npm run start

Tests

All unit tests in tests folder. Test created using jest library.
To run all tests use script:

$ npm run test