首页 数据库 mysql教程 根据status 对mysql进行性能优化_MySQL

根据status 对mysql进行性能优化_MySQL

May 30, 2016 pm 05:10 PM
性能

前言:

 

mysql同样的设置,在不同的环境下 ,由于内存,访问量,读写频率,数据差异等等情况,可能会出现不同的结果,因此简单地根据某个给出方案来配置mysql是行不通的,最好能使用 status信息对mysql进行具体的优化。

 

mysql> show global status;

  可以列出mysql服务器运行各种状态值,另外,查询mysql服务器配置信息语句:

 

mysql> show variables;

根据status信息对mysql优化的项目如下:

一、慢查询

mysql> show variables like 'slow%';

+------------------+-------+

| variable_name     | value |

+------------------+-------+

| log_slow_queries | on     |

| slow_launch_time | 2      |

+------------------+-------+

 

mysql> show global status like 'slow%';

+---------------------+-------+

| variable_name        | value |

+---------------------+-------+

| slow_launch_threads | 0      |

| slow_queries         | 4148 |

+---------------------+-------+ 

 

配置中打开了记录慢查询,执行时间超过2秒的即为慢查询,系统显示有4148个慢查询,你可以分析慢查询日志,找出有问题的sql语句,慢查询时间不宜设置过长,否则意义不大,最好在5秒以内,如果你需要微秒级别的慢查询,可以考虑给mysql打补丁:http://www.percona.com /docs/wiki/release:start,记得找对应的版本。

 

打开慢查询日志可能会对系统性能有一点点影响,如果你的mysql是主-从结构,可以考虑打开其中一台从服务器的慢查询日志,这样既可以监控慢查询,对系统性能影响又小。

 

二、连接数

经常会遇见”mysql: error 1040: too many connections”的情况,一种是访问量确实很高,mysql服务器抗不住,这个时候就要考虑增加从服务器分散读压力,另外一种情况是mysql配置文件中max_connections值过小:

 

mysql> show variables like 'max_connections';

+-----------------+-------+

| variable_name    | value |

+-----------------+-------+

| max_connections | 256   |

+-----------------+-------+

 

这台mysql服务器最大连接数是256,然后查询一下服务器响应的最大连接数:

 

mysql> show global status like 'max_used_connections';

 

mysql服务器过去的最大连接数是245,没有达到服务器连接数上限256,应该没有出现1040错误,比较理想的设置是

 

max_used_connections / max_connections * 100% ≈ 85%

 

最大连接数占上限连接数的85%左右,如果发现比例在10%以下,mysql服务器连接数上限设置的过高了。

 

三、key_buffer_size

key_buffer_size是对myisam表性能影响最大的一个参数,下面一台以myisam为主要存储引擎服务器的配置:

 

mysql> show variables like 'key_buffer_size';

 

+-----------------+------------+

| variable_name    | value       |

+-----------------+------------+

| key_buffer_size | 536870912 |

+-----------------+------------+

 

分配了512mb内存给key_buffer_size,我们再看一下key_buffer_size的使用情况:

 

mysql> show global status like 'key_read%';

+------------------------+-------------+

| variable_name           | value        |

+------------------------+-------------+

| key_read_requests       | 27813678764 |

| key_reads               | 6798830      |

+------------------------+-------------+

 

  一共有27813678764个索引读取请求,有6798830个请求在内存中没有找到直接从硬盘读取索引,计算索引未命中缓存的概率:

 

key_cache_miss_rate = key_reads / key_read_requests * 100%

 

比如上面的数据,key_cache_miss_rate为0.0244%,4000个索引读取请求才有一个直接读硬盘,已经很bt 了,key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘),如果key_cache_miss_rate在 0.01%以下的话,key_buffer_size分配的过多,可以适当减少。

 

mysql服务器还提供了key_blocks_*参数:

mysql> show global status like 'key_blocks_u%';

+------------------------+-------------+

| variable_name           | value        |

+------------------------+-------------+

| key_blocks_unused       | 0            |

| key_blocks_used         | 413543       |

+------------------------+-------------+

 

key_blocks_unused表示未使用的缓存簇(blocks)数,key_blocks_used表示曾经用到的最大的blocks数,比如这台服务器,所有的缓存都用到了,要么增加key_buffer_size,要么就是过渡索引了,把缓存占满了。比较理想的设置:

 

key_blocks_used / (key_blocks_unused + key_blocks_used) * 100% ≈ 80%

 

四、临时表

mysql> show global status like 'created_tmp%';

+-------------------------+---------+

| variable_name            | value    |

+-------------------------+---------+

| created_tmp_disk_tables | 21197    |

| created_tmp_files        | 58       |

| created_tmp_tables       | 1771587 |

+-------------------------+---------+

 

每次创建临时表,created_tmp_tables增加,如果是在磁盘上创建临时表,created_tmp_disk_tables也增加,created_tmp_files表示mysql服务创建的临时文件文件数,比较理想的配置是:

 

  created_tmp_disk_tables / created_tmp_tables * 100%

 

mysql> show variables where variable_name in ('tmp_table_size', 'max_heap_table_size');

+---------------------+-----------+

| variable_name        | value      |

+---------------------+-----------+

| max_heap_table_size | 268435456 |

| tmp_table_size       | 536870912 |

+---------------------+-----------+

 

只有256mb以下的临时表才能全部放内存,超过的就会用到硬盘临时表。

 

五、open table情况

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

+---------------+-------+

| variable_name | value |

+---------------+-------+

| open_tables    | 919    |

| opened_tables | 1951  |

+---------------+-------+

 

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

 

mysql> show variables like 'table_cache';

+---------------+-------+

| variable_name | value |

+---------------+-------+

| table_cache    | 2048  |

+---------------+-------+

 

比较合适的值为:

open_tables / opened_tables * 100% >= 85%

 

open_tables / table_cache * 100%

 

六、进程使用情况

mysql> show global status like 'thread%';

+-------------------+-------+

| variable_name      | value |

+-------------------+-------+

| threads_cached     | 46     |

| threads_connected | 2      |

| threads_created    | 570    |

| threads_running    | 1      |

+-------------------+-------+

 

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

 

mysql> show variables like 'thread_cache_size';

+-------------------+-------+

| variable_name      | value |

+-------------------+-------+

| thread_cache_size | 64     |

+-------------------+-------+

 

示例中的服务器还是挺健康的。

七、查询缓存(query cache)

mysql> show global status like 'qcache%';

+-------------------------+-----------+

| variable_name            | value      |

+-------------------------+-----------+

| qcache_free_blocks       | 22756      |

| qcache_free_memory       | 76764704  |

| qcache_hits              | 213028692 |

| qcache_inserts           | 208894227 |

| qcache_lowmem_prunes     | 4010916    |

| qcache_not_cached        | 13385031  |

| qcache_queries_in_cache | 43560      |

| qcache_total_blocks      | 111212     |

+-------------------------+-----------+

 

mysql查询缓存变量解释:

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的配置:

mysql> show variables like 'query_cache%';

+------------------------------+-----------+

| variable_name                 | value      |

+------------------------------+-----------+

| query_cache_limit             | 2097152    |

| query_cache_min_res_unit      | 4096       |

| query_cache_size              | 203423744 |

| query_cache_type              | on         |

| query_cache_wlock_invalidate | off        |

+------------------------------+-----------+

 

各字段的解释:

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%,命中率很差,可能写操作比较频繁吧,而且可能有些碎片。

 

八、排序使用情况

mysql> show global status like 'sort%';

+-------------------+------------+

| variable_name      | value       |

+-------------------+------------+

| sort_merge_passes | 29          |

| sort_range         | 37432840    |

| sort_rows          | 9178691532 |

| sort_scan          | 1860569     |

+-------------------+------------+

 

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?(引自http://qroom.blogspot.com/2007/09/mysql-select-sort.html,貌似被墙)

 

另外,增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值对排序的操作也有一点的好处,参见:http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/

 

九、文件打开数(open_files)

mysql> show global status like 'open_files';

+---------------+-------+

| variable_name | value |

+---------------+-------+

| open_files     | 1410  |

+---------------+-------+

 

mysql> show variables like 'open_files_limit';

+------------------+-------+

| variable_name     | value |

+------------------+-------+

| open_files_limit | 4590  |

+------------------+-------+

 

比较合适的设置:open_files / open_files_limit * 100%

 

十、表锁情况

mysql> show global status like 'table_locks%';

+-----------------------+-----------+

| variable_name          | value      |

+-----------------------+-----------+

| table_locks_immediate | 490206328 |

| table_locks_waited     | 2084912    |

+-----------------------+-----------+

 

  table_locks_immediate表示立即释放表锁数,table_locks_waited表示需要等待的表锁数,如果 table_locks_immediate / table_locks_waited > 5000,最好采用innodb引擎,因为innodb是行锁而myisam是表锁,对于高并发写入的应用innodb效果会好些。示例中的服务器 table_locks_immediate / table_locks_waited = 235,myisam就足够了。

 

十一、表扫描情况

mysql> show global status like 'handler_read%';

+-----------------------+-------------+

| variable_name          | value        |

+-----------------------+-------------+

| handler_read_first     | 5803750      |

| handler_read_key       | 6049319850  |

| handler_read_next      | 94440908210 |

| handler_read_prev      | 34822001724 |

| handler_read_rnd       | 405482605    |

| handler_read_rnd_next | 18912877839 |

+-----------------------+-------------+

 

各字段解释参见http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html,调出服务器完成的查询请求次数:

 

mysql> show global status like 'com_select';

+---------------+-----------+

| variable_name | value      |

+---------------+-----------+

| com_select     | 222693559 |

+---------------+-----------+

 

计算表扫描率:

表扫描率 = handler_read_rnd_next / com_select

 

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

本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

热门文章

R.E.P.O.能量晶体解释及其做什么(黄色晶体)
2 周前 By 尊渡假赌尊渡假赌尊渡假赌
仓库:如何复兴队友
4 周前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险:如何获得巨型种子
4 周前 By 尊渡假赌尊渡假赌尊渡假赌

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版,非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

vivox100s和x100区别:性能对比及功能解析 vivox100s和x100区别:性能对比及功能解析 Mar 23, 2024 pm 10:27 PM

vivox100s和x100手机都是vivo手机产品线中的代表机型,它们分别代表了vivo在不同时间段内的高端技术水平,因此这两款手机在设计、性能和功能上均有一定区别。本文将从性能对比和功能解析两个方面对这两款手机进行详细比较,帮助消费者更好地选择适合自己的手机。首先,我们来看vivox100s和x100在性能方面的对比。vivox100s搭载了最新的

如何在Windows 11中显示隐藏的性能覆盖 如何在Windows 11中显示隐藏的性能覆盖 Mar 24, 2024 am 09:40 AM

在本教程中,我们将帮助您显示Windows11中隐藏的性能覆盖。使用Windows11的性能覆盖功能,您将能够实时监视您的系统资源。您可以在电脑屏幕上查看实时的CPU使用率、磁盘使用率、GPU使用率、RAM使用率等。当您在玩游戏或使用大型图形程序(如视频编辑器)并需要检查使用特定程序时系统性能受到多大程度的影响时,这是很方便的。尽管有一些优秀的免费软件可用于监控系统性能,并且一些内置工具(如资源监视器)可用于检查系统性能,但性能叠加功能也有其优势。比如,您无需离开当前正在使用的程序或应用,也无需

Windows10与Windows11性能对比:哪个更胜一筹? Windows10与Windows11性能对比:哪个更胜一筹? Mar 28, 2024 am 09:00 AM

Windows10与Windows11性能对比:哪个更胜一筹?随着科技的不断发展和进步,操作系统也在不断更新和升级。微软公司作为全球最大的操作系统开发商之一,其发布的Windows系列操作系统一直备受用户关注。在2021年,微软发布了Windows11操作系统,这引发了广泛的讨论和关注。那么,究竟Windows10与Windows11在性能方面有何不同,哪个

Win11和Win10系统性能对比,究竟哪一个更胜一筹? Win11和Win10系统性能对比,究竟哪一个更胜一筹? Mar 27, 2024 pm 05:09 PM

一直以来,Windows操作系统一直是人们在个人电脑上使用最为广泛的操作系统之一,而Windows10长期以来一直是微软公司的旗舰操作系统,直到最近微软推出了全新的Windows11系统。随着Windows11系统的推出,人们对于Windows10和Windows11系统之间的性能差异开始感兴趣,究竟两者之间哪一个更胜一筹呢?首先,让我们来看一下W

麒麟8000处理器抗衡骁龙系列:谁能称王? 麒麟8000处理器抗衡骁龙系列:谁能称王? Mar 25, 2024 am 09:03 AM

在移动互联网时代,智能手机已经成为人们日常生活中不可或缺的一部分。而智能手机的性能表现往往直接决定了用户体验的好坏。作为智能手机的“大脑”,处理器的性能表现尤为重要。在市场上,高通骁龙系列一直以来都是性能强劲、稳定可靠的代表,而最近华为也推出了自家研发的麒麟8000处理器,据称性能优异。对于普通用户来说,如何选择一款性能强劲的手机成为一个关键问题。今天我们就

本地运行性能超越 OpenAI Text-Embedding-Ada-002 的 Embedding 服务,太方便了! 本地运行性能超越 OpenAI Text-Embedding-Ada-002 的 Embedding 服务,太方便了! Apr 15, 2024 am 09:01 AM

Ollama是一款超级实用的工具,让你能够在本地轻松运行Llama2、Mistral、Gemma等开源模型。本文我将介绍如何使用Ollama实现对文本的向量化处理。如果你本地还没有安装Ollama,可以阅读这篇文章。本文我们将使用nomic-embed-text[2]模型。它是一种文本编码器,在短的上下文和长的上下文任务上,性能超越了OpenAItext-embedding-ada-002和text-embedding-3-small。启动nomic-embed-text服务当你已经成功安装好o

不同Java框架的性能对比 不同Java框架的性能对比 Jun 05, 2024 pm 07:14 PM

不同Java框架的性能对比:RESTAPI请求处理:Vert.x最佳,请求速率达SpringBoot2倍,Dropwizard3倍。数据库查询:SpringBoot的HibernateORM优于Vert.x及Dropwizard的ORM。缓存操作:Vert.x的Hazelcast客户机优于SpringBoot及Dropwizard的缓存机制。合适框架:根据应用需求选择,Vert.x适用于高性能Web服务,SpringBoot适用于数据密集型应用,Dropwizard适用于微服务架构。

PHP与Go语言对比:性能差异大 PHP与Go语言对比:性能差异大 Mar 26, 2024 am 10:48 AM

PHP与Go语言是两种常用的编程语言,它们有着不同的特点和优势。其中,性能差异是大家普遍关注的一个问题。本文将从性能角度对比PHP和Go语言,并通过具体的代码示例来展示它们的性能差异。首先,让我们简要介绍一下PHP和Go语言的基本特点。PHP是一种脚本语言,最初设计用于Web开发,易学易用,广泛应用于Web开发领域。而Go语言是由Google开发的一种编译型

See all articles