MySQL常用的性能指标

(1) QPS(每秒Query量)

QPS = Questions(or Queries) / seconds

mysql > show global status like 'Question%';

(2) TPS(每秒事务量)

TPS = (Com_commit + Com_rollback) / seconds

mysql > show global status like'Com_commit';

mysql > show global status like'Com_rollback';

(3)key Buffer 命中率

mysql>show global status like 'key%';

key_buffer_read_hits = (1-key_reads /key_read_requests) * 100%

key_buffer_write_hits = (1-key_writes /key_write_requests) * 100%

(4)InnoDB Buffer命中率

mysql> show status like'innodb_buffer_pool_read%';

innodb_buffer_read_hits = (1 -innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%

(5)Query Cache命中率

mysql> show status like 'Qcache%';

Query_cache_hits = (Qcahce_hits /(Qcache_hits + Qcache_inserts )) * 100%;

(6)Table Cache状态量

mysql> show global status like 'open%';

比较 open_tables 与opend_tables 值

(7)Thread Cache 命中率

mysql> show global status like'Thread%';

mysql> show global status like'Connections';

Thread_cache_hits = (1 - Threads_created /connections ) * 100%

(8)锁定状态

mysql> show global status like '%lock%';

Table_locks_wAIted/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重

Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的

(9)复制延时量

mysql > show slave status

查看延时时间

(10) Tmp Table 状况(临时表状况)

mysql > show status like 'Create_tmp%';

Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，

可能是排序句子过多或者是连接句子不够优化

(11) Binlog Cache 使用状况

mysql > show status like'Binlog_cache%';

如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小

(12) Innodb_log_waits 量

mysql > show status like'innodb_log_waits';

Innodb_log_waits值不等于0的话，表明 innodblog buffer 因为空间不足而等待

(13)open file and table

mysql> mysql -e "show global statuslike 'Open_files';

mysql> mysql -e "show global statuslike 'Open_tables';

(14) 慢查询

开启慢查询：

编辑/etc/my.cnf，在[mysqld]域中添加：

slow_query_log= 1 # 开启慢查询

slow_query_log_file=/data/mysql/slow.log # 慢查询日志路径

long_query_time= 1 # 慢查询的时长

查看慢查询数量：

执行mysqladminstatus

(15)全日志

查看全日志：

show global variables like 'general_log';

开启全日志：

set global general_log=on;

注意开启全日志会消耗服务器性能，一般只有在排查问题时才会短暂打开。

########### 分割线 ###########

我们需要根据自己的情况进行配置优化，好的做法是MySQL服务器稳定运行了一段时间后，根据服务器的”状态”再进行优化。

数据库性能状态

QueryCache命中率

Query_cache_hits=(Qcahce_hits/(Qcache_hits+Qcache_inserts))*100%;

show status like 'Qcache%';

• QPS(每秒Query量)

QPS=Questions(orQueries)/seconds

show global status like 'Question%';

• TPS(每秒事务量)

TPS=(Com_commit+Com_rollback)/seconds

show status like 'Com_commit';

show status like 'Com_rollback';

(3)keyBuffer命中率

key_buffer_read_hits=(1-key_reads/key_read_requests)*100%

key_buffer_write_hits=(1-key_writes/key_write_requests)*100%

show status like 'key%';

(4)InnoDBBuffer命中率

innodb_buffer_read_hits=(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)*100%

show status like 'innodb_buffer_pool_read%';

(5)QueryCache命中率

Query_cache_hits=(Qcahce_hits/(Qcache_hits+Qcache_inserts))*100%;

show status like 'Qcache%';

(6)TableCache状态量

show status like 'open%';

(7)ThreadCache命中率

Thread_cache_hits=(1-Threads_created/connections)*100%

show status like 'Thread%';

show status like 'Connections';

(8)锁定状态

show status like '%lock%';

(9)复制延时量

show slave status;

(10)TmpTable状况(临时表状况)

show status like 'Created_tmp%';

(11)BinlogCache使用状况

show status like 'Binlog_cache%';

(12)Innodb_log_waits量

show status like 'innodb_log_waits';

MySQL5.7的新特性之一就是sys库，sys库完成MySQL信息的收集

一、用户、连接类

1、查看每个客户端IP过来的连接消耗资源情况。

select * from sys.host_summary;

total_connections:

current_connections:当前连接数

statement_latency:声明延迟

statement_avg_latency:声明平均延迟

table_scans:扫描查询的总次数

file_IOS:

file_io_latency:

current_memory:

total_memory_allocated:

unique_users:

2、查看每个用户消耗资源情况

select * from sys.user_summary;

user:

statements:

statement_latency:

statement_avg_latency:

table_scans:

file_ios:

file_io_latency:

current_connections:

total_connections:

unique_hosts:

current_memory:

total_memory_allocated:

3、查看当前连接情况（有多少连接就应该有多少行）

select host,current_connections,statements from sys.host_summary;

4、查看当前正在执行的SQL

select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session

执行show full processlist的结果

二、SQL 和io类

1、查看发生IO请求前5名的文件。

select * from sys.io_global_by_file_by_bytes order by total desc limit 5;

三、buffer pool 、内存

1、查看总共分配了多少内存

select * from sys.memory_global_total;

select * from sys.memory_global_by_current_bytes;

2、每个库（database）占用多少buffer pool

select * from sys.innodb_buffer_stats_by_schema order by allocated desc;

pages是指在buffer pool中的page数量；

pages_old指在LUR 列表中处于后37%位置的page。

当出现buffer page不够用时，就会征用这些page所占的空间。

37%是默认位置，具体可以自定义。

3、统计每张表具体在InnoDB中具体的情况，比如占多少页？

注意和前面的pages的总数都是相等的，也可以借用sum（pages）运算验证一下。

select * from sys.innodb_buffer_stats_by_table;

4、查询每个连接分配了多少内存

利用session表和
memory_by_thread_by_current_bytes分配表进行关联查询。

SELECT b.USER, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated, current_statement FROM sys.memory_by_thread_by_current_bytes a, sys.SESSION b WHERE a.thread_id = b.thd_id;

四、字段、索引、锁

1、查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考。

select * from sys.schema_auto_increment_columns;

2、MySQL索引使用情况统计

select * from sys.schema_index_statistics order by rows_selected desc;

3、MySQL中有哪些冗余索引和无用索引

若库中展示没有冗余索引，则没有数据；当有联合索引idx_abc(a,b,c)和idx_a(a)，那么idx_a就算冗余索引了。

select * from sys.schema_redundant_indexes;

4、查看INNODB 锁信息

在未来的版本将被移除，可以采用其他方式

select * from sys.innodb_lock_waits

5、查看库级别的锁信息，这个需要先打开MDL锁的监控：

--打开MDL锁监控

update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';

select * from sys.schema_table_lock_waits;

五、线程类

1、MySQL内部有多个线程在运行，线程类型及数量

select user,count(*) from sys.`processlist` group by user;

六、主键自增

查看MySQL自增id的使用情况

SELECT table_schema, table_name, ENGINE, Auto_increment FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA", "MYSQL", "SYS" )

一、对数据库服务可用性进行监控

思路：

1.1 通过测试账号ping命令返回的信息判断数据库可以通过网络连接

/usr/bin/mysqladmin -uroot -p123456 ping

mysqld is alive

1.2 确认数据库是否可读写

a.检查数据库的read_only参数是否为off

mysql -uroot -p123456 -P3306 -e "show global variables like 'read_only'" | grep read_only

read_only OFF

b.执行简单的数据库查询，如：select @@version;

mysql -uroot -p123456 -P3306 -e "select @@version"

二、对数据库性能进行监控

2.1 监控数据库连接数可用性

a.数据库最大连接数

mysql -uroot -p123456 -e "show variables like 'max_connections'"

b.数据库当前打开的连接数

mysqladmin -uroot -p123456 extended-status | grep -w "Threads_connected"

注：如何计算当前打开的连接数占用最大连接数的比例呢？

result=
Threads_connected/max_connections,在做监控报警或可视化监控时能够很好的根据这个比例及时调整最大连接数。

2.2 数据库性能监控

a.QPS:每秒的查询数

QPS计算方法

Questions = SHOW GLOBAL STATUS LIKE 'Questions';

Uptime = SHOW GLOBAL STATUS LIKE 'Uptime';

QPS=Questions/Uptime

b.TPS:每秒的事物量（commit与rollback的之和）

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

2.3 数据库并发请求数量

SHOW GLOBAL STATUS LIKE 'Threads_running';

注：并发请求数量通常会远小于同一时间内连接到数据库的连接数量。

2.4 监控innodb阻塞情况

a. innodb

三、对主从复制进行监控

3.1 主从复制链路状态的监控

3.2 主从复制延迟时间的监控

3.3 定期确认主从复制的数据是否一致