MYSQL 显示语句执行所花费的磁盘I/O活动。
发布网友
发布时间：2022-05-02 16:44
共2个回答
懂视网
时间：2022-05-02 21:05
22.磁盘I/O问题
磁盘IO是数据库性能瓶颈，一般优化是通过减少或延缓磁盘读写来减轻磁盘IO的压力及其对性能的影响。
增强磁盘读写性能和吞吐量也是重要的优化手段。
22.1 使用磁盘阵列
RAID（Redundant Array of Inexpensive Disk）是指廉价磁盘冗余阵列，即磁盘阵列。
RAID按照一定的策略将数据分布到若干物理磁盘上，增加数据存储的可靠性，提高数据的读写整体性能，实现了数据的并行读写。
22.1.1 常见RAID级别及其特性
根据数据分布和冗余方式，RAID分为不同的级别。
RAID 0：条带化，按照一定的条带大小将数据依次分布到各个磁盘，没有数据冗余。优点：数据并发读写速度快，无额外磁盘开销。缺点：数据无冗余，可靠性差。
RAID 1：磁盘镜像，两个磁盘一组，所有数据都同时写入两个磁盘，可以从任一磁盘读取。优点：数据完全冗余，提高并发读操作。缺点：磁盘有效容量只有总容量的一半。
RAID 10：RAID 0+RAID 1，先做磁盘镜像再做条带化，兼具RAID 1的可靠性和RAID 0的并发读写性能。优点：可靠性，并发读写性能优良。缺点：磁盘有效容量只有总容量的一半。
RAID 4：RAID 0+校验纠错，先做条带化，额外增加一个磁盘做各个条带的校验纠错数据。优点：一个磁盘损坏可以通过校验纠错数据计算出来，有一定的容错能力，读速度快。缺点：写性能受影响。
RAID 5：改进RAID 4，将各个条带的校验纠错数据也分别写到各个磁盘中。优点：比RAID 4有更好的写性能和数据保护能力。缺点：写性能不及RAID 0、RAID 1、RAID 10，容错能力不及RAID 1，出现坏盘时读性能下降。
22.1.2 如何选择RAID级别
数据库 读写都很频繁，可靠性要求高，选择RAID 10；
数据仓库 读很频繁，写相对较少，可靠性有一定要求，选择很RAID 5；
网站类 读写都很频繁，可靠性要求不高，选择RAID 0。
22.2 虚拟文件卷或软RAID
Linux的虚拟文件卷或软RIAD软件支持模拟实现RAID，虽然性能上不如硬件RAID，但是比单个磁盘的性能和可靠性高。
22.3 使用Symbolic Links分布I/O
针对MyIASM存储引擎可以将表、索引的数据分不到不同的磁盘，其它存储引擎不支持。
先在目标磁盘上创建目录
#mkdir /otherdisk/databases/test
再创建从Mysql数据目录到目标目录的软连接
#ln -s /otherdisk/databases/test /path/to/datadir
将MyIASM存储引擎的数据文件(.MYD)和索引文件(.MYI)指向其它的物理磁盘,表定义文件(.frm)不能指向其它磁盘。
>create table test(
id int primary key,
name varchar(20))
engine=myisam
data directory=‘/disk2/data‘
index directory=‘/disk3/index‘;
22.4 禁止操作系统更新文件的atime属性
atime是一个文件属性，读文件时，操作系统会将当前时间更新到atime属性上。
可以修改文件系统配置文件/etc/fstab来禁止更新文件的atime属性，以减轻磁盘IO负担。
LABLE=/home /home ext3 noatime 1 2
重新mount文件系统，即/home下的文件不会修改文件的atime属性
#mount -oremount /home
22.5 用裸设备(Raw Device)存放InnoDB的共享表空间
InnoDB使用缓存机制存储表和索引的数据，操作系统IO缓存对其性能没有帮助，可以使用Raw Device存放InnoDB的共享表空间。
修改my.cnf，在innodb_data_file_path参数中增加裸设备文件名并指定neweaw属性：
[mysqld]
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
启动Mysql，使其完成初始化工作，然后关闭Mysql；
将innodb_data_file_path参数中的neweaw该成raw：
[mysqld]
innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
重新启动Mysql，即可开始使用。
22.6 调整I/O调度算法
磁盘读取数据的3个步骤:
将磁头移动到磁盘表面的的正确位置，所花费的时间称为寻道时间；
等待磁盘旋转，需要的数据会移动到磁头下面，所花费的时间称为旋转时间(与磁盘转速有关)；
继续旋转，所需要的数据都经过磁头读取，所花费的时间称为传输时间。
寻道时间取值范围[5ms-10ms],旋转时间取值范围[2ms-5ms],传输时间与数据量有关取值范围[0ms-1ms]。
所以降低寻道次数，从而能够优化磁盘IO。
操作系统将IO请求放入队列，对请求进行合并和排序，即将相同或相邻扇区的请求进行合并，并按请求数据所在磁道由内向外的顺序对请求排序，已达到一次寻道处理IO请求，减少寻道次数。
Linux的4种IO调度算法：
NOOP算法(No Operation)：只对IO请求合并而不进行排序，使用先进先出FIFO队列顺序提交IO请求。
最后期限算法(Deadline)：IO请求在队列内进行合并、排序，并维护带有超时的写请求对列和读请求队列。新请求被插入普通队列和读写队列，一般按照普通对象的顺序处理请求，当读写队列某个请求快要超时时将被优先处理。
预期算法(Anticipatory)：基于预测的IO算法，在最后期限算法(Deadline)的基础上增加等待时间(6ms),等待时间内有相同扇区的新请求到达时优先处理新请求，否则按照普通队列顺序处理。
完全公平队列(Complete Fair Queuing/CFQ)：把IO请求按照进程分别放入每个进程的队列中，以时间片算法轮转处理每个队列的请求。
Mysql建议使用最后期限算法(Deadline)，如果是SSD建议使用NOOP算法(No Operation)。
查看当前系统支持的IO调度算法：
#dmseg | grep -i scheduler
查看当前设备使用的IO调度算法：
#more /sys/block/sda/queue/scheduler
修改当前块设备的IO调度算法，修改后直接生效：
#echo "deadline" > /sys/block/sda/queue/scheduler
永久修改IO调度算法，通过修改内核引导参数，增加elevator=调度程序名
#vi /boot/grub/menu.lst
kernel /boot/vmlinuz-2.6.18-308.e15 ro root=LABLE=/ elevator=deadline
22.7 RAID卡电池充放电问题
22.7.1 什么是RAID卡电池充放电
RAID卡写缓存(Battery Backed Write Cache)有助于IO性能提升。
RAID卡写缓存有电池，用于断电后将写缓存中的数据写入磁盘，防止断电导致的写缓存中的数据丢失。
RAID卡电池会定期充、放电，定期充、放电的操作称为电池校准。
电池校准的3个阶段：首先RAID控制器会将电池充满，然后开始放电，再重新充满。
查看RAID卡电池状态：
#MegaCli64 -AdpBbuCmd -GetBbuStatus -aAll
22.7.2 RAID卡缓存策略
查看RAID卡缓存策略：
#MegaCli64 -LDInfo -Lall -aAll
写缓存策略，取值包括：WriteBack、WriteThrough。
WriteBack：将数据写入RAID卡缓存后直接返回，由RAID卡控制器负责将缓存中的数据写入磁盘。
WriteThrough：将数据直接写入磁盘，不使用RAID卡缓存。
是否开启预读，取值包括：ReadAheadNone、ReadAhead、ReadAdaptive。
ReadAheadNone：不开启预读
ReadAhead：开启预读，预先把后面顺序的数据加载入缓存，提升顺序读的性能，降低了随机读的性能。
ReadAdaptive：自适应预读，在缓存和IO空闲时进行顺序预读，否则不进行预读。默认设置。
读操作是否缓存到RAID卡中，取值包括：Direct、Cached。
Direct：读操作不缓存到RAID卡中
Cached：读操作缓存到RAID卡中
电池问题时是否启用Write Cache,取值包括：Write Cache Ok if Bad BBU、No Write Cache if Bad BBU。
No Write Cache if Bad BBU：电池问题时不启用Write Cache，直接写入磁盘。
Write Cache Ok if Bad BBU：电池问题时仍启用Write Cache。
修改RAID卡缓存策略：
#MegaCli64 -LDSetProp -WB -Lall -aAll -- 写缓存策略修改为WriteBack
#MegaCli64 -LDSetProp -WT -Lall -aAll -- 写缓存策略修改为WriteThrough
#MegaCli64 -LDSetProp -CachedBadBBU -Lall -aAll -- 电池问题时启用Write Cache策略Write Cache Ok if Bad BBU
#MegaCli64 -LDSetProp -NoCachedBadBBU -Lall -aAll -- 电池问题时不启用Write Cache策略No Write Cache if Bad BBU
再RAID卡电池校准期间或电池故障期间，默认RAID卡写缓存策略会从WriteBack变为WriteThrough，会造成系统写入性能下降。
可以通过修改RAID卡缓存策略调整，无需重启。#MegaCli64 -LDSetProp -CachedBadBBU -Lall -aAll
等业务高峰期过后应即时修改为#MegaCli64 -LDSetProp -NoCachedBadBBU -Lall -aAll 避免断电RAID卡写缓存数据丢失。
22.7.3 如何应对RAID卡电池充放电带来的I/O性能波动
定期在业务量低的时候对RAID卡电池充放电，避免在高峰期发生写缓存策略切换(从WriteBack到WriteThrough)。
DELL服务器RAID卡电池充放电周期为90天；IBM服务器RAID卡电池充放电周期为30天。
从日志查看RAID卡电池下次充电时间：
#MegaCli64 -fwtermlog -dsply -a0 -nolog
从命令查看RAID卡电池下次充电时间：
#MegaCli -AdpBbuCmd -GetBbuProperties -aall
手动触发电池校准操作：
#MegaCli64 -AdpBbuCmd -BbuLearn -aALL
在有UPS电源的情况下可以强制设置写缓存策略为WriteBack,避免写入性能波动。
22.8 NUMA架构优化
商用服务器分类：SMP对称多处理器结构(Symmetric Multi-Process)、NUMA非一致存储访问结构(Non-Uniform Memory Access)、MPP海量并行处理结构(Massive Parallel Processing)。
22.8.1 SMP架构
SMP架构一组CPU共享内存和总线，系统将任务队列对称地分布在多个CPU上，各CPU平等的访问内存、IO、外设，又称为一致存储访问结构UMA。
SMP扩展方式有：增加内存、增加CPU、更换更高频的CPU、扩充IO、增加磁盘。
SMP特征是共享，缺点是通过同一套总线机制访问，存在资源争用而产生锁等待。
22.8.2 NUMA架构
NUMA架构是将物理机分为多个节点，每个节点有一组CPU和内存，节点之间通过互联模块进行连接和通信。
每个CPU可以访问物理机的整个内存，但是访问本节点内存要快于访问其它节点的内存。
显示当前NUMA的节点情况：# numactl --hardware
空闲内存(单位MB)：#:free -m
节点距离(Node Distances)是指CPU访问本节点内存和其它节点内存的资源消耗度量。
默认CPU访问本节点内存节点距离为10，访问其它节点内存节点距离为21。
NUMA的内存分配策略有4种：
缺省default:总是在本节点内分配；
绑定bind:在指定节点上分配；
交叉interleave:在所有节点或指定的多个节点上交叉分配；
优先preferred:优先在指定节点上分配，失败则在其它节点上分配。
显示当前系统的NUMA内存分配策略：# numactl --show
NUMA内存分配默认策略会导致各节点之间内存分配不均衡，某个节点产生Swap也不会从其它节点分配内存，这种现象称为Swap Insanity。
Mysql是单进程多线程架构的数据库，一个Mysql实例只能在一个NUMA节点上运行，使用本节点Swap也不会使用其它节点的内存的现象也会产生。
解决方法：
1.将NUMA的内存分配策略由default修改为interleave。
修改mysql启动脚本mysqld_safe，添加一行cmd="/usr/bin/numactl --interleave all $cmd"，含义：在Mysql启动时指定内存分配策略为interleave。
# vi $MYSQL_HOME/bin/mysqld_safe
cmd="/usr/bin/numactl --interleave all $cmd"
for i in "$ledir/$MYSQLD" "$defaults" "--basedir=$MY_BASEDIR_VERSION"
"--datadir=$DATADIR" "--plugin-dir=$plugin-dir" "$USER_OPTION"
do
cmd="$cmd "`shell_quote_string "i"`
done
保存后重启Mysql。
2.在OS内核关闭NUMA特性
修改/etc/grub.conf文件，在kernel那行追加numa=off
# vi /etc/grub.conf
...
kernel /boot/...quiet 追加numa=off
保存后重启服务器，再次检查numa节点：
# numactl --hardware 只剩一个节点了。
如果有多个Mysql实例，则可以指定到不同的节点上，采用绑定bind内存分配策略，Mysql缓冲区参数设置不能大于本节点的内存。
NUMA架构缺点在于跨节点的内存访问存在延时，性能不是随着CPU的数量线性增长的。
22.8.3 MPP架构
MPP架构由多个SMP服务器通过一定的节点互联网络进行连接，每个节点只访问本地资源，不访问其它节点资源，这种Share Nothing架构理论上支持无限扩展。
节点之间的信息交互通过节点互联网络实现，称为DATA Redistribution。
MPP服务器有机制来调度和平衡各个节点的负载和并行处理。
22.9 小结
22.Mysql磁盘I/O
标签：iad 内存分配 name 损坏 缺点 缓存策略 平衡 ibm服务器 sys
热心网友
时间：2022-05-02 18:13
