14.8.1 InnoDB 启动配置
关于InnoDB
配置的第一个决定涉及数据文件,日志文件,页面大小和内存缓冲区的配置。建议您在创建InnoDB
实例之前定义数据文件,日志文件和页面大小配置。在创建InnoDB
实例之后修改数据文件或日志文件配置可能涉及不平凡的过程,并且只能在首次初始化InnoDB
实例时定义页面大小。
除了这些主题之外,本节还提供有关在配置文件中指定InnoDB
选项,查看InnoDB
初始化信息以及重要的存储注意事项的信息。
在 MySQL 配置文件中指定选项
由于 MySQL 使用数据文件,日志文件和页面大小配置设置来初始化InnoDB
实例,因此建议您在首次初始化InnoDB
之前,在 MySQL 启动时读取的配置文件中定义这些设置。 InnoDB
在启动 MySQL 服务器时初始化,并且InnoDB
的首次初始化通常在您首次启动 MySQL 服务器时发生。
您可以将InnoDB
个选项放置在服务器启动时读取的任何选项文件的[mysqld]
组中。 MySQL 选项文件的位置在第 4.2.2.2 节“使用选项文件”中描述。
要确保mysqld仅从特定文件中读取选项,请在启动服务器时将--defaults-file选项用作命令行上的第一个选项:
mysqld --defaults-file=path_to_configuration_file
查看 InnoDB 初始化信息
要在启动过程中查看InnoDB
初始化信息,请从命令提示符处启动mysqld。从命令提示符启动mysqld时,初始化信息将打印到控制台。
例如,在 Windows 上,如果mysqld位于C:\Program Files\MySQL\MySQL Server 5.7\bin
,则启动 MySQL 服务器,如下所示:
C:\> "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld" --console
在类似 Unix 的系统上,mysqld位于 MySQL 安装的bin
目录中:
shell> bin/mysqld --user=mysql &
如果不将服务器输出发送到控制台,请在启动后检查错误日志,以查看在启动过程中打印的初始化信息InnoDB
。
有关使用其他方法启动 MySQL 的信息,请参见第 2.10.5 节“自动启动和停止 MySQL”。
Note
InnoDB
在启动时不会打开所有用户 table 和关联的数据文件。但是,InnoDB
会检查是否存在数据字典中引用的 table 空间文件(*.ibd
个文件)。如果未找到 table 空间文件,则InnoDB
记录错误并 continue 启动过程。在重做应用程序崩溃恢复期间,可能会打开重做日志中引用的 table 空间文件。
重要的存储注意事项
在 continue 启动配置之前,请查看以下与存储相关的注意事项。
-
在某些情况下,如果数据未全部放置在同一物理磁盘上,则数据库性能会提高。将日志文件与数据放在不同的磁盘上通常对性能很有帮助。例如,您可以将系统 table 空间数据文件和日志文件放在不同的磁盘上。您还可以将原始磁盘分区(原始设备)用于
InnoDB
数据文件,这可以加快 I/O 速度。参见对系统 table 空间使用原始磁盘分区。 -
InnoDB
是 MySQL 的事务安全(符合 ACID)存储引擎,具有提交,回滚和崩溃恢复功能,可以保护用户数据。 但是,如果基础 os 或硬件无法像所宣传的那样工作,则无法这样做 。许多 os 或磁盘子系统可能会延迟或重新排序写操作以提高性能。在某些 os 上,应该 await 直到刷新文件的所有未写数据的fsync()
系统调用实际上可能在将数据刷新到稳定存储之前返回。因此,os 崩溃或断电可能会破坏最近提交的数据,或者在最坏的情况下,甚至由于对写操作进行了重新排序,甚至会破坏数据库。如果数据完整性对您很重要,请在 Producing 使用任何产品之前执行一些“即插即用”测试。在 macOS 上,InnoDB
使用特殊的fcntl()
文件刷新方法。在 Linux 下,建议 禁用回写缓存 。
在 ATA/SATA 磁盘驱动器上,诸如hdparm -W0 /dev/hda
之类的命令可能会禁用写回缓存。 请注意,某些驱动器或磁盘控制器可能无法禁用回写缓存.
-
关于保护用户数据的
InnoDB
恢复功能,InnoDB
使用涉及称为doublewrite buffer的结构的文件刷新技术,该结构默认情况下(innodb_doublewrite=ON)启用。 doublewrite 缓冲区增加了崩溃或断电后的恢复安全性,并通过减少fsync()
操作的需求来提高了大多数 Unix 类型的性能。如果您担心数据完整性或可能的故障,建议保持innodb_doublewrite选项启用。有关双写缓冲区的其他信息,请参见第 14.12.1 节“ InnoDB 磁盘 I/O”。 -
在
InnoDB
上使用 NFS 之前,请查看在 MySQL 中使用 NFS中概述的潜在问题。 -
innodb_flush_method=async_unbuffered不支持在 Windows 的 4K 扇区硬盘上运行 MySQL 服务器,这是默认设置。解决方法是使用innodb_flush_method=normal。
系统 table 空间数据文件配置
innodb_data_file_path启动选项定义InnoDB
系统 table 空间数据文件的名称,大小和属性。如果在初始化 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。
您可以使用分号分隔的列 table 指定多个数据文件。例如:
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
autoextend
和max
属性只能用于最后指定的数据文件。
指定autoextend
属性后,数据文件会根据需要自动以 64MB 的增量增加大小。 innodb_autoextend_increment变量控制增量大小。
要为自动扩展的数据文件指定最大大小,请在autoextend
属性之后使用max
属性。仅在限制磁盘使用非常重要的情况下,才使用max
属性。以下配置允许ibdata1
增长到 500MB 的限制:
[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M
对* first *系统 table 空间数据文件强制使用最小文件大小,以确保有足够的空间用于双写缓冲区页。下 table 显示了每个InnoDB
页大小的最小文件大小。默认InnoDB
页面大小为 16384(16KB)。
页面大小(innodb_page_size) | 最小 Files 大小 |
---|---|
16384 (16KB)以下 | 3MB |
32768 (32KB) | 6MB |
65536 (64KB) | 12MB |
如果磁盘已满,则可以在另一个磁盘上添加数据文件。有关说明,请参见调整系统 table 空间的大小。
单个文件的大小限制由您的 os 确定。在支持大文件的 os 上,可以将文件大小设置为大于 4GB。您也可以将原始磁盘分区用作数据文件。参见对系统 table 空间使用原始磁盘分区。
InnoDB
尚不知道文件系统的最大文件大小,因此在最大文件大小为较小值(例如 2GB)的文件系统上要保持谨慎。
默认情况下,系统 table 空间文件在数据目录中创建(datadir)。要指定其他位置,请使用innodb_data_home_dir选项。例如,要在名为myibdata
的目录中创建系统 table 空间数据文件,请使用以下配置:
[mysqld]
innodb_data_home_dir = /myibdata/
innodb_data_file_path=ibdata1:50M:autoextend
为innodb_data_home_dir指定值时,必须使用斜杠。 InnoDB
不会创建目录,因此在启动服务器之前,请确保指定的目录存在。另外,请确保 MySQL 服务器具有适当的访问权限,可以在目录中创建文件。
InnoDB
通过将innodb_data_home_dir的值与数据文件名进行文本连接来形成每个数据文件的目录路径。如果未定义innodb_data_home_dir,则默认值为“ ./”,它是数据目录。 (MySQL 服务器开始执行时,将其当前工作目录更改为数据目录.)
如果将innodb_data_home_dir指定为空字符串,则可以为innodb_data_file_path值中列出的数据文件指定绝对路径。以下配置与前面的配置等效:
[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/myibdata/ibdata1:50M:autoextend
重做日志文件配置
默认情况下,InnoDB
在名为ib_logfile0
和ib_logfile1
的数据目录中创建两个 5MB 的重做日志文件。
以下选项可用于修改默认配置:
- innodb_log_group_home_dir定义
InnoDB
日志文件(重做日志)的目录路径。如果未配置此选项,则在 MySQL 数据目录(datadir)中创建InnoDB
日志文件。
您可以使用此选项将InnoDB
日志文件放置在与InnoDB
数据文件不同的物理存储位置,以避免潜在的 I/O 资源冲突。例如:
[mysqld]
innodb_log_group_home_dir = /dr3/iblogs
Note
InnoDB
不会创建目录,因此在启动服务器之前,请确保日志目录存在。使用 Unix 或 DOS mkdir
命令创建任何必要的目录。
确保 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 重做日志”。
撤消 table 空间配置
默认情况下,InnoDB
撤消日志是系统 table 空间的一部分。但是,您可以选择将InnoDB
个撤消日志存储在一个或多个单独的撤消 table 空间中,通常在另一台存储设备上。
innodb_undo_directory配置选项定义InnoDB
在其中为撤消日志创建单独的 table 空间的路径。该选项通常与innodb_rollback_segments和innodb_undo_tablespaces选项结合使用,它们确定系统 table 空间之外的撤消日志的磁盘布局。
Note
innodb_undo_tablespaces已过时,将在以后的版本中删除。
有关更多信息,请参见第 14.6.3.4 节“撤消 table 空间”。
临时 table 空间配置
缺省情况下,InnoDB
创建一个名为ibtmp1
的自动扩展临时 table 空间数据文件,该文件略大于innodb_data_home_dir目录中的 12MB。可以在启动时使用innodb_temp_data_file_path配置选项来修改默认的临时 table 空间数据文件配置。
innodb_temp_data_file_path选项指定InnoDB
临时 table 空间数据文件的路径,文件名和文件大小。通过将innodb_data_home_dir连接到innodb_temp_data_file_path指定的路径来形成文件的完整目录路径。通过将 K,M 或 G 附加到大小值,以 KB,MB 或 GB(1024MB)指定文件大小。文件大小的总和必须略大于 12MB。
innodb_data_home_dir的默认值为 MySQL 数据目录(datadir)。
在使用大型临时 table 或广泛使用临时 table 的环境中,自动扩展临时 table 空间数据文件可能会变得很大。使用临时 table 的长时间运行的查询也可能导致数据文件很大。为防止临时数据文件变得太大,请配置innodb_temp_data_file_path选项以指定最大数据文件大小。有关更多信息,请参见Management 临时 table 空间数据文件大小。
页面大小配置
innodb_page_size选项指定 MySQL 实例中所有InnoDB
table 空间的页面大小。创建实例时设置此值,此后保持不变。有效值为 64KB,32KB,16KB(默认值),8KB 和 4KB。或者,您可以指定页面大小(以字节为单位)(65536、32768、16384、8192、4096)。
默认页面大小为 16KB,适用于各种工作负载,尤其是涉及 table 扫描和涉及批量更新的 DML 操作的查询。对于涉及许多小写操作的 OLTP 工作负载,较小的页面大小可能更有效,当单个页面包含许多行时,争用可能成为问题。对于通常使用较小块大小的 SSD 存储设备,较小的页面可能也很有效。使InnoDB
页面大小接近存储设备块大小可以最大程度地减少重写到磁盘的未更改数据量。
Memory Configuration
MySQL 将内存分配给各种缓存和缓冲区,以提高数据库操作的性能。为InnoDB
分配内存时,请始终考虑 os 所需的内存,分配给其他应用程序的内存以及分配给其他 MySQL 缓冲区和缓存的内存。例如,如果使用MyISAM
table,请考虑分配给键缓冲区(key_buffer_size)的内存量。有关 MySQL 缓冲区和缓存的概述,请参见第 8.12.4.1 节“ MySQL 如何使用内存”。
InnoDB
特定的缓冲区使用以下参数进行配置:
- innodb_buffer_pool_size定义缓冲池的大小,缓冲池是用于存储
InnoDB
table,索引和其他辅助缓冲区的缓存数据的内存区域。缓冲池的大小对于系统性能很重要,通常建议将innodb_buffer_pool_size配置为系统内存的 50%到 75%。默认缓冲池大小为 128MB。有关其他指导,请参见第 8.12.4.1 节“ MySQL 如何使用内存”。有关如何配置InnoDB
缓冲池大小的信息,请参见第 14.8.3.1 节“配置 InnoDB 缓冲池大小”。缓冲池大小可以在启动时配置,也可以动态配置。
在具有大量内存的系统上,可以通过将缓冲池划分为多个缓冲池实例来提高并发性。缓冲池实例的数量由innodb_buffer_pool_instances选项控制。默认情况下,InnoDB
创建一个缓冲池实例。可以在启动时配置缓冲池实例的数量。有关更多信息,请参见第 14.8.3.2 节“配置多个缓冲池实例”。
- innodb_log_buffer_size定义
InnoDB
用于写入磁盘上的日志文件的缓冲区的大小(以字节为单位)。默认大小为 16MB。较大的日志缓冲区使大型事务可以运行,而无需在事务提交之前将日志写入磁盘。如果您有更新,插入或删除许多行的事务,则可以考虑增加日志缓冲区的大小以节省磁盘 I/O。 innodb_log_buffer_size可以在启动时配置。有关相关信息,请参见第 8.5.4 节“优化 InnoDB 重做日志”。
Warning
在 32 位 GNU/Linux x86 上,请注意不要将内存使用率设置得太高。 glibc
可能允许进程堆在线程堆栈上增长,从而使服务器崩溃。如果分配给mysqld进程的全局和每个线程缓冲区和缓存的内存接近或超过 2GB,则存在风险。
可以使用与以下类似的公式来计算 MySQL 的全局和每个线程的内存分配,以估算 MySQL 的内存使用情况。您可能需要修改公式以解决 MySQL 版本和配置中的缓冲区和缓存。有关 MySQL 缓冲区和缓存的概述,请参见第 8.12.4.1 节“ MySQL 如何使用内存”。
innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB
每个线程使用一个堆栈(Oracle 公司提供的 MySQL 二进制文件中通常为 2MB,但只有 256KB),在最坏的情况下还使用sort_buffer_size + read_buffer_size
额外的内存。
在 Linux 上,如果为内核启用了大页面支持,则InnoDB
可以使用大页面为其缓冲池分配内存。参见第 8.12.4.2 节,“启用大页面支持”。