MySQL常见变量

performance_schema.global_status

连接相关

threads_connected：当前打开的连接数

1
2
3
4
5
6
7


select round((P1.variable_value)/(P2.variable_value), 4) as connections,
       P1.variable_value as threads_connected,
       P2.variable_value as max_connections
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_VARIABLES P2
WHERE P1.variable_name = 'threads_connected'
  AND P2.variable_name = 'max_connections';

连接数/最大连接数比值较大，则需要调整max_connections或检查业务是否使用正常

max_used_connections：最大同时使用的连接数

1
2
3
4
5
6
7


select round((P1.variable_value)/(P2.variable_value), 4) as connections_ever_seen,
       P1.variable_value as max_used_connections,
       P2.variable_value as max_connections
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_VARIABLES P2
WHERE P1.variable_name = 'max_used_connections'
  AND P2.variable_name = 'max_connections';

如果这个值大于0.8，则mysql server出现过too many connections的错误，可以查询max_used_connections_time来获取最大连接数出现的时间

1

show status like '%max_used_connections_time%';

Connections: 尝试连接到mysql的连接数量（成功或失败）

Threads_created: 用来处理连接的线程数量，如果Threads_created较大，则可能需要修改thread_cache_size。cache miss rate 计算方式为Threads_created/Connections

1
2
3
4
5


select round((P2.variable_value / P1.variable_value), 4), P2.variable_value, P1.variable_value
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'Connections'
  AND P2.variable_name = 'Threads_created';

查询相关

handler_read_rnd_next：读表中下一行数据的请求的次数，如果这个值很高则说明很可能客户端在进行大量的表扫描，很有可能没有有效利用索引进行查询；

handler_read_rnd：读取表中固定一行的请求次数，这个数值高说明很多查询需要对result进行排序，也有全表扫描的可能性；

handler_read_first：读取全部索引的请求次数，如果这个值很高则说明mysql server正在进行低效的全索引扫描，例如select id from ids,其中id为索引

handler_read_next：通过索引读取下一行的请求数量，如果这个值很高则说明在对索引进行范围查询

handler_read_key：通过索引读取一行数据的请求数量，这个值越高说明索引越高效

handler_read_prev：读取索引前一行数据的请求数量，这个值越高说明使用order by * desc的次数越多

则整个server进行全表扫描的比例可以通过

$ full_scan_rate = \frac{(handler_read_rnd_next + handler_read_rnd)}{(handler_read_rnd_next + handler_read_rnd + handler_read_first + handler_read_next + handler_read_key + handler_read_prev)} $

(handler_read_rnd_next + handler_read_rnd) / (handler_read_rnd_next + handler_read_rnd + handler_read_first + handler_read_next + handler_read_key + handler_read_prev)

来计算

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21


select round((P2.variable_value + P1.variable_value) /
             (P2.variable_value + P1.variable_value + P3.variable_value + P4.variable_value + P5.variable_value +
              P6.variable_value), 4) as full_table_scan_rate,
       P1.variable_value             as Handler_read_rnd_next,
       P2.variable_value             as Handler_read_rnd,
       P3.variable_value             as Handler_read_first,
       P4.variable_value             as Handler_read_next,
       P5.variable_value             as Handler_read_key,
       P6.variable_value             as Handler_read_prev
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_STATUS P2,
     performance_schema.GLOBAL_STATUS P3,
     performance_schema.GLOBAL_STATUS P4,
     performance_schema.GLOBAL_STATUS P5,
     performance_schema.GLOBAL_STATUS P6
WHERE P1.variable_name = 'Handler_read_rnd_next'
  AND P2.variable_name = 'Handler_read_rnd'
  AND P3.variable_name = 'Handler_read_first'
  AND P4.variable_name = 'Handler_read_next'
  AND P5.variable_name = 'Handler_read_key'
  AND P6.variable_name = 'Handler_read_prev';

如果这个值大于25%则需要开启慢查询日志看下查询效率

select_full_join：没有使用index进行join的数量，大于0需要特别注意

1
2
3


select *
from performance_schema.GLOBAL_STATUS
where variable_name = 'select_full_join';

sort_merge_passes：使用排序的时候需要merge的part的数量，如果较大，可能需要调整sort_buffer_size和read_rnd_buffer_size

1
2
3


select *
from performance_schema.GLOBAL_STATUS
where variable_name = 'sort_merge_passes';

sort_scan: 通过扫描表完成排序的数量

sort_range: The number of sorts that were done using ranges.

通过sort_merge_passes / (sort_scan + sort_range)来衡量排序的效率

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


select round((P1.variable_value) / (P2.variable_value + P3.variable_value), 4) as sort_passes,
       P1.variable_value                                                       as sort_merge_passes,
       P2.variable_value                                                       as sort_scan,
       P3.variable_value                                                       as sort_range
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_STATUS P2,
     performance_schema.GLOBAL_STATUS P3
WHERE P1.variable_name = 'sort_merge_passes'
  AND P2.variable_name = 'sort_scan'
  AND P3.variable_name = 'sort_range';

innodb_buffer_pool

innodb_buffer_pool_read_requests: 读请求数

innodb_buffer_pool_reads: 需要直接从磁盘中读的请求的数量

innodb_buffer_pool命中率:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


select round((1 - P2.variable_value / P1.variable_value), 4), P2.variable_value, P1.variable_value
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
  AND P2.variable_name = 'innodb_buffer_pool_reads';
  
select pool_id,hit_rate,pages_made_young,pages_not_made_young from information_schema.innodb_buffer_pool_stats\G;
# 查询lru的状态
select table_name,space,page_number,page_type from information_schema.innodb_buffer_page_lru where space=1;
select table_name,space,page_number,page_type from information_schema.innodb_buffer_page_lru where oldest_modification>0;

innodb_buffer_pool_pages_dirty: buffer pool中脏页（dirty page：在内存中更新但是没有还没有写到datafile中的页；clean page：内存中的数据和data file中的数据一致的页）的数量

innodb_buffer_pool_pages_total: buffer pool中页的总数

衡量innodb flush的速率，如果是读少写多的库，则对应的rate会变大

1
2
3
4
5
6
7


select round((P1.variable_value)/(P2.variable_value), 4) as dirty_page,
       P1.variable_value as innodb_buffer_pool_pages_dirty,
       P2.variable_value as innodb_buffer_pool_pages_total
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_pages_dirty'
  AND P2.variable_name = 'innodb_buffer_pool_pages_total';

如果dirty_page的比例大于75%且持续时间很长，则可能需要调整innodb_buffer_pool_size或者换一个好一点的磁盘

innodb_buffer_pool_wait_free：通常情况下，innodb写buffer pool发生是后台线程完成的，但是如果innodb需要读或者创建页的时候没有clean page可用，则innodb需要先将也写dirty page刷新到data file中并等待刷新操作完成，innodb_buffer_pool_wait_free这个表明正在等待flush操作的线程数，如果innodb_buffer_pool_size设置得当，则这个数值会很低；

innodb_buffer_pool_write_requests：Innodb写完成的数量

衡量innodb等待刷新脏页数据的比例：

1
2
3
4
5
6


select round((P1.variable_value)/(P2.variable_value), 4) as dirty_page,
       P1.variable_value as innodb_buffer_pool_wait_free,
       P2.variable_value as innodb_buffer_pool_write_requests
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_wait_free'  AND P2.variable_name = 'innodb_buffer_pool_write_requests';

这个数据太大则表明innodb_buffer_pool_size太小。小于1一般是正常数据

innodb_log_waits：log buffer（缓存需要写redo log的内存区域）需要等待flush操作的次数

innodb_log_writes：写redo log的次数

Innodb等待redolog写入的概率：

1
2
3
4
5
6


select round((P1.variable_value)/(P2.variable_value), 4) as redolog_wait,
       P1.variable_value as innodb_log_waits,
       P2.variable_value as innodb_log_writes
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_log_waits'  AND P2.variable_name = 'innodb_log_writes';

如果这个数值大于1，则需要调整innodb_log_buffer_size

opened_tables：table open number

table_cache_usage=opened_tables/table_open_cache

1
2
3
4
5
6


select round((P1.variable_value)/(P2.variable_value), 4) as table_cache_usage,
       P1.variable_value as opened_tables,
       P2.variable_value as table_open_cache
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_VARIABLES P2
WHERE P1.variable_name = 'opened_tables'  AND P2.variable_name = 'table_open_cache';

小于1需要调整table_open_cache

Created_tmp_disk_tables：mysql server在磁盘上创建临时表的sql数量

Created_tmp_files：mysqld创建的tmp文件的数量

Created_tmp_tables：执行sql的时候创建临时表的数量

mysql server创建临时文件的概率

1
2
3
4
5
6


select round((P1.variable_value)/(P2.variable_value), 4) as tmp_table_disk_usage,
       P1.variable_value as Created_tmp_disk_tables,
       P2.variable_value as Created_tmp_tables
from performance_schema.GLOBAL_STATUS P1,
     performance_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'Created_tmp_disk_tables'  AND P2.variable_name = 'Created_tmp_tables';

一些SQL

查看非sleep的线程

1

SELECT * FROM information_schema.processlist WHERE command <> 'sleep';

Contents

performance_schema.global_status

连接相关

查询相关

innodb_buffer_pool

一些SQL