MySQL database, performance monitoring _ MySQL

MySQL database, performance monitoring bitsCN.com

Show status;
Flush status;

View the current connection count show status like 'thread _ % ';
Thread_cached: Number of cached threads
Thread_running: number of activated threads
Thread_connected: number of threads currently connected
Thread_created: total number of created threads

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 instances.

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 they are close to the test peak during architecture construction, 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. optimize the read when there are many reads, and optimize the write when there are many writes.

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 released, pay attention to slow queries. 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)

Optimize the index for full_join caused by not using 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 greater than Open_tables and less than Opened_tables

Author's "ylqmf column"

BitsCN.com