主页 / server / 问题 / 118504
In Process
mmattax
Asked: 2010-03-03 14:00:20 +0800 CST

如何提高 MySQL INSERT 和 UPDATE 性能？

  • 772

这个问题可能也可以在 StackOverflow 上问，但我会先在这里尝试......

我们数据库中的 INSERT 和 UPDATE 语句的性能似乎正在下降，并导致我们的 Web 应用程序性能不佳。

表是 InnoDB，应用程序使用事务。我可以进行任何简单的调整来加快速度吗？

我想我们可能会看到一些锁定问题，我该如何找到？

performance mysql innodb

6 个回答

    1. 检查您的硬件和操作系统是否已正确配置和调整：

      • 问题的根源（CPU/IO/内存/交换使用）。你有很多眼压吗？CPU负载了吗？如果你有很多读取 IOP，可能你没有足够大的 InnoDB buffer_pool。如果 CPU 已加载，您的查询可能会执行全表扫描，而不是使用正确的索引。
      • 磁盘/RAID/LVM 设置。在某些特定设置中， LVM 条带化可以通过均衡磁盘负载为您带来好处（无硬件 RAID，连接多个 LUN）
      • IO 调度器：当你有好的硬件 RAID 控制器时，noop 可能是最好的。RedHat 做了一些测试，他们说，对于 Oracle（和其他 DB）CFQ 是最佳选择。您需要运行一些基准测试（如 tpc-c 或 tpc-e）并选择最适合您的硬件的。
      • 良好的文件系统 - ext3 在特定于数据库的工作负载中表现不佳。更好的是 XFS 或 OCFS2。您再次需要一些基准。
      • 注意，如果您的系统使用交换。使用 swap 会降低 mysql 的性能。

    2. 检查您的 MySQL/InnoDB 实例是否已正确调整：

      • 缓冲池大小 - 在内存中缓存数据页
      • innodb_flush_method=O_DIRECT - 避免双 IO 缓冲
      • 增加 InnoDB 日志文件大小 - 对于写入密集型工作负载，这可以提高性能。但请记住：更大的日志文件大小意味着更长的崩溃恢复时间。有时是几个小时！！！
      • innodb_flush_log_at_trx_commit=0 或 2 - 如果您不关心 ACID 并且可以在最后一两秒丢失事务。
      • key_buffer_size - 对 MyISAM 非常重要，但它用于磁盘临时表。
      • 注意你的INNODB 状态
    3. 分析您的工作负载 - 将所有查询捕获到慢查询日志并在其上运行 mk-query-digest。您可以使用tcpdump 和 maatkit捕获所有查询
      • 哪些查询占用了您大部分的服务器时间？
      • 是否创建了任何临时表，尤其是大型临时表？
      • 学习，如何使用解释
      • 您的应用程序是否使用事务？当您使用 autocommit=1（默认为 MySQL）运行查询时，每个插入/更新查询都会开始新事务，这会产生一些开销。如果可能，最好禁用自动提交（在 python 中，默认情况下禁用 MySQL 驱动程序自动提交）并在完成所有修改后手动执行提交。
      • 您的应用程序是否在循环中对同一个表进行了一系列插入？Load data infile对于一系列插入，命令要快得多。
      • 记住：select count(*) from table;innodb 比 myisam 慢得多。
      • 哪些类型的 INSERT/UPDATE 查询占用了大部分服务器时间？如何优化它们？
      • 检查您的数据库是否具有正确的索引并在需要时添加它们。

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

    一些有用的链接：

    • 26

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

    如果您可以处理丢失 1s 的事务，这可能是极大提高写入性能的好方法。

    另外，请注意您的磁盘在做什么。RAID 10 > RAID 5 以额外磁盘为代价进行写入。

    • 5

  3. 锁定问题将以show full processlist;

    通读my.cnf和 MySQL 文档。配置选项有很好的记录。

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

    调整将针对您首选的数据库引擎和应用程序架构。有大量资源预先存在互联网搜索。

    • 1

  4. 打开 Innodb 监视器可以帮助识别锁和死锁的原因：

    SHOW ENGINE INNODB STATUS 和 InnoDB 监视器

    • 1

  5. InnoDB 是一个非常好的引擎。但是，它高度依赖于“调整”。一件事是，如果您的插入不是按主键递增的顺序，innoDB 可能需要比 MyISAM 更长的时间。这可以通过设置更高的 innodb_buffer_pool_size 轻松克服。我的建议是将其设置为总 RAM 的 60-70%。我现在在生产中运行 4 个这样的服务器，每分钟插入大约 350 万行。他们已经拥有接近 3 TB 的数据。由于高度并发的插入，它必须是 InnoDB。还有其他方法可以加快插入速度。我已经对一些进行了基准测试。

    • 1

  6. 我如何通过在 java 中禁用自动提交和提交更改一次来解决 Web 应用程序中 MySQL 中的插入和更新性能问题。正如 mysql 文档中所建议的那样。

    MySQL 文档

    • 0

相关问题