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

本文是小编为大家收集整理的关于MySQL数据库优化的最佳实践的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

处理更大的桌子时,优化MySQL安装以获得最佳性能的最佳实践是什么(> 50k记录,每张表约为100MB)?我们目前正在研究重写Delphifeeds.com(Delphi编程社区的新闻网站),并注意到简单的更新语句最多可能需要50ms.这似乎很多.是否有任何推荐的配置设置我们应该启用/设置通常在标准MySQL安装上禁用(例如,利用更多RAM来缓存查询和数据等)?

)

另外,存储引擎的选择有什么影响?我们打算与InnoDB一起使用,但是如果推荐Myisam出于绩效原因,我们可能会使用Myisam.

推荐答案

"最佳实践"是:

  1. 衡量性能,尽可能地隔离相关子系统.
  2. 确定瓶颈的根本原因.您是我/o绑定的吗? CPU绑定?内存绑定?等待锁?
  3. 进行更改以减轻您发现的根源.
  4. 再次测量,以证明您通过多少.
  5. 来固定瓶颈和
  6. 转到步骤2并根据需要重复直到系统运行得足够快.

http://www.mysqlperformanceblog.com上订阅RSS feed.这是与性能相关的智慧的极其有用的资源.例如,您询问了Innodb vs. Myisam.他们的结论是:InnoDB平均比Myisam高30%.虽然还有一些用法场景,Myisam表现出Innodb.

该博客的作者也是@Andrew Barnett提到的《高性能Mysql》的合着者.


从 @ʞɔıu发表评论:如何判断您是我/o绑定与cpu绑定与内存绑定的限制是平台依赖性的.操作系统可以提供PS,IOSTAT,VMSTAT或TOP等工具.或者,如果您的操作系统不提供一个.

,您可能必须获得第三方工具.

基本上,无论哪种资源都可以固定在100%利用/饱和的情况下,可能是您的瓶颈.如果您的CPU负载较低,但您的I/O负载最大为硬件,则您是I/O绑定的.

这只是一个数据点.补救措施也可能取决于其他因素.例如,复杂的SQL查询可能正在执行Filesort,这使I/O忙.您是否应该向其投掷更多/更快的硬件,还是应该重新设计查询以避免FileSort?

在Stackoverflow帖子中有太多因素可总结,并且该主题上存在许多书籍的事实支持了这一点.保持数据库有效运行并充分利用资源是一项需要专业技能和不断学习的全职工作.


杰夫·阿特伍德(Jeff Atwood

其他推荐答案

去购买奥莱利(O'Reilly)的"高性能mysql".关于该主题的几乎是700页,所以我怀疑您会在SO上找到一个简洁的答案.

其他推荐答案

很难宽泛的事物,但是可以使用中等高级的视图.

  • 您需要评估读取比率.对于比率低于5:1的表,您可能会从InnoDB中受益,因为插入不会阻止选择.但是,如果您不使用交易,则应将innodb_flush_log_at_trx_commit更改为1,以使Myisam的性能重新恢复.
  • 查看内存参数. MySQL的默认值非常保守,甚至在普通硬件上也可以提高10倍或更多的内存限制.这将使您的选择受益而不是插入.
  • mySQL可以记录不使用索引的查询,以及需要太长时间(用户确定)的查询.
  • 查询缓存可能很有用,但是您需要仪器(即查看使用多少).仙人掌可以做到这一点;穆宁也可以.
  • 应用程序设计也很重要:
    • 轻轻的缓存经常被获取,但小型数据集将有很大的差异(即,缓存寿命为几秒钟).
    • 不要重新提取您已经必须交出的数据.
    • 多步存储可以帮助将大量的插入插入到也忙于阅读的表中.基本想法是,您可以拥有一个用于临时插入的表(INSERT DELAYED也可能很有用),但是将MySQL中的更新从那里移动到所有读取的批处理过程.有很多变化.
  • 不要忘记这种观点和上下文也很重要:如果"长"更新仅每天发生一次,那么您可能会认为UPDATE发生的时间很长.

本文地址:https://www.itbaoku.cn/post/597416.html

问题描述

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)?

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.

推荐答案

The "best practice" is:

  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.

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.

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


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.

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.

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?

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 just wrote a nice blog article about finding bottlenecks in a system:

其他推荐答案

Go buy "High Performance MySQL" from O'Reilly. It's almost 700 pages on the topic, so I doubt you'll find a succinct answer on SO.

其他推荐答案

It's hard to broadbrush things, but a moderately high-level view is possible.

  • You need to evaluate read:write ratios. For tables with ratios lower than about 5:1, you will probably benefit from InnoDB because then inserts won't block selects. But if you aren't using transactions, you should change innodb_flush_log_at_trx_commit to 1 to get performance back over MyISAM.
  • Look at the memory parameters. MySQL's defaults are very conservative and some of the memory limits can be raised by a factor of 10 or more on even ordinary hardware. This will benefit your SELECTs rather than INSERTs.
  • MySQL can log things like queries that aren't using indices, as well as queries that just take too long (user-defineable).
  • The query cache can be useful, but you need to instrument it (i.e. see how much it is used). Cacti can do that; as can Munin.
  • Application design is also important:
    • Lightly caching frequently fetched but smallish datasets will have a big difference (i.e. cache lifetime of a few seconds).
    • Don't re-fetch data that you already have to hand.
    • Multi-step storage can help with a high volume of inserts into tables that are also busily read. The basic idea is that you can have a table for ad-hoc inserts (INSERT DELAYED can also be useful), but a batch process to move the updates within MySQL from there to where all the reads are happening. There are variations of this.
  • Don't forget that perspective and context are important, too: what you might think is a long time for an UPDATE to happen might actually be quite trivial if that "long" update only happens once a day.