mysql profiles 性能查询

2024-05-13 13:38
文章标签 mysql 查询 性能 database profiles

本文主要是介绍mysql profiles 性能查询，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

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


要使用该功能，mysql的版本必须在5.0.37版本以上。否则只能使用explain 的方式来检查。 
profiling 功能可以了解到cpu io 等更详细的信息。

show profile 的格式如下：

SHOW PROFILE [type [, type] ... ]-
 

    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS

默认方式下该功能是关闭的：

 mysql>select @@profiling;

+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

打开功能

mysql>set profiling=1;

+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

输入需要执行的sql 语句：

mysql>select count(*) from sySUSEr;

mysql>select count(*) from sySUSEr;

mysql> show profiles\G;
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00007550
   Query: select count(*) from sySUSEr
1 row in set (0.00 sec)

通过指定的Query_ID 来查询指定的sql语句的执行信息：

mysql> show profile for query 1;

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000028 |
| checking query cache for query | 0.000008 |
| checking privileges on cached  | 0.000009 |
| sending cached result to clien | 0.000023 |
| logging slow query             | 0.000004 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
6 rows in set (0.00 sec)

mysql> show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000028 |     NULL |       NULL |         NULL |          NULL |
| checking query cache for query | 0.000008 |     NULL |       NULL |         NULL |          NULL |
| checking privileges on cached  | 0.000009 |     NULL |       NULL |         NULL |          NULL |
| sending cached result to clien | 0.000023 |     NULL |       NULL |         NULL |          NULL |
| logging slow query             | 0.000004 |     NULL |       NULL |         NULL |          NULL |
| cleaning up                    | 0.000003 |     NULL |       NULL |         NULL |          NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
6 rows in set (0.00 sec)

如果不带for 参数则指列出最后一条语句的profile 信息：

mysql> show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000028 |     NULL |       NULL |         NULL |          NULL |
| checking query cache for query | 0.000008 |     NULL |       NULL |         NULL |          NULL |
| checking privileges on cached  | 0.000009 |     NULL |       NULL |         NULL |          NULL |
| sending cached result to clien | 0.000023 |     NULL |       NULL |         NULL |          NULL |
| logging slow query             | 0.000004 |     NULL |       NULL |         NULL |          NULL |
| cleaning up                    | 0.000003 |     NULL |       NULL |         NULL |          NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
6 rows in set (0.00 sec)

关闭参数：

mysql> set profiling=0

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)







这篇关于mysql profiles 性能查询的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


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

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...
MyBatis Plus大数据量查询慢原因分析及解决

MyBatis Plus大数据量查询慢原因分析及解决

《MyBatisPlus大数据量查询慢原因分析及解决》大数据量查询慢常因全表扫描、分页不当、索引缺失、内存占用高及ORM开销,优化措施包括分页查询、流式读取、SQL优化、批处理、多数据源、结果集二次... 目录大数据量查询慢的常见原因优化方案高级方案配置调优监控与诊断总结大数据量查询慢的常见原因MyBAT
阅读更多...
一文详解MySQL索引(六张图彻底搞懂)

一文详解MySQL索引(六张图彻底搞懂)

《一文详解MySQL索引(六张图彻底搞懂)》MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度,：本文主要介绍MySQL索引的相关资料,文中通过代码介绍的... 目录一、什么是索引？为什么需要索引？二、索引该用哪种数据结构？1. 哈希表2. 跳表3. 二叉排序树4.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2