mysql-alert-prometheus-instructions.md

Guidance on prometheus alerts for bosh cf-mysql-release

Alerts Galera Cluster

MySQLGaleraClusterSize

. Verification: Cluster size less than 3

mysql_global_status_wsrep_cluster_size < 3

. Message : "Galera Cluster on <deploiement/instances> < 3 nodes during the last 5m"

. Diagnostic :

• check VMs
• check monitoring
• check MariaDB traces under /var/log/mysql/mysql.err.log

MySQLGaleraClusterEvenNodes

. Verification: Cluster size must be odd in order to avoid a split brain freezing the cluster (no quorum reached)

mysql_global_status_wsrep_cluster_size % 2 != 1

. Message : "Galera Cluster on <deploiement/instances> has even of nodes during the last 5m "

. Diagnostic :

• check VMs
• check monitoring
• check MariaDB traces under /var/log/mysql/mysql.err.log

MySQLGaleraNotOperational

. Verification: State of the galera node different from NON-PRIMARY

mysql_global_status_wsrep_cluster_status != 1

. Message : "A Galera Cluster node on <deploiement/instance> had not been operational during the last 5m. It may occur in cases of multiple membership changes that result in a loss of quorum or in cases of split-brain situations"

. Diagnostic :

• check VMs
• check monitoring
• check MariaDB traces under /var/log/mysql/mysql.err.log

MySQLGaleraNotReady

. Verification: State of the galera node different OFF (Desynchronized node does not accept requests)

mysql_global_status_wsrep_ready != 1

. Message : "A Galera cluster node on <deploiement/instance> has not been ready during the last 5m"

. Diagnostic :

• check MariaDB traces under /var/log/mysql/mysql.err.log

If message

#####################################################################################
SST disabled due to danger of data loss. Verify data and run the rejoin-unsafe errand
#####################################################################################"

SST blocked by the release, you have to identify the reason then restart the synchronization manually

rm -rf /var/vcap/store/mysql
/var/vcap/jobs/mysql/bin/pre-start
monit start mariadb_ctrl

MySQLGaleraNotConnected

. Verification: Galera node not connected to the cluster

mysql_global_status_wsrep_connected != 1

. Message : "A Galera cluster node on <deploiement/instance> has not been connected to the cluster during the last 5m"

. Diagnostic :

• check MariaDB traces under /var/log/mysql/mysql.err.log

MySQLGaleraOutOfSync

. Verification: Node not Synced and not being used for an SST

mysql_global_status_wsrep_local_state != 4 AND mysql_global_variables_wsrep_desync == 0)

. Message : "A Galera cluster node on <deploiement/instance> has not been in sync ) during the last 5m"

. Diagnostic :

• check MariaDB traces under /var/log/mysql/mysql.err.log

MySQLGaleraDonorFallingBehind

. Verification: Node Donor/Desynced with response time > 100ms

mysql_global_status_wsrep_local_state == 2 AND mysql_global_status_wsrep_local_recv_queue > 100

. Message : "A Galera cluster node on <deploiement/instance> is a donor (hotbackup) and has been falling behind (queue size 100) during the last 5m"

. Diagnostic :

• check MariaDB traces under /var/log/mysql/mysql.err.log

MySQLGaleraFlowControlPaused

. Verification: Galera cluster frozen, replication does not commit

mysql_global_status_wsrep_flow_control_paused == 1)

. Message : "A Galera Cluster node on <deploiement/instance> has been paused due to flow control during the last 5m"

. Diagnostic :

• identify the blocking node via MariaDB traces under /var/log/mysql/mysql.err.log

MySQLGaleraFlowControlPauseTooHigh

. Verification: Galera cluster slowed down, replication lag between 0.5 and 1

mysql_global_status_wsrep_flow_control_paused > 0.5 and mysql_global_status_wsrep_flow_control_paused < 1)

. Message : "A Galera Cluster node on <deploiement/instance> had a flow control pause too high during the last 5m"

. Diagnostic :

• identify the blocking node via MariaDB traces under /var/log/mysql/mysql.err.log

MySQLGaleraSendQueueLengthTooHigh

. Vérification : replication (send) > 0.01

mysql_global_status_wsrep_local_send_queue_avg > 0.01

. Message : "Galera Cluster on <deploiement/instance> had a local send queue length too high ({{$value}}) during the last 5m, It may indicate that replication throttling or network throughput issues"

. Diagnostic :

• check MariaDB traces under /var/log/mysql/mysql.err.log
• check status

mysql -uroot –p<password>

and

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_local_%_queue';

Variable_name           Value   
----------------------  --------
wsrep_local_recv_queue  0       
wsrep_local_send_queue  0     

so

MariaDB [(none)]> flush status;

MySQLGaleraRecvQueueLengthTooHigh

. Vérification : replication (recv) > 0.5

mysql_global_status_wsrep_local_recv_queue_avg > 0.5

. Message : "Galera Cluster on <deploiement/instance> had a local received queue length too high ({{$value}}) during the last 5m, It may indicate that the node cannot apply write-sets as fast as it receives them, which can lead to replication throttling""

. Diagnostic :

• check MariaDB traces under /var/log/mysql/mysql.err.log
• check status

mysql -uroot –p<password>

and

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_local_%_queue';

Variable_name           Value   
----------------------  --------
wsrep_local_recv_queue  0       
wsrep_local_send_queue  0     

so

MariaDB [(none)]> flush status;

Performance Alert

MySQLInnoDBLogWaits

. Verification: Average write times in InnoDBlog too high

rate(mysql_global_status_innodb_log_waits[15m]) > 10

. Message : "the innodb logs at <deploiement/instances> are waiting for disk at a rate of /second"

. Diagnostic :

• check the fill rates of the fs
• check the performance of persistent disks

Alerte prometheus-mysqld-exporter

MySQLdExporterScrapeError

. Verification :

(mysql_exporter_last_scrape_error) != 0

. Message : "The mysqld_exporter <deploiement/instances> was unable to scrape metrics during the last 10m

. Diagnostic :

• check status

monit summary

...
Process 'mysqld_exporter'           running
...

Si ko,

monit start mysqld_exporter

If ok and traces ok in grafana

monit restart mysqld_exporter
...