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)

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

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Laoqi's Linux运维专列

CentOS下利用mysqlbinlog恢复MySQL数据库

46811
来自专栏杨建荣的学习笔记

关于视图和存储过程的权限问题探究 (r9笔记第87天)

今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式 ? 数据库中存在一个属主用户,表,存储过程等对象...

35910
来自专栏张善友的专栏

SQL Server 2008多版本并发控制

数据库的一致性,也是衡量DBMS性能的重要指标之一。目前大多数商业数据库(DB2, SQL Server)的并发控制采用的是两阶段锁(Two-Phase Loc...

2186
来自专栏耕耘实录

Zabbix最佳实践一:Zabbix4.0.2的安装与配置

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢

7323
来自专栏数据和云

DB2 Vs MySQL系列 | 体系架构对比

前些日子,我们做了DB2 VS MySQL的数据类型的对比,今天我们将体系架构的对比分享给大家,让大家对这两类数据库有更深刻的认识。 ? DB2体系结构 ? ?...

3255
来自专栏云计算教程系列

如何在CentOS 7上安装和使用PostgreSQL

关系数据库管理系统是许多网站和应用程序的关键组件。它们提供了一种存储,组织和访问信息的结构化方法。

2211
来自专栏小俊博客

Linux解决内存不足-添加SWAP交换分区

4332
来自专栏数据和云

高频错误:ORA-01555深入剖析

黄玮(Fuyuncat) 资深Oracle DBA,个人网站www.HelloDBA.com,致力于数据库底层技术的研究,其作品获得广大同行的高度评价. ORA...

3138
来自专栏散尽浮华

Mysql优化系列(0)--总结性梳理

对于一个网站来说,在运行很长一段时间后,数据库瓶颈问题会越来越暴露出来。作为运维人员,对数据库做必要的优化十分重要! 下面总结以往查阅到的以及自己工作中的一些优...

2208
来自专栏IT技术精选文摘

MySQL高可用方案

第一种:主从复制+读写分离 客户端通过Master对数据库进行写操作,slave端进行读操作,并可进行备份。Master出现问题后,可以手动将应用切换到slav...

4438

扫码关注云+社区

领取腾讯云代金券