MySQL服务器 IO 100%的分析与优化方案

前言

压力测试过程中，如果因为资源使用瓶颈等问题引发最直接性能问题是业务交易响应时间偏大，TPS逐渐降低等。而问题定位分析通常情况下，最优先排查的是监控服务器资源利用率，例如先用TOP 或者nmon等查看CPU、内存使用情况，然后在排查IO问题，例如网络IO、磁盘IO的问题。 如果是磁盘IO问题，一般问题是SQL语法问题、MYSQL参数配置问题、服务器自身硬件瓶颈导致IOPS吞吐率问题。

本文主要给大家介绍的是关于MySQL服务器 IO 100%的分析与优化方案，下面话不多说了，来一起看看详细的介绍吧

【问题】

有台MySQL 5.6.21的数据库实例以写入为主，IO %util接近100%

写入IOPS很高

【分析过程】

1、通过iotop工具可以看到当前IO消耗最高的mysql线程

2、查看线程49342的堆栈，可以看到正在进行redo log的刷新，对应的是9号文件

3、9号文件对应的是redo log的第一个文件

为什么mysql进程会频繁的刷新redo log文件，要结合redolog的刷盘策略来分析，关键是innodb_flush_log_at_trx_commit参数，

默认是1，最安全，但在写压力大的情况下，也会带来较大的性能影响，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去。

结合这个集群的写入场景来看，大部分都是小事务的写入，每次事务提交都会触发刷盘动作，这种场景下通过增大innodb_log_buffer_size和innodb_log_file_size的优化效果不明显

【优化方案】

1、应用层面，对于写压力大的系统，可以将单条的insert语句优化为小批量的insert语句，这样事务commit的次数减少，redo log刷盘减少，性能理论上会有提升

2、MySQL层面，对于日志类型的系统，如果允许宕机的情况下少量数据丢失，可以将innodb_flush_log_at_trx_commit参数调整为2，

当设置为2时，则在事务提交时只做write操作，只保证写到系统的page cache，因此实例crash不会丢失事务，但宕机则可能丢失事务

在这台服务器上测试，将参数调整为2时，IO的请求从200M/S降到约10M/S压力会减少10倍以上

3、系统层面，更换性能更佳的磁盘

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对我们的支持。

赞 (0)

相关推荐

  • MYSQL配置参数优化详解

    MySQL参数优化对于不同的网站,及其在线量,访问量,帖子数量,网络情况,以及机器硬件配置都有关系,优化不可能一次性完成,需要不断的观察以及调试,才有可能得到最佳的效果. 1)连接请求的变量 1.max_connections MySQL的最大连接数,如果服务器的并发连接请求量较大,建议调高此值,以增加并行连接数量,当然这建立在机器能支撑的情况下,因为如果连接数越多,MySQL回味每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲目提高设值. 数值过小经常会出现ERROR 1

  • MySQL中聚合函数count的使用和性能优化技巧

    本文的环境是Windows 10,MySQL版本是5.7.12-log 一. 基本使用 count的基本作用是有两个: 统计某个列的数据的数量: 统计结果集的行数: 用来获取满足条件的数据的数量.但是其中有一些与使用中印象不同的情况,比如当count作用一列.多列.以及使用*来表达整行产生的效果是不同的. 示例表如下: CREATE TABLE `NewTable` ( `id` int(11) NULL DEFAULT NULL , `name` varchar(30) NULL DEFAUL

  • 数据库sql语句优化

    为什么要优化: 随着实际项目的启动,数据库经过一段时间的运行,最初的数据库设置,会与实际数据库运行性能会有一些差异,这时我们 就需要做一个优化调整. 数据库优化这个课题较大,可分为四大类: >主机性能 >内存使用性能 >网络传输性能 >SQL语句执行性能[软件工程师] 下面列出一些数据库SQL优化方案: (01)选择最有效率的表名顺序(笔试常考) 数据库的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表将被最先处理,在FROM子句中包含多个表的情况下,你

  • Mysql优化order by语句的方法详解

    本篇文章我们将了解ORDER BY语句的优化,在此之前,你需要对索引有基本的了解,不了解的老少爷们可以先看一下我之前写过的索引相关文章.现在让我们开始吧. MySQL中的两种排序方式 1.通过有序索引顺序扫描直接返回有序数据 因为索引的结构是B+树,索引中的数据是按照一定顺序进行排列的,所以在排序查询中如果能利用索引,就能避免额外的排序操作.EXPLAIN分析查询时,Extra显示为Using index. 2.Filesort排序,对返回的数据进行排序 所有不是通过索引直接返回排序结果的操作都

  • PHP+MySQL实现对一段时间内每天数据统计优化操作实例

    PHP+MySQL实现对一段时间内每天数据统计优化操作实例

    本文实例讲述了PHP+MySQL实现对一段时间内每天数据统计优化操作.分享给大家供大家参考,具体如下: 在互联网项目中,对项目的数据分析必不可少.通常会统计某一段时间内每天数据总计变化趋势调整营销策略.下面来看以下案例. 案例 在电商平台中通常会有订单表,记录所有订单信息.现在我们需要统计某个月份每天订单数及销售金额数据从而绘制出如下统计图,进行数据分析. 订单表数据结构如下: order_id order_sn total_price enterdate 25396 A4E610E250C2D

  • 小米正式开源 SQL 智能优化与改写工具 SOAR

    小米正式开源 SQL 智能优化与改写工具 SOAR

    近日,小米正式宣布开源 SOAR. 截至今日,该项目已经获得了 350 个「star」以及 44 个「fork」(GitHub项目地址:https://github.com/XiaoMi/soar) SOAR 简介 SOAR,即 SQL Optimizer And Rewriter,是一款 SQL 智能优化与改写工具,由小米运维 DBA 团队出品 SOAR 体系架构 SOAR主要由语法解析器,集成环境,优化建议,重写逻辑,工具集五大模块组成. 语法解析和语法检查 一条SQL从文件,标准输入或命令

  • 30个mysql千万级大数据SQL查询优化技巧详解

    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引. 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用

  • 分析Mysql表读写、索引等操作的sql语句效率优化问题

    上次我们说到mysql的一些sql查询方面的优化,包括查看explain执行计划,分析索引等等.今天我们分享一些 分析mysql表读写.索引等等操作的sql语句. 闲话不多说,直接上代码: 反映表的读写压力 SELECT file_name AS file, count_read, sum_number_of_bytes_read AS total_read, count_write, sum_number_of_bytes_write AS total_written, (sum_number

  • Mysql查询最近一条记录的sql语句(优化篇)

    下策--查询出结果后将时间排序后取第一条 select * from a where create_time<="2017-03-29 19:30:36" order by create_time desc limit 1 这样做虽然可以取出当前时间最近的一条记录,但是一次查询需要将表遍历一遍,对于百万以上数据查询将比较费时:limit是先取出全部结果,然后取第一条,相当于查询中占用了不必要的时间和空间:还有如果需要批量取出最近一条记录,比方说:"一个订单表,有用户,订

  • SQL语句优化之JOIN和LEFT JOIN 和 RIGHT JOIN语句的优化

    在数据库的应用中,我们经常需要对数据库进行多表查询,然而当数据量非常大时多表查询会对执行效率产生非常大的影响,因此我们在使用JOIN和LEFT JOIN 和 RIGHT JOIN语句时要特别注意: SQL语句的join原理: 数据库中的join操作,实际上是对一个表和另一个表的关联,而很多错误理解为,先把这两个表来一个迪卡尔积,然后扔到内存,用where和having条件来慢慢筛选,其实数据库没那么笨的,那样会占用大量的内存,而且效率不高,比如,我们只需要的一个表的一些行和另一个表的一些行,如果

随机推荐