参考:http://blog.youkuaiyun.com/ylqmf/article/details/6834897


查看mysql数据库的状态

show status;


刷新状态

flush status;


查看活动连接内容
SHOW PROCESSLIST;


查看当前连接数 SHOW STATUS LIKE 'Threads_%';
Threads_cached:被缓存的线程的个数
Threads_running：处于激活状态的线程的个数
Threads_connected：当前连接的线程的个数
Threads_created：总共被创建的线程的个数


线程缓存命中率TCH（Thread cache hits）

Thread_connected = SHOW GLOBAL STATUS LIKE 'Threads_created';
Connections = SHOW GLOBAL STATUS LIKE 'Connections';
TCH=(1 - (Threads_created / Connections)) * 100
如果 TCH数小于90%,创建连接耗费了时间,增大Thread_cached数量



每秒查询率QPS（Query Per Seconds）
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


读写率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


每分钟的慢查询SQPM(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 


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,优化索引去吧

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命中率 目标 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应该大于 Open_tables 小于 Opened_tables 


Temp tables to Disk ratio

Created_tmp_tables = show global status like 'Created_tmp_tables';
Created_tmp_disk_tables = show global status like 'Created_tmp_disk_tables';

TDR=(Created_tmp_disk_tables/Created_tmp_tables)*100


SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_%';

  • Innodb_row_lock_current_waits

    The number of row locks currently being waited for.

  • Innodb_row_lock_time

    The total time spent in acquiring row locks, in milliseconds.

  • Innodb_row_lock_time_avg

    The average time to acquire a row lock, in milliseconds.

  • Innodb_row_lock_time_max

    The maximum time to acquire a row lock, in milliseconds. 

  • Innodb_row_lock_waits

    The number of times a row lock had to be waited for.


PS:主从

1、问题：主从复制不止何故停止了，我该怎么办？

答案：复制错误多半是因为日志错误引起的，所以首先要搞清楚是主日志错误还是中继日志错误，从错误信息里一般就能判断，如果不能可以使用类似下面的mysqlbinlog命令：

shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null

shell> mysqlbinlog <SLAVE_BINLOG_FILE> > /dev/null

如果没有错误，则不会有任何输出，反之如果有错误，则会显示出来。

如果是主日志错误，则需要在从服务器使用SET GLOBAL sql_slave_skip_counter，如下：

mysql> SET GLOBAL sql_slave_skip_counter = 1;

mysql> START SLAVE;

注：如果有多个错误，可能需要执行多次（提醒：主从服务器数据可能因此不一致）。

如果是中继日志错误，只要在从服务器使用SHOW SLAVE STATUS结果中的日志信息重新CHANGE MASTER TO即可，系统会抛弃当前的中继日志，重新下载：

mysql> CHANGE MASTER TO

MASTER_LOG_FILE='<Relay_Master_Log_File>',

MASTER_LOG_POS=<Exec_Master_Log_Pos>;

mysql> START SLAVE;

至于为什么使用的是Relay_Master_Log_File & Exec_Master_Log_Pos，参见概述。


2、问题：主服务器宕机了，如何把从服务器提升会主服务器？

答案：在一主多从的环境总，需选择数据最新的从服务器做新的主服务器。如下图所示：

提升从服务器为主服务器

在 一主（Server1）两从（Server2，、Server3）的环境中，Server1宕机后，等到Server1和Server2把宕机前同步到的 日志都执行完，比较Master_Log_File和Read_Master_Log_Pos就可以判断出谁快谁慢，因为Server2从 Server1同步的数据（1582）比Server3从Server1同步的数据（1493）新，所以应该提升Server2为新的主服务器，那么 Server3在CHANGE MASTER TO到Server2的时候应该使用什么样的参数呢？1582-1493=89，而Server2的最后的二进制日志位置是8167，所以答案是 8167-89=8078。