似乎这些天是否有任何人知道任何关于调优InnoDB,那就是你必须调整通过innodb_buffer_pool_size你物理内存的80%。这是多产的优化建议,似乎在许多DBA的思想根深蒂固。的MySQL手册这一天是指这条规则,谁能怪罪DBA吗?问题是:它是有意义的吗?
使用服务器上的内存呢?
问题这样的建议之前,让我们考虑占用内存能在一个典型的MySQL服务器的大类。这个列表并不一定完成,但是我认为它概述了大面积MySQL服务器可能会消耗内存。
- 操作系统使用方法:内核,运行过程中,文件系统缓存等。
- MySQL固定用法:查询缓存,InnoDB的缓冲池大小,mysqld rss等。
- MySQL基于工作负载的用法:连接,每个查询的缓冲区(加入缓冲,缓冲,等等)。
- MySQL复制用法:二进制日志缓存、复制连接Galera gcache cert指数,等等。
- 在同一台服务器上任何其他服务:Web服务器、缓存服务器的计划等。
毫无疑问,对于InnoDB调优,通过innodb_buffer_pool_size是最重要的变量。它将占据大部分的RAM在一个专用的MySQL / Innodb服务器,当然,其他本地服务可能会影响如何调整。如果它(和其他服务器上的内存消耗)太大,迅速交换可以踢和降低性能。
此外,MySQL服务器本身的工作负载可能会引起大量的变异。服务器有很多开放的连接和活跃的查询工作负载消耗内存?这可以造成的内存消耗显著不同的服务器到服务器。
最后,复制机制(如Galera有自己的内存使用模式和需要一些调整缓冲池。
我们可以清楚地看到,80%规则不如微妙的现实。
一个经验法则
然而,为了讨论,假设80%的规则是一个起点。一个经验法则来帮助我们得到一个快速优化运行的服务器数量。假设我们真的什么都不知道的工作负载的系统,但是我们知道系统致力于InnoDB,我们80%规则如何上演?
| 总服务器内存 | 缓冲池以80%的规则 | 剩余的内存 |
|---|---|---|
| 1克 | 800 mb | 200 mb |
| 16克 | 13克 | 3 g |
| 32 g | 26克 | 6克 |
| 64克 | 51克 | 13克 |
| 128克 | 102克 | 26克 |
| 256克 | 205克 | 51克 |
| 512克 | 409克 | 103克 |
| 1024克 | 819克 | 205克 |
较低的数字,我们80%规则看起来很合理。然而,当我们进入大型服务器,它开始看起来不那么理智。规则的适用,它必须意味着工作量增加内存消耗的比例需要缓冲池的大小,但这通常并非如此。我们的服务器有1 tb的内存可能不需要205克,处理诸如连接和查询(MySQL可能无法处理许多活跃的连接和查询)。
所以,如果你只是花了那么多钱买一个结实的服务器你真的想支付20%的税,资源因为这个经验法则?
规则的起源
MySQL的我第一次会议,大概2006 - 2007年,当时我在雅虎,我参加了一个InnoDB调优和主持海基Tuuri (InnoDB的原始作者)和彼得•扎伊采夫。我清楚地记得当时问80%规则,因为雅虎有一些结实的64 g服务器和规则不与我身旁。
海基的答案把我难住了。他说的东西(不是直接引用)的影响:“我测试的服务器有1 gb的RAM和80%似乎正确的”。然后他,如果没记错,澄清它,并表示将不适用与更大的服务器。
你应该如何调整通过innodb_buffer_pool_size吗?
80%可能是一个好的开始和经验法则。你要确定服务器有足够的空闲RAM通常操作系统和未知的工作负载。然而,正如上面我们可以看到,服务器越大,越有可能规则将会浪费内存。我认为对大多数人来说它开始和结束的经验法则,主要是因为改变InnoDB缓冲池需要重启在当前的版本中。
一个更好的经验法则是什么?我的规则是,你通过innodb_buffer_pool_size尽可能大调整时不使用交换系统生产运行工作负载。原则上这听起来不错,但再一次,它需要一系列的重启,可能说起来容易做起来难。
幸运的是MySQL 5.7和它的在线缓冲池大小功能应该让这一个简单的原则。看到很多空闲RAM(和/或文件系统缓存使用)?将缓冲池动态。看到一些交换活动吗?只是把它打倒不需要重启。在实践中,我想会有一些使用这个特性的性能相关的问题,但它至少是朝着正确方向迈出的一大步。
更多资源:
的帖子
在线研讨会
演讲
免费电子书
- 实际的MySQL性能调优:第一节——了解MySQL查询参数调优和优化
- 实际的MySQL性能调优:第二节——故障诊断性能问题和优化MySQL
- 实际的MySQL性能调优:第三节——查询优化
我们使用的大部分运行CentOS的128 gb的服务器(不同口味)专用MySQL (Percona 5.5)。雷竞技下载官网我们已经通过innodb_buffer_pool_size设置为104 gb在那些和他们也有8 gb ram磁盘/ tmp。他们是大量利用,但从未使用过任何交换。当然vm。swappiness设置为1。
我们使用在较低的层的服务器上(包括128 gb为简便起见),他们从未使用过任何交换和巨大的数据库运行(2-3TB):
128 gb RAM:通过innodb_buffer_pool_size = 104 gb
64 gb RAM:通过innodb_buffer_pool_size = 56 g
32 gb RAM:通过innodb_buffer_pool_size = 28 g
这个设置已经完美地工作了我们在过去的3年。
当我第一次开始做MySQL性能工作在我现在的工作,我开始考虑的第一件事就是IO利率和缓存问题。当然DB配置使用一个80%规则计算就像大多数配置“推荐”。
根据工作负载和我们分配系统,我发现我们真正想要的是一些保留内存操作系统,监控工具,一些基地mysqld的记忆。raybet雷竞技竞猜在线官网在那之后,我们可以分配百分之一的剩余内存缓冲池
我们有几个类别的数据库节点。32 g, 64克,192克。
在大多数情况下,我们分配(N - 7 g) *。9。对于一个64 g节点,我们最终~ 51克缓冲池分配的内存。
一些高负荷数据库我们撞,系统保留数量10 g,因为他们倾向于处理更多的连接,并有较高的查询处理内存的必要性。
我认为这可以真正取决于您的特定工作负载和应用程序。我倾向于保守的原因。
1)较大的情况下仍将占据10 + %以上设置缓冲池,很大程度上是由于内部的哈希表,特别是自适应哈希索引。自适应哈希索引对于某些工作负载可以是非常有益的,但如果缓冲池可以瘫痪骤然上升太高了。
2)工作负载较高的体积明显会有更多连接开销,特别是如果有临时建设无论什么原因。这种结合在每个连接缓冲分配、临时表等。能迅速消耗大量的内存。
3)您的开发人员可能会决定一些新的应用程序使用的内存引擎有或没有你的知识
3)我认为这是公平地说在记忆差的情况下,一个mysql实例和一个缓冲池的侵犯饥饿服务器的内存和被杀的危险甚至比系统小于所需的缓冲池。
3 b)在正常操作,假设你的缓冲池,增加可以帮助如果你工作数据集不完全在内存中,然而对于许多工作负载(与时间有关,动作结束的时候表),工作集已经在内存中并没有影响。好处可能只是看到了随着负载/工作集。
3 c)假设这种增长,你的工作有时会难以确定和跟踪,尤其是在你的公司可能会定期推出新的应用程序功能,你可能已经发现(在此期间的几天)工作数据集不再适合到内存中。节省很多的痛苦有额外的内存空间可以立即调整,是与一个LRU转储/恢复全面运作15分钟,担心内存升级后在受控的情况下,而不是请求紧急内存升级,最好的情况下6小时转身完全停止至少30分钟,或者坏的情况下,天找到所需的内存。
很高兴见到5.7是试图解决动态调整。
1 tb的机器我戳在MySQL, 80%似乎相当过度…但它也可以处理大量的连接,每个线程数据结构有所增加,所需的RAM Linux一样存在,做其事。
你好杰,
会有大量的内存(比如80 GB)分配给InnoDB缓冲池大小导致性能问题当缓存失效?或将任何更新表发生直接在缓冲区中,然后被写入磁盘(而不是无效的缓存然后recaching表)。
提前谢谢,我找不到任何文档缓冲区是如何工作的。
当我有机会设置一个MYSQL服务器与一个巨大的内存分配,这是一个通常的步骤来计算,您将定义80% InnoDB缓冲池大小。然而,当我意识到有多少巨大的剩余的20%的操作系统,我试着寻找答案是否会伤害如果我增加缓冲池使用一定比例的20%。事实上,经验法则是合理的,但总是会有一个案例,你必须打破它。
好文章!
这是你应该做的是。第一次运行这个查询
选择上限(Total_InnoDB_Bytes * 1.6 /电力(1024 3))RIBPS
(选择(data_length + index_length) Total_InnoDB_Bytes求和
从information_schema。表引擎= ' InnoDB ');
+ - - - +
| RIBPS |
+ - - - +
8 | |
+ - - - +
1行集(4.31秒)
这样的设置,设置my . cnf中所做
(mysqld)
通过innodb_buffer_pool_size = 8 g
这是明智的改变从sql mariadb 5.6吗?
我们目前有32 gb Ram的服务器,4个CPU。CPU负载总是高于6。
有人可以帮忙吗?
谢谢
很难说是或否。CPU负载取决于几件事情,“6”是什么?(6%,60%)的所有核心?)。反正CPU负载可能与密集的查询增加每连接请求,(缺乏)分配内存等。
Mariadb(我检查一个非最新版本10.1.29)似乎有点快于mysql 5.6但这是一个个人观点。
我读过的评论表明mysql 8快:
“MySQL 8.0实现了一个全新的数据字典…虽然MariaDB只有一小部分数据字典…保证更高的性能和更好的数据一致性和它与其余的良好集成服务器”
参考:https://www.雷竞技下载官网percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/
注意正确的升级(很容易做到)如果你从mysql mariadb往南面,否则你会有高峰与mariadb服务器cpu负载和不稳定的连接。我只是固定服务器的问题。
的大小应该什么innodb_log_file_size服务器有128 g (102 g通过innodb_buffer_pool_size) ?
我通常读建议指向值应该是1/4的缓冲池大小和在其他情况下我看到评论,它不应大于256 mb。
innodb_buffer_pool_instances也是一样。有多少?实际上我使用每GB的通过innodb_buffer_pool_size加1。
各种测试后我有一个总体性能下降102克通过innodb_buffer_pool_size和任何大小innodb_log_file_size (ib_logfile0和ib_logfile1删除之前重新启动)。谢谢你！
我认为80%的RAM是我们应该开始一个安全点。一切都取决于哪个服务你的服务器是否正在运行消耗过多的内存直到20%吗?如果他们消费太少,我们可以增加通过innodb_buffer_pool_size到一个更高的价值。