MySQL InnoDB存储引擎优化

hudson2022
120 阅读8分钟

MySQL存储引擎优化：优化InnoDB配置以实现高性能

Lukas Vileikis 【hudson 译】

2020年8月21日

图片

InnoDB是 MySQL 中使用最广泛的存储引擎之一, 具有高可靠性和高性能，其主要优点包括支持行级锁定、外键和遵循ACID模型。自2010年发布MySQL5.5以来，InnoDB取代 MyISAM 成为MySQL默认存储引擎。

如果优化得当，InnoDB会具有优异性能和强大的功能。今天，我们要看看可以做些什么来让它发挥最大的性能，但在深入了解InnoDB之前，应该了解前面提到的ACID模型是什么。

什么是ACID？为什么它很重要？

ACID是数据库事务的一组属性。首字母缩写词翻译为四个单词：原子性、一致性、隔离性和持久性。简而言之，这些属性确保了数据库事务的可靠处理，并保证了数据的有效性，尽管存在错误、停电或任何此类问题。符合这些原则的数据库管理系统称为符合ACID的DBMS。InnoDB中的一切工作原理如下：

  • 原子性确保事务中的语句作为一个不可分割的单元运行，并确保它们的效果被集体看到或根本看不到；
  • 一致性由MySQL的日志机制处理，该机制记录数据库的所有更改；
  • 隔离性是InnoDB的行级锁定；
  • 由于InnoDB维护一个日志系统，跟踪系统所有更改，因此可以保持持久性。

了解InnoDB

前面已经介绍了ACID，现在应该看看InnoDB引擎内部是什么样子。以下是InnoDB从内部看起来的样子（图片由Percona提供）：

image.png

从上图中我们可以清楚地看到，InnoDB内部有几个对其性能至关重要的参数，如下所示：

  • innodb_data_file_path 参数描述系统表空间（系统表空间是innodb数据字典、双写和更改缓冲区以及undo日志的存储区域）。该参数描述了从InnoDB表派生的数据将存储在其中的文件；
  • innodb_buffer_pool_size 参数是innodb用来缓存其表的数据和索引的内存缓冲区；
  • innodb_log_file_size 参数描述innodb日志文件的大小；
  • innodb_log_buffer_size 参数用于写入磁盘上的日志文件缓冲区；
  • innodb_flush_log_at_trx_commit 参数控制严格遵守ACID和更高性能之间的平衡；
  • innodb_lock_wait_timeout参数是innodb事务在放弃前等待行锁的时间 (以秒为单位)；
  • innodb_flush_method 参数定义了将数据刷新到innodb数据文件和日志文件的方法，这可能会影响I/O吞吐量。

InnoDB还将其表中的数据存储在一个名为ibdata1的文件中 ，但日志存储在名为ib_logfile0 和ib_logfile1的两个单独文件中：这三个文件都位于/var/lib/mysql目录中。

为了使InnoDB具有尽可能高的性能，我们必须通过查看可用的硬件资源来微调这些参数并尽可能优化它们。

调整InnoDB以实现高性能

要调整InnoDB在硬件上的性能，请执行以下步骤：

  • 为了自动扩展innodb_data_file_path，请在设置中指定autoextend属性，然后重新启动服务器。例如：
innodb_data_file_path=ibdata1:10M:autoextend

使用autoextend参数时，每次需要空间时，数据文件的大小会自动增加8MB。也可以这样指定新的自动扩展数据文件（在本例中，新的数据文件称为ibdata2）：

innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend

  • 使用InnoDB时，其主要机制是缓冲池。InnoDB严重依赖缓冲池， 根据经验， innodb_buffer_pool_size参数应设置大约为服务器上总可用RAM的60%至80%。请记住，您也应该为操作系统中运行的进程留一些RAM；

  • InnoDB的innodb_log_file_size应该设置得尽可能大，但不能超过必要的大小。在这种情况下，请记住，日志文件越大，性能越好，但越大，崩溃后需要的恢复时间就越长。因此，没有“一刀切”的解决方案，但据说日志文件的合并大小应该足够大。这有助于MySQL服务器定期处理检查点和磁盘刷新活动，并节省大量CPU和磁盘IO，并且可以在峰值时间或高工作负载活动期间平稳运行。建议自己测试和试验，寻找最佳值；

  • innodb_log_buffer_size值应至少设置为16M。大型日志缓冲区允许运行大型事务，而无需在事务提交之前将日志写入磁盘，从而节省一些磁盘I/O；

  • 在调优innodb_flush_log_at_trx_commit时，请记住，此参数接受三个值： 0、1和2。为1时，您可以获得ACID遵从性，值为0或2时，您获得更高的性能，但可靠性更低，因为在这种情况下，尚未将日志刷新到磁盘的事务可能会在崩溃中丢失；

  • 为了将innodb_lock_wait_timeout设置为合适的值，请记住，在发出以下错误并回滚当前语句之前，此参数定义了以秒为单位的时间（默认值为50）：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

  • InnoDB中有多种刷新方法可用。默认情况下，如果设置innodb_flush_method为NULL，则在Windows计算机上设置为“async_unbuffered”，在Linux计算机上设置为“fsync”。以下是这些方法及其作用：

    InnoDB Flush MethodPurpose
    normalInnoDB will use simulated asynchronous I/O and buffered I/O.
    unbufferedInnoDB will use simulated asynchronous I/O and non-buffered I/O.
    async_unbufferedInnoDB will use Windows asynchronous I/O and non-buffered I/O. Default settings on Windows machines.
    fsyncInnoDB will use the fsync() function to flush the data and the log files. Default setting on Linux machines.
    O_DSYNCInnoDB will use O_SYNC to open and flush the log files and the fsync()function to flush the data files. O_DSYNC is faster than O_DIRECT, but data may or may not be consistent due to latency or an outright crash.
    nosyncUsed for internal performance testing – unsupported.
    littlesyncUsed for internal performance testing – unsupported.
    O_DIRECTInnoDB will use O_DIRECT to open the data files and the fsync()function to flush both the data and the log files. In comparison with O_DSYNC, O_DIRECT is more stable and more data consistent, but slower. The OS cache will be avoided using this setting – this setting is the recommended setting on Linux machines.
    O_DIRECT_NO_FSYNCInnoDB will use O_DIRECT during flushing I/O – the “NO_FSYNC” part defines that the fsync() function will be skipped.

  • 您还应该考虑启用 innodb_file_per_table设置。在MySQL 5.6及更高版本中，此参数默认为ON。此参数通过将InnoDB表存储在单独的文件中避免主字典和系统表臃肿，从而消除了与InnoDB表相关的管理问题。启用此变量还可以避免在某个表损坏时面临数据恢复复杂性

现在，您已经按照上述说明修改了这些设置，您应该已经准备好了！在开始运行之前，您可能应该关注整个InnoDB基础设施中最繁忙的文件 - ibdata1。

处理ibdata1

ibdata1中存储了几类信息：

  1. InnoDB表的数据；
  2. InnoDB表的索引；
  3. InnoDB表元数据；
  4. 多版本并发控制（MVCC）数据；
  5. 双写缓冲区–这样的缓冲区使InnoDB能够从半写页面恢复。这种缓冲区的目的是防止数据损坏；
  6. 插入缓冲区–InnoDB使用这种缓冲区来缓冲对同一页面的更新，以便可以一次执行，而不是一个接一个地执行。

在处理大型数据集时，ibdata1文件可能会变得非常大，这可能是一个非常令人沮丧的问题的核心——文件只能增长，默认情况下不能收缩。您可以关闭MySQL并删除此文件，但不建议这样做，除非您知道自己在做什么。删除后，MySQL将无法正常运行，因为字典和系统表都不见了，因此主系统表已损坏。 要一次性收缩ibdata1，请执行以下步骤：

  1. 转储InnoDB数据库中的所有数据。您可以使用mysqldump或mysqlpump执行此操作；
  2. 删除除mysql、performance_schema和information_schema数据库之外的所有数据库；
  3. 停止MySQL；
  4. 将以下内容添加到my.cnf文件：
  [mysqld]
  innodb_file_per_table = 1
  innodb_flush_method = O_DIRECT
  innodb_log_file_size = 25% of innodb_buffer_pool_size
  innodb_buffer_pool_size = up to 60-80% of available RAM.

  1. 删除ibdata1和ib_logfile*文件（这些文件将在下次重启MySQL时重新创建）；

  2. 启动MySQL并从之前的转储中恢复数据。执行上述步骤后，ibdata1文件仍将增长，但它将不再包含InnoDB表中的数据–该文件将仅包含元数据，每个InnoDB表将存放ibdata1之外。现在，如果转到/var/lib/mysql目录，您将看到两个文件，代表InnoDB引擎中的每个表。文件将如下所示：

    1.demotable.frm

    2.demotable.ibd

.frm文件包含存储引擎头，.ibd文件包含表数据和表索引。

但是，在进行更改之前，请确保根据基础结构微调参数。这些参数可以决定或影响InnoDB的性能，因此请务必时刻关注它们。

小结

总之，如果您开发的应用程序同时需要数据完整性和高性能，那么优化InnoDB的性能将是一个巨大的好处–InnoDB允许您更改引擎允许使用的内存量，更改日志文件大小，引擎使用的刷新方法等等–如果调整得当，这些更改可以使InnoDB表现极佳。但是，在执行任何增强之前，请注意您的操作对服务器和MySQL的影响。 与往常一样，在优化性能之前，一定要先进行测试备份，以便您可以在必要时恢复数据，并始终在本地服务器上测试任何更改，然后再将更改发布到生产环境中。

avatar
文章
阅读
粉丝