MemSQL is a distributed system. Because of this, all machines running MemSQL should be monitored to ensure smooth operation. Cluster status can be visualized through MemSQL Ops but it is also possible to programmatically query MemSQL nodes to get the status of the cluster. This section shows how to programmatically monitor a MemSQL cluster.
Monitor all MemSQL nodes
Similar to heartbeats sent by intra-cluster communication, all MemSQL nodes should be pinged with:
select 1;
It is recommended to do this every minute.
Monitor OS resources
If you are using third party monitoring tools, make sure to monitor the following resources within each machine of the MemSQL cluster:
- CPU Usage
- CPU Load Average
- Memory Usage
- Memory Paging (page ins, page outs)
- Disk Utilization
- Disk Queue Time
- Network Usage
- Dropped packets / TCP retransmits
Paging refers to a technique that Linux and other operating systems use to deal with high memory usage. If your system is consistently paging, you should add more memory capacity or you will experience severely performance degradation.
When the operating system predicts that it will require more memory than it has physically available, it will move infrequently accessed pages of memory out of RAM and onto the disk to make room for more frequently accessed memory. When this memory is used later by a process, the process must wait for the page to be read off disk and into RAM. If memory used by MemSQL is moved to disk, the latency of queries that access that memory will be substantially increased.
You can measure paging on the command line by using the Linux tool by running the command vmstat 1 and looking at the swap section (si and so refer to paging memory off the disk and into RAM and out of RAM and onto disk, respectively)
Monitor cluster status through MV_CLUSTER_STATUS table
To know the status of the databases on your cluster, as well as information about the nodes in your cluster, query the information_schema.MV_CLUSTER_STATUS table from an aggregator. You can also access this table through SHOW CLUSTER STATUS; however, querying the table provides the advantage of being able to join against it.
Table description
| Field | Data Type (Size) | Description | Example Value |
|---|---|---|---|
| NODE_ID | bigint(10) | ID of node | 1 |
| HOST | varchar(512) | Host of the node | 127.0.0.1 |
| PORT | bigint(10) | The port of the node | 10000 |
| DATABASE_NAME | varchar(512) | Name of database | vigilantia_0_AUTO_SLAVE |
| ROLE | varchar(512) | Database’s role (e.g. orphan, master, slave, reference) | master |
| STATE | varchar(256) | Database state | replicating |
| POSITION | varchar(256) | Position in transaction log | 0:8832 |
| MASTER_HOST | varchar(256) | Host of this node’s aggregator | 127.0.0.1 |
| MASTER_PORT | bigint(10) | Port of this node’s aggregator | 127.0.0.1 |
| METADATA_MASTER_NODE_ID | bigint(10) | Master’s node ID expected by metadata | 1 |
| METADATA_MASTER_HOST | varchar(256) | Master’s host expected by metadata | 127.0.0.1 |
| METADATA_MASTER_PORT | bigint(10) | Master’s port expected by metadata | 3306 |
| METADATA_ROLE | varchar(512) | Database’s role based on metadata | Orphan |
| DETAILS | varchar(512) | Extra details | stage: packet wait, state: x_streaming, err: no |
When querying from the master aggregator, the value for any “Master” fields (e.g. MASTER_PORT, METADATA_MASTER_HOST, etc.) will be NULL.
Sample output
SELECT * FROM information_schema.MV_CLUSTER_STATUS;
+---------+-----------+-------+-------------------------+-------------+-------------+----------+-------------+-------------+-------------------------+----------------------+----------------------+---------------+-------------------------------------------------+
| NODE_ID | HOST | PORT | DATABASE_NAME | ROLE | STATE | POSITION | MASTER_HOST | MASTER_PORT | METADATA_MASTER_NODE_ID | METADATA_MASTER_HOST | METADATA_MASTER_PORT | METADATA_ROLE | DETAILS |
+---------+-----------+-------+-------------------------+-------------+-------------+----------+-------------+-------------+-------------------------+----------------------+----------------------+---------------+-------------------------------------------------+
| 1 | 127.0.0.1 | 10000 | cluster | master | online | 0:46 | NULL | NULL | NULL | NULL | NULL | Reference | |
| 1 | 127.0.0.1 | 10000 | monitoring | master | online | 0:8832 | NULL | NULL | NULL | NULL | NULL | Reference | |
| 1 | 127.0.0.1 | 10000 | vigilantia | master | online | 0:24616 | NULL | NULL | NULL | NULL | NULL | Reference | |
| 3 | 127.0.0.1 | 10001 | cluster | async slave | replicating | 0:45 | 127.0.0.1 | 10000 | 1 | 127.0.0.1 | 10000 | Reference | stage: packet wait, state: x_streaming, err: no |
| 3 | 127.0.0.1 | 10001 | monitoring | sync slave | replicating | 0:8832 | 127.0.0.1 | 10000 | 1 | 127.0.0.1 | 10000 | Reference | |
| 3 | 127.0.0.1 | 10001 | monitoring_0 | master | online | 0:58893 | NULL | NULL | NULL | NULL | NULL | Master | |
| 3 | 127.0.0.1 | 10001 | monitoring_0_AUTO_SLAVE | async slave | replicating | 0:58893 | 127.0.0.1 | 10001 | NULL | NULL | NULL | Orphan | |
| 3 | 127.0.0.1 | 10001 | monitoring_1 | master | online | 0:57439 | NULL | NULL | NULL | NULL | NULL | Master | |
| 3 | 127.0.0.1 | 10001 | monitoring_1_AUTO_SLAVE | async slave | replicating | 0:57439 | 127.0.0.1 | 10001 | NULL | NULL | NULL | Orphan | |
| 3 | 127.0.0.1 | 10001 | monitoring_2 | master | online | 0:49952 | NULL | NULL | NULL | NULL | NULL | Master | |
| 3 | 127.0.0.1 | 10001 | monitoring_2_AUTO_SLAVE | async slave | replicating | 0:49952 | 127.0.0.1 | 10001 | NULL | NULL | NULL | Orphan | |
| 3 | 127.0.0.1 | 10001 | vigilantia | sync slave | replicating | 0:24616 | 127.0.0.1 | 10000 | 1 | 127.0.0.1 | 10000 | Reference | |
| 3 | 127.0.0.1 | 10001 | vigilantia_0 | master | online | 0:25874 | NULL | NULL | NULL | NULL | NULL | Master | |
| 3 | 127.0.0.1 | 10001 | vigilantia_0_AUTO_SLAVE | async slave | replicating | 0:25874 | 127.0.0.1 | 10001 | NULL | NULL | NULL | Orphan | |
| 3 | 127.0.0.1 | 10001 | vigilantia_1 | master | online | 0:25874 | NULL | NULL | NULL | NULL | NULL | Master | |
| 3 | 127.0.0.1 | 10001 | vigilantia_1_AUTO_SLAVE | async slave | replicating | 0:25874 | 127.0.0.1 | 10001 | NULL | NULL | NULL | Orphan | |
| 3 | 127.0.0.1 | 10001 | vigilantia_2 | master | online | 0:25874 | NULL | NULL | NULL | NULL | NULL | Master | |
| 3 | 127.0.0.1 | 10001 | vigilantia_2_AUTO_SLAVE | async slave | replicating | 0:25874 | 127.0.0.1 | 10001 | NULL | NULL | NULL | Orphan | |
+---------+-----------+-------+-------------------------+-------------+-------------+----------+-------------+-------------+-------------------------+----------------------+----------------------+---------------+-------------------------------------------------+
Monitor cluster events through MV_EVENTS table
As another facet in monitoring the health of your cluster, the information_schema.MV_EVENTS table provides cluster-level event reporting that you can query against. Querying the information_schema.MV_EVENTS table provides events from the entire cluster and can only be done from an aggregator. To monitor events from individual leaves, connect to that leaf and query the information_schema.LMV_EVENTS table, which is exactly the same in structure.
Table description
| Field | Data Type (Size) | Description | Example Value |
|---|---|---|---|
| ORIGIN_NODE_ID | bigint(4) | ID of node where the event happened. | 3 |
| EVENT_TIME | timestamp | Timestamp when event occurred. | 2018-04-25 18:08:13 |
| SEVERITY | varchar(512) | Severity of the event. Can be one of the following values: NOTICE, WARNING, or ERROR. |
NOTICE |
| EVENT_TYPE | varchar(512) | Type of event that occurred. See the section below for more details. | NODE_ONLINE |
| DETAILS | varchar(512) | Additional information about the event, in JSON format. | {“node”:“172.18.0.2:3306”} |
Event type definitions
Node events
| Event type | Description |
|---|---|
NODE_ONLINE |
A node has come online |
NODE_OFFLINE |
A node has gone offline |
NODE_ATTACHING |
A node is in the process of attaching |
NODE_DETACHED |
A node has become detached |
Details output
| Variable | Value | Description |
|---|---|---|
node |
“Hostname:port” | Address of node |
Rebalance events
| Event type | Description |
|---|---|
REBALANCE_STARTED |
A partition rebalance has started |
REBALANCE_FINISHED |
A partition rebalance has ended |
Details output
| Variable | Value | Description |
|---|---|---|
Database |
“database_name or (null)” | Database being rebalanced (80 characters truncated) |
user_initiated |
“true/false” | If the rebalance was initiated by the user or cluster |
Replication events
| Event type | Description |
|---|---|
DATABASE_REPLICATION_START |
A database has started replication |
DATABASE_REPLICATION_STOP |
A database has stopped or paused replication |
Details output
| Variable | Value | Description |
|---|---|---|
local_database |
“local_database_name” | The name of the database being replicated to |
remote_database |
“remote_database_name” | The name of the database being replicated from |
Network status events
| Event type | Description |
|---|---|
NODE_UNREACHABLE |
A node is unreachable from the master aggregator, either starting the grace period or going offline |
NODE_REACHABLE |
A node is now reachable from the master aggregator, recovering within the grace period |
Details output
| Variable | Value | Description |
|---|---|---|
node |
“Hostname:port” | Address of node |
message |
“message about event” | For unreachable: describing which stage of unreachable_node the node is in |
grace_period_in_secs |
“int” | The number of seconds the grace period is set to |
Backup/Restore events
| Event type | Description |
|---|---|
BACKUP_DB |
A database has completed a BACKUP DATABASE command |
RESTORE_DB |
A database has completed a RESTORE DATABASE command |
Details output
| Variable | Value | Description |
|---|---|---|
db |
“database_name” | Name of the database being backed up |
type |
“S3 or FS” | Where the backup is going to, S3 or Filesystem |
backup_id |
“unsigned int” | Id of the backup (only for backup) |
Out of Memory Events
| Event type | Description |
|---|---|
MAX_MEMORY |
Maximum server memory has been hit |
MAX_TABLE_MEMORY |
A table has hit the max table memory value |
Details output
| Variable | Value | Description |
|---|---|---|
actual_memory_mb |
“memory use in mb” | Current memory usage in mb |
maximum_memory_mb |
“maximum memory in mb” | Value of variable maximum_memory |
actual_table_memory_mb |
“memory use in mb” | Memory use of offending table |
maximum_table_memory_mb |
“maximum table memory variable value” | Value of variable maximum_table_memory |
memory_needed_for_redundancy |
“memory in mb needed " | Memory needed to allow the requested redundancy to fit in memory |
Miscellaneous events
| Event type | Description |
|---|---|
NOTIFY_AGGREGATOR_PROMOTED |
An aggregator has been promoted to master |
SYSTEM_VAR_CHANGED |
A sensitive engine variable has been changed |
PARTITION_UNRECOVERABLE |
A sensitive engine variable has been changed |
Sensitive variables
- auto_attach
- leaf_failure_detection
- enable_background_merger
- enable_background_flusher
- columnstore_window_size
- internal_columnstore_window_minimum_blob_size
- sync_permissions
- max_connection_threads
See the list of engine variables for more information on these variables.
Details output
For NOTIFY_AGGREGATOR_PROMOTED: "{}"
For SYSTEM_VAR_CHANGED:
| Variable | Value | Description |
|---|---|---|
variable |
“variable_name” | Name of sensitive variable |
new_value |
“new_value” | The new value for this variable |
For PARTITION_UNRECOVERABLE:
Variable | Value | Description
— | — | —
database | “db_name” | Name of the partition that is unrecoverable
reason | “Database couldn’t commit transaction” | Reason for partition going unrecoverable
Examples
SELECT * FROM information_schema.MV_EVENTS;
+----------------+---------------------+----------+----------------------------+------------------------------------------------------------+
| ORIGIN_NODE_ID | EVENT_TIME | SEVERITY | EVENT_TYPE | DETAILS |
+----------------+--------------------+-----------+----------------------------+------------------------------------------------------------+
| 2 | 2018-05-15 13:21:03 | NOTICE | NODE_ONLINE | {"node":"127.0.0.1:10001"} |
| 3 | 2018-05-15 13:21:05 | NOTICE | NODE_ONLINE | {"node":"127.0.0.1:10002"} |
| 1 | 2018-05-15 13:21:12 | NOTICE | REBALANCE_STARTED | {"database":"db1", "user_initiated":"true"} |
| 1 | 2018-05-15 13:21:12 | NOTICE | REBALANCE_FINISHED | {"database":"db1", "user_initiated":"true"} |
| 3 | 2018-05-15 13:21:15 | WARNING | NODE_DETACHED | {"node":"127.0.0.1:10002"} |
| 3 | 2018-05-15 13:21:16 | NOTICE | NODE_ATTACHING | {"node":"127.0.0.1:10002"} |
| 3 | 2018-05-15 13:21:22 | NOTICE | NODE_ONLINE | {"node":"127.0.0.1:10002"} |
| 2 | 2018-05-15 13:21:25 | WARNING | NODE_OFFLINE | {"node":"127.0.0.1:10001"} |
| 2 | 2018-05-15 13:21:29 | NOTICE | NODE_ATTACHING | {"node":"127.0.0.1:10001"} |
| 2 | 2018-05-15 13:21:30 | NOTICE | NODE_ONLINE | {"node":"127.0.0.1:10001"} |
| 1 | 2018-05-15 13:21:35 | NOTICE | DATABASE_REPLICATION_START | {"local_database":"db2", "remote_database":"db1"} |
| 1 | 2018-05-15 13:21:40 | NOTICE | DATABASE_REPLICATION_STOP | {"database":"db2"} |
| 2 | 2018-05-15 13:21:42 | WARNING | NODE_OFFLINE | {"node":"127.0.0.1:10001"} |
| 2 | 2018-05-15 13:21:47 | NOTICE | NODE_ATTACHING | {"node":"127.0.0.1:10001"} |
| 2 | 2018-05-15 13:21:48 | NOTICE | NODE_ONLINE | {"node":"127.0.0.1:10001"} |
| 3 | 2018-05-15 13:23:48 | NOTICE | REBALANCE_STARTED | {"database":"(null)", "user_initiated":"false"} |
| 3 | 2018-05-15 13:23:57 | NOTICE | REBALANCE_FINISHED | {"database":"(null)", "user_initiated":"false"} |
| 1 | 2018-05-15 13:23:57 | NOTICE | SYSTEM_VAR_CHANGED | {"variable": "leaf_failure_detection", "new_value": "off"} |
+----------------+---------------------+----------+--------------------+------------------------------------------------------------+
SELECT * FROM information_schema.LMV_EVENTS;
+----------------+---------------------+----------+--------------------+------------------------------------------------------------+
| ORIGIN_NODE_ID | EVENT_TIME | SEVERITY | EVENT_TYPE | DETAILS |
+----------------+---------------------+----------+--------------------+------------------------------------------------------------+
| 1 | 2018-06-28 11:56:09 | NOTICE | SYSTEM_VAR_CHANGED | {"variable": "max_connection_threads", "new_value": "256"} |
| 1 | 2018-06-28 11:56:11 | NOTICE | NODE_STARTING | {} |
| 1 | 2018-06-28 11:56:47 | NOTICE | NODE_ONLINE | {"node":"127.0.0.1:10001"} |
| 1 | 2018-06-28 11:56:47 | NOTICE | LEAF_ADD | {"node":"127.0.0.1:10001"} |
| 1 | 2018-06-28 17:42:28 | NOTICE | LEAF_REMOVE | {"node":"127.0.0.1:10001"} |
| 1 | 2018-06-28 17:42:37 | NOTICE | NODE_ONLINE | {"node":"127.0.0.1:10001"} |
| 1 | 2018-06-28 17:42:37 | NOTICE | LEAF_ADD | {"node":"127.0.0.1:10001"} |
+----------------+---------------------+----------+--------------------+------------------------------------------------------------+
