MySQL Tuner returns: MySQL's maximum memory usage is dangerously high

Can someone help me optimize my MSQL configuration.

I run Zabbix and Grafana on a Ubuntu 18.4. MySQL Tuner shows me that my memory usage is dangerously high, but when I use the free command it shows that I have more than enough memory.

Here is the full mysqltuner rapport: MySQLTuner

Here is the output of: SHOW GLOBAL VARIABLES and SHOW GLOBAL STATUS
and SHOW FULL PROCESSLIST

And the output of: SHOW ENGINE INNODB STATUS since Zabbix uses excursively innodb tables.

The output of the top command
And the htop command
I also have the output of innotop: Google Drive

And the output of ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 31652
max locked memory       (kbytes, -l) 16384
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 31652
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

and free -h

total        used        free      shared  buff/cache   available
Mem:           7,8G        7,0G        146M         37M        689M        554M
Swap:          2,0G        417M        1,6G

How can I increase the mysql memory usage? How can I optimize my MySQL configuration, so that I have maximum CPU and Memory usage?

Thank you for any help!

RAM consumption for mysql/mariadb (and probably for other engines) can be represented this way:

+----+----------------+-------------------+-----------------------------+
| OS | Other services | DB common caching |      DB clients buffers     |
|    |                |                   +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|    |                |                   | | | | | | | | | | | | | | | |
+----+----------------+-------------------+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Common DB caching is a sum of key_buffer and InnoDB_buffer_pool values.

Buffer size per each client is a sum of read_buffer, read_rnd_buffer, join_buffer and sort_buffer. This amount is multiplied by max_connections value. Both values together shouldn't be bigger than reasonable part of the whole RAM. Some RAM is required by host's OS itself and some amount of RAM should be leaved for 3d-party services on the host.

Maximum possible memory usage: 9.0G (116.34% of installed RAM) means that you have dedicated to the mysql service even more RAM than your host physically has installed. This doesn't mean some problems until you'll get too many simultaneous connections to the DB. And even then you'll meet a slowdown first because of swapping.

If you want to tune your mysql perfectly you should know how many connections can be established at the same time. This value is shown here:

Highest usage of available connections: 40% (61/151)

Then you have to decide how much RAM each client needs. This value depend on your DB data and queries. If your DB isn't very critical you can start from some reasonable values and then decrease them until the overall performance goes down significantly. Then increase them 2x. But in general 1-2MB for each client's buffer and 4-8MB in total is good enough.

Now multiply total client's buffers by max_connections value. By default it is 151 but you can change it to the 1.2-1.5x of the real peak number of connections.

Say, for max 61 actual connections I'll reserve max_connections = 80 with 8MB buffers per connection. That mean that I reserved 80x8=640MB of RAM.

Now I leave 1GB for OS itself and 512MB for other services. The remainder is:

8GB - 1GB - 512MB - 640MB = 6016MB

Let's round it to the 6GB. This RAM you can split between MyISAM key_buffer and innodb_buffer_pool. If you have no MyISAM tables in the DB you can set key_buffer_size = 256k because mariadb 10.1.x is still used MyISAM/ARIA for internals. And all the rest could be dedicated to the innodb caching: innodb_buffer_pool_size = 6G

Now you'll get something like that:

Total buffers: 6.G global + 8M per thread (80 max threads)
Maximum possible memory usage: 6.7G (84% of installed RAM)

StackExchange Database Administrators Q#264574, answer score: 2