likes
comments
collection
share

InnoDB引擎配置高阶篇

作者站长头像
站长
· 阅读数 23

本文正在参加「技术专题19期 漫谈数据库技术」活动 

高级配置参数

本文将涵盖更高级的使用和配置以及边缘情况操作。

此外，这些设置可以帮助解决InnoDB引擎的问题，根据特定需求微调引擎，以及深入了解事务性能的不同操作状态。

重要的是要注意，这些设置会极大地改变InnoDB处理数据的方式，如果使用不当，对其中许多变量的错误或公然更改可能会导致数据丢失，数据损坏和服务器进程故障。

只有在管理员完全测试了数据库的稳定性后，才应使用这些高级设置。

InnoDB和输入/输出资源

InnoDB处理数据更改操作的方法要求将数据更改存储在基于内存的缓冲区中，然后写入日志文件，最终写入基于磁盘的数据表。

从 I/O 的角度来看，写入磁盘的过程会占用大量资源，并且是事务提交过程中最慢的部分。因此，在提交发生后将数据更改写入磁盘的性能由以下因素决定：

  • 磁盘速度

  • RAID 体系结构和设置

  • 底层文件系统类型和行为

  • InnoDB 用于在提交事务后刷新日志的方法

从MySQL 5.5开始，InnoDB具有变量以更好地处理高性能存储阵列，因此了解存储的性能是调优过程中的基本要求。

我们可以使用许多工具测试 I/O 系统的性能。

最流行的 I/O 特定测试应用如下：

  • SysBench

  • Bonnie++

  • Fio

典型的测试旨在使用可重复的测试方法使I / O系统饱和，以便可以在任意数量的机器上进行相同的测试，从中可以比较每台机器的统计输出以进行性能评估。

以下命令是常规数据库相关 I/O 测试方法的示例

SysBench

首先，我们运行一个 prepare 命令来设置测试文件，然后使用第二个命令运行实际测试。在此测试中，我们运行顺序写入测试，如 --file-test-mode=seqwr，但我们也可以运行以下测试模式：

  • seqrewr

  • seqrd

  • rndrd

  • rndwr

  • rndrw

Fio

Fio测试程序功能非常齐全。文档和更多示例可以在freecode.com/projects/fi…的开发人员网站上看到。

Bonnie++

长期以来，Bonnie++ 一直被认为是开源文件系统基准测试的行业标准，它有许多深入的测试可用于完全自定义的负载测试。完整的文档和示例测试可以在开发人员页面 http://www.coker.com.au/bonnie++/中参考。

Disk speed: standard IOPS（磁盘速度：标准 IOPS）

InnoDB引擎配置高阶篇 通常，我们可以参考下表来显示当前磁盘的 I/O 容量，也称为每秒输入输出。

了解基础知识是调整与 I/O 使用相关的各种 InnoDB 变量的必要条件。

这些值是基于平均磁盘速度结果的近似值。出于容量规划目的，应始终执行实际测试。

InnoDB引擎配置高阶篇

一个非常简化的例子是，如果我们的MySQL服务器每秒接收1000个已提交的事务，并且每个事务都必须写入和刷新（默认的ACID要求），这意味着我们的存储层将需要处理1000 IOPS，每秒输入输出操作，或者更多如果InnoDB启用了双重写入设置。

在此示例中，在 IOPS 容量规划的最基本术语中，如果我们服务器的数据分区托管在 SSD 驱动器上，那么我们可以根据上表中列出的常规 IOPS 性能数字处理此负载。但是，如果我们的数据位于基于心轴的磁盘上，那么我们将需要使用某种形式的 RAID（最好是 RAID-10）来聚合磁盘并实现更高的 IOP 吞吐量以支持我们的性能要求。

在可能的情况下，我们可以选择带有电池备份缓存的 RAID 控制器，因为它既能提供在断电期间维持状态的弹性，又能提供比缺少此功能的控制器更好的性能。一般来说，高性能存储系统的预算很高，但在许多情况下，我们可以调整InnoDB以通过更改刷新加写入特征来实现每秒更多的事务，而无需更改我们的存储层，因此我们可以在更高的查询流量负载下运行，而不会增加更快的存储系统的支出。

关于存储的一个重要架构考虑因素是，如果您在 RAID 或 SAN 系统上操作 MySQL 数据分区，无论使用何种存储引擎，该系统通过基于易失性 RAM 的缓存系统缓冲更改;更改不会跨系统脱机/联机状态保留，则数据库在电源故障或硬件故障情况下存在数据丢失的风险。

如果使用基于缓存的存储，请确保它是非易失性的，并维护电池备份缓存，以确保缓存中的数据能够在电源故障情况下进行处理并写入磁盘。一些硬盘驱动器，包括固态硬盘，提供用于禁用易失性缓存设置的固件选项;

如果缓存是易失性的，那么InnoDB可能会认为它保留了对磁盘的写入，只是在缓存失败或以其他方式在数据从缓存中刷新并完全写入磁盘之前脱机时，该数据会丢失。如果存储提供程序将 LUN 设备、光纤通道或 iSCSI 或其他方式分配给数据库服务器，请确保在生产中操作之前指定此要求。

InnoDB variables（InnoDB 变量）

InnoDB具有两种类型的配置变量：

  • 静态：静态变量仅在进程重启时生效

  • 动态：动态变量可以在运行时通过 SET GLOBAL key=key=key=value;query 进行更改

Dynamic InnoDB variables （动态 InnoDB 变量）

动态变量是可以在进程启动期间设置的设置，但如有必要，可以在运行时进行更改。这允许在操作期间调整数据库服务器，而无需重新启动以更改配置参数。

InnoDB引擎配置高阶篇

innodb_table_locks

这是一个布尔变量选项，用于控制 InnoDB 如何处理行级别的锁定。默认情况下，InnoDB不会像MyISAM那样锁定整个表，但有时需要全表锁定。如果使用 LOCK TABLES SQL 命令，此变量为 InnoDB 提供了锁定整个表的功能，但前提是您的自动提交模式设置为 0。将自动提交设置为零值，通过 SET autocommit = 0;在查询期间，只有在执行 COMMIT 后才会对表数据进行 MySQL 提交更改，否则它将立即进行更改。这一点特别重要，因为它会影响处理交易的方式以及如何保护我们的数据免受一致性和 ACID 合规性的影响。

仅当您有特定需要更改将事务数据提交到数据库的方法时，才应从其默认设置 1 更改此值。在故障排除过程中，这是一个很好的变量，可以检查以确保

数据库按预期运行。此变量在锁定表的边缘情况行为方面发生了细微变化。MySQL 版本 5.5.3 之后的 WRITE 命令;应该检查版本以确定它是否会影响服务器。

仅当您有特定需要更改将事务数据提交到数据库的方法时，才应从其默认设置 1 更改此值。在故障排除过程中，这是一个很好的变量，可以检查以确保

数据库按预期运行。此变量在锁定表的边缘情况行为方面发生了细微变化。MySQL 版本 5.5.3 之后的 WRITE 命令;应该检查版本以确定它是否会影响服务器。

innodb_flush_log_at_trx_commit

除了innodb_buffer_pool_size之外，innodb_flush_log_at_trx_commit变量是提高事务提交性能的最有效变量之一。默认情况下，此变量设置为值 1，这意味着 InnoDB 会将基于内存的日志缓冲区的内容写入基于磁盘的日志文件，并在每次事务提交后对日志文件发出刷新。根据服务器文件系统的性能特征和 I/O 容量，此刷新操作可能会导致大量 I/O 使用，从而产生性能瓶颈，从而阻止其他数据库操作。我们可以通过更改此变量来更改 InnoDB 刷新写入磁盘子系统的频率。此变量的备用值包括 o 和 2。

值 o 将导致 InnoDB 每秒一次将日志缓冲区的内容写入日志文件，并在文件系统级别对日志文件执行刷新;无论每秒提交次数如何，都会执行此操作。

值 2 将导致 InnoDB 在每次提交期间将日志缓冲区写入磁盘，但不在每次提交时刷新。此外，根据文件系统上使用的I / O调度程序，InnoDB可能会也可能不会每秒将日志文件刷新到磁盘一次，尽管它会尝试这样做。如果将此变量设置为值 2，以确保按预期处理 InnoDB 的 I/O 事件，则为文件系统测试和选择正确的 I/O 调度程序至关重要。

更改此变量有优点和缺点。

性能提升不是免费的;它们通常是速度、稳定性和成本之间的折衷。在通过更改InnoDB存储引擎的写入和刷新特性获得性能的情况下，我们正在损害事务处理序列的稳定性和数据完整性。

默认设置 1 保证能够提供 ACID 合规性，任何其他设置都将禁用 ACID 合规性;特别是这会影响耐久性特性。如果innodb_flush_log_at_trx_commit设置为 2 或 0，则我们冒着丢失应用程序认为它已以持久方式提交的最多一秒事务数据的风险。

具体来说，当设置为 0 时，如果 MySQL 进程崩溃，我们可能会丢失数据，而设置为 2 只会在物理服务器本身遭受严重故障（例如断电）时导致数据丢失。因此，按照数据完整性与性能提升的顺序，最安全且最不注重性能的设置是默认值 1，然后是 2，然后是 o。

innodb_io_capacity

InnoDB在写入磁盘之前依赖于RAM，但最终每个更改的数据都必须进入磁盘才能保持持久性。

MySQL 5.1 中的新功能是 InnoDB 能够通过设置存储 I/O 容量的上限来针对更高性能的存储系统进行微调。从MySQL 5.5开始，此值的默认设置为200 IOPS，以前的版本将其硬编码为100。

默认设置等效于在单个 15k SAS 驱动器（或使用相同的磁盘的 RAID-1 阵列）或四磁盘 RAID-10 SATA 驱动器阵列上运行存储层;这些是 MySQL 服务器的常见本地磁盘存储设置。但是，如果您使用的是更大的磁盘阵列或光纤连接的 DAS 或 SAN，则可以将此变量调整得更高，以支持提高的 IOPS 性能。调整此变量时，明智的做法是同时调整以下变量：

  • innodb_write io_threads

  • innodb_read_io_threads

innodb_thread_concurrency

此变量的默认值设置为 8，但在高性能服务器上，由于用于处理线程执行的方法，此值可能会导致 InnoDB 性能延迟。当 InnoDB 收到工作负载请求时，它会从操作系统请求工作线程并开始执行工作负载。

InnoDB 在任何给定时间将使用的最大并发工作线程数受此变量的限制，因此默认情况下，InnoDB 最多只能使用八个工作线程来执行工作负载。

如果 InnoDB 在分配最大数量后需要额外的工作线程，它们将被放置在 FIFO 队列中，以便在现有工作线程完成或终止之前进行分配和处理。这种等待线程锁定的方法可能会导致队列饱和和事务处理延迟。

具有大量线程的服务器通常会受益于将此变量增加到总执行线程数的 50% 到 100% 的数量级。如果您的服务器支持英特尔的超线程并已启用，则最好不要考虑在调整过程中使用这些虚拟线程，而是将报告的总线程数除以 2 倍以获得总物理线程数。

根据所使用的MySQL版本，此变量的解释会有所不同。

在 5.1.12 之前，大于 20 的设置被视为无限设置，并且将被视为与 21 或 221 相同，这可能会在优化过程中引起混淆。但是，后来的版本不会遭受这种无限状态。在 5.1.12 之后的版本中，可以将此变量设置为 o 值以完全禁用并发检查，这允许 InnoDB 使用操作系统分配的线程数;请谨慎使用此设置，尤其是在您在同一台服务器上运行其他服务器应用程序（例如集成的 LAMP 服务器）时，因为 InnoDB 可能会使用所有可能的线程并使其他系统进程

innodb_commit_concurrency

默认情况下，InnoDB将使用尽可能多的线程来提交数据。在某些情况下，这可能会有问题，尤其是在使用没有资源配额的虚拟化时，或者在具有共享资源的多租户和集成服务平台的情况下。因此，如果我们分析 InnoDB 以仅使用一组特定的总可用资源，则可以对其进行调整以限制并发提交线程的数量。

虽然这是一个动态变量，但从 5.1.36 开始，在运行时无法将其更改为/从零值更改;但是，允许动态控制不包括零的值。

innodb_support_xa

默认情况下启用，可以更改此设置以防止InnoDB支持

两阶段全局事务提交查询。此外，如果您的服务器正在运行多个线程，并使用二进制日志系统进行复制或其他存档/时间点恢复，则此设置对于确保按顺序将数据更改写入 bin 日志至关重要。按顺序记录数据在复制方案、时间点恢复和所有 ACID 系统中至关重要。

只有在主服务器仅通过单个线程写入更改，因此无法同时执行对数据的无序更改的情况下，以及在单线程复制服务器上，复制本身通过单个执行线程运行。在这些情况下禁用此设置可以提高性能，但应注意不要在不考虑这些 uuse 情况的情况下将此设置从一台服务器复制到另一台服务器，否则可能会发生数据损坏和/或复制失败。

innodb_fast_shutdown

默认情况下，InnoDB 将在发出服务停止时发出快速关闭。这意味着它会跳过完全清除操作，并且不会发出插入缓冲区合并过程。在具有繁重插入工作负载的大型数据库上，这可以节省关闭过程中的时间，这是大多数方案的理想选择。

MySQL 崩溃相当于将此变量设置为值 2，通常保留用于需要立即停止数据库的用例。在这种情况下，InnoDB将在下一次进程启动期间发出并尝试崩溃恢复。崩溃恢复可能需要很长时间，具体取决于崩溃时的活动工作负载。

将此变量设置为值 o 的适当时间（发出完全清除和插入缓冲区合并）适用于以下方案：

  • 在MySQL升级或降级之前

  • 关闭数据库以进行二进制数据迁移

  • 服务器的物理重定位

  • 添加、增长或收缩 InnoDB 日志文件

Static InnoDB variables（静态 InnoDB 变量）

静态变量仅在MySQL进程启动序列期间读取。这意味着一旦在mysql中定义了静态变量。它将在整个运行时进程中处于活动状态，并且在数据库服务器进程的操作期间无法更改。

innodb_file_per_table

分配和管理磁盘上的数据库表时，使用了几种典型方法：

  • 原始分区表空间

  • 共享文件表空间

  • 每个表一个文件

    像往常一样，在解决问题时，有多种解决方案，每种解决方案都有好处和坏处，因此有些解决方案比其他解决方案更适合某些用例。innodb_file_per_table 的变量就是这样一种体系结构选择

管理非常适合其预期用例的数据;将数据分离到唯一命名的组中，并在资源级别进行管理。很简单，此变量配置 InnoDB，以便它将为每个 InnoDB 表创建表数据并将其存储在私有、非共享的单个数据文件中。

使用这种数据管理方法具有以下好处：

  • 如果使用符号链接将大型表分发到不同的分区，则更容易管理数据文件

  • 更容易对每个表的 I/O 资源使用情况进行故障排除，因为与监视共享表空间文件相比，可以监视文件以查看哪个正在使用更多/更少的 I/O 流量

  • 更容易修复和从损坏的数据中恢复：

    • 数据是每个表分开的，因此一个损坏的表不会影响我们其余的InnoDB表

    • 导出和重新导入表的数据仅影响单个

  • 表空间文件而不是更大的聚合共享表空间 允许 DBA 将表分发到单独的文件，以便在处理非常大的模式时绕过具有单个文件大小限制（例如，Ext3 中的 2 TB）运行的文件系统

  • 如果从表中操作大量删除，则由于InnoDB清除数据并在磁盘上保留块的方式，使用innodb_file_per_table回收磁盘上的文件系统空间比回收共享表空间要容易得多 与共享表空间相比，碎片更少，更容易碎片整理（优化表）

  • 如果在多租户情况下运行MySQL，例如共享主机，则每个客户端/模式将位于单独的文件中，而不是在共享表空间中，这可以被视为某些审计中的安全优势

  • 更轻松的备份过程

    • 与一个潜在的非常大的共享表空间相比，通过网络进行基于冷拷贝二进制数据的备份将更快地发送和缓冲较小的单个表空间文件和索引文件，如果没有复杂的分块和校验和处理和验证，则无法拆分为较小的文件。此外，我们不能发出多个复制线程来利用更多的网络带宽，因为单个表空间文件只能通过单个流传输。
    • 热备份仅复制现有数据，而不复制预分配的空数据，如果在预先分配了 100 GB 但只有 25 GB 空间用于活动数据的共享表空间上使用 InnoDB 热备份，则备份仍将复制所有预分配的 100 GB 用于备份。如果我们使用 innodb_file_per_table并且我们的总组合表空间文件在磁盘上有 25 GB 的数据，那么 InnoDB 热备份将生成一个 25 GB 的备份集。

如您所见，启用此变量有很多好处

表空间文件将位于其各自的模式目录中，而不是嵌套在由变量定义创建的共享表空间文件中：innodb_data_file_path。如果在创建 InnoDB 表后设置此变量，则数据将不会迁移到唯一文件，而是会像以前一样继续存储在共享表空间文件中。只有在启用此变量后创建的新表才会被分隔到它们自己的表空间文件中。要在功能上收缩或删除共享表空间中的内容，必须通过备份到 SQL 或其他平面格式将数据导出出表空间，然后可以删除表空间并重新导入数据。

innodb_additional_mem_pool_size

此变量控制分配给 InnoDB 数据字典的内存量，其中包含有关正在存储的 InnoDB 数据的内容的基本引用和元数据。默认设置为 8 MB，因此如果您有很多表，这是一个不错的增加变量。

典型设置包括从 16 MB 到 128 MB 的合理数量的 RAM。InnoDB只会根据需要存储尽可能多的数据，因此将其增加到所需大小以上不会加快事务处理速度。

多年来，MySQL以不同的方式处理此缓冲区设置，因此建议在进行任何调整之前参考MySQL版本的文档。

innodb_buffer_pool_instances

从MySQL 5.5开始，此变量允许增强对内存的控制

通过将innodb_buffer_pool_size变量的配置值分配到 N 个单独的区域进行管理。

仅当缓冲池的大小为 1 GB 或更大时，此操作才会生效。从默认值更改此值的主要好处是，在使用数据更改率较高的较大缓冲池时，可以提高并发性。

MySQL建议将其设置为一个值，以便每个缓冲池实例保持最小大小为1 GB或更大。性能改进来自于在缓冲池上存在大量读取和写入流量的情况下减少访问争用。

innodb_write_io_threads

这控制了InnoDB将用于的后台I / O线程的数量各种基于写入的工作负载。

默认设置为 4，但在支持每秒更高 I/O 操作的高端存储系统上，它可以设置为 64。

请记住，每个后台线程最多可以处理 256 个挂起的操作，因此此变量是该数字的倍数。

这可能是一个难以预测最佳设置的设置，但理智的过程是在InnoDB状态输出中监视>64个innodb写入io请求，这通常表明应该增加此变量以降低挂起的请求

innodb_read_io_threads

此变量类似于 innodb write_io_threads，但指定 InnoDB 可以利用的读取 I/O 线程数。此值的优化以与写入线程变量类似的方式完成。

skip-innodb-doublewrite

InnoDB 默认启用innodb_doublewrite设置，因此它将将所有传入数据写入两次。数据将写入双写缓冲区，然后写入物理数据文件，以确保在断电或其他系统故障时的数据完整性。这可以通过--skip-innodb-doublewrite配置设置禁用，以实现最大的写入性能，因为它减少了I/O系统上所需的写入工作负载。