专栏首页北京马哥教育MySQL 数据库上线后根据 status 状态优化

MySQL 数据库上线后根据 status 状态优化

马哥linux运维 | 最专业的linux培训机构


网上有很多的文章教怎么配置mysql服务器,但考虑到服务器硬件配置的不同,具体应用的差别,那些文章的做法只能作为初步设置参考,我们需要根据自己的情况进行配置优化,好的做法是MySQL服务器稳定运行了一段时间后运行,根据服务器的”状态”进行优化。

查看MySQL服务器配置信息:

show variables;

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

show global status;

1. 慢查询

show variables like '%slow%';
show global status like '%slow%';

分析慢查询日志,找出有问题的SQL语句,慢查询时间不宜设置过 长,否则意义不大,最好在5秒以内,如果你需要微秒级别的慢查询,可以考虑给MySQL打补丁:http://www.percona.com/docs /wiki/release:start,记得找对应的版本。

2. 连接数

经常会遇见” MySQL: ERROR 1040: Too manyconnections” 的情况:

  • 一种是访问量确实很高,MySQL服务器抗不住,这个时候就要考虑增加从服务器分散读压力。
  • 一种情况是MySQL配置文件中max_connections值过小。

查看最大连接数:

show variables like 'max_connections';

查看mysql服务器过去的最大连接数:

show global status like 'max_used_connections';

理想值:max_used_connections / max_connections * 100% ≈ 85%

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

3. key_buffer_size

key_buffer_size是对MyISAM表性能影响最大的一个参数,不过数据库中多为Innodb

查看key_buffer_size设置大小:

show variables like 'key_buffer_size';

查看key_buffer_size使用情况:

show global status like 'key_read%';

计算索引未命中缓存的概率:key_cache_miss_rate = Key_reads / Key_read_requests * 100%

key_cache_miss_rate在0.1%以下都很好(每1000个请求有一个直接读硬盘),如果key_cache_miss_rate在0.01%以下的话,key_buffer_size分配的过多,可以适当减少。

4. key_blocks_*参数

show global status like 'key_blocks_u%';
  • Key_blocks_unused:表示未使用的缓存簇(blocks)数
  • Key_blocks_used:表示曾经用到的最大的blocks数

理想值:Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

5. 临时表

当执行语句时,关于已经被创造了的隐含临时表的数量,查看命令:

show global status like 'created_tmp%';

每次创建表时Created_tmp_tables 都会增加,如果在磁盘上创建,Created_tmp_disk_tables也会增加,Created_tmp_files表示服务器创建的临时文件数

理想值:Created_tmp_disk_tables / Created_tmp_tables * 100% ≤25%

查看服务器对临时表的配置:

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

6.打开表的情况

show global status like 'open%tables%';

open_tables 表示打开表的数量,opened_tables表示打开过的表数量,如果opened_tables数量过大,说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小

查询服务器table_cache值:

show variables like 'table_open_cache';
  • 理想值:open_tables / opened_tables * 100% ≥ 85%
  • 理想值:open_tables / table_cache * 100% ≤95%

7. 进程使用情况

如果我们在MySQL服务器配置文件中设置了thread_cache_size,当客户端断开之后,服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。Threads_created表示创建过的线程数:

show global status like 'thread%';

如果发现Threads_created值过大的话,表明 MySQL服务器一直在创建线程,这也是比较耗资源,可以适当增加配置文件中thread_cache_size值,查询服务器 thread_cache_size配置:

show variables like 'thread_cache_size';

8. 查询缓存(query cache)

查看服务器query_cache配置情况:

show variables like 'query_cache%';

参数解释:

  • query_cache_limit:超过此大小的查询将不缓存。
  • query_cache_min_res_unit:缓存块的最小值。
  • query_cache_size:查询缓存大小。
  • query_cache_type:缓存类型,决定缓存什么样的查询。
  • query_cache_wlock_invalidate:表示当前客户端,正在对MyISAM表进行写操作时,读请求是要等SRITE LOCK释放资源后再查询,还是允许直接从Query Cache中读取结果,默认为OFF(可以直接从Query Cache 中读取结果)。

查看服务器query_cache使用情况:

show global status like 'qcache%';

参数解释:

  • Qcache_free_blocks:缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整理,从而得到一个空闲块。
  • Qcache_free_memory:缓存中的空闲内存。
  • Qcache_hits:每次查询在缓存中命中时就增大
  • Qcache_inserts:每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
  • Qcache_lowmem_prunes:缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字不断增长,表示可能碎片非常严重,或内存很少。
  • Qcache_not_cached:不适合进行缓存的查询的数量,通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
  • Qcache_queries_in_cache:当前缓存的查询(和响应)的数量。
  • Qcache_total_blocks:缓存中块的数量。

9. 排序使用情况

它表示系统中对数据进行排序时所使用Buffer,查看命令:

show global status like 'sort%';

增大sort_buffer_size 会减少Sort_merge_passes和创建临时文件的次数,但盲目增加并不一定能提高速度。

10. 文件打开数

当open_files大于open_files_limit值时,mysql数据库就会发生卡住的现象,导致web服务器打开不响应的页面。

查看open_files命令:

show global status like 'open_files';

查看open_files_limit命令:

show variables like 'open_files_limit';

理想值:open_files / open_files_limit *100% ≤ 75%

本文分享自微信公众号 - 马哥Linux运维(magedu-Linux)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2015-03-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL/MariaDB数据库基于SSL实现主从复制

    前言 备份数据库是生产环境中的首要任务,重中之重,有时候不得不通过网络进行数据库的复制,由于MySQL/MariaDB的主从复制是明文传送的,如果在生产环境中跨...

    小小科
  • Web Cache原理,你真的造吗?

    一、Web Cache 在介绍Web cache时,我们需要简单介绍缓存的理解 1.1 缓存解释 缓存通常是基于键值对来缓存的,键通过hash计算后,存放于内存...

    小小科
  • Python缓存神奇库cacheout全解

    python的缓存库(cacheout) 链接: 项目: https://github.com/dgilland/cacheout 文档地址: https:/...

    小小科
  • 网站高并发解决方案(理论知识)

    当面试官问:"网站高并发怎么做?"时,该怎么回? 在高并发下,我们(初级程序员)能做什么?

    仙士可
  • 为什么我们做分布式要用 Redis ?

    绝大部分写业务的程序员,在实际开发中使用 Redis 的时候,只会 Set Value 和 Get Value 两个操作,对 Redis 整体缺乏一个认知。这里...

    芋道源码
  • WebSocket客户端断开连接后,服务器端的处理机制

    版权声明:本文为博主汪子熙原创文章,未经博主允许不得转载。 https://jerry.bl...

    Jerry Wang
  • 缓存雪崩、击穿、穿透,该如何避免?

    帅气迷人的面试官您好,我了解的,目前电商首页以及热点数据都会去做缓存 ,一般缓存都是定时任务去刷新,或者是查不到之后去更新的,定时任务刷新就有一个问题。

    黄泽杰
  • 面试被问频率最高的几道Redis面试题

    Redis相关面试题确实很多,主要是因为知识点很多,但是面试的时候,不可能都问个遍,所以本文就来总结一下,面试被问频率最高的几道Redis的面试题。

    用户4143945
  • Stack Overflow 2016最新架构探秘

    这篇文章主要揭秘 Stack Overflow 截止到 2016 年的技术架构。   首先给出一个直观的数据,让大家有个初步的印象。   相比于 2013 ...

    逸鹏
  • 所有程序员都要注意,往后工作会越来越难找

    最近传的轰轰烈烈的裁员潮,想必大家都有耳闻了,各个公司缩进开支,不但减少人员招收,而且还裁员,这无疑给我们程序员在敲着一个警钟,互联网的寒冬真的来了

    JAVA高级架构开发

扫码关注云+社区

领取腾讯云代金券