mysql表设置阈值_mysql8参考⼿册--优化InnoDB磁盘IO
如果您遵循数据库设计的最佳实践和SQL操作的调整技术，但是由于磁盘I / O活动繁重，数据库仍然很慢，请考虑这些磁盘I/O优化。如果Unix top⼯具或Windows任务管理器显⽰您的⼯作负载中的CPU使⽤率百分⽐⼩于70％，则您的⼯作负载可能是磁盘绑定的。
1、增加缓冲池⼤⼩
当表数据缓存在InnoDB 缓冲池中时，可以通过查询重复访问它，⽽⽆需任何磁盘I / O。使⽤innodb_buffer_pool_size 选项指定缓冲池的⼤⼩ 。此内存区域⾮常重要，因此通常建议将 innodb_buffer_pool_size其配置为系统内存的50％到75％。有关更多信息，请参见第8.12.3.1节“ MySQL如何使⽤内存”。
2、调整冲洗⽅法
在某些版本的GNU / Linux和Unix中，使⽤Unix fsync()调⽤( InnoDB默认情况下使⽤)和类似⽅法将⽂件刷新到磁盘上的速度出奇地慢。如果存在数据库写⼊性能问题，请使⽤innodb_flush_method 参数设置为进⾏基准测试 O_DSYNC。
3、配置fsync阈值
默认情况下，当InnoDB创建新的数据⽂件(例如新的⽇志⽂件或表空间⽂件)时，在将⽂件刷新到磁盘之前，该⽂件已完全写⼊操作系统缓存，这可能导致在以下位置发⽣⼤量磁盘写活动：⼀旦。要强制从操作系统缓存中进⾏较⼩的定期数据刷新，可以使⽤该
innodb_fsync_threshold 变量以字节为单位定义阈值。当达到字节阈值时，操作系统⾼速缓存的内容将刷新到磁盘。默认值0强制执⾏默认⾏为，即仅在将⽂件完全写⼊⾼速缓存后才将数据刷新到磁盘。
在多个MySQL实例使⽤同⼀存储设备的情况下，指定阈值以强制进⾏较⼩的定期刷新可能是有益的。例如，创建⼀个新的MySQL实例及其关联的数据⽂件可能会导致⼤量磁盘写⼊活动，从⽽妨碍使⽤相同存储设备的其他MySQL实例的性能。配置阈值有助于避免写⼊活动激增。
4、在Linux上将noop或截⽌⽇期I / O调度程序与本机AIO⼀起使⽤
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”。
5、在Solaris 10上对x86_64体系结构使⽤直接I/O
InnoDB在Solaris 10的x86_64体系结构(AMD Opteron)上 使⽤存储引擎时，请对InnoDB相关⽂件使⽤直接I / O，以避免性能下降InnoDB。要对⽤于存储InnoDB相关⽂件的整个UFS⽂件系统使⽤直接I / O，请使⽤ forcedirectio选件将其挂载 。见
mount_ufs(1M)。(在Solaris 10 / x86_64上，默认设置是不使⽤此选项。)要将直接I / O仅应⽤于InnoDB⽂件操作⽽不是整个⽂件系统，请设置 innodb_flush_method = O_DIRECT。使⽤此设置， InnoDB呼叫 directio()代替 fcntl() ⽤于数据⽂件的I / O(不适⽤于⽇志⽂件的I / O)。
6、在Solaris 2.6或更⾼版本中将原始存储⽤于数据和⽇志⽂件
在任何版本的Solaris 2.6和更⾼版本以及任何平台(sparc / x86 / x64 / amd64)上InnoDB使⽤具有较⼤innodb_buffer_pool_size价值的存储引擎时，请 InnoDB对原始设备或单独的直接I / O UFS上的数据⽂件和⽇志⽂件进⾏基准测试 ⽂件系统，使⽤forcedirectio如前所述的安装选项。(innodb_flush_method如果要对⽇志⽂件进⾏直接I / O ，则必须使⽤mount选项，⽽不是进⾏设置 。)Veritas⽂件系统VxFS的⽤户应使⽤ convosync=directmount选项。
不要将其他MySQL数据⽂件(例如 MyISAM表的数据)放置在直接I / O⽂件系统上。可执⾏⽂件或库不得放置在直接I / O⽂件系统上。
7、使⽤其他存储设备
mysql下载哪个盘
其他存储设备可⽤于设置RAID配置。有关相关信息，请参见 第8.12.1节“优化磁盘I / O”。
或者，InnoDB可以将表空间数据⽂件和⽇志⽂件放置在不同的物理磁盘上。
8、考虑⾮旋转存储
⾮旋转存储通常为随机I / O操作提供更好的性能。以及⽤于顺序I / O操作的旋转存储。在旋转和⾮旋转存储设备上分布数据和⽇志⽂件时，请考虑主要在每个⽂件上执⾏的I / O操作的类型。
⾯向随机I / O的⽂件通常包括 每表⽂件 和常规表空间数据⽂件， 撤消表空间 ⽂件以及 临时表空间⽂件。⾯向I / O的顺序⽂件包括InnoDB 系统表空间⽂件(由于 doublewrite缓冲和 更改缓冲)和⽇志⽂件，例如⼆进制⽇志⽂件和重做⽇志⽂件。
使⽤⾮旋转存储时，请查看以下配置选项的设置：
(1)、innodb_checksum_algorithm
该crc32选项使⽤更快的校验和算法，建议⽤于快速存储系统。
(1)、innodb_flush_neighbors
优化旋转存储设备的I / O。禁⽌将其⽤于⾮旋转存储或旋转与⾮旋转存储的混合。默认情况下禁⽤。
(1)、innodb_idle_flush_pct
允许在空闲期间限制页⾯刷新，这可以帮助延长⾮旋转存储设备的寿命。在MySQL 8.0.18中引⼊。
(1)、innodb_io_capacity
对于低端⾮旋转存储设备，默认设置200通常就⾜够了。对于⾼端的，总线连接的设备，请考虑更⾼的设置，例如1000。
(1)、innodb_io_capacity_max
默认值2000适⽤于使⽤⾮旋转存储的⼯作负载。对于⾼端的，总线连接的⾮旋转存储设备，请考虑更⾼的设置，例如2500。
(1)、innodb_log_compressed_pages
如果重做⽇志位于⾮循环存储上，请考虑禁⽤此选项以减少⽇志记录。请参阅 禁⽤压缩页⾯的⽇志记录。
(1)、innodb_log_file_size
如果重做⽇志位于⾮循环存储上，请配置此选项以最⼤化缓存和写⼊组合。
(1)、innodb_page_size
考虑使⽤与磁盘的内部扇区⼤⼩匹配的页⾯⼤⼩。早期的SSD设备通常具有4KB的扇区⼤⼩。⼀些较新的设备具有16KB的扇区⼤⼩。默认InnoDB 页⾯⼤⼩为16KB。使页⾯⼤⼩接近存储设备块⼤⼩可以最⼤程度地减少重写到磁盘的未更改数据量。
(1)、binlog_row_image
如果⼆进制⽇志位于⾮循环存储上，并且所有表都具有主键，请考虑设置此选项minimal以减少⽇志记录。
确保为您的操作系统启⽤了TRIM⽀持。通常默认情况下启⽤它。
9、增加I / O容量以避免积压
如果由于InnoDB 检查点 操作导致吞吐量周期性下降 ，请考虑增加innodb_io_capacity 配置选项的值 。较⾼的值会导致更频繁的 刷新，避免积压的⼯作量⽽导致⼯作量下降。
10、如果不落后冲洗，则I / O容量会降低
如果系统没有在InnoDB 刷新操作⽅⾯落后 ，请考虑降低innodb_io_capacity 配置选项的值 。通常，您将此选项值保持尽可能低，但⼜不要太低，以致导致周期性的吞吐量下降，如前⾯的项⽬符号所述。在可能降低选项值的典型情况下，您可能会在的输出中看到类似的组合SHOW ENGINE INNODB STATUS：
(1)历史记录列表长度很低，低于⼏千。
(2)插⼊缓冲区合并到插⼊的⾏附近。
(3)缓冲池中的已修改页⾯始终低于 innodb_max_dirty_pages_pct 缓冲池。(在服务器不执⾏批量插⼊时进⾏测量；在批量插⼊期间，修改后的页⾯百分⽐显着增加是正常的。)
(4)Log sequence number - Last checkpoint ⼩于InnoDB ⽇志⽂件总⼤⼩的7/8，或者理想情况下⼩于⽇志⽂件总⼤⼩的6/8 。
11、将系统表空间⽂件存储在Fusion-io设备上
通过在⽀持原⼦写⼊的Fusion-io设备上存储系统表空间⽂件(“ ibdata⽂件 ”)，可以利⽤与双写缓冲区
相关的I / O优化。在这种情况下，双写缓冲(innodb_doublewrite)将⾃动禁⽤，并且Fusion-io原⼦写操作将⽤于所有数据⽂件。此功能仅在Fusion-io硬件上受⽀持，并且仅在Linux上的Fusion-io NVMFS中启⽤。要充分利⽤此功能， 建议innodb_flush_method设置O_DIRECT为。
注意
由于doublewrite缓冲区设置是全局的，因此，对于⾮Fusion-io硬件上驻留的数据⽂件，也禁⽤doublewrite缓冲。
12、禁⽤压缩页⾯的⽇志记录
使⽤InnoDB表 压缩功能时，对压缩数据进⾏更改时，将重新压缩的页⾯的图像 写⼊ 重做⽇志。此⾏为由控制
innodb_log_compressed_pages，默认情况下启⽤，以防⽌zlib 在恢复过程中使⽤不同版本的压缩算法时可能发⽣的损坏。如果确定zlib 版本不会更改，请禁⽤ innodb_log_compressed_pages 以减少修改压缩数据的⼯作负载的重做⽇志⽣成。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。