如何预测MySQL引爆点？

我在一个使用带有InnoDB表的MySQL 5.0数据库的大型Web应用程序上工作.在过去几个月中,我们经历了以下两种情况:

数据库服务器运行良好数周,负载低,查询速度慢.

以前快速执行的频繁执行的查询将突然开始非常缓慢地运行.

数据库负载高峰和站点挂起.

两种情况下的解决方案是在慢查询日志中找到慢查询并在表上创建新索引以加快速度.应用索引后,数据库性能恢复正常.

令人沮丧的是,在这两种情况下,我们都没有对即将到来的厄运发出警告; 我们所有的监控系统(例如,系统负载图,CPU使用率,查询执行率,慢查询)告诉我们数据库服务器运行状况良好.

问题1:我们如何预测这些临界点或完全避免它们？

我们没有做任何规律性的事情是运行OPTIMIZE TABLE或ANALYZE TABLE.我们很难找到关于手动执行这些操作的频率(如果有的话)的好经验法则.(由于这些命令是LOCK表,我们不想无差别地运行它们.)这些场景听起来像是未经优化的表的结果吗？

问题2:我们应该手动运行OPTIMIZE还是ANALYZE？如果是这样,多久一次？

关于应用程序的更多细节:数据库使用模式大约是95%读取,5%写入; 数据库执行大约300个查询/秒; 慢速查询中使用的表在两种情况下都是相同的,并且有数十万条记录.

MySQL性能博客是一个很棒的资源.也就是说,这篇文章介绍了正确调整InnoDB特定参数的基础知识.

我还发现MySQL参考手册的PDF版本是必不可少的.第7章介绍了一般优化,第7.5节介绍了可以使用的特定于服务器的优化.

从服务器的声音来看,查询缓存可能具有IMMENSE值.

参考手册还为您提供了一些有关慢速查询,缓存,查询优化甚至索引的磁盘搜索分析的详细信息.

您可能值得花时间研究多主复制,允许您完全锁定一台服务器并运行OPTIMIZE/ANALYZE,而不会影响性能(因为95%的查询是读取,另一台服务器可以管理写入精细).

第12.5.2.5节详细介绍了OPTIMIZE TABLE,12.5.2.1详细介绍了ANALYZE TABLE.

更新您的编辑/重点:

问题#2很容易回答.从参考手册:

OPTIMIZE:

如果删除了表的大部分,或者对具有可变长度行的表进行了许多更改,则应使用OPTIMIZE TABLE.[...]您可以使用OPTIMIZE TABLE回收未使用的空间并对数据表进行碎片整理.

和分析:

ANALYZE TABLE分析并存储表的密钥分发.[...] MySQL使用存储的密钥分发来决定在对常量以外的其他内容执行连接时应该连接表的顺序.此外,在决定用于查询中的特定表的索引时,可以使用密钥分发.

有空闲时,OPTIMIZE很适合运行.MySQL可以很好地优化已删除的行,但是如果你从表中删除20GB的数据,那么运行它可能是一个好主意.在大多数情况下,绝对不需要良好的性能.

分析更为关键.如上所述,当涉及到几乎任何查询时,将所需的表数据提供给MySQL(随ANALYZE提供)非常重要.这是应该在共同基础上运行的东西.

问题#1更多的是一招.发生这种情况时,我会仔细观察服务器,即磁盘I/O. 我敢打赌,你的服务器正在颠覆你的交换或(InnoDB)缓存.在任何一种情况下,它可能是查询,调整或负载相关.未经优化的表可能会导致这种情况.如前所述,运行ANALYZE可以极大地帮助提高性能,并且可能也会有所帮助.