如何提高 MySQL INSERT 和 UPDATE 性能？

Question 1

检查您的硬件和操作系统是否配置和调整正确：
- 问题来源（CPU/IO/内存/交换使用情况）。您是否有大量 IOP？CPU 是否已满？如果您有大量读取 IOP，则可能是您没有足够大的 InnoDB buffer_pool。如果 CPU 已满，则可能是您的查询执行全表扫描，而不是使用正确的索引。
- 磁盘/RAID/LVM 设置。在某些特定设置中LVM 条带化可以通过均衡磁盘负载（无硬件 RAID，连接多个 LUNS）为您带来好处
- IO 调度程序：当您拥有良好的硬件 RAID 控制器时，noop 可能是最好的。RedHat 做了一些测试他们说，对于 Oracle（和其他数据库）来说，CFQ 是最佳选择。您需要运行一些基准测试（如 tpc-c 或 tpc-e）并选择最适合您硬件的选项。
- 好的文件系统 - ext3 在数据库特定工作负载中表现不佳。XFS 或 OCFS2 更好。您再次需要一些基准测试。
- 注意，您的系统是否使用交换。使用 swap 会降低 mysql 的性能。
检查你的 MySQL/InnoDB 实例是否已正确调整：
- 缓冲池大小——在内存中缓存数据页
- innodb_flush_method=O_DIRECT - 避免双重 IO 缓冲
- 增加 InnoDB 日志文件大小 - 对于写入密集型工作负载，这可以提高性能。但请记住：日志文件越大意味着崩溃恢复时间越长。有时需要几个小时！！！
- innodb_flush_log_at_trx_commit=0 或 2 - 如果您不关心 ACID 并且可以在最后一秒或两秒内丢失交易。
- key_buffer_size - 对 MyISAM 非常重要，但它用于磁盘临时表。
- 小心你的INNODB 状态
分析您的工作负载 - 将所有查询捕获到 slowquery 日志中并在其上运行 mk-query-digest。您可以使用以下方式捕获所有查询tcpdump 和 maatkit
- 哪些查询占用了大部分服务器时间？
- 是否创建了任何临时表，特别是大型临时表？
- 学习，如何使用解释
- 您的应用程序是否使用事务？当您使用 autocommit=1（MySQL 的默认设置）运行查询时，每个插入/更新查询都会开始新的事务，这会产生一些开销。如果可能的话，最好禁用自动提交（在 python MySQL 驱动程序中，自动提交默认是禁用的），并在完成所有修改后手动执行提交。
- 您的应用程序是否循环对同一张表进行一系列插入？Load data infile命令对于一系列插入来说速度要快得多。
- 请记住：select count(*) from table;innodb 比 myisam 慢得多。
- 哪些类型的 INSERT/UPDATE 查询占用了大部分服务器时间？如何优化它们？
- 检查您的数据库是否有适当的索引，并在必要时添加它们。

在我们的环境中，我们遇到过一种情况，即一种更新查询很慢。完成批处理作业的估计时间为 2 天！！！分析 slowquery 日志后，我们发现这种类型的更新查询需要 4 秒才能完成。查询如下所示： update table1 set field1=value1 where table1.field2=xx table2.field3=yy and table2.field4=zz。将更新查询转换为选择查询并对该选择查询运行解释后，我们发现这种类型的查询不使用索引。创建适当的索引后，我们将更新查询的执行时间缩短至毫秒，整个作业在不到两小时内完成。

一些有用的链接：

Answer

检查您的硬件和操作系统是否配置和调整正确：
- 问题来源（CPU/IO/内存/交换使用情况）。您是否有大量 IOP？CPU 是否已满？如果您有大量读取 IOP，则可能是您没有足够大的 InnoDB buffer_pool。如果 CPU 已满，则可能是您的查询执行全表扫描，而不是使用正确的索引。
- 磁盘/RAID/LVM 设置。在某些特定设置中LVM 条带化可以通过均衡磁盘负载（无硬件 RAID，连接多个 LUNS）为您带来好处
- IO 调度程序：当您拥有良好的硬件 RAID 控制器时，noop 可能是最好的。RedHat 做了一些测试他们说，对于 Oracle（和其他数据库）来说，CFQ 是最佳选择。您需要运行一些基准测试（如 tpc-c 或 tpc-e）并选择最适合您硬件的选项。
- 好的文件系统 - ext3 在数据库特定工作负载中表现不佳。XFS 或 OCFS2 更好。您再次需要一些基准测试。
- 注意，您的系统是否使用交换。使用 swap 会降低 mysql 的性能。
检查你的 MySQL/InnoDB 实例是否已正确调整：
- 缓冲池大小——在内存中缓存数据页
- innodb_flush_method=O_DIRECT - 避免双重 IO 缓冲
- 增加 InnoDB 日志文件大小 - 对于写入密集型工作负载，这可以提高性能。但请记住：日志文件越大意味着崩溃恢复时间越长。有时需要几个小时！！！
- innodb_flush_log_at_trx_commit=0 或 2 - 如果您不关心 ACID 并且可以在最后一秒或两秒内丢失交易。
- key_buffer_size - 对 MyISAM 非常重要，但它用于磁盘临时表。
- 小心你的INNODB 状态
分析您的工作负载 - 将所有查询捕获到 slowquery 日志中并在其上运行 mk-query-digest。您可以使用以下方式捕获所有查询tcpdump 和 maatkit
- 哪些查询占用了大部分服务器时间？
- 是否创建了任何临时表，特别是大型临时表？
- 学习，如何使用解释
- 您的应用程序是否使用事务？当您使用 autocommit=1（MySQL 的默认设置）运行查询时，每个插入/更新查询都会开始新的事务，这会产生一些开销。如果可能的话，最好禁用自动提交（在 python MySQL 驱动程序中，自动提交默认是禁用的），并在完成所有修改后手动执行提交。
- 您的应用程序是否循环对同一张表进行一系列插入？Load data infile命令对于一系列插入来说速度要快得多。
- 请记住：select count(*) from table;innodb 比 myisam 慢得多。
- 哪些类型的 INSERT/UPDATE 查询占用了大部分服务器时间？如何优化它们？
- 检查您的数据库是否有适当的索引，并在必要时添加它们。

在我们的环境中，我们遇到过一种情况，即一种更新查询很慢。完成批处理作业的估计时间为 2 天！！！分析 slowquery 日志后，我们发现这种类型的更新查询需要 4 秒才能完成。查询如下所示： update table1 set field1=value1 where table1.field2=xx table2.field3=yy and table2.field4=zz。将更新查询转换为选择查询并对该选择查询运行解释后，我们发现这种类型的查询不使用索引。创建适当的索引后，我们将更新查询的执行时间缩短至毫秒，整个作业在不到两小时内完成。

一些有用的链接：

Question 2

使用默认的 innoDB 配置，您将受到将事务写入和刷新到磁盘的速度限制。如果您可以承受少量 ACID 损失，请尝试使用 innodb_flush_log_at_trx_commit。设置为 0 以每秒写入和刷新日志到磁盘。设置为 1（默认）以在每次提交时写入和刷新。设置为 2 以在每次提交后写入日志文件但每秒仅刷新一次。

如果您可以处理丢失 1 的交易，这可以成为大幅提高写入性能的好方法。

另外，请注意磁盘的运行情况。RAID 10 > RAID 5 的写入操作需要额外的磁盘。

Answer

使用默认的 innoDB 配置，您将受到将事务写入和刷新到磁盘的速度限制。如果您可以承受少量 ACID 损失，请尝试使用 innodb_flush_log_at_trx_commit。设置为 0 以每秒写入和刷新日志到磁盘。设置为 1（默认）以在每次提交时写入和刷新。设置为 2 以在每次提交后写入日志文件但每秒仅刷新一次。

如果您可以处理丢失 1 的交易，这可以成为大幅提高写入性能的好方法。

另外，请注意磁盘的运行情况。RAID 10 > RAID 5 的写入操作需要额外的磁盘。

Question 3

锁定问题将以连接状态为例show full processlist;

通读my.cnf和 MySQL 文档。配置选项的文档非常详尽。

一般来说，您希望尽可能多的数据在内存中处理。对于查询优化，这意味着避免使用临时表。正确使用索引。

调优将针对您首选的数据库引擎和应用程序架构。互联网上有大量现成的资源可供搜索。

Answer