InnoDB Configuration-startup

最新推荐文章于 2023-03-03 17:23:48 发布

举杯酌酒望明月

最新推荐文章于 2023-03-03 17:23:48 发布

阅读量211

点赞数

分类专栏： # InnoDB存储引擎

本文链接：https://blog.csdn.net/wangchaofan1992/article/details/103589857

版权

InnoDB存储引擎专栏收录该内容

7 篇文章 0 订阅

订阅专栏

Undo Tablespace Configuration

Temporary Tablespace Configuration

Page Size Configuration

Memory Configuration

关于InnoDB配置的第一个决定涉及到数据文件、日志文件、页面大小和内存缓冲区的配置。建议在创建InnoDB实例之前定义数据文件、日志文件和页面大小配置。在创建InnoDB实例后修改数据文件或日志文件配置可能涉及一个非常重要的过程，只有在InnoDB实例首次初始化时才能定义页面大小。

除了这些主题之外，本节还提供了有关在配置文件中指定InnoDB选项、查看InnoDB初始化信息和重要存储注意事项的信息。

在MySQL配置文件中指定选项

由于MySQL使用数据文件，日志文件和页面大小配置设置来初始化 InnoDB实例，因此建议您InnoDB在首次初始化之前，在MySQL启动时读取的配置文件中定义这些设置。InnoDB在启动MySQL服务器时初始化，InnoDB的第一次初始化通常在第一次启动MySQL服务器时进行。

您可以将InnoDB options放在服务器启动时读取的任何选项文件的[mysqld]组中。

要确保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。

注意

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

重要的存储注意事项

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

在某些情况下，如果数据不是全部放在同一个物理磁盘上，则数据库性能会提高。将日志文件与数据放在不同的磁盘上通常有助于提高性能。例如，您可以将系统表空间数据文件和日志文件放在不同的磁盘上。您也可以对InnoDB数据文件使用原始磁盘分区（原始设备），这可能会加快I/O速度。请参阅对系统表空间使用原始磁盘分区。
InnoDB是一个面向MySQL的事务安全（ACID兼容）存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。但是，如果底层操作系统或硬件不按公布的方式工作，则无法执行此操作。许多操作系统或磁盘子系统可能会延迟或重新排序写入操作以提高性能。在某些操作系统上，系统调用fsync（）应该等到文件的所有未写入数据都被刷新之后，它可能会在数据被刷新到稳定存储之前返回。因此，操作系统崩溃或断电可能会破坏最近提交的数据，或者在最坏的情况下，甚至会损坏数据库，因为写入操作已重新排序。如果数据完整性对您很重要，请在生产中使用任何东西之前执行一些“拔掉插头”测试。在macOS上，InnoDB使用一个特殊的fcntl（）文件刷新方法。在Linux下，建议禁用写回缓存。

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

关于保护用户数据的InnoDB恢复功能，InnoDB使用一种文件刷新技术，该技术涉及一个名为doublewrite buffer的结构，该结构在默认情况下是启用的（InnoDB_doublewrite=ON）。doublewrite缓冲区为崩溃或断电后的恢复增加了安全性，并通过减少对fsync（）操作的需要提高了大多数Unix上的性能。如果您担心数据完整性或可能出现的故障，建议继续启用innodb_doublewrite选项。
在将NFS与InnoDB一起使用之前，请查看在将NFS与MySQL一起使用中概述的潜在问题。
默认设置innodb_flush_method=async_unbuffered不支持在Windows上的4K扇区硬盘上运行MySQL server。解决方法是使用innodb_flush_method=normal。

系统表空间数据文件配置

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

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M: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变量控制增量大小。

若要指定自动扩展数据文件的最大值，请使用AutoExchange属性之后的Max属性。仅在约束磁盘使用非常重要的情况下才使用max属性。以下配置允许ibdata1扩展到500MB的限制：

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

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

Page Size (innodb_page_size)	Minimum File Size
16384 (16KB) or less	3MB
32768 (32KB)	6MB
65536 (64KB)	12MB

如果磁盘已满，可以在另一个磁盘上添加数据文件。

单个文件的大小限制由您的操作系统决定。在支持大文件的操作系统上，可以将文件大小设置为大于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和 ib_logfile1的5MB的重做日志文件。

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

innodb_log_group_home_dir 定义InnoDB日志文件的目录路径（重做日志）。如果未配置此选项， InnoDB则在MySQL数据目录（datadir）中创建日志文件。

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

[mysqld]
innodb_log_group_home_dir = /dr3/iblogs

注意

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 l日志文件接近限制，但不超过该限制。默认日志文件大小为48MB。通常，日志文件的组合大小应该足够大，以便服务器能够平滑工作负载活动中的高峰和低谷，这通常意味着有足够的重做日志空间来处理一个多小时的写入活动。值越大，缓冲池中需要的检查点刷新活动就越少，从而节省了磁盘I/O。

Undo Tablespace Configuration

默认情况下，InnoDB undo日志是系统表空间的一部分。但是，您可以选择将InnoDB undo日志存储在一个或多个单独的undo表空间中，通常存储在不同的存储设备上。

innodb_undo_directory 配置选项定义了innodb为撤销日志创建单独表空间的路径。此选项通常与 innodb_rollback_segments and innodb_undo_tablespaces 选项一起使用，后者决定了系统表空间之外的undo日志的磁盘布局。

注意

innodb_undo_tablespaces 已弃用，将在以后的版本中删除。

Temporary Tablespace Configuration

默认情况下，InnoDB会在InnoDB_data_home_dir目录中创建一个名为ibtmp1的自动扩展临时表空间数据文件，该文件的大小略大于12MB。默认的临时表空间数据文件配置可以在启动时使用innodb_temp_data_file_path配置选项进行修改。

innodb_temp_data_file_path选项指定innodb临时表空间数据文件的路径、文件名和文件大小。文件的完整目录路径是通过将innodb_data_home_dir连接到innodb_temp_data_file_path指定的路径形成的。文件大小是以KB、MB或GB（1024MB）为单位指定的，方法是在大小值后面附加K、M或G。文件大小之和必须略大于12MB。

innodb_data_home_dir默认值是MySQL数据目录（datadir）。

在使用大型临时表或广泛使用临时表的环境中，自动扩展临时表空间数据文件可能会变大。使用临时表的长时间运行查询也可能导致大型数据文件。若要防止临时数据文件变得太大，请配置innodb_temp_data_file_path选项，以指定最大数据文件大小。

Page Size Configuration

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

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

Memory Configuration

MySQL将内存分配给不同的缓存和缓冲区，以提高数据库操作的性能。在为InnoDB分配内存时，始终考虑操作系统所需的内存、分配给其他应用程序的内存以及分配给其他MySQL缓冲区和缓存的内存。例如，如果使用MyISAM表，请考虑为键缓冲区分配的内存量 (key_buffer_size)。

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

innodb_buffer_pool_size定义缓冲池的大小，它是为NIDB表、索引和其他辅助缓冲区保存缓存数据的内存区域。缓冲池的大小对系统性能很重要，通常建议将 innodb_buffer_pool_size 配置为系统内存的50%到75%。默认缓冲池大小为128MB。
在具有大量内存的系统上，可以通过将缓冲池划分为多个缓冲池实例来提高并发性。缓冲池实例的数量由 innodb_buffer_pool_instances 选项控制。默认情况下，InnoDB创建一个缓冲池实例。启动时可以配置缓冲池实例的数量。
innodb_log_buffer_size以字节为单位定义innodb用来写入磁盘上的日志文件的缓冲区大小。默认大小为16MB。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将日志写入磁盘。如果有更新、插入或删除许多行的事务，可以考虑增大日志缓冲区的大小以保存磁盘I/O。可以在启动时配置innodb_log_buffer_size。

Warning

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

类似于以下计算MySQL全局和每线程内存分配的公式可用于估计MySQL内存使用量。您可能需要修改公式，以便在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可以使用大页面为其缓冲池分配内存。