What does "MySQL's maximum memory usage is dangerously high" mean by mysqltuner?

Question

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)

Answer 1

"[!!] Maximum possible memory usage: 2.2G (119% of installed RAM)"

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.

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.

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% 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.

Just testing here:

"[!!] Maximum possible memory usage: 3.4G (88% of installed RAM)"

Lower my.cnf settings again.

"[!!] Maximum possible memory usage: 3.3G (86% of installed RAM)"

Still too high?

"[OK] Maximum possible memory usage: 3.2G (83% of installed RAM)"

Answer 2

Take the advice from mysqltuner with a grain of salt. It's making incorrect estimates about the maximum possible memory usage. And it can't make a correct estimate.

See http://www.percona.com/blog/2009/02/12/how-much-memory-can-mysql-use-in-the-worst-case/ for an explanation.

It's true that there each connection uses some memory, but how much varies. You won't always have 300 connections in use, and even when they are, they won't always be running queries simultaneously, and even if they are, the queries won't always be using all possible buffers to the maximum size.

Mysqltuner is warning about a theoretical maximum memory usage that will never happen.

Another way of looking at it: I've analyzed hundreds of MySQL configurations, and every one of them could in theory allocate more memory than the physical RAM on the server.