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

Default AMQP Logger settings you can find 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
             */
            // port: 4608,
            // 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
    globalPoolSettings: {
        user: "user",
        password: "password",
        database: "db_name",
        validators: [
            { key: 'wsrep_ready', operator: '=', value: 'ON' },
            { key: 'wsrep_local_state_comment', operator: '=', value: 'Synced' },
            { key: 'available_connection_count', operator: '>', value: 50 }
        ],
        loadFactors: [
            { key: 'Connections', multiplier: 2 },
            { key: 'wsrep_local_recv_queue_avg', multiplier: 10 }
        ],
    },
    redis: new RedisLib(),
    useAmqpLogger: true,
    // amqpLoggerSettings: amqpSettings, // default amqp settings
    logLevel: LOGLEVEL.FULL
}

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

Name	Description	Type	Values	Default
connected	Status of cluster	boolean	true - cluster completely created false - not connected yet or had some errors	false
LOGLEVEL	Console log levels	Enum	QUIET - only warning and errors REGULAR - all information instead debug FULL - all log information	REGULAR

Functions

createPoolCluster

Creating the cluster and initialize with user settings
Params:

1. Cluster configuration to configure library. Example configuration here

galeraCluster.createPoolCluster(cfg)

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

Parameter name	Description	Required	Type	Values	Default
clusterName	Cluster name like global prefix for all names used in the library	true	string	-	-
hosts	Configuration for each pool	true	Array of object	User pool settings	-
globalPoolSettings	Configuration for all pools	true	object	Global pool settings	-
redis	Redis object created using ioredis library. If Object passed then Redis is enabled	false	Redis object	Redis	Don't have default Redis object (null)
useRedis	Enable cache query using Redis. To enable this key pass Redis object using key redis	false	boolean	-	true
errorRetryCount	How much retry query in different servers if you have error	false	number	-	2
serviceMetrics	Configuration for service metrics	false	object	Service metrics	{ database: 'swagger_realtime', table: 'Service' }
useClusterHashing	Enable cluster hashing	false	boolean	-	true
clusterHashing	Cluster hashing configuration	false	object	Cluster hashing	{ nextCheckTime: 5000, dbName: "mysql_dynamic_cluster" }
showMetricKeys	Show metric keys instead metric names	false	boolean	-	false
useAmqpLogger	Enable AMQP logger	false	boolean	-	true
amqpLoggerSettings	AMQP logger configuration	false	object	AMQP settings	AMQP settings
useConsoleLogger	Enable console logger in this library	false	boolean	-	true
redisSettings	Redis configuration	false	object	Redis settings	Redis settings
logLevel	Console logging level	false	enum	QUIET - only warning and errors REGULAR - all information instead debug FULL - all log information	QUIET

Pool settings

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

Parameter name	Description	Required	Type	Values	Default
port	Port to connect to database	false	number	-	3306
connectionLimit	Connection limit in 1 database	false	number	-	100
queryTimeout	Timeout for query	false	number	-	120 000
validators	Validator params to check if pool is valid (pool status) and ready for work	false	Array of objects	key - name (variable_name) of mysql global status operator - operator to check with value. Exist: `=`, `<`, `>`, `Like`. For text only `=` or `Like` operator. `Like` is not strict equal check. value - value what must be to complete pool check	[]
loadFactors	Load factor params to count pool score by load. Using to sort pools	false	Array of objects	key - name (variable_name) of mysql global status multiplier - multiplies the result to achieve the corresponding pool score	[]
timerCheckRange	Time range for next check pool status and pool score	false	Object	start - min time end - max time	{ start: 5000, end: 15000 }
timerCheckMultiplier	Multiplier to increase time if check finish correctly and decrease it if had error in check. Time used for next check pool status and pool score	false	number	-	1.3
slowQueryTime	Logs query time that will consider slow in ms	false	number	-	1
redisFactor	Multiplier for set expire time in Redis	false	number	-	100

Global pool settings

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

Parameter name	Description	Required	Type	Default
user	Username to connect to database	true	string	-
password	Password to connect to database	true	string	-
database	Default database name to connect	true	string	-

User pool settings

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

Parameter name	Description	Required	Type	Default
host	Host to database	true	string	-
id	ID for pool	false	number	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
name	Custom name for pool	false	string	Automatically generated from host and port
user	Username to connect to database	false	string	Set in global pool settings
password	Password to connect to database	false	string	Set in global pool settings
database	Default database name to connect	false	string	Set in global pool settings
redisExpire	Expire time for data in redis	false	number	Redis settings expire

Redis settings

Configuration for Redis. Cashing the query
Used in user settings

Parameter name	Description	Required	Type	Value	Default
keyPrefix	Prefix for all keys	false	string	-	mdc:
expire	Expire for stored data	false	number	-	1 000 000
expiryMode	Expire mode	false	string	-	EX
algorithm	Algorithm for hashing	false	string	-	md5
encoding	Encoding for hashing	false	BinaryToTextEncoding	base64 hex	base64
clearOnStart	Clear all data on library start	false	boolean	-	false

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

Default:

const amqpSettings = {
    log_amqp: [
        {
            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: {}
            }
        }
    ],
    pattern: {
        DateTime: "",
        Title: "",
        Message: "",
        LoggerSpecificData: "localhost",
        LogSpecificData: "ThisLogType"
    },
    log_lvl: 1,
    self_log_lvl: -1
}

Parameter name	Description	Type	Value
log_amqp	Configuration for connection and channel for logging	array of object	connection - configuration for AMQP connection AMQP connection channel - configuration for AMQP channel AMQP channel
pattern	Pattern for AMQP	object	AMQP pattern
log_lvl	Logging level send to AMQP	number	Number means allow logging from a level number to a lower number 0 - error 1 - warning 2 - info 3 - debug 4 - trace
self_log_lvl	Logging level to console from AMQP library	number	Number means allow logging from a level number to a lower number 0 - error 1 - warning 2 - info 3 - debug 4 - trace

AMQP connection

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

Parameter name	Description	Type
host	Host to connect to AMQP	string
port	Port to hosted AMQP	number
ssl	If AMQP host use ssl	boolean
username	Username to connect to AMQP	string
password	Password to connect to AMQP	string
vhost	Vhost for AMQP	string
heartbeat	Heartbeat rate for AMQP	number

AMQP channel

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

Parameter name	Description	Type
directives	Directives AMQP	string
exchange_name	Exchange name AMQP	string
exchange_type	Exchange type AMQP	string
exchange_durable	Exchange durable AMQP	boolean
topic	Topic AMQP	string
options	Options for AMQP channel	object

AMQP pattern

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

Parameter name	Description	Type	Default
DateTime	Date time AMQP	string	" "
Title	Title AMQP	string	" "
Message	Message AMQP	string	" "
LoggerSpecificData	Logger specific data AMQP	string	"localhost"
LogSpecificData	Log specific data AMQP	string	"ThisLogType"

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

Parameter name	Description	Type	Default
nextCheckTime	Next check time in database	number	5000
dbName	Database name for hashing	string	"mysql_dynamic_cluster"

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

Parameter name	Description	Type	Default
database	Database name where stored information about services	string	"swagger_realtime"
table	Table name where stored all service ids	string	"Service"

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

Parameter name	Description	Type
timeout	Timeout of waiting query request	number
database	Database name where query should run	string
serviceName	Service name to add this query to service metrics. By this name will find service id from table configured in service metrics. Don't use it, if passed serviceId	string
serviceId	Service id to add this query to service metrics. Don't use it, if passed serviceName	number
maxRetry	How much retry query in different servers if you have error	number
redis	Use redis for current query or not	boolean
redisFactor	Multiplier for set expire time in Redis for current query	number
redisExpire	Expire time for data in redis for current query	number

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