排除 InnoDB 性能故障

在许多情况下，我们可以指出硬件资源是数据库性能问题的瓶颈，在这些情况下的解决方案是升级硬件以增加可用的 CPU 周期，为内存缓冲区添加更多 RAM，或为增加 IOPS 工作负载添加更快的磁盘系统。

查询优化是提高性能的另一个常见方法，应与硬件和 InnoDB 调优一起解决。

但是，使用 InnoDB 时，性能瓶颈可能由不同的问题引起，并且并不总是与硬件或查询相关。

InnoDB 性能不佳的常见原因是应用程序工作负载的配置值不正确或无法更改服务器上的默认 InnoDB 设置。

通过基准变量修改以防止性能瓶颈

以下是基准变量，可以从其默认值进行更改，以帮助防止性能瓶颈。如果遇到性能问题，也应检查这些变量，因为它们是 InnoDB 更改资源利用率的主要方法。

I/O 资源

innodb_io_capacity
innodb_file_io_threads（仅限 5.1）
innodb_write_io_threads（5.5 及更高）
innodb_read_io_threads（5.5 及更高）
innodb_flush_method
innodb_flush_log_at_trx_commit
skip-innodb-doublewrite
sync_binlog

表空间和日志记录

innodb_data_file_path
innodb_log_file_size
innodb_log_files_in_group
innodb_file_per_table

内存资源

innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_additional_mem_pool_size
innodb_log_buffer_size

并发设置

innodb_thread_concurrency
innodb_commit_concurrency

全局状态变量 - 趋势统计

可以随着时间的推移分析和监视以下变量，以便进行趋势分析。

当通过时间序列图查看这些指标的输出时，我们可以看到关系和相关性，这对于将服务和性能问题与 InnoDB 引擎的运行状况和状态保持一致非常有用。

收集这些变量的输出是通过两种类型的查询完成的：

mysq1> show global status;

mysq1> show global status like 'innodb_%'; 

mysq1> show global status like '$variable';

InnoDB 性能状态变量

检查和监视以下变量，可以深入了解InnoDB的持续性能。

以下文档提供了每个变量的详细信息：官方文档

Innodb_buffer_poo1 pages_data
Innodb_buffer_pool_pages_dirty
Innodb_buffer_pool_pages_total
Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads
Innodb_buffer_pool_write_requests
Innodb_data_fsyncs
Innodb_data_reads
Innodb_data_writes
Innodb_pages_read
Innodb_pages_written
Innodb_row_lock_time_avg
Innodb_rows_deleted
Innodb_rows_inserted
Innodb_rows_read
Innodb_rows_updated

InnoDB 挂起操作状态变量

可以监视以下内容并呈现趋势，以分析正在等待其他资源（即处于挂起状态）的操作。

Innodb_log_waits
Innodb_os_log_pending_fsyncs
Innodb_os_log_pending_writes
Innodb_data_pending_fsyncs
Innodb_data_pending_reads
Innodb_data pending_writes
Innodb_row_lock_current_waits
Innodb_row_lock_waits

掌握InnoDB错误代码

在InnoDB的操作过程中，由于冲突或非法操作，您可能会遇到特定的错误代码。这里解释了更常见的由于外键设计的性质、键操、规则导致的常见错误。

1005（ER_CANT_CREATE_TABLE）

这是表创建失败错误。可能的错误包括对（-1）的引用;正在创建的表中的列名与更改冲突;a 到 InnoDB 数据字典中的现有表，并且（150） FK 约束未正确定义。

1016（ER_CANT_OPEN_FILE）

InnoDB数据文件丢失，无法打开进行操作。通常为 .此表存在 FRM 文件，但主数据文件不可用。这通常可以通过删除 .手动 FRM 文件并从备份重新创建表。

1114（ER_RECORD_FILE_FULL）

此错误表示表空间文件正在使用的空间已用完。解决方案是添加额外的表空间文件或将自动扩展选项添加到现有表空间。如果您已将 InnoDB 配置为对表空间使用 RAW 分区，则需要配置额外的表空间。

1205（ER_LOCK_WAIT_TIMEOUT）

如果在事务执行期间发现此错误，则表示已超过锁定等待时间并且事务已回滚。如果您知道应用程序的查询执行统计信息，则可以增加 innodb 1ock_wait_timeout值以防止事务超过超时。

请记住，对于所有应用程序来说，增加超时值不一定是一个好主意，因为手头的原始问题通常是一个有问题的长时间运行的事务，该事务会锁定其他查询需要访问的数据。通过增加超时来解决这些问题并不能解决有问题的长时间运行的查询的基础执行问题。

1206（ER_LOCK_TABLE_FULL）

此错误常见于数据库服务器配置了较小的innodb_buffer_pool_size值的情况，这是锁表大小不够大，无法支持数据库流量在事务锁处理方面的并发性的结果。解决方案取决于基础查询;如果在具有大型缓冲池的服务器上收到此错误，但仅在发出需要大量锁的非常大的 INSERT 事务时收到此错误，并且无法增加缓冲池的大小，则可以发出几个较小的事务来完成相同的更改，从而每个事务需要更少的锁。如果无法将查询拆分为较小的事务，则必须增加缓冲池的大小。

1213（ER_LOCK_DEADLOCK）

有问题的事务遇到死锁，必须再次运行才能完成。当事务 A 正在等待资源或事务 B 持有的锁时，就会发生死锁，但事务 B 正在等待 A 持有的锁;因此，这两个查询都不会执行，因为它们正在等待对方完成并且处于永久循环中。最终事务将超时，必须再次执行才能完成。为了帮助防止死锁，请确保您已为ACID合规性设置了InnoDB。

1216（ER_NO_REFERENCED_ROW）

当执行的查询包含引用父行的外键，但父行不存在时，会发生此错误。通常，如果您正在恢复不同步的备份数据，并且在导入过程中未禁用密钥检查，则会看到此错误。活动数据库的解决方案是事先插入父行，然后重试事务。对于非活动数据库或正在还原的数据库，解决方案是按顺序还原数据或在操作期间禁用密钥检查。

1217（ER_ROW_IS_REFERENCED）

当请求删除行，但该行引用了外键子行，并且您尚未在表定义中配置级联删除时，会发生此错误。解决方案是删除父行之前的子行，或者发出 alter table 命令以允许级联删除，这将处理外键关系，然后重新发出删除事务。

此表外键语句在遇到父/子删除关系时将导致错误 1217：FOREIGN KEY (id) REFERENCES referenced table(child_id);.。

此表外键语句将处理级联删除：FOREIGN KEY(id) REFERENCES referenced_table(child_id) ON DELETE CASCADE;.

本文正在参加「金石计划 . 瓜分6万现金大奖」

InnoDB 性能故障排除解决方案参考