mysql怎么越用越慢,mysql 速度慢
MySQL数据库服务器逐渐变慢 该怎么分析与解决
我们先来看第一个阶段,MySQL慢的诊断思路,一般我们会从三个方向来做:
创新互联建站自2013年起,先为易门等服务建站,易门等地企业,进行企业商务咨询服务。为易门企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
第一个方向是MySQL内部的观测
第二个方向是外部资源的观测
第三个方向是外部需求的改造
1.1 MySQL 内部观测
我们来看MySQL内部的观测,常用的观测手段是这样的,从上往下看,第一部分是Processlist,看一下哪个SQL压力不太正常,第二步是explain,解释一下它的执行计划,第三步我们要做Profilling,如果这个SQL能再执行一次的话, 就做一个Profilling,然后高级的DBA会直接动用performance_schema ,MySQL 5.7 以后直接动用sys_schema,sys_schema是一个视图,里面有便捷的各类信息,帮助大家来诊断性能。再高级一点,我们会动用innodb_metrics进行一个对引擎的诊断。
除了这些手段以外,大家还提出了一些乱七八糟的手段,我就不列在这了,这些是常规的一个MySQL的内部的状态观测的思路。除了这些以外,MySQL还陆陆续续提供了一些暴露自己状态的方案,但是这些方案并没有在实践中形成套路,原因是学习成本比较高。
1.2 外部资源观测
外部资源观测这部分,我引用了一篇文章,这篇文章的二维码我贴在上面了。这篇文章是国外的一个神写的,标题是:60秒的快速巡检,我们来看一下它在60秒之内对服务器到底做了一个什么样的巡检。一共十条命令,这是前五条,我们一条一条来看。
1.uptime,uptime告诉我们这个机器活了多久,以及它的平均的负载是多少。
2.dmesg -T | tail,告诉我们系统日志里边有没有什么报错。
3.vmstat 1,告诉我们虚拟内存的状态,页的换进换出有没有问题,swap有没有使用。
4. mpstat -P ALL,告诉我们CPU压力在各个核上是不是均匀的。
5.pidstat 1,告诉我们各个进程的对资源的占用大概是什么样子。
我们来看一下后五条:
首先是iostat-xz 1,查看IO的问题,然后是free-m内存使用率,之后两个sar,按设备网卡设备的维度,看一下网络的消耗状态,以及总体看TCP的使用率和错误率是多少。最后一条命令top,看一下大概的进程和线程的问题。
这个就是对于外部资源的诊断,这十条命令揭示了应该去诊断哪些外部资源。
1.3 外部需求改造
第三个诊断思路是外部的需求改造,我在这里引用了一篇文档,这篇文档是MySQL的官方文档中的一章,这一章叫Examples of Common Queries,文档中介绍了常规的SQL怎么写, 给出了一些例子。文章的链接二维码在slide上。
我们来看一下它其中提到的一个例子。
它做的事情是从一个表里边去选取,这张表有三列,article、dealer、price,选取每个作者的最贵的商品列在结果集中,这是它的最原始的SQL,非常符合业务的写法,但是它是个关联子查询。
关联子查询成本是很贵的,所以上面的文档会教你快速地把它转成一个非关联子查询,大家可以看到中间的子查询和外边的查询之间是没有关联性的。
第三步,会教大家直接把子查询拿掉,然后转成这样一个SQL,这个就叫业务改造,前后三个SQL的成本都不一样,把关联子查询拆掉的成本,拆掉以后SQL会跑得非常好,但这个SQL已经不能良好表义了,只有在诊断到SQL成本比较高的情况下才建议大家使用这种方式。
为什么它能够把一个关联子查询拆掉呢?
这背后的原理是关系代数,所有的SQL都可以被表达成等价的关系代数式,关系代数式之间有等价关系,这个等价关系通过变换可以把关联子查询拆掉。
上面的这篇文档是一个大学的教材,它从头教了关于代数和SQL之间的关系。然后一步步推导怎么去简化这句SQL。
第一,MySQL本身提供了很多命令来观察MySQL自身的各类状态,大家从上往下检一般能检到SQL的问题或者服务器的问题。
第二,从服务器的角度,我们从巡检的脚本角度入手,服务器的资源就这几种,观测手法也就那么几种,我们把服务器的资源全部都观察一圈就可以了。
第三,如果实在搞不定,需求方一定要按照数据库容易接受的方式去写SQL,这个成本会下降的非常快,这个是常规的MySQL慢的诊断思路。
MySQL速度变慢,怎么办
MySQL 在崩溃恢复时,会遍历打开所有 ibd 文件的 header page 验证数据字典的准确性,如果 MySQL 中包含了大量表,这个校验过程就会比较耗时。 MySQL 下崩溃恢复确实和表数量有关,表总数越大,崩溃恢复时间越长。另外磁盘 IOPS 也会影响崩溃恢复时间,像这里开发库的 HDD IOPS 较低,因此面对大量的表空间,校验速度就非常缓慢。另外一个发现,MySQL 8 下正常启用时居然也会进行表空间校验,而故障恢复时则会额外再进行一次表空间校验,等于校验了 2 遍。不过 MySQL 8.0 里多了一个特性,即表数量超过 5W 时,会启用多线程扫描,加快表空间校验过程。
如何跳过校验MySQL 5.7 下有方法可以跳过崩溃恢复时的表空间校验过程嘛?查阅了资料,方法主要有两种:
1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ,那么 validate = false,即可以跳过表空间校验。实际测试的时候设置 innodb_force_recovery =1,也就是强制恢复跳过坏页,就可以跳过校验,然后重启就是正常启动了。通过这种临时方式可以避免崩溃恢复后非常耗时的表空间校验过程,快速启动 MySQL,个人目前暂时未发现有什么隐患。2. 使用共享表空间替代独立表空间这样就不需要打开 N 个 ibd 文件了,只需要打开一个 ibdata 文件即可,大大节省了校验时间。自从听了姜老师讲过使用共享表空间替代独立表空间解决 drop 大表时性能抖动的原理后,感觉共享表空间在很多业务环境下,反而更有优势。
临时冒出另外一种解决想法,即用 GDB 调试崩溃恢复,通过临时修改 validate 变量值让 MySQL 跳过表空间验证过程,然后让 MySQL 正常关闭,重新启动就可以正常启动了。但是实际测试发现,如果以 debug 模式运行,确实可以临时修改 validate 变量,跳过表空间验证过程,但是 debug 模式下代码运行效率大打折扣,反而耗时更长。而以非 debug 模式运行,则无法修改 validate 变量,想法破灭。
MySQL数据库服务器逐渐变慢分析与解决方法分享
一、检查系统的状态
通过操作系统的一些工具检查系统的状态,比如CPU、内存、交换、磁盘的利用率,根据经验或与系统正常时的状态相比对,有时系统表面上看起来看空闲,这也可能不是一个正常的状态,因为cpu可能正等待IO的完成。除此之外,还应观注那些占用系统资源(cpu、内存)的进程。
1.使用sar来检查操作系统是否存在IO问题
#sar-u210—
即每隔2秒检察一次,共执行20次。
结果示例:
注:在redhat下,%system就是所谓的%wio。
Linux2.4.21-20.ELsmp
(YY075)05/19/2005
10:36:07AMCPU%user%nice%system%idle
10:36:09AMall0.000.000.1399.87
10:36:11AMall0.000.000.00100.00
10:36:13AMall0.250.000.2599.49
10:36:15AMall0.130.000.1399.75
10:36:17AMall0.000.000.00100.00
其中:
%usr指的是用户进程使用的cpu资源的百分比;
%sys指的是系统资源使用cpu资源的百分比;
%wio指的是等待io完成的百分比,这是值得观注的一项;
%idle即空闲的百分比。
如果wio列的值很大,如在35%以上,说明系统的IO存在瓶颈,CPU花费了很大的时间去等待I/O的完成。Idle很小说明系统CPU很忙。像以上的示例,可以看到wio平均值为11,说明I/O没什么特别的问题,而idle值为零,说明cpu已经满负荷运行了。
2.使用vmstat监控内存
cpu资源
[root@mysql1
~]#
vmstat
procs
———–memory———-—swap–
—–io—-–system–
—–cpu——
r
b
swpd
free
buff
cache
si
so
bi
bo
in
cs
us
sy
id
wa
st
72
25428
54712672264
14
43
53
59
1
198
vmstat
的输出那些信息值得关注?
io
bo:
磁盘写的数据量稍大,如果是大文件的写,10M以内基本不用担心,如果是小文件写2M以内基本正常
①
CPU问题
下面几列需要被察看,以确定cpu是否有问题
Processesinthe
run
queue
(procs
r)
Usertime
(cpu
us)
System
time
(cpu
sy)
Idle
time
(cpu
id)
问题情况:
如果processes
in
run
queue
(procs
r)的数量远大于系统中cpu的数量,将会使系统便慢。
如果这个数量是cpu的4倍的话,说明系统正面临cpu能力短缺,这将使系统运行速度大幅度降低
如果cpu的idle时间经常为0的话,或者系统占用时间(cpu
sy)是用户占用时间(cpu
us)两辈的话,系统面临缺少cpu资源
解决方案
:
解决这些情况,涉及到调整应用程序,使其能更有效的使用cpu,同时增加cpu的能力或数量
②内存问题
主要查看页导入的数值(swap中的si),如果该值比较大就要考虑内存,大概方法如下:
最简单的,加大RAM
减少RAM的需求
3.磁盘IO问题
处理方式:做raid10提高性能
4.网络问题
telnet一下MySQL对外开放的端口,如果不通的话,看看防火墙是否正确设置了。另外,看看MySQL是不是开启了skip-networking的选项,如果开启请关闭。
mysql数据库突然变慢 数据库变慢是什么原因
MySQL 在崩溃恢复时,会遍历打开所有 ibd 文件的 header page 验证数据字典的准确性,如果 MySQL 中包含了大量表,这个校验过程就会比较耗时。 MySQL 下崩溃恢复确实和表数量有关,表总数越大,崩溃恢复时间越长。另外磁盘 IOPS 也会影响崩溃恢复时间,像这里开发库的 HDD IOPS 较低,因此面对大量的表空间,校验速度就非常缓慢。另外一个发现,MySQL 8 下正常启用时居然也会进行表空间校验,而故障恢复时则会额外再进行一次表空间校验,等于校验了 2 遍。不过 MySQL 8.0 里多了一个特性,即表数量超过 5W 时,会启用多线程扫描,加快表空间校验过程。
如何跳过校验MySQL 5.7 下有方法可以跳过崩溃恢复时的表空间校验过程嘛?查阅了资料,方法主要有两种:
1. 配置 innodb_force_recovery可以使 srv_force_recovery != 0 ,那么 validate = false,即可以跳过表空间校验。实际测试的时候设置 innodb_force_recovery =1,也就是强制恢复跳过坏页,就可以跳过校验,然后重启就是正常启动了。通过这种临时方式可以避免崩溃恢复后非常耗时的表空间校验过程,快速启动 MySQL,个人目前暂时未发现有什么隐患。2. 使用共享表空间替代独立表空间这样就不需要打开 N 个 ibd 文件了,只需要打开一个 ibdata 文件即可,大大节省了校验时间。自从听了姜老师讲过使用共享表空间替代独立表空间解决 drop 大表时性能抖动的原理后,感觉共享表空间在很多业务环境下,反而更有优势。
临时冒出另外一种解决想法,即用 GDB 调试崩溃恢复,通过临时修改 validate 变量值让 MySQL 跳过表空间验证过程,然后让 MySQL 正常关闭,重新启动就可以正常启动了。但是实际测试发现,如果以 debug 模式运行,确实可以临时修改 validate 变量,跳过表空间验证过程,但是 debug 模式下代码运行效率大打折扣,反而耗时更长。而以非 debug 模式运行,则无法修改 validate 变量,想法破灭。
分享标题:mysql怎么越用越慢,mysql 速度慢
当前链接:http://myzitong.com/article/dsiejeo.html