[MDEV-33390] MariaDB does not respect memory limits from Created: 2024-02-05  Updated: 2024-02-06

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.11.6
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Carl-Philip Hänsch Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Server version: 10.11.6-MariaDB-1:10.11.6+maria~deb11
32 GB RAM, 4 CPU cores, Linux on x64, SSD storage


Attachments: PNG File image-2024-02-05-22-02-44-405.png    

 Description   

MariaDB does not respect the memory limit of 16 GB and fills up 90% of the system memory (28G)

This happens especially when a lot of INSERT statements are executed that call a AFTER INSERT TRIGGER.

Severity: The MariaDB server process crashes every 15 minutes or more often because of OOM killer. We wrote a script that restarts mariadb as soon as 75% RAM are occuped but sometimes this script is not fast enough and OOM killer ist faster.



 Comments   
Comment by Daniel Black [ 2024-02-05 ]

The memory limits from where? What is your mariadb configuration?

Comment by Carl-Philip Hänsch [ 2024-02-05 ]

this is the output of mysqltuner. The max MySQL Memory of 4.8G is exceeded by 6-fold:

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 6m 56s (49K q [118.413 qps], 432 conn, TX: 13M, RX: 23M)
[--] Reads / Writes: 18% / 82%
[--] Binary logging is disabled
[--] Physical Memory : 31.2G
[--] Max MySQL memory : 4.8G
[--] Other process memory: 0B
[--] Total buffers: 1.5G global + 6.7M per thread (500 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.6G (5.07% of installed RAM)
[OK] Maximum possible memory usage: 4.8G (15.39% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[!!] Slow queries: 56% (27K/49K)
[OK] Highest usage of available connections: 1% (5/500)
[OK] Aborted connections: 0.00% (0/432)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 23.5% (3K cached / 15K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 466 sorts)
[!!] Joins performed without indexes: 31
[OK] Temporary tables created on disk: 7% (218 on disk / 2K total)
[OK] Thread cache hit rate: 98% (5 created / 432 connections)
[!!] Table cache hit rate: 6% (4K open / 60K opened)
[OK] Open file limit used: 0% (59/64K)
[OK] Table locks acquired immediately: 99% (29K immediate / 29K locks)

BUT: The real memory consumption according to htop and other system tools is 18 G of RAM (while mariadb itself thinks it is only using 1.6G)

Comment by Carl-Philip Hänsch [ 2024-02-05 ]

SHOW GLOBAL STATUS tells:

Memory_used 2665858512

htop however says:
22G (which is 10x more than STATUS claims)

Comment by Carl-Philip Hänsch [ 2024-02-05 ]

This is a log of all crashes:

2024-02-05 09:00:46 MEM_CRIT: 31726092 - restart mariadb-server
2024-02-05 09:15:38 MEM_CRIT: 29104500 - restart mariadb-server
2024-02-05 09:31:02 CRASHED: start mariadb-server
2024-02-05 09:45:37 MEM_CRIT: 30282144 - restart mariadb-server
2024-02-05 10:00:36 MEM_CRIT: 28055600 - restart mariadb-server
2024-02-05 10:15:36 MEM_CRIT: 24246396 - restart mariadb-server
2024-02-05 10:30:36 MEM_CRIT: 24133768 - restart mariadb-server
2024-02-05 10:45:29 MEM_CRIT: 34178228 - restart mariadb-server
2024-02-05 11:00:30 MEM_CRIT: 22367804 - restart mariadb-server
2024-02-05 11:15:30 MEM_CRIT: 24386304 - restart mariadb-server
2024-02-05 11:30:29 MEM_CRIT: 24058304 - restart mariadb-server
2024-02-05 11:45:33 MEM_CRIT: 30246664 - restart mariadb-server
2024-02-05 12:00:31 MEM_CRIT: 29184444 - restart mariadb-server
2024-02-05 12:15:01 CRASHED: start mariadb-server
2024-02-05 12:30:34 MEM_CRIT: 32789068 - restart mariadb-server
2024-02-05 12:45:36 MEM_CRIT: 30915580 - restart mariadb-server
2024-02-05 13:00:34 MEM_CRIT: 31627160 - restart mariadb-server
2024-02-05 13:00:38 SWAP_WARN: 2136016
2024-02-05 13:15:36 SWAP_WARN: 2051536
2024-02-05 13:30:35 MEM_CRIT: 27203764 - restart mariadb-server
2024-02-05 13:30:40 SWAP_CRIT: 2625536 - restart mariadb-server
2024-02-05 13:45:33 MEM_CRIT: 28711708 - restart mariadb-server
2024-02-05 13:45:36 SWAP_CRIT: 2602480 - restart mariadb-server
2024-02-05 14:00:38 MEM_CRIT: 29051868 - restart mariadb-server
2024-02-05 14:00:42 SWAP_CRIT: 2609768 - restart mariadb-server
2024-02-05 14:15:35 MEM_CRIT: 34405256 - restart mariadb-server
2024-02-05 14:15:40 SWAP_WARN: 2447876
2024-02-05 14:30:37 MEM_CRIT: 21432660 - restart mariadb-server
2024-02-05 14:30:41 SWAP_WARN: 2426100
2024-02-05 14:45:35 SWAP_WARN: 2412168
2024-02-05 15:00:36 MEM_CRIT: 26707468 - restart mariadb-server
2024-02-05 15:00:40 SWAP_WARN: 2418920
2024-02-05 15:15:37 MEM_CRIT: 26416144 - restart mariadb-server
2024-02-05 15:15:41 SWAP_WARN: 2405424
2024-02-05 15:30:34 MEM_CRIT: 31726064 - restart mariadb-server
2024-02-05 15:30:38 SWAP_WARN: 2479700
2024-02-05 15:45:37 MEM_CRIT: 23153288 - restart mariadb-server
2024-02-05 15:45:40 SWAP_WARN: 2450004
2024-02-05 16:00:38 MEM_CRIT: 32272416 - restart mariadb-server
2024-02-05 16:00:43 SWAP_CRIT: 2606936 - restart mariadb-server
2024-02-05 16:30:37 MEM_CRIT: 28644620 - restart mariadb-server
2024-02-05 16:45:37 MEM_CRIT: 28760484 - restart mariadb-server
2024-02-05 17:15:01 CRASHED: start mariadb-server
2024-02-05 17:45:37 MEM_CRIT: 23925420 - restart mariadb-server
2024-02-05 18:00:35 MEM_CRIT: 29372856 - restart mariadb-server
2024-02-05 18:30:36 MEM_CRIT: 29184744 - restart mariadb-server
2024-02-05 19:00:01 CRASHED: start mariadb-server
2024-02-05 19:30:38 MEM_CRIT: 24255240 - restart mariadb-server
2024-02-05 19:45:33 MEM_CRIT: 26680092 - restart mariadb-server
2024-02-05 20:00:39 MEM_CRIT: 25893060 - restart mariadb-server
2024-02-05 20:15:40 MEM_CRIT: 33069852 - restart mariadb-server
2024-02-05 20:30:39 MEM_CRIT: 33667204 - restart mariadb-server
2024-02-05 20:45:34 MEM_CRIT: 24988640 - restart mariadb-server
2024-02-05 21:15:34 MEM_CRIT: 26416056 - restart mariadb-server

Comment by Daniel Black [ 2024-02-05 ]

What is your mariadb configuration my_print_defaults --mysqld?

Your 4k table_open_cache appears insufficient for the tables actually being used.

Try enabling the performance_schema memory instrumentation. If this shows similar figures to Memory_used, install dgbsyms with package, and use bcc's memleak try an interval of 1 minute.

What a previous version of MariaDB running ok on your workload? Which version?

Comment by Carl-Philip Hänsch [ 2024-02-05 ]

--socket=/run/mysqld/mysqld.sock
--pid-file=/run/mysqld/mysqld.pid
--basedir=/usr
--log_error=/var/log/mysql/error.log
--expire_logs_days=10
--character-set-server=utf8mb4
--collation-server=utf8mb4_general_ci
--innodb_buffer_pool_size=1G
--innodb_log_file_size=256M
--innodb_buffer_pool_instances=1
--innodb_flush_log_at_trx_commit=2
--tmp_table_size=256M
--table_open_cache=4000
--key_buffer_size=32M
--join_buffer_size=4M
--query_cache_limit=8M
--query_cache_size=128M
--open_files_limit=48000
--max_heap_table_size=256M
--myisam_sort_buffer_size=512M
--slow_query_log_file=/var/log/mysql/mariadb-slow.log
--long_query_time=10
--log_slow_rate_limit=1000
--log-queries-not-using-indexes
--max_connections=500
--tmpdir=/var/tmp
--myisam_repair_threads=2
--low_priority_updates=1
--max_allowed_packet=64M
--performance_schema=ON
--ssl_cert=/etc/mysql/ssl/bnet008.b-net.cloud.crt
--ssl_key=/etc/mysql/ssl/bnet008.b-net.cloud-nopw.key
--ssl_ca=/etc/mysql/ssl/mysql_2020_bundle.crt

Comment by Carl-Philip Hänsch [ 2024-02-05 ]

@daniel Black why do you think, increasing table_open_cache will reduce the amount of memory leaks during sequential INSERT statements?

Comment by Daniel Black [ 2024-02-05 ]

> why do you think, increasing table_open_cache will reduce the amount of memory

It would potentially reduce memory fragmentation, and it if somehow reduces the leak, then it could be a cause.

Aside min_examined_row_limit can reduce the slow query log to something more manageable.

Can you show the table structure, the form of the insert statements and the triggers? Change the fieldnames/table names (consistently) if you don't want to show them publicly.

Generated at Thu Feb 08 10:38:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.