The key to database management is to be able to detect problems in advance, and to detect problems in advance, you have to have monitoring systems, and open source systems do

  • Zabbix
  • Nagios

However, these systems are generally used directly, focusing on what indicators are monitored, and the following focuses on the specific indicators, rather than what monitoring software is used.

MySQL commonly used monitoring indicators

Non-functional indicators:

  • QPS: The number of requests processed by the database per second, including DML and DDL, to reflect the performance of the database
  • TPS: The number of transactions processed by the database per second
  • Concurrency: the number of concurrent sessions currently processed by the database
  • Connection number: The number of sessions connected to the database
  • Cache hit ratio: InnoDB cache hit ratio

Functional indicators:

  • Availability: Indicates whether the database provides services properly
  • Blocking: Whether there are currently blocked sessions, locking resources that others need
  • Deadlock: Whether the current transaction is deadlocked, locking each other’s resources
  • Slow query: Real-time slow query monitoring
  • Master-slave delay: Required in asynchronous replication architectures

QPS=(Queries1 – Queries2)/ interval

show global status where variable_name in ('Queries'.'uptime'); // Query Queries for the first time Uptime 32769425 // Query Queries for the second time Uptime 32769452 QPS = (3532977031-3532976857 )/(32769452-32769425) = 6.44Copy the code

TPS=sum(delete, add, update)/ interval

show global status where variable_name in ('com_insert'.'com_update'.'com_delete'.'uptime');

Com_delete	1721
Com_insert	3404633923
Com_update	30505256
Uptime	32769668

Com_delete	1721
Com_insert	3404633970
Com_update	30505314
Uptime	32769706

TPS = ((3404633970 - 3404633923) + (30505314 - 30505256 ) ) / (32769706 - 32769668) = 2.76
Copy the code

The maximum number of concurrent connections is the configured max_connection variable

show global status where variable_name in ('Threads_running'.'Threads_connected');

Threads_connected	5
Threads_running	3
Copy the code

InnoDB cache hit ratio, cache hit can save read disk

(innodb_buffer_pool_read_requests – innodb_buffer_pool_reads) / innodb_buffer_pool_read_requests

Innodb_buffer_pool_read_requests: Number of reads from the cache pool

Innodb_buffer_pool_reads: Indicates the number of reads from the physical disk

show global status where variable_name like 'innodb_buffer_pool_read%'Innodb_buffer_pool_read_requests 122914434867 Innodb_buffer_pool_reads 47710383 HIT ratio = (122914434867-47710383) / 122914434867 = 0.99Copy the code

Nonfunctional index

Availability, periodic connection to the server to execute select @@version

Block, caused by multiple threads applying an exclusive lock on the same resource

  • Greater than version 5.7, check sys. innodb_lock_WAITS, wait_started > how many seconds

Deadlock:

  • Pt-deadlock- Logger tool monitoring, more intuitive
  • Set global innodb_print_all_deadLOCKS =on

Slow query: You can also query the information_schema. processList table through log monitoring

Master-slave delay

show slave status;
Copy the code

reference

  • Does MySQL deadlock occur?
  • Product monitoring index
  • MySQL Interview Guide