Shulou(Shulou.com)05/31 Report--

This article mainly introduces the meaning of MySQL monitoring project indicators, 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.

I. MYSQL.QPS

Definition: QPS actually refers to the average total amount of Query executed by MySQL Server in the past 10 seconds (including show. Select, set names,set global, etc.)

Calculation formula: QPS=questions (current value)-questions (value before 10 seconds) / 10

Whether to keep: yes

Alarm threshold: depending on the business situation (mainly the complexity of query) and server configuration settings. Most of them are set to 10000.

Alarm type: more than 10000 serious. Greater than 12000 disaster

II. MYSQL.TPS

Definition: TPS refers to the changes submitted to mysql sever per second (including update.insert.delete.replace)

The old way of calculation:

T1=Handler_commit+Handler_rollback (all current values)

T2 = Handler _ commit+ Handler _ rollback (all values before 10s)

TPS=T1-T2/10

New method of calculation:

T1 = Com_insert+ Com_replace+Com_delete+Com_update (all current values)

T2 = Com_insert+ Com_replace+Com_delete+Com_update (all values before 10s)

TPS=T1-T2/10

Whether to keep: yes

Alarm threshold: depending on each business situation and server configuration settings. Most of them are set to 4000.

Alarm type: more than 4000 serious. Greater than 6000 disaster

III. MYSQL.READS

Definition: READS refers to the number of read requests submitted to msyql sever per second (select only)

Calculation formula:

R1compressed compositions select + qcache_hits (all current values)

R2percent compositions select + qcache_hits (all values from 10 seconds ago)

READS=R1-R2/10

Whether to keep: yes

Alarm threshold:

IV. MYSQL.WRITES

Definition: WRITERS refers to the number of write requests submitted to mysql server per second:

Calculation formula:

W1=com_insert+com_delete+com_update (all current values)

W2=com_insert+com_delete+com_update (all values before 10s)

WRITES=W1-W2/10

Whether or not to retain: do not retain

Reason for removal: this monitoring option repeats with tps.

V. MYSQL.RWRATIO

Definition: RWRATIO refers to the read-write ratio since mysql server was started. Note that it is not measured before or after 10s.

Calculation formula:

R = com_select+ qcache_hits (all current values)

W = com_insert+ com_delete+ com_update+ com_replace (all current values)

RWRATIO=R/W (the resulting value retains the value before the decimal point)

Whether or not to retain: do not retain

Reason for removal: this monitoring option can be calculated indirectly through mysql.reads and mysql.tps. There is no need to set up additional monitoring items.

Six. MYSQL.Key _ BUFFER_READ_HITS

Definition: KEY_BUFFER_READ_HITS refers to the read hit ratio of the myisam engine to key buffer. This buffer is used to store the index key of myisam

Calculation formula:

KEY_BUFFER_READ_HITS= (1-(key_reads/ key_read_requests)) * 100% (all current values)

(leave the value before the decimal point)

Whether or not to retain: retention is not recommended

Reason for removal: now only sso and Jiuxianqiao cms are myisam engines. All the follow-up projects adopt innodb. And both projects will be converted to innodb when appropriate. And at present, the data pressure of this 2 projects is stable. Or only monitor these two services for the time being. Delete this monitoring item after the engine has been replaced.

VII. MYSQL.KEY_BUFFER_WRITE_HITS

Definition: KEY_BUFFER_WRITE_HIT refers to the write hit ratio of the myisam engine to key buffer

Calculation formula:

KEY_BUFFER_WRITE_HIT = (1-(key_writes/key_write_requests)) * 100 (all current values)

The resulting value remains the value before the decimal point.

Whether or not to retain: retention is not recommended

Reason for removal: this monitoring item only reflects the hit rate of myisam. Myisam will be eliminated in future projects. In addition, there is no need to specifically monitor the write hit of mysiam. Pay attention to the overall hit of key buffer. It makes more sense.

VIII. MYSQL.QUERY_CACHE_HITS

Definition: QUERY_CACHE_HITS refers to the hit rate of the query cache. The higher the value, the greater the effect of query cache. So as to reduce the engine pressure. User results are returned at the mysql server layer

Calculation formula:

QUERY_CACHE_HITS = qcache_hits/ (qcache_hits+ qcache_inserts) * 100% (both are current values)

The resulting value remains the value before the decimal point.

Whether or not to retain: retention is not recommended

The reason for removal: the amount of modification of key business online is relatively large. And the operation query cache is a global lock. As a result, sql often stays in the state of Waiting for query cache lock and failure query cache. In addition. If more than 95% of the sql of a business is select. It is recommended to open query cache.

IX. MYSQL.INNODB_BUFFER_READ_HITS

Definition: INNODB_BUFFER_READ_HITS. An extremely important state value. Reflect the hit of the data needed by the innodb engine in memory.

Calculation formula:

INNODB_BUFFER_READ_HITS= (1-(innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)) * 100% (all current values) (the obtained values retain the values before the decimal point).

Whether to keep: keep

Alarm threshold: less than 95%

Alarm type: less than 95% warning

X.MYSQL.Thread _ CACHE_HITS

Definition: THREAD_CACHE_HITS= (1-(threads_created/ connections)) * 100% (all current values)

The resulting value remains the value before the decimal point.

Whether to keep: keep

Alarm threshold: less than 90%

Alarm type: less than 90% warning

Eleventh. MYSQL.SLOW _ QUERIES_PER_SECOND

Definition: SLOW_QUERIES_PER_SECOND refers to the average total amount of slow log generated per second every last 10 seconds

Calculation formula:

SLOW_QUERIES_PER_SECOND = Slow_queries (current value)-Slow_queries (value before 10 seconds) / 10

Whether to keep: keep

Alarm threshold:

Alarm type:

12. MYSQL.temp _ TABLES_TO_DISK_RATIO

Definition: TEMP_TABLES_TO_DISK_RATIO refers to the probability of creating a temporary table on the hard disk, the smaller the better. Represents that temporary tables are created in memory.

Calculation formula:

TEMP_TABLES_TO_DISK_RATIO= (created_tmp_disk_tables/ created_tmp_tables) * 100%

(all current values) (the obtained values retain the values before the decimal point).

Whether or not to retain: retention is not recommended

Reason for removal: find out if there is a problem with sql performance. This ratio should not be the only concern. A poor performance sql that produces disk temporary tables may have a much greater performance impact than a hundred sql that produces memory temporary tables. Active attention should be paid to those sql with very long execution times in slow queries.

XIII. MYSQL.TMP _ DISK_TABLES_PER_SECOND

Definition: TMP_DISK_TABLES_PER_SECOND refers to the number of disk temporary tables created per second in the past 10 seconds

Calculation formula:

TMP_DISK_TABLES_PER_SECOND=created_tmp_disk_tables (current value)-created_tmp_disk_tables (value before 10 seconds) / 10

Whether or not to retain: it is recommended to retain

Alarm threshold:

Alarm type:

XIV. MYSQL.Select _ FULL_JOIN

Definition: SELECT_FULL_JOIN refers to the total amount of full join executed per second. This parameter usually occurs when the index is not used during join

Calculation formula:

SELECT_FULL_JOIN= Select_full_join (current value)-Select_full_join (value before 10 seconds) / 10

Whether or not to retain: retention is not recommended

Reason for removal: it's not that join performance is poor without using the index. For unreasonable sql. If there is a performance impact. You can open a slow query to tune according to. Full join may be just one of the reasons. The full join of some small watches has little impact on performance.

XIV. MYSQL.Select _ FULL_JOIN_IN_ALL_SELECT

Definition: SELECT_FULL_JOIN_IN_ALL_SELECT refers to the percentage of full join to total select in the past 10 seconds.

Calculation formula:

S1 = select_full_join (current value)-select_full_join (value before 10 seconds)

S2 = com_select (current value)-com_select (value before 10 seconds)

SELECT_FULL_JOIN_IN_ALL_SELECT=s1/s2*100% (the resulting value keeps the value before the decimal point).

Whether or not to retain: retention is not recommended

Reason for removal: same reason as above

Sixteen. MYSQL.connections

Definition: CONNECTIONS refers to the total number of connections produced since mysql server was started. This value does not reflect the current number of connections. It is suggested that Threads_connected be used to measure it.

New calculation method: CONNECTIONS= Threads_connected (current value)

Whether to keep: keep

Alarm threshold: 800

Alarm type: more than 800 serious. Greater than 1500 disaster

Seventeen. MYSQL.connection _ TIME

Definition: the time it takes to send a simple query from the server where mysql server resides.

Whether or not to retain: retention is not recommended

Reason for removal: tcp_average is more valuable than this parameter. Because the time sends a request to the server itself. Tcp average, on the other hand, focuses on the time calculated from ip to db server.

XVIII. MYSQL.INNODB _ ROW_LOCK_CURRENT_WAITS (added)

Definition: INNODB_ROW_LOCK_CURRENT_WAITS. Because most of the company's engines are innodb. Therefore, it is necessary to monitor the waiting of innodb row locks. The status value reflects how many sql have been waiting for row lock in the past 10 seconds.

Calculation formula: INNODB_ROW_LOCK_CURRENT_WAITS= Innodb_row_lock_current_waits (current value)

Whether to keep: keep

Alarm threshold: none

Alarm type: none

XIX. MYSQL.process _ OF_MYSQLD

Definition: the number of processes used by process_of_mysqld to monitor mysqld. If 0. Indicates that the mysqld process is down.

Whether to keep: keep

Alarm threshold: if 0, alarm

Type of alarm: disaster

Twenty. MYSQL.HA _ STATUS

Definition: ha_status is used to monitor the existence of MHA processes. If 0. Indicates that MHA automatically exits due to the switching of MHA. Or the MHA service is not started.

Whether to keep: keep

Alarm threshold: if 0, alarm

Type of alarm: severe

21. MYSQL.SLAVE _ STATUS

Definition: io thread and sql thread used by mysql.slave_status to monitor slave. If the state of any thread is no. Returns 0. 0. Otherwise, 1 is returned.

Whether to keep: keep

Alarm threshold: if 0, alarm

Type of alarm: severe

22: MYSQL.TCP_COUNT

Definition: the average number of requests sent to mysql server per second from the underlying probe using percona's tcprstat. Instead of doing statistics inside mysql server.

Whether or not to retain: retention is not recommended

Reason for removal: the result of this monitoring is consistent with the number of qps. Repetitive monitoring

23: MYSQL.TCP_AVG

Definition: the average response time of all requests sent to mysql server per second. This value is large. It shows that mysql server is abnormal. It took too long to respond to the request. The unit is subtle.

Whether to keep: keep

Alarm threshold: none

Alarm type: none

24: MYSQL.TCP_MAX

Definition: in the past one second. The time it takes to send to the request with the longest response time of all requests in mysql server. The unit is subtle.

Whether to keep: keep

Alarm threshold: none

Alarm type: none

21. MYSQL.SLAVE _ DELAYS

Definition: mysql.slave_delay is used to monitor the sql thread synchronization master of slave for latency. If there is. Returns the number of seconds of delay. If there is no delay. Return empty

Whether to keep: keep

Alarm threshold: 1200

Alarm type: more than 1200 serious. Greater than 3600 disaster

Thank you for reading this article carefully. I hope the article "what is the meaning of MySQL monitoring project indicators" shared by the editor will be helpful to everyone? at the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.