MySQl的几个配置项

对对于MySQL的日志功能，我们可以完全自己控制到底写还是不写。一般来说，binlog我们一般会开启，而对于慢查询我们一般会在开发的时候调试和观 察SQL语句的执行速度。但今天发现一个问题。在使用show variables like 'log%';查看slow日志是否开启时，发现为OFF,使用set global log_low_queries=on;成功了。而使用show variales like 'long%'时发现是10，于是想把它修改成1，照葫芦画瓢使用set global long_query_time=1,再一次查询发现还是10。

 

mysql> show variables like '%quer%'; 

mysql> show variables like '%long_query_time%';

mysql> set global long_query_time=100;

 

将MySQL慢查询日志写入表的的设置为。set global log_output='TABLE';

和慢查询相关的还有一个参数是log_queries_not_using_indexes;用来设置是否记录没有使用索引的查询。开启该查询后，如果你的日志是记录到表中的，那么你每次显示慢日志语句的时候，从该表中的查询记录也会被添加进行。

如果日志是写在文件中。用mysqldumpslow工具可以对慢查询日志文件进行浏览。

 

通过show variables like xxx 详解mysql运行时参数

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

　　Java代码　

  1. mysql> show variables;　

 

　　2, 查看MySQL服务器运行的各种状态值

　　Java代码　

  1. mysql> show global status;　

 

　　3, 慢查询

　　Java代码　

  1. mysql> show variables like '%slow%';　
  2. +------------------+-------+　
  3. | Variable_name　　| Value |　
  4. +------------------+-------+　
  5. | log_slow_queries | OFF　 |　
  6. | slow_launch_time | 2　　 |　
  7. +------------------+-------+　
  8. mysql> show global status like '%slow%';　
  9. +---------------------+-------+　
  10. | Variable_name　　　 | Value |　
  11. +---------------------+-------+　
  12. | Slow_launch_threads | 0　　 |　
  13. | Slow_queries　　　　| 279　 |　
  14. +---------------------+-------+　

 

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

4, 连接数 

　　Java代码　

  1. mysql> show variables like 'max_connections';　
  2. +-----------------+-------+　
  3. | Variable_name　 | Value |　
  4. +-----------------+-------+　
  5. | max_connections | 500　 |　
  6. +-----------------+-------+　
  7. mysql> show global status like 'max_used_connections';　
  8. +----------------------+-------+　
  9. | Variable_name　　　　| Value |　
  10. +----------------------+-------+　
  11. | Max_used_connections | 498　 |　
  12. +----------------------+-------+　

 

　　设置的最大连接数是500，而响应的连接数是498 

max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%） 

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

　　Java代码　

  1. mysql> show variables like 'key_buffer_size';　
  2. +-----------------+----------+　
  3. | Variable_name　 | Value　　|　
  4. +-----------------+----------+　
  5. | key_buffer_size | 67108864 |　
  6. +-----------------+----------+　
  7. mysql> show global status like 'key_read%';　
  8. +-------------------+----------+　
  9. | Variable_name　　 | Value　　|　
  10. +-------------------+----------+　
  11. | Key_read_requests | 25629497 |　
  12. | Key_reads　　　　 | 66071　　|　
  13. +-------------------+----------+　

 

　　一共有25629497个索引读取请求，有66071个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率： 
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =0.27% 
需要适当加大key_buffer_size 

　　Java代码　

  1. mysql> show global status like 'key_blocks_u%';　
  2. +-------------------+-------+　
  3. | Variable_name　　 | Value |　
  4. +-------------------+-------+　
  5. | Key_blocks_unused | 10285 |　
  6. | Key_blocks_used　 | 47705 |　
  7. +-------------------+-------+　

 

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

6， 临时表 

　　Java代码　

  1. mysql> show global status like 'created_tmp%';　
  2. +-------------------------+---------+　
  3. | Variable_name　　　　　 | Value　 |　
  4. +-------------------------+---------+　
  5. | Created_tmp_disk_tables | 4184337 |　
  6. | Created_tmp_files　　　 | 4124　　|　
  7. | Created_tmp_tables　　　| 4215028 |　
  8. +-------------------------+---------+　

 

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

　　Java代码　

  1. mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');　
  2. +---------------------+-----------+　
  3. | Variable_name　　　 | Value　　 |　
  4. +---------------------+-----------+　
  5. | max_heap_table_size | 134217728 |　
  6. | tmp_table_size　　　| 134217728 |　
  7. +---------------------+-----------+　

 

　　需要增加tmp_table_size 

7,open table 的情况

　　Java代码　

  1. mysql> show global status like 'open%tables%';　
  2. +---------------+-------+　
  3. | Variable_name | Value |　
  4. +---------------+-------+　
  5. | Open_tables　 | 1024　|　
  6. | Opened_tables | 1465　|　
  7. +---------------+-------+　

 

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

　　Java代码　

  1. mysql> show variables like 'table_cache';　
  2. +---------------+-------+　
  3. | Variable_name | Value |　
  4. +---------------+-------+　
  5. | table_cache　 | 1024　|　
  6. +---------------+-------+　

 

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

8, 进程使用情况

　　Java代码　

  1. mysql> show global status like 'Thread%';　
  2. +-------------------+-------+　
  3. | Variable_name　　 | Value |　
  4. +-------------------+-------+　
  5. | Threads_cached　　| 31　　|　
  6. | Threads_connected | 239　 |　
  7. | Threads_created　 | 2914　|　
  8. | Threads_running　 | 4　　 |　
  9. +-------------------+-------+　

 

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

　　Java代码　

  1. mysql> show variables like 'thread_cache_size';　
  2. +-------------------+-------+　
  3. | Variable_name　　 | Value |　
  4. +-------------------+-------+　
  5. | thread_cache_size | 32　　|　
  6. +-------------------+-------+　

 

　　9, 查询缓存(query cache)

　　Java代码　

  1. mysql> show global status like 'qcache%';　
  2. +-------------------------+----------+　
  3. | Variable_name　　　　　 | Value　　|　
  4. +-------------------------+----------+　
  5. | Qcache_free_blocks　　　| 2226　　 |　
  6. | Qcache_free_memory　　　| 10794944 |　
  7. | Qcache_hits　　　　　　 | 5385458　|　
  8. | Qcache_inserts　　　　　| 1806301　|　
  9. | Qcache_lowmem_prunes　　| 433101　 |　
  10. | Qcache_not_cached　　　 | 4429464　|　
  11. | Qcache_queries_in_cache | 7168　　 |　
  12. | Qcache_total_blocks　　 | 16820　　|　
  13. +-------------------------+----------+　

 

　　Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。 
Qcache_free_memory：缓存中的空闲内存。 
Qcache_hits：每次查询在缓存中命中时就增大 
Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。 
Qcache_lowmem_prunes： 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存 很少。（上面的　　　　 free_blocks和free_memory可以告诉您属于哪种情况） 
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。 
Qcache_queries_in_cache：当前缓存的查询（和响应）的数量。 
Qcache_total_blocks：缓存中块的数量。 

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

　　Java代码　

  1. mysql> show variables like 'query_cache%';　
  2. +------------------------------+----------+　
  3. | Variable_name　　　　　　　　| Value　　|　
  4. +------------------------------+----------+　
  5. | query_cache_limit　　　　　　| 33554432 |　
  6. | query_cache_min_res_unit　　 | 4096　　 |　
  7. | query_cache_size　　　　　　 | 33554432 |　
  8. | query_cache_type　　　　　　 | ON　　　 |　
  9. | query_cache_wlock_invalidate | OFF　　　|　
  10. +------------------------------+----------+　

 

　　各字段的解释： 

query_cache_limit：超过此大小的查询将不缓存 
query_cache_min_res_unit：缓存块的最小大小 
query_cache_size：查询缓存大小 
query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询 
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回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,排序使用情况 

　　Java代码　

  1. mysql> show global status like 'sort%';　
  2. +-------------------+----------+　
  3. | Variable_name　　 | Value　　|　
  4. +-------------------+----------+　
  5. | Sort_merge_passes | 2136　　 |　
  6. | Sort_range　　　　| 81888　　|　
  7. | Sort_rows　　　　 | 35918141 |　
  8. | Sort_scan　　　　 | 55269　　|　
  9. +-------------------+----------+　

 

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

另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的好处，参见：

11.文件打开数(open_files) 

　　Java代码　

  1. mysql> show global status like 'open_files';　
  2. +---------------+-------+　
  3. | Variable_name | Value |　
  4. +---------------+-------+　
  5. | Open_files　　| 821　 |　
  6. +---------------+-------+　
  7. mysql> show variables like 'open_files_limit';　
  8. +------------------+-------+　
  9. | Variable_name　　| Value |　
  10. +------------------+-------+　
  11. | open_files_limit | 65535 |　
  12. +------------------+-------+　

 

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

正常 

12。 表锁情况

　　Java代码　

  1. mysql> show global status like 'table_locks%';　
  2. +-----------------------+---------+　
  3. | Variable_name　　　　 | Value　 |　
  4. +-----------------------+---------+　
  5. | Table_locks_immediate | 4257944 |　
  6. | Table_locks_waited　　| 25182　 |　
  7. +-----------------------+---------+　

 

　　Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，如果 Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些. 

13. 表扫描情况

　　Java代码　

  1. mysql> show global status like 'handler_read%';　
  2. +-----------------------+-----------+　
  3. | Variable_name　　　　 | Value　　 |　
  4. +-----------------------+-----------+　
  5. | Handler_read_first　　| 108763　　|　
  6. | Handler_read_key　　　| 92813521　|　
  7. | Handler_read_next　　 | 486650793 |　
  8. | Handler_read_prev　　 | 688726　　|　
  9. | Handler_read_rnd　　　| 9321362　 |　
  10. | Handler_read_rnd_next | 153086384 |　
  11. +-----------------------+-----------+　

 

　　各字段解释参见，调出服务器完成的查询请求次数：

　　Java代码　

  1. mysql> show global status like 'com_select';　
  2. +---------------+---------+　
  3. | Variable_name | Value　 |　
  4. +---------------+---------+　
  5. | Com_select　　| 2693147 |　
  6. +---------------+---------+　

 

　　计算表扫描率： 

表扫描率 ＝ Handler_read_rnd_next / Com_select 

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

 

转载请注明出处：http://www.cnblogs.com/haochuang/ 8年IT工作经验,5年测试技术与管理,2年产品与项目管理,曾参与过云计算\云存储\车联网产品研发工作； 业余自媒体人,有技术类垂直微信公众号；如有招聘或求职方面需求,请Mail to uetest@qq.com ；或通过 QQ：363573922 微博：@念槐聚 联系；

时间： 2024-11-08 20:24:29

MySQl的几个配置项的相关文章

centos下利用rsyslog+mysql+loganalyzer部署日志服务器

rsyslog简介: 在CentOS上rsyslog服务专门负责记录系统日志信息(更早的版本系统使用的是syslog,rsyslog是syslog的下一代版本),rsyslog有三部分组成:syslogd,klogd,logrotate syslogd主要记录系统与网络等服务的日志信息: klogd主要记录内核产生的各项信息: logrotate主要用来对日志文件进行切割循环记录: mysql简介: MySQL是一个关系型数据库管理系统,在 WEB 应用方面MySQL是最好的 RDBMS (Re

这些MySQL配置“修改条令”，你有必要熟识默记！

大多数开发者可能不太会关注MySQL的配置,毕竟在基本配置没有问题的情况下,把更多的精力放在schema设计.索引优化和SQL优化上,是非常务实的策略.这时,如果再花力气去优化配置项,获得的收益通常都比较小.更多的时候,基于安全因素的考量,普通开发者很少能够接触到生产环境的MySQL配置.正是这样,导致开发者(包括我)对MySQL的配置不甚了解,希望本文能帮你更好地了解MySQL配置. 如果让你在某种环境上安装配置MySQL,你会怎么做?安装后,直接copy修改示例配置文件,应该是大多数人的做法

Mysql 11配置项

配置文件一般在/etc/my.cnf或者是/etc/mysql/my.cnf 语法:properties的语法,注意单词用-或_分割比如: auto-increment-offset=5 分个类 按照作用域,可以分为全局的也就是配置在配置文件中的,或者是会话的,就是连接中的 还可分为动态和静态的. 动态就是在运行期间可以改写的,这个要特别小心,因为有可能造成全局刷新和阻塞 语法: SHOW variables like '%sort_buffer_size%'; //全局的: SET GLOBA

[MySQL 5.6] Performance Schema 之 PS配置项（1）

尽管Performance Schema(以下简称PS)在5.5中已经出现,但一直没有使用过,并且相比5.6,5.5的PS表要少很多. 以下从一个初学者的角度,阅读PS的官方文档,做一些简单的笔记 官方文档见:http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html 目录:  1.开启PS 2.配置PS 2.1 setup_timers表决定了不同的instrument使用的timer类型 2.2setup_instrument

影响LIMIT子句使用的一个mysql配置项

select FOUND_ROWS()总是返回上一次select所返回的行数.它的结果受到上一次select中是否使用SQL_CALC_FOUND_ROWS的 影响测试如下: 1.运行语句:select SQL_CALC_FOUND_ROWS * from penalties where amount>=20 limit 2 然后运行:select FOUND_ROWS();返回的行数是:4 2.不使用SQL_CALC_FOUND_ROWSselect * from penalties wher

Sequelize 和 MySQL 对照

如果你觉得Sequelize的文档有点多.杂,不方便看,可以看看这篇. 在使用NodeJS来关系型操作数据库时,为了方便,通常都会选择一个合适的ORM(Object Relationship Model)框架.毕竟直接操作SQL比较繁琐,通过ORM框架,我们可以使用面向对象的方式来操作表.NodeJS社区有很多的ORM框架,我比较喜欢Sequelize,它功能丰富,可以非常方便的进行连表查询. 这篇文章我们就来看看,Sequelize是如何在SQL之上进行抽象.封装,从而提高开发效率的. 安装

Mysql第七天 查询优化2

接上文,其他的一些Mysql对于查询的优化,或者Mysql的不足,我们应该注意怎样优化. 关联子查询 Mysql的子查询实现比较有问题, 特别是对于IN(子查询),这样的方式. 比如:查询一个订单表中,所有支付方式为4也就是京东网银钱包的订单: EXPLAIN SELECT * FROM virtual_order vo WHERE vo.jd_order_id IN (SELECT jd_order_id FROM biz_pay_task p WHERE p.pay_type=4); //

Mysql 第十日 字符集，XA事务，查询缓存

字符集和校对 客户端和服务器设置要要保持一致. 校对规则主要是mysql用来比较字符串,比如按照大小写敏感,或者是二进制. 分别对应了cs,ci(不敏感),bin三个对应项 可以指定校对规则对字段排序,但是这样可能会不使用原来的索引. 分布式事务 XA事务需要一个事务协调器来保证所有的事务参与者都完成了准备工作(第一阶段). 都准备好,则提交所有事务(第二阶段). Mysql不能扮演这个协调者,只能是参与节点. 内部XA 平衡多个存储引擎, 以及存储引擎和写二进制日志之间. 如果打开了写二进制日

UNIX设置MySql数据同步 实现复制功能

mysql从3.23.15版本以后提供数据库复制功能.利用该功能可以实现两个数据库同步,主从模式,互相备份模式的功能. 数据库同步复制功能的设置都在mysql的设置文件中体现.mysql的配置文件(一般是my.cnf) 在unix环境下在/etc/mysql/my.cnf 或者在mysql用户的home目录下面的my.cnf. window环境中,如果c:根目录下有my.cnf文件则取该配置文件.当运行mysql的winmysqladmin.exe工具时候,该工具会把 c:根目录下的my.cnf