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

Question

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!

Answer 1

Analysis of GLOBAL STATUS and VARIABLES:
 

Observations:

• Version: 10.1.44-MariaDB-0ubuntu0.18.04.1
• 7.8 GB of RAM
• Uptime = 1d 00:37:45
• 71.7 QPS

The More Important Issues:

Version 10.1 is getting quite old; consider upgrading.

There were a lot of DELETEs during that day. Is that normal?

Suggested changes to settings:

key_buffer_size = 20M

If using SSD disk:

innodb_io_capacity = 1000
innodb_flush_neighbors = 0

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 8 * 1024 / 262144 = 3.1% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 262144) to avoid unnecessary memory usage.

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 6144M / 1 = 6144MB -- Size of each buffer_pool instance. -- An instance should be at least 1GB. In very large RAM, have 16 instances.

( innodb_lru_scan_depth ) = 1,024 -- innodb_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky. Use 2000 if using SSD drive.

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 88,665 / 60 * 48M / 3815175168 = 19.5 -- Minutes between InnoDB log rotations Beginning with 5.6.8, innodb_log_file_size can be changed dynamically; I don't know about MariaDB. Be sure to also change my.cnf -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( Innodb_row_lock_time_avg ) = 38,990 -- Avg time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.

( Innodb_row_lock_time_max ) = 51,002 -- Max time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.

( innodb_flush_neighbors ) = innodb_flush_neighbors = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor. Limits write IO requests per second (IOPS). -- For starters: HDD: 200; SSD: 2000.

( innodb_strict_mode ) = innodb_strict_mode = OFF -- When ON, this catches some subtle errors earlier. -- OFF leaves some warnings as warnings; ON makes them errors.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI). -- ON for mostly readonly; OFF for DDL-heavy

( innodb_flush_log_at_trx_commit ) = 0 -- 1 = secure; 2 = faster -- (You decide) Use 1, along with sync_binlog (now 0)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (after 5.7.9) and innodb_adaptive_hash_index_partitions (now 1) (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( innodb_purge_threads ) = 1 -- Number of threads to clean up history list. -- If you have a lot of writes, recommend 4 in versions 5.6 and 10.0 or later.

( max_connections ) = 80 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections (now 80) is too high and various memory settings are high, you could run out of RAM.

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 8375186227.2 = 23.9% -- Byte limit on FULLTEXT resultset. (It grows as needed.) -- Lower the setting.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (760824 + 1003342 + 2842 + 0) / 2014065 = 0.877 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Com__biggest ) = Com__biggest = Com_begin -- Which of the "Com_" metrics is biggest. -- Normally it is Com_select (now 499597).

( binlog_format ) = binlog_format = STATEMENT -- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

( back_log ) = 66 -- (Autosized as of 5.6.6; based on max_connections) -- Raising to min(150, max_connections (now 80)) may help when doing lots of connections.

Abnormally small:

(Com_select) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.283
Acl_proxy_users = 0
Key_blocks_unused = 194
host_cache_size = 208

Abnormally large:

Acl_table_grants = 18
Com_begin = 23 /sec
Com_show_master_status = 2.1 /HR
Com_show_slave_hosts = 0.24 /HR
Com_show_status = 0.2 /sec
Handler_delete = 99 /sec
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.651
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 11.5%
Innodb_ibuf_merged_delete_marks = 48 /sec
Innodb_num_index_pages_written = 46 /sec
Innodb_rows_deleted = 99 /sec
Tc_log_page_size = 4,096

Abnormal strings:

binlog_checksum = NONE
innodb_default_row_format = compact
innodb_fast_shutdown = 1
innodb_log_compressed_pages = OFF
myisam_stats_method = NULLS_UNEQUAL
sql_slave_skip_counter = 0

Answer 2

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)

Answer 3

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 917
innodb_change_buffer_max_size=15  # from 25 (percent) set aside for maintenance
innodb_flush_neighbors=2  # from 1 to clear in current extent to reduce innodb_buffer_pool_pages_dirty count faster
innodb_flushing_avg_loops=5  # from 30 to reduce flushing delay
innodb_lru_scan_depth=100  # from 1024 to reduce 90% of CPU cycles used for function every second
innodb_thread_concurrency=6  # from 10 to expedite query completion by your 4 cores

These changes will reduce RAM required and improve response time. For additional suggestions and free downloadable Utility Scripts, view my profile, Network profile for contact information.

Observations: Your 8GB server will continue to struggle to support the 26GB of innodb data. 48GB would be a reasonable step up for your workload. com_begin had 34 more counted than com_commit activities. Did someone forget to COMMIT when done? Incidentally it appears you had 34 rollbacks as well during the period covered 88,665 seconds (just over 1 day).