如何提高MySQL的INSERT和UPDATE性能?

这个问题也可以在StackOverflow上被问到,但我会先在这里尝试…

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

表是InnoDB,应用程序使用事务。 我可以做些简单的调整来加快速度吗?

我想我们可能会看到一些locking问题,我怎么知道?

  1. 检查您的硬件和操作系统是否正确configuration和调整:

    • 问题的来源(CPU / IO /内存/交换使用)。 你有很多IOP的吗? CPU是否加载? 如果你有很多读取IOP的可能你没有足够大的InnoDB buffer_pool。 如果CPU加载可能您的查询做全表扫描,而不是使用适当的索引。
    • 磁盘/ RAID / LVM设置。 在一些特定的设置中, LVM条带化可以通过磁盘负载(无硬件RAID,连接多个LUN)
    • IO调度程序:当你有好的硬件RAID控制器时,可能noop是最好的。 RedHat做了一些testing ,他们说,对于Oracle(和其他DB)CFQ是最好的select。 您需要运行一些基准testing(如tpc-c或tpc-e)并select最适合您的硬件。
    • 好的文件系统 – ext3在数据库特定的工作负载中performance不佳。 更好的是XFS或OCFS2。 你需要一些基准。
    • 注意,如果你的系统使用交换。 使用交换会降低mysql的性能 。
  2. 检查一下,如果你的MySQL / InnoDB实例调整得当,

    • 缓冲池大小 – caching内存中的数据页面
    • 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来捕获所有的查询
    • 什么查询需要大部分服务器时间?
    • 是否创build了临时表,尤其是大的临时表?
    • 学习,如何使用解释
    • 你的应用程序使用交易吗? 当你使用autocommit = 1(默认为MySQL)运行查询时,每个插入/更新查询开始新的事务,这会产生一些开销。 如果可能的话,最好禁用自动提交(在Python中,默认情况下禁用MySQL驱动程序自动提交),并在所有修改完成后手动执行提交。
    • 您的应用程序是否将循环中的一系列插入操作添加到同一个表中? Load data infile命令对于一系列插入要快得多。
    • 记住: select count(*) from table; innodb比myisam慢得多。
    • 什么types的INSERT / UPDATE查询占用大部分服务器时间? 他们如何优化?
    • 检查,如果你的数据库有适当的索引,并添加它们,如果需要的话。

在我们的环境中,我们遇到了一种更新查询速度慢的情况。 估计完成批量工作的时间是2天! 分析slowquery日志后,我们发现,这种types的更新查询需要4秒钟才能完成。 查询是这样的: update table1 set field1=value1 where table1.field2=xx table2.field3=yy and table2.field4=zz 。 在转换更新查询以select查询并运行该select查询的解释之后,我们发现,这种types的查询不使用索引。 在创build适当的索引后,我们将更新查询执行时间缩短到了几毫秒,整个作业在不到两个小时内完成。

一些有用的链接:

  • 显示INNODB状态走过
  • 安装后在MySQL服务器中调整什么
  • Innodb性能优化基础
  • 如何计算一个好的InnoDB日志文件的大小
  • selectinnodb_buffer_pool_size
  • select适当的innodb_log_file_size

使用默认的innoDBconfiguration,你将受限于你写入和刷新交易到磁盘的速度。 如果您可以处理丢失一点ACID,请试用innodb_flush_log_at_trx_commit。 设置为0可以每秒钟将日志写入磁盘并刷新到磁盘。 设置为1(默认)在每次提交时写入和刷新。 设置为2以在每次提交后写入日志文件,但每秒仅刷新一次。

如果您可以处理丢失1笔交易,这可以大大提高写入性能。

另外,请注意你的磁盘正在做什么。 RAID 10>用于写入的RAID 5是以额外的磁盘为代价的。

locking问题将以show full processlist;的连接状态show full processlist;

通读my.cnf和MySQL文档。 configuration选项有很好的logging。

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

调优将特定于您的首选数据库引擎和应用程序体系结构。 有大量的资源预先存在互联网search。

  • MySQL文档
  • 安装后在MySQL服务器中调整什么

开启Innodb监视器可以帮助识别locking和死锁的原因:

SHOW ENGINE INNODB STATUS和InnoDB Monitors

InnoDB是一个不错的引擎。 但是,它高度依赖于“调整”。 有一件事是,如果你的插入不是增加主键的顺序,innoDB可能比MyISAM稍长一些。 这可以通过设置更高的innodb_buffer_pool_size轻松解决。 我的build议是把它设置在你的总RAM的60-70%。 我现在正在生产4台这样的服务器,每分钟插入大约350万行。 他们已经有接近3千兆字节。 必须是InnoDB,因为高度并发的插入。 还有更多的方法来加速插入。 我已经做了一些基准testing。