"MySQL's maximum memory usage is dangerously high"

[birdie-github]

After running MySQLTuner 1.7.17 I get this

Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***

It would be great to show what causes these warnings and what calculations have been performed. As it is the user has no clue why these scary messages are shown.

The entire log:

-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file  doesn't exist
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 8.9G (Tables: 71)
[!!] Total fragmented tables: 1
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
  
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 12h 8m 39s (534M q [1K qps], 72K conn, TX: 1312G, RX: 82G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 62.6G
[--] Max MySQL memory    : 64.7G
[--] Other process memory: 0B
[--] Total buffers: 50.3G global + 12.3M per thread (1200 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 56.3G (89.99% of installed RAM)
[!!] Maximum possible memory usage: 64.7G (103.38% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (310K/534M)
[OK] Highest usage of available connections: 41% (501/1200)
[OK] Aborted connections: 0.00%  (0/72342)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 37.3% (284M cached / 763M selects)
[!!] Query cache prunes per day: 253264
[!!] Sorts requiring temporary tables: 798% (8M temp sorts / 1M sorts)
[!!] Joins performed without indexes: 118753
[OK] Temporary tables created on disk: 4% (48K on disk / 1M total)
[OK] Thread cache hit rate: 99% (629 created / 72K connections)
[!!] Table cache hit rate: 7% (400 open / 5K opened)
[OK] Open file limit used: 0% (65/7K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)
[OK] Binlog cache memory access: 99.99% (7886099 Memory / 7887065 Total)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 8 thread(s).
[--] Using default value is good enough for your version (10.4.17-MariaDB-1:10.4.17+maria~focal-log)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 48.0G/8.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50 %): 12.0G * 2/48.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 384 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (294585478509 hits/ 294586010723 total)
[!!] InnoDB Write Log efficiency: 44.01% (6326292 hits/ 14375704 total)
[OK] InnoDB log waits: 0.00% (0 waits / 8049412 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/312.0K
[OK] Aria pagecache hit rate: 100.0% (172M cached / 45K reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] This server is acting as master for 1 server(s).
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] No replication setup for this server or replication not started.
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `xxx`; -- can free 191 MB
    Total freed space after theses OPTIMIZE TABLE : 191 Mb
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/1mi7c4C
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (7639) variable 
    should be greater than table_open_cache (400)
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 2G) [see warning above]
    sort_buffer_size (> 4M)
    read_rnd_buffer_size (> 4M)
    join_buffer_size (> 2.0M, or always use indexes with JOINs)
    table_open_cache (> 400)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=6G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=48)

[birdie-github]

RAM: 64GB dedicated to just MariaDB

The innoDB section of my.cnf

innodb_log_file_size    = 12G
innodb_buffer_pool_size = 48G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT

This might be interesting as well

max_connections = 1200
thread_cache_size = 1200
skip-name-resolve = 1
join_buffer_size = 2M

connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 64M
max_heap_table_size     = 64M

[nijave]

Not affiliated with this website, but it may be helpful http://mysqlcalculator.com/

[locksmithunit]

The same issue, is the tuner have a bug from 2015 people complain:
https://forums.cpanel.net/threads/mysqltuner-shows-wrong-amount-of-installed-ram.491271/

when you look at the Cpanel status everything fine.
I search for solutions as well.

[jmrenouard]

Hi,

This is not a forum.

MySQLTuner is a adviser for tuning MYSQL and MariaDB.

This is not a sharing zone for your specific case config..

What is reported here, is not a bug
Details about calculation are on Perl code itself and this page:
https://github.com/major/MySQLTuner-perl/blob/master/INTERNALS.md

There is a lot of specific issue related to memory usage alredy opened can you check that you are not in one of this cases.

Best regards,