데이터 베이스 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 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

뜨거운 도구

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)

vivox100s와 x100의 차이점: 성능 비교 및 ​​기능 분석 vivox100s와 x100의 차이점: 성능 비교 및 ​​기능 분석 Mar 23, 2024 pm 10:27 PM

vivox100s와 x100 휴대폰은 모두 in vivo 휴대폰 제품군의 대표적인 모델입니다. 두 휴대폰은 각각 서로 다른 시대의 vivo 첨단 기술 수준을 대표하므로 디자인, 성능, 기능 면에서 일정한 차이가 있습니다. 이번 글에서는 소비자들이 자신에게 꼭 맞는 휴대폰을 선택할 수 있도록 두 휴대폰을 성능비교와 기능분석 측면에서 자세히 비교해보겠습니다. 먼저 vivox100s와 x100의 성능 비교를 살펴보겠습니다. vivox100s에는 최신 기술이 탑재되어 있습니다.

Windows 11에서 숨겨진 성능 오버레이를 표시하는 방법 Windows 11에서 숨겨진 성능 오버레이를 표시하는 방법 Mar 24, 2024 am 09:40 AM

이 튜토리얼에서는 Windows 11의 숨겨진 성능 오버레이를 공개하는 데 도움을 드립니다. Windows 11의 성능 오버레이 기능을 사용하면 시스템 리소스를 실시간으로 모니터링할 수 있습니다. 컴퓨터 화면에서 실시간 CPU 사용량, 디스크 사용량, GPU 사용량, RAM 사용량 등을 볼 수 있습니다. 이는 게임을 하거나 대용량 그래픽 프로그램(비디오 편집기 등)을 사용할 때, 특정 프로그램을 사용할 때 시스템 성능이 얼마나 영향을 받는지 확인해야 할 때 편리합니다. 시스템 성능을 모니터링하는 데 사용할 수 있는 뛰어난 무료 소프트웨어가 있고 리소스 모니터와 같은 일부 내장 도구를 사용하여 시스템 성능을 확인할 수 있지만 성능 오버레이 기능에도 장점이 있습니다. 예를 들어 현재 사용하고 있는 프로그램이나 앱을 종료할 필요가 없거나

Windows 10 vs. Windows 11 성능 비교: 어느 것이 더 낫나요? Windows 10 vs. Windows 11 성능 비교: 어느 것이 더 낫나요? Mar 28, 2024 am 09:00 AM

Windows 10 vs. Windows 11 성능 비교: 어느 것이 더 낫나요? 지속적인 기술 개발과 발전으로 운영 체제는 지속적으로 업데이트되고 업그레이드됩니다. 세계 최대 운영 체제 개발자 중 하나인 Microsoft의 Windows 운영 체제 시리즈는 항상 사용자로부터 많은 관심을 받아 왔습니다. 2021년에 Microsoft는 Windows 11 운영 체제를 출시하여 광범위한 논의와 관심을 불러일으켰습니다. 그렇다면 Windows 10과 Windows 11의 성능 차이는 무엇입니까?

Win11과 Win10 시스템의 성능을 비교하면 어느 것이 더 낫습니까? Win11과 Win10 시스템의 성능을 비교하면 어느 것이 더 낫습니까? Mar 27, 2024 pm 05:09 PM

Windows 운영 체제는 항상 개인용 컴퓨터에서 가장 널리 사용되는 운영 체제 중 하나였으며, Windows 10은 Microsoft가 새로운 Windows 11 시스템을 출시한 최근까지 오랫동안 Microsoft의 주력 운영 체제였습니다. Windows 11 시스템이 출시되면서 사람들은 Windows 10과 Windows 11 시스템 중 어느 것이 더 나은지에 관심을 가지게 되었습니다. 먼저 W부터 살펴보겠습니다.

Kirin 8000 프로세서는 Snapdragon 시리즈와 경쟁합니다. 누가 왕이 될 수 있습니까? Kirin 8000 프로세서는 Snapdragon 시리즈와 경쟁합니다. 누가 왕이 될 수 있습니까? Mar 25, 2024 am 09:03 AM

모바일 인터넷 시대를 맞아 스마트폰은 국민의 일상생활에서 없어서는 안 될 존재가 되었습니다. 스마트폰의 성능은 사용자 경험의 질을 직접적으로 결정하는 경우가 많습니다. 스마트폰의 '두뇌'인 프로세서의 성능은 특히 중요합니다. 시장에서 Qualcomm Snapdragon 시리즈는 항상 강력한 성능, 안정성 및 신뢰성을 대표해 왔으며 최근 Huawei는 뛰어난 성능을 갖춘 것으로 알려진 자체 Kirin 8000 프로세서도 출시했습니다. 일반 사용자들에게는 강력한 성능의 휴대폰을 어떻게 선택하느냐가 중요한 이슈가 되었다. 오늘 우리는

Embedding 서비스의 로컬 실행 성능은 OpenAI Text-Embedding-Ada-002를 능가하므로 매우 편리합니다! Embedding 서비스의 로컬 실행 성능은 OpenAI Text-Embedding-Ada-002를 능가하므로 매우 편리합니다! Apr 15, 2024 am 09:01 AM

Ollama는 Llama2, Mistral, Gemma와 같은 오픈 소스 모델을 로컬에서 쉽게 실행할 수 있는 매우 실용적인 도구입니다. 이번 글에서는 Ollama를 사용하여 텍스트를 벡터화하는 방법을 소개하겠습니다. Ollama를 로컬에 설치하지 않은 경우 이 문서를 읽을 수 있습니다. 이 기사에서는 nomic-embed-text[2] 모델을 사용합니다. 짧은 컨텍스트 및 긴 컨텍스트 작업에서 OpenAI text-embedding-ada-002 및 text-embedding-3-small보다 성능이 뛰어난 텍스트 인코더입니다. o를 성공적으로 설치한 후 nomic-embed-text 서비스를 시작하십시오.

다양한 Java 프레임워크의 성능 비교 다양한 Java 프레임워크의 성능 비교 Jun 05, 2024 pm 07:14 PM

다양한 Java 프레임워크의 성능 비교: REST API 요청 처리: Vert.x가 최고이며 요청 속도는 SpringBoot의 2배, Dropwizard의 3배입니다. 데이터베이스 쿼리: SpringBoot의 HibernateORM은 Vert.x 및 Dropwizard의 ORM보다 우수합니다. 캐싱 작업: Vert.x의 Hazelcast 클라이언트는 SpringBoot 및 Dropwizard의 캐싱 메커니즘보다 우수합니다. 적합한 프레임워크: 애플리케이션 요구 사항에 따라 선택하세요. Vert.x는 고성능 웹 서비스에 적합하고, SpringBoot는 데이터 집약적 애플리케이션에 적합하며, Dropwizard는 마이크로서비스 아키텍처에 적합합니다.

PHP와 Go 언어의 비교: 큰 성능 차이 PHP와 Go 언어의 비교: 큰 성능 차이 Mar 26, 2024 am 10:48 AM

PHP와 Go는 일반적으로 사용되는 두 가지 프로그래밍 언어이며 서로 다른 특성과 장점을 가지고 있습니다. 그 중 성능 차이는 모두가 일반적으로 우려하는 문제이다. 이 기사에서는 성능 관점에서 PHP와 Go 언어를 비교하고 구체적인 코드 예제를 통해 성능 차이를 보여줍니다. 먼저 PHP와 Go 언어의 기본 기능을 간략하게 소개하겠습니다. PHP는 원래 웹 개발을 위해 설계된 스크립팅 언어로, 배우기 쉽고 사용하기 쉬우며 웹 개발 분야에서 널리 사용됩니다. Go 언어는 Google에서 개발한 컴파일 언어입니다.

See all articles