'MySql Maximum Memory dangerously high - mariadb optimization

i have a 64gb dedicated machine and tuning primer is giving maximum memory dangerously high error. Any ideas for what to change? Below you can find my configration and the notifications the tuner says

      [mysqld]
bind-address = ::ffff:127.0.0.1
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=300
innodb_file_per_table= 1
innodb_buffer_pool_instances = 45
innodb_buffer_pool_size = 45G
skip-name-resolve
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 300M
tmp_table_size= 12200M
max_heap_table_size= 12200M
max_allowed_packet = 512M
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

and tuning primer settings are below , any ideas?

MEMORY USAGE Max Memory Ever Allocated : 45.59 G Configured Max Per-thread Buffers : 28.41 G Configured Max Global Buffers : 45.43 G Configured Max Memory Limit : 73.85 G Physical Memory : 62.70 G

Max memory limit exceeds 90% of physical memory

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 9d 6h 5m 36s (497M q [622.706 qps], 999K conn, TX: 828G, RX: 134G)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 62.7G
[--] Max MySQL memory    : 5085.9G
[--] Other process memory: 0B
[--] Total buffers: 57.5G global + 514.9M per thread (10000 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 86.6G (138.16% of installed RAM)
[!!] Maximum possible memory usage: 5085.9G (8110.25% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

and more

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)
    table_definition_cache(400) > 566 or -1 (autosizing if supported)
    performance_schema=ON
    key_buffer_size (~ 29M)
mysqlmariadb


Solution 1:[1]

That product tends to scare people. The simple rule is: Don't change any settings unless you have an old version of MySQL/MariaDB.

Certain settings are "per-connection", so raising them is dangerous, possibly leading to swapping. Swapping is terrible for performance. tmp_table_size and max_heap_table_size should not be set to more than 1% of RAM unless you have a valid argument for such.

The main tunable, innodb_buffer_pool_size, should be about 70% of RAM.

Don't raise max_connections above the default. If you feel the need to, then probably you have some slow queries that should be addressed.

I can give you a more thorough analysis: http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

That document also has a discussion about the slowlog; it is a good starting point for dealing with slow MySQL/MariaDB.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Rick James

Related Questions