mysql-dynamic-cluster
Galera cluster with implementation of dynamic choose mysql server for queries, caching, hashing it and metrics
Features
-
Pool checks by 2 points:
- Pool status - if pool is valid and ready for work checked by validators
- Pool score - prioritizing pools by load checked by load factors. Less load on the top
- 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
-
Filter pools by criteria:
- Pool status
-
Sort pools by criteria:
- Cluster hashing
- Pool score
- Caching query by Redis
- Realtime metrics for cluster and each database node
- Console logger and AMQP logger
Technologies
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
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:
- 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:
- Name of event
- 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:
- Query string - request to the database
-
Query values - values used in query string. Can be one of this structure:
string | any[] | { [paramName: string]: any }
. Similar to mysql2 query values - 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 | - | |
globalPoolSettings | Configuration for all pools | true | object | - | |
redis | Redis object created using ioredis library | false | Redis object | Default redis object using ioredis library | |
errorRetryCount | How much retry query in different servers if you have error | false | number | - | 2 |
useRedis | Enable cache query using Redis | false | boolean | - | true |
serviceMetrics | Configuration for service metrics | false | object |
{ database: 'swagger_realtime', table: 'Service' } |
|
useClusterHashing | Enable cluster hashing | false | boolean | - | true |
clusterHashing | Cluster hashing configuration | false | object |
{ 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 | ||
useConsoleLogger | Enable console logger in this library | false | boolean | - | true |
redisSettings | Redis configuration | false | object | ||
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 |
[
{ key: 'wsrep_ready', operator: '=', value: 'ON' },
{ key: 'wsrep_local_state_comment', operator: '=', value: 'Synced' },
{ key: 'Threads_running', operator: '<', value: 50 }
] |
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 |
[
{ key: 'Connections', multiplier: 2 },
{ key: 'wsrep_local_recv_queue_avg', multiplier: 10 }
] |
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
Parameter name | Description | Type | Value | Default |
---|---|---|---|---|
log_amqp | Configuration for connection and channel for logging | array of object |
connection - configuration for AMQP connection
AMQP connection
|
[
{
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 | Pattern for AMQP | string | ||
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 |
1 |
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 |
1 |
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