MySQL monitoring key indicators and acquisition methods

reprint: Http://www.linuxidc.com/Linux/2016-11/136788.htm

MySQL monitoring is one of the modules of DB monitoring, including collecting, displaying and monitoring alarms. This paper mainly introduces the main indexes and collection methods of MySQL monitoring.

MySQL monitoring and Redis monitoring logic similar, can refer to the article "Redis Monitoring Key Indicators and collection method Http://www.linuxidc.com/Linux/2016-11/136783.htm".

When the DBA's front desk adds MySQL monitoring, the system invokes the automatic dispatch platform interface to send information such as the password and IP port of the MySQL monitor to the target, while sending the collection agent.

I. Collection of indicators and orders

1. mysql Service running status

Contract all MySQL services must be ip1 (intranet IP) to bind, each machine has only one ip1, can have multiple ports, that is, multiple MySQL Server. The capture program reads the IP port information file to determine if the server exists.

sockparam= ' ps aux | Grep-p "Mysqld.*--port=${port}" | Grep-op "--socket.*\.sock" ' # Empty will not get to the server port MySQL socket configuration, please check the MySQL configuration is correct
Mysql= "/usr/local/mysql/bin/mysql-hlocalhost--port=${port} ${sockparam}-u${user}-p${password}"
Mysql_admin= "/usr/local/mysql/bin/mysqladmin-hlocalhost--port=${port} ${sockparam}-u${user}-p${password}"
Curstatus= ' ${mysql}-e ' show global status ' # empty is not getting to the server MySQL status, please check if MySQL is working properly
If [-Z "${curstatus}"]
Then
Portexists=0
Else
echo "${curstatus}" >> ${curfile}
Portexists=1

2. Number of connections

${mysql_admin} processlist-v | Wc-l

3. Number of threads

grep ' threads_connected ' ${curfile} | awk ' {print $} '

4, slow query number

grep ' slow_queries ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times gets the difference, equal to the last 1 minutes of slow queries. Last data saved in Last.cache.

5. Open the number of tables

grep ' Open_tables ' ${curfile} | Awk-f ' {print $} '

6. Execute Select number per second

grep ' Com_select ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

7. Execute Delete number per second

grep ' Com_delete ' ${curfile} | Grep-v ' Multi ' | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

8. Number of inserts executed per second

grep ' Com_insert ' ${curfile} | Grep-v ' SELECT ' | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

9, the number of update executions per second

grep ' com_update ' ${curfile} | Grep-v ' Multi ' | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

10, perform the replace number per second

grep ' Com_replace ' ${curfile} | Grep-v ' SELECT ' | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

11. innodb_rows_deleted executed per second

grep ' innodb_rows_deleted ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

12. innodb_rows_inserted executed per second

grep ' innodb_rows_inserted ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

13. Innodb_rows_read executed per second

grep ' Innodb_rows_read ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

14. innodb_rows_updated executed per second

grep ' innodb_rows_updated ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution. Last data saved in Last.cache.

15. InnoDB rows Total per second

Expr ${innodbrowsdeletedps} + ${innodbrowsinsertedps} + ${innodbrowsreadps} + ${innodbrowsupdatedps}

equals the sum of the previous four innodb_rows_* execution times

16. Number of processing commands per second QPS

Expr ${mysqlselectnumps} + ${mysqlinsertnumps} + ${mysqlupdatenumps} + ${mysqldeletenumps} + ${mysqlreplacenumps}

equals the sum of the number of previous five MySQL command com_*

17. Bytes Received per second kbyte/s

grep ' bytes_received ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution, divided by 1024 to get the unit kbyte/s. Last data saved in Last.cache.

18. Bytes Sent per second

grep ' bytes_sent ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times the difference is divided by the time difference, equal to the last 1 minutes of execution, divided by 1024 to get the unit kbyte/s. Last data saved in Last.cache.

19. The number of locks that can be acquired immediately

grep ' table_locks_immediate ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times gets the difference, equal to the amount of locks that can be acquired immediately in the last 1 minutes. Last data saved in Last.cache.

20. The number of times a lock cannot be acquired immediately

grep ' table_locks_waited ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times gets the difference, equal to the last 1 minutes of not acquiring the lock immediately. Last data saved in Last.cache.

21. Wait time for one row to be locked

grep ' innodb_row_lock_waits ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times gets the difference, which equals the wait time for the last 1 minutes of a row lock. Last data saved in Last.cache.

22. Current Dirty Pages

grep ' Innodb_buffer_pool_pages_dirty ' ${curfile} | Awk-f ' {print $} '

23. Number of buffer pool pages required to be emptied

grep ' innodb_buffer_pool_pages_flushed ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times gets the difference, equal to the last 1 minutes of buffer pool pages that are required to be emptied. Last data saved in Last.cache.

24. Innodb Write Log bytes KByte

grep ' Innodb_os_log_written ' ${curfile} | Awk-f ' {print $} '

The number of slow queries that need to be calculated two times gets the difference, equal to the last 1 minutes of write log bytes, divided by 1024 to get kbyte. Last data saved in Last.cache.

25. Memory Size MByte

Pid= ' ps aux | grep ' mysqld ' | Grep-ev ' Safe|grep ' | awk ' {print $} '
Mem= ' Cat/proc/${pid}/status | grep ' Vmrss ' | awk ' {print $} '
Mysqlmem= ' echo ' Scale=2;${mem}/1024 "| BC '

Divide by 1024 to get MByte

26, Handler socket per second processing number

curhstablelock= ' grep ' Hs_table_lock ' ${curfile} | awk ' {print $} '
prehstablelock= ' grep ' Hs_table_lock ' ${prefile} | awk ' {print $} '
If [-N "${curhstablelock}"]
Then
Hsqps= ' echo ' scale=0; (${curhstablelock}-${prehstablelock})/${intervaltime} "| BC '
Else
Hsqps=0
Fi

27. Master-Slave synchronization and status

#主从信息
#是否为从服务器
slave_running= ' grep ' slave_running ' ${curfile} | awk ' {print $} '
If ["${slave_running}a" = "ONA"]
then
    slaverunning=1
    slavestatus= ' ${mysql}-e ' show slave status\g '
& nbsp   echo "${slavestatus}" > ${slavefile}
    
    slaveiorunning= ' grep ' Slave_io_ Running ' ${slavefile} | Awk-f ': ' {print $} '
    slavesqlrunning= ' grep ' slave_sql_running ' ${slavefile} | awk-f ': ' {print $} ' `

If ["${slaveiorunning}a" = = "NoA"-o "${slavesqlrunning}a" = = "NoA"]
Then
Slaverunning=3
Fi

Secondsbehindmaster= ' grep ' seconds_behind_master ' ${slavefile} | Awk-f ': ' {print $} '
If ["${secondsbehindmaster}a" = "NULLA"]
Then
secondsbehindmaster=8888 # indicates that the master never synchronizes
Fi

#是从库时 get the main library IP
Master= ' grep ' master_host ' ${slavefile} | Awk-f ': ' {print $} '
masterport= ' grep ' Master_port ' ${slavefile} | Awk-f ': ' {print $} '
Else
Master= ""
Masterport= ""
Slaverunning=0
secondsbehindmaster=10000 # No detection
Fi

Note: Seconds_behind_master, this value as the indicator of master-slave delay, then it is how to get this value, at the same time, why it was questioned by many people?

Seconds_behind_master is a difference that is obtained by comparing the timestamp of the event performed by the Sql_thread with the timestamp (abbreviated TS) of the Io_thread copy good event. We all know the Relay-log and the main library bin-log inside the same content, in the record SQL statement will be recorded at the time of the TS, so the reference value from Binlog, in fact, there is no need to synchronize with NTP, that is, there is no need to ensure the consistency of the master-slave clock. You will also find that the comparison really occurs between Io_thread and Sql_thread, and Io_thread is really associated with the main library, so the problem comes out, when the main library I/O load is large or network congestion, Io_ Thread can not replicate Binlog (no interruption, also in copy), and Sql_thread has always been able to keep up with the Io_thread script, then the value of Seconds_behind_master is 0, that is, we think of no delay, but, actually not, You know. This is why we have to criticize the use of this parameter to monitor the database is not allowed to delay the reason, but this value is not always allowed, if the Io_thread and master network is very good, then this value is also very valuable.

Before, the reference to seconds_behind_master this parameter will have a negative value, we already know that the value is Io_thread recently with the new TS and sql_thread to perform the TS difference, the former is always greater than the latter, The only possibility is that the TS of an event is wrong, smaller than the previous one, and when this happens, a negative value appears.

28. Detect the heartbeat of the collection agent

MySQL monitoring key indicators and acquisition methods