【ACDU翻译】 MySQL 15.8.1 InnoDB 启动配置

InnoDB 对配置 做出的第一个决定涉及数据文件、日志文件、页面大小和内存缓冲区的配置，这些都应该在初始化之前进行配置InnoDB。初始化后修改配置InnoDB可能涉及非平凡的过程。

本节提供有关 InnoDB在配置文件中指定设置、查看InnoDB初始化信息和重要存储注意事项的信息。

• 在 MySQL 选项文件中指定选项
• 查看 InnoDB 初始化信息
• 重要的存储注意事项
• 系统表空间数据文件配置
• InnoDB Doublewrite 缓冲区文件配置
• 重做日志文件配置
• 撤消表空间配置
• 全局临时表空间配置
• 会话临时表空间配置
• 页面大小配置
• 内存配置

在 MySQL 选项文件中指定选项

因为 MySQL 使用数据文件、日志文件和页面大小设置来进行初始化InnoDB，所以建议您在 MySQL 启动时读取的选项文件中定义这些设置，然后再进行初始化InnoDB。通常，InnoDB在 MySQL 服务器第一次启动时进行初始化。

您可以将InnoDB选项放在 [mysqld]服务器启动时读取的任何选项文件的组中。MySQL 选项文件的位置在第 4.2.2.2 节，“使用选项文件”中描述。

要确保mysqld仅从特定文件（和mysqld-auto.cnf）读取选项--defaults-file，请在启动服务器时将该选项用作命令行上的第一个选项：

mysqld --defaults-file=path_to_option_file

查看 InnoDB 初始化信息

要在启动期间查看InnoDB初始化信息，请从命令提示符启动mysqld，它将初始化信息打印到控制台。

例如，在 Windows 上，如果mysqld位于C:\Program Files\MySQL\MySQL Server 8.0\bin，则像这样启动 MySQL 服务器：

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --console

在类 Unix 系统上，mysqld位于binMySQL 安装目录中：

$> bin/mysqld --user=mysql &

如果不将服务器输出发送到控制台，请在启动后查看错误日志以查看 InnoDB启动过程中打印的初始化信息。

有关使用其他方法启动 MySQL 的信息，请参阅 第 2.10.5 节，“自动启动和停止 MySQL”。

笔记

InnoDB启动时不会打开所有用户表和关联的数据文件。但是， InnoDB会检查数据字典中引用的表空间文件是否存在。如果未找到表空间文件，则InnoDB记录错误并继续启动序列。在重做应用程序的崩溃恢复期间，可能会打开重做日志中引用的表空间文件。

重要的存储注意事项

在继续进行启动配置之前，请查看以下与存储相关的注意事项。

• 在某些情况下，您可以通过将数据和日志文件放在单独的物理磁盘上来提高数据库性能。您还可以将原始磁盘分区（原始设备）用于 InnoDB数据文件，这可能会加快 I/O。请参阅为系统表空间使用原始磁盘分区。

• InnoDB是一个事务安全（符合 ACID）的存储引擎，具有提交、回滚和崩溃恢复功能以保护用户数据。 但是，如果底层操作系统或硬件不像宣传的那样工作**，它就不能这样做。许多操作系统或磁盘子系统可能会延迟或重新排序写入操作以提高性能。在某些操作系统上，非常fsync() 应该等到文件的所有未写入数据都已刷新的系统调用实际上可能会在数据已刷新到稳定存储之前返回。因此，操作系统崩溃或断电可能会破坏最近提交的数据，或者在最坏的情况下，甚至会损坏数据库，因为写入操作已重新排序。如果数据完整性对您很重要，请在生产中使用任何东西之前执行 “即插即用”测试。在 macOS 上，InnoDB使用特殊的fcntl()文件刷新方法。在 Linux 下，建议禁用回写缓存**。

  在 ATA/SATA 磁盘驱动器上，这样的命令hdparm -W0 /dev/hda可以用来禁用回写缓存。 请注意，某些驱动器或磁盘控制器可能无法禁用回写缓存。

• 关于InnoDB保护用户数据的恢复功能， InnoDB使用了一种文件刷新技术，该技术涉及一种称为 双写缓冲区的结构，默认情况下启用 ( innodb_doublewrite=ON)。双写缓冲区增加了意外退出或断电后恢复的安全性，并通过减少对 fsync()操作的需求来提高大多数 Unix 的性能。innodb_doublewrite 如果您担心数据完整性或可能的故障，建议您保持启用该选项。有关双写缓冲区的信息，请参阅第 15.11.1 节，“InnoDB 磁盘 I/O”.

• 在将 NFS 与 MySQL 结合使用之前，请查看将 NFS 与 MySQL 结合使用InnoDB中列出的潜在问题 。

系统表空间数据文件配置

该选项定义系统表空间数据文件innodb_data_file_path 的名称、大小和属性 。InnoDB如果您在初始化 MySQL 服务器之前没有配置此选项，则默认行为是创建一个略大于 12MB 的单个自动扩展数据文件，命名为 ibdata1：

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

完整的数据文件规范语法包括文件名、文件大小、autoextend属性和 max属性：

file_name:file_size[:autoextend[:max:max_file_size]]

文件大小以千字节、兆字节或千兆字节为单位，通过附加K或M到 G大小值。如果以千字节为单位指定数据文件大小，请以 1024 的倍数执行。否则，千字节值将四舍五入到最接近的兆字节 (MB) 边界。文件大小的总和必须至少略大于 12MB。

您可以使用分号分隔的列表指定多个数据文件。例如：

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

和 属性只能用于最后指定的数据文件 autoextend。max

指定autoextend属性后，数据文件的大小会随着空间的需要自动增加 64MB。该 innodb_autoextend_increment 变量控制增量大小。

要指定自动扩展数据文件的最大大小，请使用max属性后面的 autoextend属性。max仅在限制磁盘使用非常重要的情况下使用该 属性。以下配置允许ibdata1增长到 500MB 的限制：

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

对第一个系统表空间数据文件 强制执行最小文件大小， 以确保有足够的空间用于双写缓冲区页面。下表显示了每个 InnoDB页面大小的最小文件大小。默认 InnoDB页面大小为 16384 (16KB)。

如果您的磁盘已满，您可以在另一个磁盘上添加数据文件。有关说明，请参阅 调整系统表空间的大小。

单个文件的大小限制由您的操作系统决定。在支持大文件的操作系统上，您可以将文件大小设置为 4GB 以上。您还可以将原始磁盘分区用作数据文件。请参阅 为系统表空间使用原始磁盘分区。

InnoDB不知道文件系统的最大文件大小，因此在最大文件大小为较小值（例如 2GB）的文件系统上要小心。

默认情况下，系统表空间文件在数据目录中创建 ( datadir)。要指定备用位置，请使用该 innodb_data_home_dir选项。例如，要在名为 的目录中创建系统表空间数据文件myibdata，请使用以下配置：

[mysqld]
innodb_data_home_dir = /myibdata/
innodb_data_file_path=ibdata1:50M:autoextend

为 指定值时需要尾部斜杠 innodb_data_home_dir。 InnoDB不创建目录，所以在启动服务器之前确保指定的目录存在。此外，确保 MySQL 服务器具有在目录中创建文件的适当访问权限。

InnoDBinnodb_data_home_dir通过以文本方式将 的值连接到数据文件名，形成每个数据文件的目录路径 。如果 innodb_data_home_dir未定义，则默认值为“ ./ ”，即数据目录。（MySQL 服务器在开始执行时将其当前工作目录更改为数据目录。）

或者，您可以指定系统表空间数据文件的绝对路径。以下配置等价于前面的配置：

[mysqld]
innodb_data_file_path=/myibdata/ibdata1:50M:autoextend

当您为 指定绝对路径时 innodb_data_file_path，该设置不会与该 innodb_data_home_dir设置连接。系统表空间文件在指定的绝对路径中创建。在启动服务器之前，指定的目录必须存在。

InnoDB Doublewrite 缓冲区文件配置

从 MySQL 8.0.20 开始，双写缓冲区存储区驻留在双写文件中，这为双写页面的存储位置提供了灵活性。在以前的版本中，双写缓冲区存储区域位于系统表空间中。该 innodb_doublewrite_dir变量定义了InnoDB在启动时创建双写文件的目录。如果未指定目录，则在该 innodb_data_home_dir目录中创建双写文件，如果未指定，则默认为数据目录。

要在目录以外的位置创建双写文件 innodb_data_home_dir，请配置 innodb_doublewrite_dir 变量。例如：

innodb_doublewrite_dir=/path/to/doublewrite_directory

其他双写缓冲区变量允许定义双写文件的数量、每个线程的页数和双写批量大小。有关双写缓冲区配置的更多信息，请参阅 第 15.6.4 节，“双写缓冲区”。

重做日志文件配置

InnoDB默认在数据目录中创建两个 5MB 的重做日志文件，名为 ib_logfile0和 ib_logfile1。

以下选项可用于修改默认配置：

• innodb_log_group_home_dirInnoDB定义日志文件 的目录路径。如果未配置此选项， 则会在 MySQL 数据目录 ( ) InnoDB中创建日志文件。datadir

  您可以使用此选项将InnoDB 日志文件放置在与数据文件不同的物理存储位置， InnoDB以避免潜在的 I/O 资源冲突。例如：

  [mysqld]
  innodb_log_group_home_dir = /dr3/iblogs
  

  笔记

  InnoDB不创建目录，因此在启动服务器之前确保日志目录存在。使用 Unix 或 DOSmkdir 命令创建任何必要的目录。

  确保 MySQL 服务器具有在日志目录中创建文件的适当访问权限。更一般地，服务器必须在它需要创建日志文件的任何目录中具有访问权限。

• innodb_log_files_in_group 定义日志组中的日志文件数。默认值和推荐值为 2。

• innodb_log_file_size 定义日志组中每个日志文件的大小（以字节为单位）。合并的日志文件大小（innodb_log_file_size* innodb_log_files_in_group) 不能超过最大值，略小于 512GB。例如，一对 255 GB 的日志文件接近限制但没有超过限制。默认日志文件大小为 48MB。通常，日志文件的组合大小应该足够大，以便服务器可以消除工作负载活动的高峰和低谷，这通常意味着有足够的重做日志空间来处理超过一个小时的写入活动。较大的日志文件大小意味着缓冲池中的检查点刷新活动较少，从而减少了磁盘 I/O。有关其他信息，请参阅 第 8.5.4 节，“优化 InnoDB 重做日志”。

撤消表空间配置

默认情况下，撤消日志驻留在 MySQL 实例初始化时创建的两个撤消表空间中。

该innodb_undo_directory 变量定义了InnoDB 创建默认撤消表空间的路径。如果该变量未定义，则在数据目录中创建默认撤消表空间。该 innodb_undo_directory变量不是动态的。配置它需要重新启动服务器。

撤消日志的 I/O 模式使撤消表空间成为SSD存储 的理想选择。

有关配置其他撤消表空间的信息，请参阅第 15.6.3.4 节，“撤消表空间”。

全局临时表空间配置

全局临时表空间存储对用户创建的临时表所做更改的回滚段。

默认情况下ibtmp1，在 目录中 命名的单个自动扩展全局临时表空间数据文件。innodb_data_home_dir初始文件大小略大于 12MB。

该innodb_temp_data_file_path 选项指定全局临时表空间数据文件的路径、文件名和文件大小。通过将 K、M 或 G 附加到大小值，以 KB、MB 或 GB 指定文件大小。文件大小或组合文件大小必须略大于 12MB。

要为全局临时表空间数据文件指定备用位置，请 innodb_temp_data_file_path 在启动时配置该选项。

会话临时表空间配置

在 MySQL 8.0.15 及更早版本中，会话临时表空间存储用户创建的临时表和优化器在InnoDB配置为内部临时表的磁盘存储引擎时创建的内部临时表 ( internal_tmp_disk_storage_engine=InnoDB)。从 MySQL 8.0.16 开始，InnoDB始终用作内部临时表的磁盘存储引擎。

该innodb_temp_tablespaces_dir 变量定义了InnoDB 创建会话临时表空间的位置。默认位置是#innodb_temp数据目录中的目录。

要为会话临时表空间指定备用位置，请 innodb_temp_tablespaces_dir 在启动时配置变量。允许使用完全限定的路径或相对于数据目录的路径。

页面大小配置

该innodb_page_size选项指定InnoDB MySQL 实例中所有表空间的页面大小。该值在创建实例时设置，之后保持不变。有效值为 64KB、32KB、16KB（默认）、8KB 和 4KB。或者，您可以以字节为单位指定页面大小（65536、32768、16384、8192、4096）。

默认的 16KB 页面大小适用于各种工作负载，尤其是涉及表扫描的查询和涉及批量更新的 DML 操作。对于涉及许多小写入的 OLTP 工作负载，较小的页面大小可能更有效，当单个页面包含许多行时，争用可能是一个问题。对于通常使用小块大小的 SSD 存储设备，较小的页面也可能更有效。使InnoDB页面大小接近存储设备块大小可以最大限度地减少重写到磁盘的未更改数据量。

内存配置

MySQL 将内存分配给各种缓存和缓冲区，以提高数据库操作的性能。在为 分配内存时 InnoDB，请始终考虑操作系统所需的内存、分配给其他应用程序的内存以及分配给其他 MySQL 缓冲区和缓存的内存。例如，如果您使用表，请考虑为键缓冲区 ( )MyISAM分配的内存量。key_buffer_size有关 MySQL 缓冲区和缓存的概述，请参阅 第 8.12.3.1 节，“MySQL 如何使用内存”。

InnoDB使用以下参数配置 特定于的缓冲区：

• innodb_buffer_pool_size 定义缓冲池的大小，它是保存InnoDB表、索引和其他辅助缓冲区的缓存数据的内存区域。缓冲池的大小对系统性能很重要，通常建议将 innodb_buffer_pool_size其配置为系统内存的 50% 到 75%。默认缓冲池大小为 128MB。有关其他指导，请参阅 第 8.12.3.1 节，“MySQL 如何使用内存”。有关如何配置InnoDB缓冲池大小的信息，请参阅 第 15.8.3.1 节，“配置 InnoDB 缓冲池大小”。缓冲池大小可以在启动时或动态配置。

  在具有大量内存的系统上，您可以通过将缓冲池划分为多个缓冲池实例来提高并发性。缓冲池实例的数量由 by innodb_buffer_pool_instances 选项控制。默认情况下，InnoDB创建一个缓冲池实例。缓冲池实例的数量可以在启动时配置。有关更多信息，请参阅 第 15.8.3.2 节，“配置多个缓冲池实例”。

• innodb_log_buffer_size 定义 InnoDB用于写入磁盘上的日志文件的缓冲区的大小。默认大小为 16MB。大型日志缓冲区使大型事务能够在事务提交之前运行而无需将日志写入磁盘。如果您有更新、插入或删除许多行的事务，您可能会考虑增加日志缓冲区的大小以节省磁盘 I/O。 innodb_log_buffer_size可以在启动时配置。有关相关信息，请参阅 第 8.5.4 节，“优化 InnoDB 重做日志记录”。

警告

在 32 位 GNU/Linux x86 上，如果内存使用率设置得太高， glibc可能会允许进程堆在线程堆栈上增长，从而导致服务器故障。如果分配给mysqld 进程的全局和每个线程缓冲区和缓存的内存接近或超过 2GB，则存在风险。

类似于以下计算 MySQL 的全局和每线程内存分配的公式可用于估计 MySQL 内存使用情况。您可能需要修改公式以考虑 MySQL 版本和配置中的缓冲区和缓存。有关 MySQL 缓冲区和缓存的概述，请参阅第 8.12.3.1 节，“MySQL 如何使用内存”。

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

每个线程使用一个堆栈（通常为 2MB，但在 Oracle Corporation 提供的 MySQL 二进制文件中只有 256KB。），在最坏的情况下还使用sort_buffer_size + read_buffer_size额外的内存。

在 Linux 上，如果内核启用了大页面支持， InnoDB则可以使用大页面为其缓冲池分配内存。请参阅第 8.12.3.2 节，“启用大页面支持”