【转载】经过show variables like xxx 详解mysql运行时参数

2024-05-05 21:48
文章标签 参数 mysql variables 运行 详解 转载 xxx like database show

本文主要是介绍【转载】经过show variables like xxx 详解mysql运行时参数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

原文地址:http://www.111cn.net/database/mysql/55482.htm

通过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。

　　----------------------------------------------


这篇关于【转载】经过show variables like xxx 详解mysql运行时参数的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/962758。

相关文章

C++ move 的作用详解及陷阱最佳实践

C++ move 的作用详解及陷阱最佳实践

《C++move的作用详解及陷阱最佳实践》文章详细介绍了C++中的`std::move`函数的作用,包括为什么需要它、它的本质、典型使用场景、以及一些常见陷阱和最佳实践,感兴趣的朋友跟随小编一起看... 目录C++ move 的作用详解一、一句话总结二、为什么需要 move？C++98/03 的痛点⚡C++
阅读更多...
MySQL字符串转数值的方法全解析

MySQL字符串转数值的方法全解析

《MySQL字符串转数值的方法全解析》在MySQL开发中,字符串与数值的转换是高频操作,本文从隐式转换原理、显式转换方法、典型场景案例、风险防控四个维度系统梳理,助您精准掌握这一核心技能,需要的朋友可... 目录一、隐式转换：自动但需警惕的&ld编程quo;双刃剑”二、显式转换：三大核心方法详解三、典型场景
阅读更多...
MySQL中between and的基本用法、范围查询示例详解

MySQL中between and的基本用法、范围查询示例详解

《MySQL中betweenand的基本用法、范围查询示例详解》BETWEENAND操作符在MySQL中用于选择在两个值之间的数据,包括边界值,它支持数值和日期类型,示例展示了如何使用BETWEEN... 目录一、between and语法二、使用示例2.1、betwphpeen and数值查询2.2、be
阅读更多...
python中的flask_sqlalchemy的使用及示例详解

python中的flask_sqlalchemy的使用及示例详解

《python中的flask_sqlalchemy的使用及示例详解》文章主要介绍了在使用SQLAlchemy创建模型实例时,通过元类动态创建实例的方式,并说明了如何在实例化时执行__init__方法,... 目录@orm.reconstructorSQLAlchemy的回滚关联其他模型数据库基本操作将数据添
阅读更多...
Java中ArrayList与顺序表示例详解

Java中ArrayList与顺序表示例详解

《Java中ArrayList与顺序表示例详解》顺序表是在计算机内存中以数组的形式保存的线性表,是指用一组地址连续的存储单元依次存储数据元素的线性结构,：本文主要介绍Java中ArrayList与... 目录前言一、Java集合框架核心接口与分类ArrayList二、顺序表数据结构中的顺序表三、常用代码手动
阅读更多...
MySQL快速复制一张表的四种核心方法(包括表结构和数据)

MySQL快速复制一张表的四种核心方法(包括表结构和数据)

《MySQL快速复制一张表的四种核心方法(包括表结构和数据)》本文详细介绍了四种复制MySQL表（结构+数据）的方法,并对每种方法进行了对比分析,适用于不同场景和数据量的复制需求,特别是针对超大表（1... 目录一、mysql 复制表（结构+数据）的 4 种核心方法（面试结构化回答）方法 1：CREATE
阅读更多...
JAVA线程的周期及调度机制详解

JAVA线程的周期及调度机制详解

《JAVA线程的周期及调度机制详解》Java线程的生命周期包括NEW、RUNNABLE、BLOCKED、WAITING、TIMED_WAITING和TERMINATED,线程调度依赖操作系统,采用抢占... 目录Java线程的生命周期线程状态转换示例代码JAVA线程调度机制优先级设置示例注意事项JAVA线程
阅读更多...
详解C++ 存储二进制数据容器的几种方法

详解C++ 存储二进制数据容器的几种方法

《详解C++存储二进制数据容器的几种方法》本文主要介绍了详解C++存储二进制数据容器,包括std::vector、std::array、std::string、std::bitset和std::ve... 目录1.std::vector<uint8_t>（最常用）特点：适用场景：示例：2.std::arra
阅读更多...
C++构造函数中explicit详解

C++构造函数中explicit详解

《C++构造函数中explicit详解》explicit关键字用于修饰单参数构造函数或可以看作单参数的构造函数,阻止编译器进行隐式类型转换或拷贝初始化,本文就来介绍explicit的使用,感兴趣的可以... 目录1. 什么是explicit2. 隐式转换的问题3.explicit的使用示例基本用法多参数构造
阅读更多...
SQL Server中行转列方法详细讲解

SQL Server中行转列方法详细讲解

《SQLServer中行转列方法详细讲解》SQL行转列、列转行可以帮助我们更方便地处理数据,生成需要的报表和结果集,：本文主要介绍SQLServer中行转列方法的相关资料,需要的朋友可以参考下... 目录前言一、为什么需要行转列二、行转列的基本概念三、使用PIVOT运算符进行行转列1.创建示例数据表并插入数
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2