AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

MySQl的几个配置项

2017-11-27 1139
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS PostgreSQL，集群系列 2核4GB
简介：
对对于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代码　15x14

  1. mysql> show variables;　

 

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

　　Java代码　15x14

  1. mysql> show global status;　

 

　　3, 慢查询

　　Java代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

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

 

　　9, 查询缓存(query cache)

　　Java代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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代码　15x14

  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/p/5162602.html，如需转载请自行联系原作者

文章标签：
云数据库 RDS MySQL 版
日志服务
缓存
Java
关系型数据库
索引
监控
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
技术小阿哥
目录
相关文章
流子
|
安全 关系型数据库 MySQL
MySQL my.cnf参数配置优化详解
MySQL my.cnf参数配置优化详解
流子
170 0
冰点.
|
关系型数据库 MySQL
mysql 服务器日志配置
底下这种配置方式我配置了之后mysql 服务无法启动 是否启用了日志 mysql>show variables like 'log_%'; 查看当前的日志 mysql> show master status; 首先你得知道mysql的日志类型： 错误日志： -log-err 查询日志： -log 慢查询日志: -log-slow-queries 更新日志: -log-update
冰点.
114 0
芝麻软件
|
缓存 关系型数据库 MySQL
通过配置Mysql参数提高写入速度
1） innodb_buffer_pool_size 如果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。
芝麻软件
1658 0
xaubllxwtvaqiu
|
关系型数据库 MySQL
[MySQL FAQ]系列 — 修改my.cnf配置不生效
[MySQL FAQ]系列 — 修改my.cnf配置不生效
xaubllxwtvaqiu
673 0
javaedge
MySQL5.7配置文件详细说明
MySQL5.7配置文件详细说明
javaedge
730 0
技术小阿哥
|
缓存 关系型数据库 MySQL
MySQL 配置文件优化
技术小阿哥
1332 0
吴梦涵
|
关系型数据库 MySQL
mysql etc下my.conf配置详情
简介 配置mysql  etc下my.conf配置详情如下 [client]port = 3306socket = /tmp/mysql.sockdefault-character-set = utf8mb4 [mysql]prompt="MySQL [\d]> "no-auto-rehash [mysqld]port = 3306socket = /tmp/mysql.
吴梦涵
1120 0
优惠码大使
|
监控 关系型数据库 MySQL
Mysql 数据库常用配置命令
Mysql 数据库常用配置命令1.查看mysql数据库默认编码:　　 　　mysql> show variables like "character%"; +--------------------------+----------------------------+ | Variable_nam.
优惠码大使
1215 0
sktj
|
关系型数据库 MySQL
mysql常用配置
[mysql] default-character-set=utf8 socket=/tmp/mysql.sock [mysqld] skip-name-resolve port = 3306 socket=/tmp/mysql.
sktj
879 0
wsc449
|
SQL 移动开发 Java
mysql到MPPDB自动刷表
代码实例 package oa.epoint.com.mysql_mpp; import java.sql.Connection; import java.
wsc449
1550 0

热门文章

最新文章

  • 1
    QSqlDatabase: QMYSQL driver not loaded 解决方案
  • 2
    这 12 个基于 AI 的 VSCode “杀手级” 生产力插件，了解一下？（一）
  • 3
    关于规则引擎的选型和疑惑思考
  • 4
    面试必问的4种单点登录的实现方式，你知道几个？
  • 5
    揭秘2017双11背后的网络－双11的网络产品和技术概览
  • 6
    我的NVIDIA开发者之旅——作为一名初学者，我是如何开启 NVIDIA Jetson Nano 开发的
  • 7
    FluentData-新型轻量级ORM 利用T4模板 批量生成多文件 实体和业务逻辑 代码
  • 8
    Hadoop-No.10之列簇
  • 9
    HDU 1213 How Many Tables
  • 10
    GitHub push文件失败时
  • 1
    js+jquery实现贪吃蛇经典小游戏
    27
  • 2
    ROS2：从初识到深入，探索机器人操作系统的进化之路
    30
  • 3
    《精准掌控时间：C++ 人工智能开发中的运行时间监测之道》
    25
  • 4
    《解锁 C++数据读写秘籍：赋能人工智能训练》
    27
  • 5
    git 常用命令
    107
  • 6
    学习Git的最佳实践有哪些？
    32
  • 7
    通义灵码开发者社区的重要性 —— 知识共享与学习
    30
  • 8
    Git 学习----基本概念
    27
  • 9
    java小工具util系列4：基础工具代码(Msg、PageResult、Response、常量、枚举)
    31
  • 10
    dllhost.exe进程占用CPU很高怎么解决?
    26

    • 相关课程

    更多
  • MySQL高级应用 - 索引和锁
  • Analytic DB MySql产品介绍
  • MySQL实战进阶
  • RDS MySQL 高效设计及性能调优
  • 数据库及SQL/MySQL基础
  • 云数据库RDS MySQL从入门到高阶

    • 相关电子书

    更多
  • MySQL 5.7让优化更轻松
  • 好的 MySQL 兼容可以做到什么程度
  • 深入MySQL实战

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 5分钟上手Flink MySQL连接器
  • 云端MySQL的安装及应用

    • 推荐镜像

    更多
  • mysql
  • mongodb
  • postgresql
    • 下一篇
    无影云桌面