什么是“MySQL 的最大内存使用量非常高"?mysqltuner 是什么意思? [英] What does "MySQL's maximum memory usage is dangerously high" mean by mysqltuner?

问题描述

我正在尝试优化我的 mysql 在 2GB mem VPS 上的运行，我使用了 mysqltuner，我不太明白如何处理以下建议，尤其是那个说:MySQL 的最大内存使用量非常高，处理这个问题?有人可以帮忙解释一下吗?谢谢.

I am trying to optimize my mysql runs on a 2GB mem VPS, I used mysqltuner, and I dont quite understand how to deal with the following recommendations, especially the one says: MySQL's maximum memory usage is dangerously high, deal with this one? Can someone help explain? Thanks.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 17m 7s (49K q [4.190 qps], 1K conn, TX: 70M, RX: 7M)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 314.0M global + 6.4M per thread (300 max threads)
[!!] Maximum possible memory usage: 2.2G (119% of installed RAM)
[OK] Slow queries: 1% (785/49K)
[OK] Highest usage of available connections: 85% (256/300)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[OK] Query cache efficiency: 92.4% (38K cached / 41K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 633 sorts)
[!!] Temporary tables created on disk: 45% (315 on disk / 699 total)
[OK] Thread cache hit rate: 74% (359 created / 1K connections)
[OK] Table cache hit rate: 95% (141 open / 148 opened)
[OK] Open file limit used: 12% (189/1K)
[OK] Table locks acquired immediately: 99% (6K immediate / 6K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

推荐答案

"[!!] 最大可能内存使用量:2.2G(已安装 RAM 的 119%)"

这意味着你本质上对 MySQL 撒了谎，告诉它你有比实际更多的可用内存，2.2G > 2G.这可能会持续数周或数月，但这是个坏主意.如果 MySQL 没有你告诉它使用的内存，MySQL 会在最坏的情况下随机崩溃.

This means you essentially lied to MySQL, telling it you have more memory available than you really have, 2.2G > 2G. This might work for weeks or months, but it's a bad idea. If MySQL doesn't have the memory you told it to use, MySQL will crash randomly at the worst possible time.

如果您将skip-innodb"添加到您的/etc/my.cnf 文件中，这可能会为您节省一些内存.我假设您没有使用 InnoDB.这是一个切线，但我强烈建议您将数据从 MyISAM 转换为 InnoDB.MyISAM 是旧技术.InnoDB 是更现代的引擎.

If you add "skip-innodb" to your /etc/my.cnf file, that might save you some memory. I assume you're not using InnoDB. This is a tangent but I strongly advise you convert your data from MyISAM to InnoDB. MyISAM is old technology. InnoDB is the more modern engine.

在 my.cnf 中查找可以降低以节省内存的任何内容.我通常会看到的第一件事是未使用的连接.15% 的连接未被使用，但请听听24 小时内开始"警告.通常在 my.cnf 中降低(未使用的)连接会节省大量内存.我不知道你的应用程序是做什么的，但 256 个连接对我来说听起来很高.因此，我会确保您的应用程序确实需要那么多连接.也许您的服务器上有 256 个 PHP 子级，而这可以减少 12 个子级.更多的孩子！=更快的反应.如果你有 12 个 PHP 子节点，也许你只需要 13 个数据库连接.

Look for anything in your my.cnf that you can lower to save memory. The first thing I typically look at, unused connections. 15% of your connections aren't being used, but listen to the "started within 24 hours" warning. Typically lowering (unused) connections in my.cnf will save lots of memory. I don't know what your application does, but 256 connections sounds high to me. So I'd make sure your application really needs that many connections. Maybe you have 256 PHP children on your server and that could be cut way down 12 children. More children != faster response. If you have 12 PHP children, maybe you only need 13 database connections.

119% 显然太高了，但我认为 96% 也太高了.(这就是为什么我在这里寻找最好的 % 来使用.)显然操作系统也需要一些内存.您应该为操作系统保留多少内存未使用，我想知道！如果还没有被问到，我会在这里作为一个单独的问题提出这个问题.(如果您这样做，请在此处发布链接.)或者您可以只听 mysqltuner 的建议.

119% is obviously too high but I think 96% is too high also. (Which is why I'm here looking for the best % to use.) Obviously the operating system needs some memory too. How much memory should you leave unused for your operating system, I would like to know! I would ask this as a separate question here, if it hasn't been asked already. (Please post the link here if you do this.) Or you can just listen to mysqltuner's recommendation.

只是在这里测试:

"[!!] 最大可能内存使用量:3.4G(已安装 RAM 的 88%)"

再次降低 my.cnf 设置.

Lower my.cnf settings again.

"[!!] 最大可能内存使用量:3.3G(已安装 RAM 的 86%)"

还是太高了?

"[OK] 最大可能内存使用量:3.2G(已安装 RAM 的 83%)"

这篇关于什么是“MySQL 的最大内存使用量非常高"?mysqltuner 是什么意思?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！