MySQL数据库优化最佳实践 [英] MySQL database optimization best practices

问题描述

当处理较大的表（大约50k条记录，每个表总共大约100MB）时，优化MySQL安装以获得最佳性能的最佳实践是什么？我们目前正在寻找重写DelphiFeeds.com（一个Delphi编程社区的新闻网站），并注意到简单的Update语句可能需要长达50ms。这看起来很多。是否有任何推荐的配置设置，我们应该启用/设置，通常禁用标准的MySQL安装（例如，利用更多的RAM缓存查询和数据等）？

What are the best practices for optimizing a MySQL installation for best performance when handling somewhat larger tables (> 50k records with a total of around 100MB per table)? We are currently looking into rewriting DelphiFeeds.com (a news site for the Delphi programming community) and noticed that simple Update statements can take up to 50ms. This seems like a lot. Are there any recommended configuration settings that we should enable/set that are typically disabled on a standard MySQL installation (e.g. to take advantage of more RAM to cache queries and data and so on)?

此外，存储引擎的选择会对性能产生什么影响？我们计划使用InnoDB，但如果出于性能原因推荐MyISAM，我们可能使用MyISAM。

Also, what performance implications does the choice of storage engines have? We are planning to go with InnoDB, but if MyISAM is recommended for performance reasons, we might use MyISAM.

推荐答案

实践是：

1. 测量性能，隔离相关子系统以及可以。


2. 找出瓶颈的根本原因。你是I / O绑定吗？ CPU绑定？内存边界？正在等待锁？


3. 进行更改以缓解您发现的根本原因。


4. 再次测量以证明您已解决瓶颈和

转到步骤2，并根据需要重复操作，直到系统运行足够快。

1. Measure performance, isolating the relevant subsystem as well as you can.
2. Identify the root cause of the bottleneck. Are you I/O bound? CPU bound? Memory bound? Waiting on locks?
3. Make changes to alleviate the root cause you discovered.
4. Measure again, to demonstrate that you fixed the bottleneck and by how much.
5. Go to step 2 and repeat as necessary until the system works fast enough.

通过 http://www.mysqlperformanceblog.com 订阅RSS Feed，并阅读历史文章。这是一个非常有用的资源的性能相关的智慧。例如，你问过InnoDB和MyISAM。他们的结论：InnoDB的性能比MyISAM平均高出30％。虽然也有一些使用场景，其中MyISAM执行InnoDB。

Subscribe to the RSS feed at http://www.mysqlperformanceblog.com and read its historical articles too. That's a hugely useful resource for performance-related wisdom. For example, you asked about InnoDB vs. MyISAM. Their conclusion: InnoDB has ~30% higher performance than MyISAM on average. Though there are also a few usage scenarios where MyISAM out-performs InnoDB.

博客的作者也是高性能MySQL的合着者，由@Andrew Barnett提到的书。

The authors of that blog are also co-authors of "High Performance MySQL," the book mentioned by @Andrew Barnett.

从@ʞɔʞɔu注释：如何判断你是I / O绑定还是CPU绑定与内存绑定是平台相关的。操作系统可以提供诸如ps，iostat，vmstat或top等工具。如果您的操作系统没有提供第三方工具，您可能必须获得第三方工具。

Re comment from @ʞɔıu: How to tell whether you're I/O bound versus CPU bound versus memory bound is platform-dependent. The operating system may offer tools such as ps, iostat, vmstat, or top. Or you may have to get a third-party tool if your OS doesn't provide one.

基本上，无论哪种资源与100％利用率/饱和度挂钩，成为你的瓶颈。如果你的CPU负载很低，但你的I / O负载在你的硬件的最大值，那么你是I / O绑定。

Basically, whichever resource is pegged at 100% utilization/saturation is likely to be your bottleneck. If your CPU load is low but your I/O load is at its maximum for your hardware, then you are I/O bound.

这只是一个数据点， 。补救措施还可能取决于其他因素。例如，一个复杂的SQL查询可能正在做一个文件，这使I / O忙。你应该抛出更多/更快的硬件，或者你应该重新设计查询以避免filesort？

That's just one data point, however. The remedy may also depend on other factors. For instance, a complex SQL query may be doing a filesort, and this keeps I/O busy. Should you throw more/faster hardware at it, or should you redesign the query to avoid the filesort?

在StackOverflow文章中总结的因素太多，而且许多书籍在这个问题上存在的事实支持这一点。保持数据库高效运作，充分利用资源是一项需要专业技能和不断学习的全职工作。

There are too many factors to summarize in a StackOverflow post, and the fact that many books exist on the subject supports this. Keeping databases operating efficiently and making best use of the resources is a full-time job requiring specialized skills and constant study.

Jeff Atwood刚刚写了一篇关于在系统中发现瓶颈的好文章：

Jeff Atwood just wrote a nice blog article about finding bottlenecks in a system:

• 电脑性能壳游戏

• The Computer Performance Shell Game

这篇关于MySQL数据库优化最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！