InnoDB
关于配置
的第一个决定涉及数据文件、日志文件、页面大小和内存缓冲区的配置,这些应该在初始化之前配置InnoDB
。在初始化后修改配置InnoDB
可能涉及不平凡的过程。
本节提供有关
InnoDB
在配置文件中指定设置、查看InnoDB
初始化信息和重要存储注意事项的信息。
因为 MySQL 使用数据文件、日志文件和页面大小设置来初始化InnoDB
,所以建议您在初始化之前在 MySQL 启动时读取的选项文件中定义这些设置InnoDB
。通常,InnoDB
在 MySQL 服务器第一次启动时被初始化。
您可以将InnoDB
设置放在
[mysqld]
服务器启动时读取的任何选项文件组中。MySQL 选项文件的位置在第 4.2.2.2 节,“使用选项文件”中描述。
要确保mysqld仅从特定文件读取选项,
--defaults-file
请在启动服务器时将该选项用作命令行上的第一个选项:
mysqld --defaults-file=path_to_option_file
要在启动期间查看InnoDB
初始化信息,请从命令提示符启动mysqld,这会将初始化信息打印到控制台。
例如,在 Windows 上,如果mysqld位于 中C:\Program Files\MySQL\MySQL Server
5.6\bin
,则像这样启动 MySQL 服务器:
C:\> "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --console
在类 Unix 系统上,mysqld位于bin
MySQL 安装目录中:
$> 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
如果您关心数据完整性或可能出现的故障,建议该选项保持启用状态。有关双写缓冲区的信息,请参阅第 14.12.1 节,“InnoDB 磁盘 I/O”.在将 NFS 与 一起使用之前,请查看将 NFS 与 MySQL 一起使用
InnoDB
中概述的潜在问题 。不支持在 Windows 上的 4K 扇区硬盘驱动器上运行 MySQL 服务器
innodb_flush_method=async_unbuffered
,这是默认设置。解决方法是使用innodb_flush_method=normal
.
该选项定义系统表空间数据文件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
如果您的磁盘已满,您可以在另一个磁盘上添加数据文件。有关说明,请参阅 调整系统表空间的大小。
单个文件的大小限制由您的操作系统决定。在支持大文件的操作系统上,您可以将文件大小设置为大于 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 服务器具有在目录中创建文件的适当访问权限。
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
和
的5MB 重做日志文件
。ib_logfile1
以下选项可用于修改默认配置:
innodb_log_group_home_dir
InnoDB
定义日志文件 的目录路径。如果未配置此选项, 则会在 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 重做日志记录”。
默认情况下,撤消日志是系统表空间的一部分。但是,您可以选择将撤消日志存储在一个或多个单独的撤消表空间中,通常是在不同的存储设备上。
innodb_undo_directory
配置选项定义了
InnoDB
为撤消日志创建单独表空间
的路径。此选项通常与
innodb_rollback_segments
和
innodb_undo_tablespaces
选项结合使用,它们确定系统表空间之外的撤消日志的磁盘布局。
有关详细信息,请参阅 第 14.6.3.3 节,“撤消表空间”。
该innodb_page_size
选项指定InnoDB
MySQL 实例中所有表空间的页面大小。该值在创建实例时设置并在之后保持不变。有效值为 16KB(默认值)、8KB 和 4KB。或者,您可以以字节为单位指定页面大小(16384、8192、4096)。
默认的 16KB 页面大小适用于范围广泛的工作负载,特别是涉及表扫描的查询和涉及批量更新的 DML 操作。对于涉及许多小写入的 OLTP 工作负载,较小的页面大小可能更有效,当单个页面包含许多行时,争用可能成为问题。对于通常使用小块大小的 SSD 存储设备,较小的页面也可以更高效。使InnoDB
页面大小接近存储设备块大小可以最大限度地减少重写到磁盘的未更改数据量。
MySQL 将内存分配给各种缓存和缓冲区,以提高数据库操作的性能。为 分配内存时
InnoDB
,请始终考虑操作系统所需的内存、分配给其他应用程序的内存以及分配给其他 MySQL 缓冲区和高速缓存的内存。例如,如果您使用表,请考虑为键缓冲区 ( )MyISAM
分配的内存量。key_buffer_size
有关 MySQL 缓冲区和缓存的概述,请参阅
第 8.12.4.1 节,“MySQL 如何使用内存”。
InnoDB
使用以下参数配置
特定于的缓冲区:
innodb_buffer_pool_size
定义缓冲池的大小,缓冲池是保存InnoDB
表、索引和其他辅助缓冲区的缓存数据的内存区域。缓冲池的大小对于系统性能很重要,通常建议innodb_buffer_pool_size
配置为系统内存的 50% 到 75%。默认缓冲池大小为 128MB。有关其他指导,请参阅 第 8.12.4.1 节,“MySQL 如何使用内存”。有关如何配置InnoDB
缓冲池大小的信息,请参阅 配置 InnoDB 缓冲池大小。缓冲池大小可以在启动时配置。在具有大量内存的系统上,您可以通过将缓冲池划分为多个缓冲池实例来提高并发性。缓冲池实例的数量由 by
innodb_buffer_pool_instances
选项控制。默认情况下,InnoDB
创建一个缓冲池实例。缓冲池实例的数量可以在启动时配置。有关详细信息,请参阅 第 14.8.3.1 节,“配置多个缓冲池实例”。innodb_additional_mem_pool_size
InnoDB
定义用于存储数据字典信息和其他内部数据结构 的内存池的大小(以字节为单位) 。应用程序中的表越多,在这里分配的内存就越多。如果InnoDB
此池中的内存用完,它会开始从操作系统分配内存并将警告消息写入 MySQL 错误日志。默认值为 8MB。innodb_additional_mem_pool_size
在 MySQL 5.6.3 中已弃用。innodb_log_buffer_size
定义InnoDB
用于写入磁盘上的日志文件的缓冲区的大小。默认大小为 8MB。大日志缓冲区使大事务无需在事务提交之前将日志写入磁盘即可运行。如果您有更新、插入或删除许多行的事务,您可能会考虑增加日志缓冲区的大小以节省磁盘 I/O。innodb_log_buffer_size
可以在启动时配置。有关相关信息,请参阅 第 8.5.4 节,“优化 InnoDB 重做日志记录”。
在 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
每个线程使用一个堆栈(通常为 2MB,但在 Oracle 公司提供的 MySQL 二进制文件中只有 256KB。)并且在最坏的情况下还使用sort_buffer_size +
read_buffer_size
额外的内存。
在 Linux 上,如果内核启用了大页面支持,
InnoDB
则可以使用大页面为其缓冲池和附加内存池分配内存。请参阅
第 8.12.4.2 节,“启用大页面支持”。