"MySQL's maximum memory usage is dangerously high" #520
Description
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)