MySQL性能突然下降的原因_Mysql

来源:脚本之家  责任编辑:小易  

跟mysql版本有关系,bai如果是5.7的话,是du几千zhi万。但是5.7之后基本上就dao不用考虑数据量的问题内了容。但是这个问题问的不好,因为性能急剧下降不但但是跟数据量这一个因素有关系。还有机器的配置,比如内存,如果内存放不下索引而把索引放在了虚拟内存上,那么效率就会急剧下降了。还有就是sql建立合适的索引了,晋升空间多大,主要的工作内容还可以问问HR他们企业人员流动性大不大www.zgxue.com防采集请勿采集本网。

有时会碰到这样的情况,一条 SQL 在平时执行没问题,很快。但是突然某个时间执行的就会很慢,而且这种场景并不能复现,只能随机发送的。

可能是某个程序有问题,比如连接数据库之后不释放,造成无数的数据库进程,那样数据库就会越来越慢,甚至无法连接。 出现故障的时候,你NETSTAT -N查看一下网络连接,看看是不是许多3306的TCP连接。同时也可以在mysql里面使用show processlist查

SQL 执行突然变慢的原因

数据库慢一般有三种情况 1。逐渐变慢 2。突然变慢 3。不定时变慢 第一种情况 “逐渐变慢”,要建立一个长期的监控机制。比如,写个shell脚本每天的忙时(通常9~10 etc.)定时收集os,network,db的信息, 每个星期出report对收集到的信息进行分析。这

在之前讲解 MySQL Redo log 时,说到了 WAL 机制,为了保证 MySQL 更新的速度,在进行更新操作时,先将更新内容写入 redo log,后续系统空闲时,再将 redo log 的内容应用到磁盘。

原则上数据量不超过百万,都影响不大,如果超过了可以考虑使用索引或者分表策略 具体可能还要参见具体的业务场景

当内存数据页(redo log)和磁盘数据页内容不一致时,将该内存也称为 “脏页”。将内存数据写入到磁盘后,数据一致,内存页称为 "干净页"。

1、show processlist; 2、select * from information_schema.processlist ; 3、可以在[mysqld]中添加如下: log =/var/log/mysql.log 如果需要监控慢查询可以添加如下内容: log-slow-queries = /var/log/slowquery.log long_query_time = 1

在内存数据写入磁盘时,这个过程称为 flush 过程。SQL 突然执行变得很慢,性能下降。原因就可能和 flush 操作有关。

不是mysql,是所有的数据库都是这样。 所以在创建数据库表的时候需要合理划分存储,建立高效的索引。定期对大数据表做维护(如备份后移除无用数据等)

因为在进行 flush 操作时,更新操作会等待 redo log 的写入。

引起 flush 操作的原因

场景一:redo log 日志已经记满。这时系统会停止更新操作,将 check point 向前推进,让 redo log 留出空间可以继续写。

这里假设 CP 到 CP‘ 间隙已经写入到磁盘,这部分就变成了干净页,此时 write pos 就可以写入这部分区域了。

场景二:系统内存不足,需要新的内存页时,发现内存不够用了,就需要淘汰一些数据页。如果淘汰时,这时数据页时脏页,就要将脏页写到磁盘。

这时有个问题是,命名 redo log 中的内容已经被记录到日志中了,假如内存满了,直接删除不就可以吗?下次读入时,再把 redo log 日志中的内容应用到磁盘。

没有选择直接清空内存,是从性能考虑的,因为在查询数据时,有两种情况: 首先数据页在内存中,内存是就是正确的结果,直接返回 内存里没有数据,从数据文件上读入内存。

所以这样效率比较高。

场景三:MySQL 会在系统空闲时,进入 flush 操作。

场景四:在 MySQL 正常关闭时,会把内存脏页 flush 到磁盘上。

引起 flush 对性能的影响

对于第三,四场景来说,是比较正常的情况,不需要考虑性能问题。

对于第一种场景,InnoDB 会尽量避免,因为在这种情况下,整个系统不再接受更新。

但有时出现人为的配置错误,比如内存为 128 GB,innodb_io_capacity 设置为 20000 的实例。通常建议将 redo log 设置成 4 个 1GB 的文件。但由于配置错误,设置成 100M 的文件。

这里由于 redo log 设置的太小,很快就会被写满。write pos 一直追着 check point. 这时,系统只能停止所有更新,推进 checkpoint.

表现就是,磁盘 IO 很小,但是出现间歇性的性能下降。

对于第二种场景,内存不够用的情况,InnoDB 会用缓冲池(buffer pool)管理内存

内存页在缓冲池中会有三种状态: 没用使用的数据页 使用了,但是是干净页 使用了,是脏页

每个数据页头部有LSN,8字节,每次修改都会变大。

对比这个 LSN 跟 checkpoint 的 LSN,比checkpoint小的一定是干净页

由于 InnoDB 的策略是尽可能使用内存,所以对于长时间运行的库来说,未被使用的页面很少。

当发现想读入的数据页没有在内存中时,必须到缓冲池申请数据页。并会把最久不用得数据页从内存中淘汰

如果是干净页,直接释放使用

如果是脏页,必须先刷盘,变成干净页才能复用

当时,如果在下面的情况进行刷脏页,会明显影响性能:

要淘汰的脏页太多,导致查询响应时间较长。

日志写满,更新被阻塞。

为了解决这个问题,InnoDB 使用控制脏页比例的机制,来避免上面的情况。

InooDB 控制刷脏页的策略

在 InnoDB 中,通过 innodb_io_capacity 参数,来告诉 InnoDB 目前主机的磁盘能力是多少,这个值建议设置成磁盘的 IOPS.

可以通过 fio 这个工具来测试:

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

由于 innodb_io_capacity 导致的性能问题很常见,比如有时系统吞吐量(TPS)很低,写入很慢,但是磁盘 IO 并不高。就有可能是该参数设置的不正确。例如,innodb_io_capacity 的值设置的很低,但是磁盘用的 SSD,导致 InooDB 认为系统能力很差,所以刷脏页特别慢。造成脏页累计,影响查询和更新性能。

InnoDB 在刷盘时主要考虑两个因素:

    脏页的比例

    redo log 写盘速度

会通过这两个因素单独先算出两个数字。

innodb_max_dirty_pages_pct 脏页比例上限,默认 75%.

InnoDB 会根据脏页的比例(M),算出范围在 0 - 100 的数字,过程称为 F1(M)

# M 脏页比例F1(M){ if M>=innodb_max_dirty_pages_pct then return 100; return 100*M/innodb_max_dirty_pages_pct;}

除此之外,InnoDB 每次写入日志都会有一个序号 N. 然后根据 N 再算出一个 0 到 100 的数字,这个计算过程称为 F2(N)

N: 当前写入的序号和 checkpoint 对应序号之间的差值。

最后,根据 F1(M)和 F2(N)两个值,取其中较大的值为 R,之后引擎就可以按照 innodb_io_capacity * R 来控制刷脏页的速度。

所以无论是在查询,需要加载数据到内存数据页,而淘汰脏页。还是更新时,导致刷盘操作都有可能造成 MySQL 的性能下降。

为了避免这种情况,要合理的设置 innodb_io_capacity 的值,平时要多关注脏页比例,不让其接近 75%.

其中脏页比例可以通过下面的方式获取:

mysql> use performance_schema;mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';select @a/@b;

初次之外,在一个查询操作进行时,如果需要 flush 脏页的话,如果这个该脏页的邻居也是脏页的话,就会把这个邻居一起刷掉,如果恰好旁边还是脏页的话,就会一直连坐。这时导致 flush 过慢的原因。

可以通过 innodb_flush_neighbors 来控制该行为,值为 1 打开上述机制,为 0 则关闭。

对于机械硬盘来说,是可以减少很多随机 IO ,因为机械硬盘 IOPS 一般就几百,减少随机 IO 就意味着性能提升。

但如果用 SSD 这类 IOPS 较高的设备,IOPS 往往不是瓶颈,关闭就好,减少 SQL 语句的响应时间。

在 8.0 中,已经默认是 0 了.

总结

这篇中,主要介绍了 WAL 时的 flush 操作可能会造成 MySQL 突然的性能下降。

引起的原因一般是由于内存不够导致的,进而可以通过设置合适的 innodb_io_capacity 参数,来控制 InnoDB flush 的过程。

以上就是MySQL性能突然下降的原因的详细内容,更多关于MySQL性能下降的资料请关注真格学网其它相关文章! 您可能感兴趣的文章:Mysql索引性能优化问题解决方案MySQL性能优化技巧分享mysql优化之like和=性能详析MySQL20个高性能架构设计原则(值得收藏)MYSQL 性能分析器 EXPLAIN 用法实例分析使用Grafana+Prometheus监控mysql服务性能mysql和oracle的区别小结(功能性能、选择、使用它们时的sql等对比)MySQL 查询速度慢与性能差的原因与解决方法

NAME(CHAR32)、2113STR1(CHAR32)......STR10(CHAR32)选择NAME域做Primary索引;5261当采用顺序加入、查询、修改或删4102除(如Name依次取1653取Id000000、Id000001......Id199999)时速度可以,大约30s~40s左右可以完成20万条;可是当打乱顺序访问(Name随机取IdXXXXXX)时,性能下降很厉害,一般下降3~5倍,偶尔也有下降将近10倍的,感觉是和索引乱序使用有关,但如何优化呢?使用的是Linux,引擎是MyISAM,客户端采用C API访问;试过将索引域改为BigINT,问题依旧,自己加了Linux的取操作时间打印,最慢的结果竟有1s多才返回的!内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql 的模块不能安装的解决方法
  • mysql性能优化配置参数之thread_cache和table_cache详解
  • mysql 开启慢查询日志的方法
  • mysql外键约束的禁用与启用命令
  • mysql 5.7.17 winx64解压版安装配置方法图文教程
  • mysql_connect(): connection using old (pre-4.1.1) authentica
  • mysql 8.0.12 安装配置教程
  • mysql表排序规则不同错误问题分析
  • mysql 错误incorrect string value for column
  • idea连接mysql又报错!server returns invalid timezone. go to
  • 请教:乱序操作MySQL性能下降很快,不知是何原因
  • 面试问题,mysql处理什么数量级的数据时,性能会急...
  • mysql数据库突然变慢 数据库变慢是什么原因
  • mysql高并发时性能不如oracle最核心的原因是什么
  • 关于MYSQL服务器突然变慢的问题
  • 请教大神,mysql运行突然变特别慢
  • mysql多少数据量会性能下降
  • mysql数据库性能下降,想找到哪些sql耗时较长,应该...
  • mysql 为什么数据越大 性能越差
  • mysql中哪些因素会影响到查询性能
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql索引性能优化问题解决方案mysql性能优化技巧分享mysql优化之like和=性能详析mysql20个高性能架构设计原则(值得收藏)mysql 性能分析器 explain 用法实例分析使用grafana+prometheus监控mysql服务性能mysql和oracle的区别小结(功能性能、选择、使用它们时的sql等对比)mysql 查询速度慢与性能差的原因与解决方法mysql 的模块不能安装的解决方法mysql性能优化配置参数之thread_cache和table_cache详解mysql 开启慢查询日志的方法mysql外键约束的禁用与启用命令mysql 5.7.17 winx64解压版安装配置方法图文教程mysql_connect(): connection using old (pre-4.1.1) authenticamysql 8.0.12 安装配置教程mysql表排序规则不同错误问题分析mysql 错误incorrect string value for columnidea连接mysql又报错!server returns invalid timezone. go to mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql之timestamp(时间戳)用法mysql update语句的用法详解浅析mysql 定时备份任务mysql 5.6.36 64位绿色版安装图文教程使用mysql mysqldump命令导出数据时的注意mysql配置参数优化详解fedora环境下装mysql命令方法介绍mysql 修改root密码命令小结centos 7.0下使用yum安装mysql的方法详解一千行的mysql学习笔记汇总mysql 开启慢查询日志的方法mysql count多个表的数据实例详解
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved