优化InnoDB默认设置

Question

我查看了my.ini并看到了各种默认设置。我的数据库运行在一台独立的电脑上。我想从总体上优化InnoDB和MySQL的性能以提高性能。没有对磁盘空间的限制。应该更改哪些默认设置以优化性能、可靠性和可能的实时备份( 高可用性 )。

编辑的

目前，每当我在MySQL管理员上通过维护运行“优化表”时，它都会显示：

表不支持优化，而是重新创建+分析

在所有桌子上。我所有的表都是InnoDB，但是为什么它们不支持优化呢？

Answer 1

调优InnoDB的方法是围绕

• InnoDB缓冲池:它缓存数据页和索引页。可以缓存的数据量和索引不是磁盘空间约束的函数，而是InnoDB当前使用的可用内存和磁盘空间的函数。
• InnoDB MetaData :默认情况下，ibdata1文件通常包含任何内容和所有内容( InnoDB )。这将包括数据页、索引页、表元数据、MVCC数据。

下面是我在过去5年中使用的基于InnoDB数据和索引页所使用的磁盘空间计算InnoDB缓冲池的公式：

SELECT CONCAT(ROUND(KBS/POWER(1024,IF(Power1024<0,0,
IF(Power1024>3,0,Power1024)))+0.49999),SUBSTR(' KMG',IF(Power1024<0,0,
IF(Power1024>3,0,Power1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 2 Power1024) B;

• 对字节使用(选择0 Power1024)
• KB使用(选择1 Power1024)
• 对MB使用(选择2 Power1024)
• GB使用(选择3 Power1024)
• 对TB使用(选择4 Power1024) (如果您有内存的TerraBytes，请发送电子邮件给我)

当然，我说的是InnoDB目前使用的可用内存和磁盘空间的函数。从这里开始，用常识。上述查询中推荐的数字不应超过安装RAM的75%！这是调整InnoDB缓冲池大小的最简单的经验法则。

您还应该设置诺姆b，因为它将提供InnoDB的稳定同步写入。我还写了一篇关于如何优化InnoDB磁盘存储的文章。

关于message不支持优化，而是重新创建+分析，您获得该错误消息的原因是存储引擎是InnoDB。机械地，优化表只是将表复制到临时表并执行分析表。

在现实中，针对InnoDB分析表是完全无用的。即使在InnoDB表上运行ANALYZE表，InnoDB存储引擎也会一次又一次地跳过基数近似的索引，从而破坏刚才编译的统计数据。事实上，Percona在分析表上进行了一些测试，并得出了同样的结论。。

下面是我一年来做过的关于InnoDB调优的其他文章

• https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261
• https://stackoverflow.com/questions/5248420/at-what-point-does-mysql-innodb-fine-tuning-become-a-requirement/5250306#5250306
• https://serverfault.com/questions/155455/how-do-myisam-and-innodb-utilize-hd-space/246111#246111
• https://serverfault.com/questions/230551/mysql-innodb-innodb-file-per-table-cons/231400#231400
• https://serverfault.com/questions/253059/mysql-innodb-optimisation/253233#253233
• 如何对MySQL进行调优以适应繁重的InnoDB工作负载？

Answer 2

这是一篇旧的文章对重要变量进行“粗调”。最重要的可能是innodb_buffer_pool_size

我强烈建议升级到MySQL 5.5 (如果您还没有运行它)。他们对InnoDB性能做了相当多的修改。他们甚至提供了一个关于如何为InnoDB优化的主流部分，因为它是默认引擎：http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb.html

至于点对点备份，您需要查看二进制日志。启用此功能的基础是在您的log-bin中设置my.cnf变量

Answer 3

优化有很多种颜色。

我认为你的第一步应该是决定“优化”对你意味着什么。对某些人来说，这意味着“最快的选择查询”。对其他人来说，这意味着“选择性能和插入性能之间的最佳平衡”。对于其他人，“最快的插入性能”。

在开始调优之前，您需要决定您的标准是什么，以及如何判断您的更改是否有帮助。

然后将您的配置文件和启动选项置于版本控制之下，并开始试验。记录下你所遵循的建议，以及你在哪里找到的。(随着代码库和硬件的变化，建议会随着时间的推移而变化。)将这些文档也置于版本控制之下。