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

This article shows you what the percona monitoring mysql graphics are like, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Several graphical explanations of percona Monitoring mysql

Recently, I took a closer look at the meaning of several pictures of percona monitoring mysql.

Com_XXX represents the number of times each XXX statement is executed, such as:

The number of select operations performed by Com_select, adding only 1 to a query

The number of times Com_update performed update operations

The number of times Com_insert performs insert operations, only once for batch inserts.

The number of times Com_delete performed delete operations

Number of operations only for the InnoDB storage engine:

The number of rows deleted by Innodb_rows_deleted from the InnoDB table.

The number of rows that Innodb_rows_inserted inserts into the InnoDB table.

The number of rows read by Innodb_rows_read from the InnoDB table.

The number of rows updated in the Innodb_rows_updated InnoDB table.

The data above is from: show global status like'% innodb_row%'.

InnoDB semaphore

InnoDB semaphore map displays information about InnoDB semaphore activity: number of wheels, rotation

Wait, the operating system waits. You may see these graphs peak during periods of high concurrency or contention. These charts

Basically indicates the different types of activities involved in acquiring row locks or mutexes, which is the cause of poor performance

In some cases, zoom.

The above data comes from:

Mysql > show engine innodb status\ G

SEMAPHORES (semaphore)

OS WAIT ARRAY INFO: reservation count 6428579

OS WAIT ARRAY INFO: signal count 6391102

Mutex spin waits 10807638, rounds 51362184, OS waits 1135246

RW-shared spins 5183147, rounds 154424701, OS waits 5133668

RW-excl spins 187041, rounds 4866289, OS waits 132629

Spin rounds per wait: 4.75 mutex, 29.79 RW-shared, 26.02 RW-excl

Czxin comment: the number of "spin waits" and "spin rounds". A spin lock is a low-cost wait compared to a system wait; however, it is an active wait that wastes some cpu resources. So if you see a lot of spin waiting and spin rotation, it's obviously wasting a lot of cpu resources. Innodb_sync_spin_loops can be used to strike a balance between wasting cpu time and unnecessary context switching.

Reservation count "and" signal count "show how active innodb is using internal synchronous arrays-time slice (slot) allocation and how frequently thread signals use synchronous arrays. These statistics can be used to indicate how often the innodb falls back to the system waiting. There is also directly related information about the system waiting, you can see the mutex semaphore (mutexes) of "OS Waits", as well as read-write locks. Mutex and shared locks are shown in this information. System waits are not exactly the same as "reservation". Before falling back to the complex wait mode with sync_array, innodb tries to "yield" to the system, hoping that the named thread has been released in the next scheduled time object. System waits are relatively slow, and there may be problems if tens of thousands of system waits occur per second. Another way to observe is to look at the frequency of context (context) exchanges in the system state.

Lock resource

The figure above shows the waiting of a row lock.

Innodb_lock_wait refers to the number of times / per second that a row lock cannot be acquired immediately.

The data in the above figure comes from show global status like 'innodb_lock_wait'.

MySQL table locks

The figure above shows the waiting of a table-level lock.

Table_locks_immediate refers to the number of times a table-level lock can be acquired immediately / per second

Table_locks_waited refers to the number of times / per second that a table-level lock cannot be acquired immediately.

The data above is from show global status like'% table_lock%'.

Innodb table in use

The InnoDB Tables In Use (InnoDB table in use) diagram shows how many tables are in use and how many tables are locked in InnoDB. If there is

In these figures, you may also see spikes in lock waiting and other signs of contention between queries.

The above two parameter values of innodb tables in use and innodb locked tables come from show engine innodb status\ G; search table to find them. Note that these two values are not values per second, but real values.

The above is what the graphics of percona monitoring mysql are like. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.