mysql基础：变量 状态

在SQLyog工具中，可查看“变量”、”线程列表“、”状态“。变量、状态？

看版本：mysql -V 客户端版本 select version 服务器版本

变量与状态的介绍

类似于Oracle的参数文件，MySQL的选项文件(如my.cnf)用于配置MySQL服务器，但和Oracle叫法不一样，在MySQL里， 官方叫变量（Varialbes），但其实叫参数也是可以的，只要明白这俩是同一个东西就可以了。
MySQL的变量分为以下两种：
1）系统变量：配置MySQL服务器的运行环境，可以用show variables查看
2）状态变量：监控MySQL服务器的运行状态，可以用show status查看。
 
系统变量
系统变量按其作用域的不同可以分为以下两种：
1）分为全局（GLOBAL）级：对整个MySQL服务器有效
2）会话（SESSION或LOCAL）级：只影响当前会话
有些变量同时拥有以上两个级别，MySQL将在建立连接时用全局级变量初始化会话级变量，但一旦连接建立之后，全局级变量的改变不会影响到会话级变量。

服务器启动时，将所有全局变量初始化为默认值。可以在选项文件或命令行中指定的选项来更改这些默认值。服务器启动后，通过连接服务器并执行SET GLOBAL var_name语句可以更改动态全局变量。要想更改全局变量，必须具有SUPER权限。

服务器还为每个客户端连接维护会话变量。连接时使用相应全局变量的当前值对客户端会话变量进行初始化。客户可以通过SET SESSION var_name语句来更改动态会话变量。设置会话变量不需要特殊权限，但客户可以只更改自己的会话变量，而不更改其它客户的会话变量。

任何访问全局变量的客户端都可以看见对全局变量的更改。但是，它只影响在更改后连接的从该全局变量初始化相应会话变量的客户端。它不会影响已经连接上的客户端的会话变量(甚至是执行SET GLOBAL语句的客户端)。

查看系统变量的值
可以通过show vairables语句查看系统变量的值：
mysql> show variables like 'log%';  
mysql> show variables where Variable_name like 'log%' and value='ON';  
注意：show variables优先显示会话级变量的值，如果这个值不存在，则显示全局级变量的值，当然你也可以加上GLOBAL或SESSION关键字区别：
 

show global variables;  

show session/local variables;  
在写一些存储过程时，可能需要引用系统变量的值，可以使用如下方法：
@@GLOBAL.var_name  
@@SESSION.var_name 或  
@@LOCAL.var_name  
如果在变量名前没有级别限定符，将优先显示会话级的值。
最后一种查看变量值的方法是从INFORMATION_SCHEMA数据库里的GLOBAL_VARIABLES和SESSION_VARIABLES表获得。
  

设置和修改系统变量的值

在MySQL服务器启动时，有以下两种方法设置系统变量的值：
1）命令行参数，如：mysqld --max_connections=200
2）选项文件（my.cnf）
在MySQL服务器启动后，如果需要修改系统变量的值，可以通过SET语句：
SET GLOBAL var_name = value;  
SET @@GLOBAL.var_name = value;  
SET SESSION var_name = value;  
SET @@SESSION.var_name = value;  
如果在变量名前没有级别限定符，表示修改会话级变量。
当使用启动选项设置变量时，变量值可以使用后缀K、M或G分别表示千字节、兆字节或gigabytes。例如，下面的命令启动服务器时的键值缓冲区大小为16 megabytes：mysqld --key_buffer_size=16M
注意：和启动时不一样的是，在运行时设置的变量不允许使用后缀字母'K'、‘M'等，但可以用表达式来达到相同的效果，如：
SET GLOBAL read_buffer_size = 2*1024*1024  
 
这里一个容易把人搞蒙的地方是如果查询时使用的是show variables的话，会发现设置好像并没有生效，这是因为单纯使用show variables的话就等同于使用的是show session variables，查询的是会话变量，只有使用show global variables，查询的才是全局变量。

网络上很多人都抱怨说他们set global之后使用show variables查询没有发现改变，原因就在于混淆了会话变量和全局变量，如果仅仅想修改会话变量的话，可以使用类似set wait_timeout=10;或者set session wait_timeout=10;这样的语法。
 
状态变量
状态变量可以使我们及时了解MySQL服务器的运行状况，可以使用show status语句查看。
状态变量和相同变量类似，也分为全局级和会话级，show status也支持like匹配查询，比较大的不同是状态变量只能由MySQL服务器本身设置和修改，对于用户来说是只读的，不可以通过SET语句设置和修改它们。
 

 使用show status查看MySQL服务器状态信息

 如果需要了解MySQL的服务器状态信息，譬如当前MySQL启动后的运行时间，当前MySQL的客户端会话连接数，当前MySQL服务器执行的慢查询数，当前MySQL执行了多少SELECT语句、执行了多少UPDATE/DELETE/INSERT语句等统计信息，从而便于我们根据当前MySQL服务器的运行状态进行对应的调整或优化工作。当执行show status语句时，MySQL将会列出多达300多条的MySQL服务器状态信息记录。可以在show status语句后加上对应的like子句，实现「按需查看」，LIKE关键字也可以使用'_' 或'%'等通配符来进行模糊匹配。
SHOW [统计范围] STATUS [LIKE '状态项名称'] //统计范围关键字分为GLOBAL和SESSION(或LOCAL)两种，默认是session
--查看MySQL本次启动后的运行时间(单位：秒)
show status like 'uptime';

--查看select语句的执行数
show [global] status like 'com_select';

--查看insert语句的执行数
show [global] status like 'com_insert';

--查看update语句的执行数
show [global] status like 'com_update';

--查看delete语句的执行数
show [global] status like 'com_delete';

--查看试图连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';

--查看线程缓存内的线程的数量。
show status like 'threads_cached';

--查看当前打开的连接的数量。
show status like 'threads_connected';

--查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
show status like 'threads_created';

--查看激活的(非睡眠状态)线程数。
show status like 'threads_running';

--查看立即获得的表的锁的次数。
show status like 'table_locks_immediate';

--查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
show status like 'table_locks_waited';

--查看创建时间超过slow_launch_time秒的线程数。
show status like 'slow_launch_threads';

--查看查询时间超过long_query_time秒的查询的个数。
show status like 'slow_queries';

 

实战

安装的mysql5.6版本，执行的各项查询，由于mysql环境基本没怎么使用过，所以取值只是做参考的。

 

1、查看MySQL服务器配置信息 

mysql> show variables; 
2、查看MySQL服务器运行的各种状态值 
　mysql> show global status; 
 
3、慢查询 
 
　mysql> SHOW VARIABLES LIKE '%slow%';  

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

 

配置中关闭了记录慢查询（最好是打开，方便优化），超过2秒即为慢查询，图中无慢查询 

 

4、连接数

mysql> SHOW VARIABLES LIKE 'max_connections'; 

mysql> SHOW GLOBAL STATUS LIKE 'max_used_connections';

设置的最大连接数是100，而响应的连接数是2。max_used_connections / max_connections * 100% = 2% （理想值 ≈ 85%）

 

5、key_buffer_size

key_buffer_size是对MyISAM表性能影响最大的一个参数, 不过数据库中多为Innodb 

mysql> SHOW VARIABLES LIKE 'key_buffer_size';

mysql> SHOW GLOBAL STATUS LIKE 'key_read%';

一共有74个索引读取请求，有67个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率： 
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% 
如果此值比较小，则需要适当加大key_buffer_size 

mysql> show global status like 'key_blocks_u%'; 

Key_blocks_unused表示未使用的缓存簇(blocks)数，Key_blocks_used表示曾经用到的最大的blocks数 
Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% （理想值 ≈ 80%） 

 
6、临时表 
mysql> show global status like 'created_tmp%'; 
 

每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数： Created_tmp_disk_tables / Created_tmp_tables * 100%  （理想值<= 25%） 

mysql> SHOW VARIABLES WHERE Variable_name IN ('tmp_table_size', 'max_heap_table_size');  

判断是否需要增加tmp_table_size ？从现在结果不需要，如果上边取值是99%，则肯定需要增加。

 

7、open table 的情况 

mysql> show global status like 'open%tables%'; 

Open_tables 表示打开表的数量，Opened_tables表示打开过的表数量，如果Opened_tables数量过大，说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小，我们查询一下服务器table_cache值 

mysql> SHOW VARIABLES LIKE 'table_open_cache';  

 

Open_tables / Opened_tables * 100%     理想值 （>= 85%） 
Open_tables / table_open_cache * 100%    理想值 (<= 95%) 

 

8、进程使用情况 

mysql> show global status like 'Thread%';  

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。

如果在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置： 

 mysql> SHOW VARIABLES LIKE 'thread_cache_size';  

 

thread_cache_size 每建立一个连接，都需要一个线程来与之匹配。
thread_cache_size：用来缓存空闲的线程，以至不被销毁，如果线程缓存中有空闲线程，这时候如果建立新连接，MYSQL就会很快的响应连接请求。

最好将thread_cache_size设置成与threads_connected一样。不过很少有情况将threads_cache_size设置成比200还大的数。

 

9、查询缓存(query cache) 

mysql> SHOW GLOBAL STATUS LIKE 'qcache%';  

查询一下服务器关于query_cache的配置： 

mysql> SHOW VARIABLES LIKE 'query_cache%'; 

 

query_cache_min_res_unit默认是4KB，设置值大对大数据查询有好处，但如果查询都是小数据查询，就容易造成内存碎片和浪费。 
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果查询都是小数据量的话。 

查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100% 
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。 

查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100% 
如果，查询缓存碎片率 ＝ 20.46％，查询缓存利用率 ＝ 62.26％，查询缓存命中率 ＝ 1.94％，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。 

 

10、排序使用情况 

mysql> SHOW GLOBAL STATUS LIKE 'sort%'; 

 Sort_merge_passes 包括两步。首先会尝试在内存中做排序，使用的内存大小由系统变量 Sort_buffer_size 决定，如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等 MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上，MySQL 会用另一个临时文件来存再次排序的结果，所以通常会看到 Sort_merge_passes 增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增加 Sort_buffer_size 会减少 Sort_merge_passes 和 创建临时文件的次数。但盲目的增加 Sort_buffer_size 并不一定能提高速度。

 mysql> SHOW VARIABLES LIKE 'sort%'; 

 另外，增加read_rnd_buffer_size的值对排序的操作也有一点的好处。

 

11、文件打开数(open_files)  

mysql> SHOW GLOBAL STATUS LIKE 'open_files';

 mysql> SHOW VARIABLES LIKE 'open_files_limit';

 

比较合适的设置：Open_files / open_files_limit * 100% <= 75％  正常 

 

12、表锁情况 

mysql> SHOW GLOBAL STATUS LIKE 'table_locks%';

如果 Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些.

 

13、表扫描情况 

 mysql> SHOW GLOBAL STATUS LIKE 'handler_read%';

 

 mysql> show global status like 'com_select';  

 

 计算表扫描率： 
表扫描率 ＝ Handler_read_rnd_next / Com_select 
如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，增加read_buffer_size值会有一些好处，但最好不要超过8MB。

 

--查看MySQL本次启动后的运行时间(单位：秒)
show status like 'uptime';

--查看select语句的执行数
show [global] status like 'com_select';

--查看insert语句的执行数
show [global] status like 'com_insert';

--查看update语句的执行数
show [global] status like 'com_update';

--查看delete语句的执行数
show [global] status like 'com_delete';

--查看试图连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';

--查看线程缓存内的线程的数量。
show status like 'threads_cached';

--查看当前打开的连接的数量。
show status like 'threads_connected';

--查看当前打开的连接的数量。
show status like 'threads_connected';

--查看创建用来处理连接的线程数。如果Threads_created较大，你可能要增加thread_cache_size值。
show status like 'threads_created';

--查看激活的(非睡眠状态)线程数。
show status like 'threads_running';

--查看立即获得的表的锁的次数。
show status like 'table_locks_immediate';

--查看不能立即获得的表的锁的次数。如果该值较高，并且有性能问题，你应首先优化查询，然后拆分表或使用复制。
show status like 'table_locks_waited';

--查看创建时间超过slow_launch_time秒的线程数。
show status like 'slow_launch_threads';

--查看查询时间超过long_query_time秒的查询的个数。
show status like 'slow_queries';

 

  • show status
    • 查看select语句的执行数 show global status like ‘Com_select’;
    • 查看慢查询的个数 show global status like ‘Slow_queries’;
    • 表扫描情况 show global status like ‘Handler_read%’; Handler_read_rnd_next / com_select > 4000 需要考虑优化索引
  • show variables
    • 查看慢查询相关的配置 show variables like ‘long_query_time’;
    • 将慢查询时间线设置为2s set global long_query_time=2;
    • 查看InnoDB缓存 show variables like ‘innodb_buffer_pool_size’;
    • 查看InnoDB缓存的使用状态 show status like ‘Innodb_bufferpool%’; 缓存命中率=(1-Innodb_buffer_pool_reads/ Innodb_buffer_pool_read_requests) 100%；缓存率=（Innodb_buffer_pool_pages_data/ Innodb_buffer_pool_pages_total) 100%
    • SHOW PROFILES;该命令可以trace在整个执行过程中各资源消耗情况(会话级)
    • SHOW PROCESSLIST; 查看当前有哪些线程正在运行，并且处在何种状态
    • SHOW ENGINE INNODB STATUS; 可用于分析死锁，但需要super权限

 

参考资料：
 
posted @ 2017-04-14 17:13  milkty  阅读(327)  评论(0)    收藏  举报