How to monitor the status of MySQL

This article mainly introduces how to monitor the status of MySQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Get the total number of processes under mysql users

Ps-ef | awk'{print $1}'| grep "mysql" | grep-v "grep" | wc-l

Note: when looking for information on the Internet, I found that this instruction was misspelled on some websites, the last symbol is the character l, not the number 1, and there is a space between wc and-l.

Second, host performance status

# uptime

[root@ ~] # uptime

13:05:52 up 53 days, 52 min, 1 user, load average: 0.00, 0.00, 0.00 、

Introduction to Load averge: Load Average is the Load of CPU. The information it contains is not the utilization status of CPU, but the statistics of the total number of processes being processed by CPU and waiting for CPU processing over a period of time, that is, statistics on the length of queues used by CPU. Reflect the busy degree of cpu from the side, except that the load value is not directly equal to the number of processes in the waiting queue, generally speaking. The system will not have much of a problem until the load value does not exceed the number of physical cpu of the system (or the total number of cpu cores).

This instruction can only be checked once. "load average: 0.00,0.00,0.00" represents the load average of the previous 1 second, 5 second, and 15 second, respectively.

This indicator can also be viewed with the top directive, as shown in:

Http://blog.csdn.net/lengyuhong/archive/2010/09/01/5855235.aspx

Http://blog.csdn.net/lengyuhong/archive/2010/09/29/5913658.aspx

Third, CPU utilization rate

# top

Or

# vmstat

Fourth, disk IO quantity

# vmstat or # iostat

Fifth, swap in and out [memory]

# free

Sixth, database performance status

(1) QPS (number of queries per second)

QPS = Questions (or Queries) / seconds

Mysql > show status like 'Question'

(2) TPS (transactions per second)

TPS = (Com_commit + Com_rollback) / seconds

Mysql > show status like 'Com_commit'

Mysql > show status like 'Com_rollback'

(3) key Buffer hit rate

Key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%

Key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

Mysql > show status like 'Key%'

(4) InnoDB Buffer hit rate

Innodb_buffer_read_hits = (1-innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

Mysql > show status like 'innodb_buffer_pool_read%'

(5) Query Cache hit rate

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts)) * 100%

Mysql > show status like 'Qcache%'

(6) Table Cache state quantity

Mysql > show status like 'open%'

(7) Thread Cache hit rate

Thread_cache_hits = (1-Threads_created / connections) * 100%

Mysql > show status like 'Thread%'

Mysql > show status like 'Connections'

(8) locked statu

Mysql > show status like'% lock%'

(9) replication delay

Mysql > show slave status

(10) Tmp Table status (temporary table status)

Mysql > show status like 'Create_tmp%'

(11) Binlog Cache usage

Mysql > show status like 'Binlog_cache%'

(12) Innodb_log_waits quantity

Mysql > show status like 'innodb_log_waits'

Open source monitoring software

One, RRDTool

Second, Nagios

Three, MRTG

Four, Cacti

Thank you for reading this article carefully. I hope the article "how to monitor MySQL status" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!