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

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!

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.