如果您遵循SQL操作的数据库设计和调整技术的最佳实践,但由于较重的磁盘I / O活动,数据库仍然慢,请考虑这些磁盘I / O优化。如果是Unix.前
工具或Windows任务管理器显示您的工作负载的CPU使用率低于70%,您的工作负载可能是磁盘绑定的。
增加缓冲池大小
当表数据缓存
InnoDB
缓冲池,可以通过查询重复访问它,而无需任何磁盘I / O.使用该池的大小指定缓冲池的大小innodb_buffer_pool_size.
选项。这个内存区域非常重要,通常建议使用它innodb_buffer_pool_size.
配置为50到75%的系统内存。有关更多信息,请参阅,第8.12.3.1节“MySQL如何使用内存”.调整冲洗方法
在某些版本的GNU/Linux和Unix中,用Unix刷新文件到磁盘
fsync ()
呼叫(哪个InnoDB
默认使用)和类似的方法是令人惊讶的慢。如果数据库写性能是个问题,可以使用innodb_flush_method.
参数设置为O_DSYNC
.配置FSYNC阈值
默认情况下,何时
InnoDB
创建一个新的数据文件,例如一个新的日志文件或表空间文件,该文件在被刷新到磁盘之前被完全写入到操作系统缓存中,这可能会导致一次性发生大量的磁盘写活动。要强制从操作系统缓存中更小的、定期的数据刷新,可以使用Innodb_fsync_threshold.
变量以定义阈值,以字节为单位。达到字节阈值时,操作系统缓存的内容被刷新到磁盘。默认值为0强制默认行为,该行为仅在文件完全写入缓存后才会刷新到磁盘。在多个MySQL实例使用相同的存储设备的情况下,指定要强制较小的阈值可能是有益的。例如,创建新的MySQL实例及其关联的数据文件可能会导致磁盘写入活动的大量浪涌,阻碍使用相同存储设备的其他MySQL实例的性能。配置阈值有助于避免在写活动中的这种浪涌。
在Linux上使用具有本机AIO的Noop或截止日期I / O调度程序
InnoDB
使用Linux上的异步I / O子系统(本机AIO)对数据文件页面执行读得前方和写入请求。这种行为由此控制innodb_use_native_aio.
配置选项,默认情况下启用。使用本机AIO,I / O调度器的类型对I / O性能产生了更大的影响。通常,建议使用Noop和截止日期I / O调度员。进行基准测试以确定哪个I / O调度程序为您的工作量和环境提供最佳结果。有关更多信息,请参阅第15.8.6节“使用Linux上使用异步I / O”.在Solaris 10的x86_64架构上使用直接I/O
使用时
InnoDB
Solaris 10的存储引擎用于x86_64架构(AMD Opteron),使用直接I / O forInnoDB
- 避免劣化的文件InnoDB
表现。使用用于存储的整个UFS文件系统的直接I / O.InnoDB
相关文件,用迫使
选项;看mount_ufs(1米)
.(Solaris 10/x86_64的默认值是不是使用此选项。)仅适用I / O仅适用于InnoDB
文件操作而不是整个文件系统,设置innodb_flush_method = o_direct.
.有这个设置,InnoDB
呼叫译:()
代替fcntl()
对于数据文件(不为I / O到日志文件)。在Solaris 2.6或更高版本中,为数据和日志文件使用原始存储
使用时
InnoDB
存储引擎用大innodb_buffer_pool_size.
在Solaris 2.6和UP和UP和任何平台上的任何释放(SPARC / X86 / X64 / AMD64)的价值,进行基准InnoDB
数据文件和原始设备上的日志文件或在单独的直接I / O UFS文件系统上,使用迫使
如前所述的挂载选项。(必须使用挂载选项而不是设置innodb_flush_method.
如果您想要直接I / O用于日志文件。)VERITAS文件系统VXFS的用户应该使用康复= Direct.
挂载选项。不要放置其他MySQL数据文件,例如
myisam.
表,在直接I/O文件系统上。可执行文件或库禁止放在直接I / O文件系统上。使用其他存储设备
可以使用其他存储设备来设置RAID配置。有关相关信息,请参阅第8.12.1节“优化磁盘I / O”.
或者,
InnoDB
表空间数据文件和日志文件可以放在不同的物理磁盘上。有关更多信息,请参阅以下部分:考虑non-rotational存储
非旋转存储通常为随机I / O操作提供更好的性能;和顺序I / O操作的旋转存储。在跨旋转和非旋转存储设备跨越数据和日志文件时,请考虑主要在每个文件上执行的I / O操作的类型。
随机的面向I/ o的文件通常包括文件和一般的表空间数据文件,撤消表空间文件,临时表空间文件。定期I / O导向文件包括
InnoDB
系统表空间文件(由于双重缓冲在MySQL 8.0.20之前改变缓冲)、MySQL 8.0.20中引入的doublewrite文件、日志文件等二进制日志文件和文件重做日志文件。使用非旋转存储时,请检查以下配置选项的设置:
这
CRC32.
Option使用更快的校验和算法,推荐用于快速存储系统。为旋转存储设备优化I/O。禁用非旋转存储或混合旋转和非旋转存储。默认关闭。
允许在空闲周期内放置页面刷新的页面,这可以帮助延长非旋转存储设备的寿命。在MySQL 8.0.18中介绍。
默认设置为200通常足以用于低端非旋转存储设备。对于高端的总线连接设备,考虑更高的设置,例如1000。
默认值2000用于使用非旋转存储的工作负载。对于高端,总线连接的非旋转存储设备,考虑更高的设置,例如2500。
如果重做日志在非旋转存储上,请考虑禁用此选项以减少日志记录。看禁用压缩页面的日志记录.
如果重做日志在非旋转存储上,则配置此选项以最大化缓存和写入组合。
考虑使用与磁盘内部扇区大小匹配的页大小。早期的SSD设备通常有4KB的扇区大小。一些较新的设备有16KB的扇区大小。默认的
InnoDB
页面大小为16KB。保持靠近存储设备块大小的页面大小最小化将其重写为磁盘的不变数据量。如果二进制日志是非旋转存储并且所有表都有主键,请考虑将此选项设置为
最小
减少日志记录。
确保为操作系统启用了Trim支持。它通常默认启用。
增加I/O容量以避免积压
如果吞吐量定期下降,因为
InnoDB
检查站操作,考虑增加价值Innodb_io_capacity.
配置选项。值越高,频率越高冲洗,避免了可能导致吞吐量下降的积压。如果冲洗不会落后,则降低I / O容量
如果系统没有落后
InnoDB
冲洗操作时,考虑降低Innodb_io_capacity.
配置选项。通常,您将此选项值保持低于实用性,但不如前面的子弹中提到的吞吐量中的周期性下降。在典型的方案中,您可以降低选项值,您可能会在输出中看到这样的组合显示引擎的innodb状态
:历史名单长度低,几千以下。
插入缓冲区合并靠近插入的行。
缓冲池中的修改页面始终如一
innodb_max_dirty_pages_pct.
缓冲池。(在服务器未完成批量插入时测量;在批量插入中为修改的页面百分比显着上升,它是正常的。)日志序列号 - 最后一个检查点
处于少于7/8或理想地小于总尺寸的6/8InnoDB
日志文件.
在Fusion-IO设备上存储系统表空间文件
您可以通过存储支持原子写入的Fusion-IO设备上包含双手存储区域的文件来利用双套接缓冲区相关的I / O优化。(在MySQL 8.0.20之前,Dublwrite缓冲区存储在系统表空间数据文件中。就像MySQL 8.0.20一样,存储区域驻留在双手会文件中。看第15.6.4节,“Doublewrite缓冲区”。)当双手存储区域文件放在支持原子写入的Fusion-IO设备上时,自动禁用双手缓冲区,并且融合-IO原子写入所有数据文件。此功能仅支持Fusion-IO硬件,仅支持Linux上的Fusion-IO NVMFS。充分利用这个功能,一个
innodb_flush_method.
设置O_DIRECT
被推荐。笔记因为doublewrite缓冲区设置是全局的,所以对于不驻留在Fusion-io硬件上的数据文件,doublewrite缓冲区也被禁用。
使用时
InnoDB
表格压缩功能,图像重新压缩页面写信给重做日志当更改进行压缩数据时。这种行为是由innodb_log_compression_pages.
控件的不同版本时,它默认启用,以防止发生损坏Zlib.
在恢复期间使用压缩算法。如果你肯定的话Zlib.
版本不受更改影响,禁用innodb_log_compression_pages.
减少修改压缩数据的工作负载的重做日志生成。