Performance Monitoring method of MySQL Database
This article mainly explains "the performance monitoring method of MySQL database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "the performance monitoring method of MySQL database".
SHOW STATUS
FLUSH STATUS
View the current number of connections SHOW STATUS LIKE 'Thread_%'
Thread_cached: the number of threads cached
Thread_running: number of threads in the active state
Thread_connected: the number of threads currently connected
Thread_created: the 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 number of TCH is less than 90%, it takes time to create a connection and increase the number of Thread_cached.
QPS
Questions = SHOW GLOBAL STATUS LIKE 'Questions'
Uptime = SHOW GLOBAL STATUS LIKE 'Uptime'
QPS=Questions/Uptime
TPS
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 must be monitored in real time. If you approach the testing peak when the architecture is built, 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 Qcache_hits W = (Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) * 100
The ratio of reading to writing is an important basis for optimization. If you read more, optimize reading and 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
When the new version is launched, we should focus on slow queries and let the test kick the developer's ass.
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% Mak 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 greater than Open_tables and less than Opened_tables
At this point, I believe that everyone on the "MySQL database performance monitoring methods" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!
