MySQL focuses on performance and detailed explanation of related analysis commands-PHP开发-php.cn

1. Focus on MySQL performance monitoring

QPS (Query per second): The QPS here actually refers to the total amount of Query executed by MySQL Server per second:
QPS = Queries / Seconds

TPS (TPS) Transaction volume): There is no direct transaction counter in MySQL Server. We can only calculate the system transaction volume through rollback and commit counters. Therefore, we need to get the TPS value requested by the client application in the following way:
TPS = (Com_commit + Com_rollback) / Seconds

Key Buffer hit rate: Key Buffer hit rate represents the index of the MyISAM type table Cache hit rate. The size of this hit rate will directly affect the read and write performance of MyISAM type tables. Key Buffer hit rate actually includes read hit rate and write hit rate. The values of these two hit rates are not directly given in MySQL, but they can be calculated as follows:
key_buffer_read_hits = (1 - Key_reads / Key_read_requests) * 100%
key_buffer_write_hits= (1 - Key_writes / Key_write_requests) * 100%

Innodb Buffer hit rate: Here Innodb Buffer refers to innodb_buffer_pool, which is the memory used to cache data and indexes of Innodb type tables space. Similar to Key buffer, we can also calculate its hit rate based on the corresponding status value provided by MySQL Server:
innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

Query Cache hit rate: If we use Query Cache , then it is also necessary to monitor the Query Cache hit rate, because it may tell us whether we are using Query Cache correctly.

Query Cache hit rate is calculated as follows:
Query_cache_hits= (Qcache_hits / (Qcache_hits + Qcache_inserts)) * 100%

Table Cache status: The current status of Table Cache can help us judge whether the setting of the system parameter table_open_cache is reasonable. If the ratio between the status variables Open_tables and Opened_tables is too low, it means that the Table Cache setting is too small:
SHOW STATUS LIKE 'Open%';

Thread Cache hit rate: Thread Cache hit rate can directly reflect our system parameter thread_cache_size Are the settings reasonable? A reasonable thread_cache_size parameter can save a lot of resources needed to create new connections.
Thread Cache hit rate is calculated as follows:
Thread_cache_hits = (1 - Threads_created / Connections) * 100%

Lock status: Lock status includes table lock and row lock. We can get the total number of locks through the system status variable, lock The number of times other threads are caused to wait, and lock waiting time information.
SHOW STATUS LIKE '%lock%';
Through the lock-related system variables, we can get the total number of table locks, including the number of times other existing threads are waiting. At the same time, you can also get very detailed row lock information, such as the total number of row locks, the total row lock time, the waiting time for each row lock, the maximum waiting time caused by row locks, and the number of threads currently waiting for row locks. By monitoring these quantities, we can clearly understand whether the overall system lock-up is serious. If the ratio of Table_locks_waited to Table_locks_immediate is large, it means that the blocking caused by our table locks is serious, and the Query statement may need to be adjusted, the storage engine may need to be changed, or the business logic may need to be adjusted. Of course, specific improvement methods must be judged based on actual scenarios. If Innodb_row_lock_waits is larger, it means that Innodb's row lock is also serious and affects the normal processing of other threads. The cause also needs to be found and resolved. The cause of serious Innodb row locks may be that the index used by the Query statement is not reasonable enough (Innodb row locks are locked based on indexes), causing the gap lock to be too large. It may also be that the system itself has limited processing capabilities, and other aspects (such as hardware devices) need to be considered.

Replication delay: The replication delay will directly affect the length of time the Slave database is in an inconsistent state.
Execute the "SHOW SLAVE STATUS" command on the Slave node and take the value of the Seconds_Behind_Master item to understand the current delay of the Slave (unit: seconds).

Tmp table status: The status of Tmp Table is mainly used to monitor whether MySQL uses too much temporary tables, and whether any temporary tables are too large and have to be swapped out from memory to disk files. Temporary table usage status information can be obtained through the following methods:
SHOW STATUS LIKE 'Created_tmp%';
+-------------------------+-- -----+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
+------------------------+-------+
If Created_tmp_tables is very If it is large, it may be that there are too many sorting operations in the system, or the table connection method is not very optimized. And if the ratio of Created_tmp_disk_tables to Created_tmp_tables is too high, such as more than 10%, then we need to consider whether the tmp_table_size system parameter is set large enough.

Binlog Cache usage status: Binlog Cache is used to store Binlog information that has not yet been written to the disk.
The relevant status variables are as follows:
SHOW STATUS LIKE 'Binlog_cache%';
The value of Binlog_cache_disk_use is not 0, which means the Binlog Cache size may not be enough, and you can increase the binlog_cache_size system parameter size.

Innodb_log_waits quantity: Innodb_log_waits status variable directly reflects the number of waits caused by insufficient space in Innodb Log Buffer.
SHOW STATUS LIKE 'Innodb_log_waits';
The frequency of occurrence of this variable value will directly affect the writing performance of the system, so when the value reaches 1 time per second, the value of the system parameter innodb_log_buffer_size should be increased. After all, this is shared by the system. If the cache is increased appropriately, it will not cause the problem of insufficient memory.

2. Detailed explanation of performance analysis commands

SHOW STATUS;
FLUSH STATUS;

View the current number of connections SHOW STATUS LIKE 'Thread_%';
Thread_cached: the number of cached threads
Thread_running: the number of activated threads
Thread_connected: the currently connected threads Number of
Thread_created: Total number of threads created

Thread cache hits
Thread_connected = SHOW GLOBAL STATUS LIKE Thread_created;
Connections = SHOW GLOBAL STATUS LIKE 'Connections';
TCH=(1 - (Threads_created / Connections) ) * 100

View active connection content
SHOW PROCESSLIST;

If the TCH number is less than 90%, it takes time to create the connection, increase the number of Thread_cached

QPS (query processing per second) MyISAM engine

Questions = SHOW GLOBAL STATUS LIKE 'Questions';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
QPS=Questions/Uptime

TPS (number of transactions transmitted per second), that is, the number of transactions processed by the server per second, if it is InnoDB Display, it will not be displayed without InnoDB.

Com_commit = SHOW GLOBAL STATUS LIKE 'Com_commit';
Com_rollback = SHOW GLOBAL STATUS LIKE 'Com_rollback';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
TPS=(Com_commit + Com_rollback)/Uptime

QPS and TPS values Be sure to monitor in real time. If you are close to the test peak when building the architecture, may God be with you

Read/Writes Ratio
Qcache_hits = SHOW GLOBAL STATUS LIKE 'Qcache_hits';
Com_select = SHOW GLOBAL STATUS LIKE 'Com_select';
Com_insert = SHOW GLOBAL STATUS LIKE 'Com_insert';
Com_update = SHOW GLOBAL STATUS LIKE 'Com_update';
Com_delete = SHOW GLOBAL STATUS LIKE 'Com_delete';
Com_replace = SHOW GLOBAL STATUS LIKE 'Com_replace';
R/W=(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100

Read-write ratio, an important basis for optimizing the database. If you read more, optimize reading, if you write more, optimize writing

Slow queries per minute
Slow_queries = SHOW GLOBAL STATUS LIKE 'Slow_queries';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
SQPM=Slow_queries / (Uptime/60)

Slow queries /Questions Ratio
Slow_queries = SHOW GLOBAL STATUS LIKE 'Slow_queries';
Questions = SHOW GLOBAL STATUS LIKE 'Questions';
S/Q=Slow_queries/Questions

Pay attention to slow queries when the new version goes online

Full_join per minute
Select_full_join = SHOW GLOBAL STATUS LIKE 'Select_full_join';
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';
FJPM=Select_full_join / (Uptime/60)

Full_join caused by not using the index, optimize the index

Innodb buffer read hits
Innodb_buffer_pool_reads = SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
Innodb_buffer_pool_read_ requests = SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
IFRH=(1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100

InnoDB Buffer hit rate target 95%-99%;

Table Cache
Open_tables= SHOW GLOBAL STATUS LIKE 'Open _tables';
Opened_tables= SHOW GLOBAL STATUS LIKE 'Opened_tables';
table_cache= SHOW GLOBAL STATUS LIKE 'table_cache';

table_cache should be larger than Open_tables and smaller than Opened_tables