OS - Debian Bullseye
MariaDB - 10.6.12
Virtual Machine
Total Memory - 96 GB
CPUs -
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
Address sizes: 45 bits physical, 48 bits virtual
CPU(s): 12
On-line CPU(s) list: 0-11
Thread(s) per core: 1
Core(s) per socket: 12
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Platinum 8358 CPU @ 2.60GHz
Stepping: 7
CPU MHz: 2593.905
BogoMIPS: 5187.81
Hypervisor vendor: VMware
Virtualization type: full
L1d cache: 576 KiB
L1i cache: 384 KiB
L2 cache: 15 MiB
L3 cache: 48 MiB
NUMA node0 CPU(s): 0-11

Please see attached pattern of memory usage. The timings marked with downward arrows are when the DB service was restarted or crashed due to OOM.

Also note how mariadb does not release memory back to the OS even after crash or restarts.

Please see below .cnf configs we have:

### MANAGED BY PUPPET ###

[client]

port                                  = 3306

socket                                = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #

user                                  = mysql

default_storage_engine                = InnoDB

character_set_server                  = utf8mb4

collation_server                      = utf8mb4_unicode_ci

tmpdir                                = /opt/mysql/tmp

socket                                = /var/run/mysqld/mysqld.sock

bind_address                          = *

wait_timeout                          = 1800

interactive_timeout                   = 1800

# MyISAM #

key_buffer_size                       = 32M

myisam_recover_options                = BACKUP,QUICK

# SAFETY #

max_allowed_packet                    = 256M

max_connect_errors                    = 1000000

sysdate_is_now                        = 1

# DATA STORAGE #

datadir                               = /opt/mysql/data/

# BINARY LOGGING #

binlog_checksum                       = NONE

max_binlog_size                       = 1G

log_bin                               = /opt/mysql/log/<server name>-bin

log_bin_index                         = /opt/mysql/log/<server name>-bin-index

expire_logs_days                      = 7

sync_binlog                           = 1

log_bin_trust_function_creators       = 1

binlog_format                         = ROW

server_id                             = 42193

log_slave_updates                     = 1

# SLAVE #

slave_parallel_threads                = 0

slave_max_allowed_packet              = 1073741824

relay_log                             = /opt/mysql/log/mysqld-relay-bin

relay_log_index                       = /opt/mysql/log/mysqld-relay-bin.index

read_only                             = 0

# CACHES AND LIMITS #

tmp_table_size                        = 32M

max_heap_table_size                   = 32M

max_connections                       = 250

open_files_limit                      = 65535

table_definition_cache                = 1024

table_open_cache                      = 2048

# INNODB #

innodb_flush_method                   = O_DIRECT

innodb_log_file_size                  = 1G

innodb_log_buffer_size                = 16M

innodb_flush_log_at_trx_commit        = 1

innodb_file_per_table                 = 1

innodb_buffer_pool_size               = 78784M

innodb_flush_neighbors                = 0

innodb_buffer_pool_dump_at_shutdown   = 0

innodb_buffer_pool_load_at_startup    = 0

innodb_force_primary_key              = 1

innodb_autoinc_lock_mode              = 2

innodb_monitor_enable                 = all

# LOGGING #

log_output                            = FILE

log_error                             = /opt/mysql/log/<server name>.err

slow_query_log                        = 1

slow_query_log_file                   = /opt/mysql/log/mysql-slow.log

long_query_time                       = 10

general_log                           = 0

general_log_file                      = /opt/mysql/log/mysql-general.log

# PERFORMANCE_SCHEMA #

performance_schema                    = 1

performance_schema_consumer_statements_digest = 1

performance_schema_consumer_events_statements_history_long = 1

!includedir /etc/mysql/conf.d/

# MariaDB-specific config file.

# Read by /etc/mysql/my.cnf

[client]

# Default is Latin1, if you need UTF-8 set this (also in server section)

#default-character-set = utf8 

[mysqld]

#table_open_cache=8192

#table_definition_cache=8192

innodb_force_primary_key=0

innodb_strict_mode=0

transaction_isolation='READ-COMMITTED'

max_allowed_packet                    = 1G

expire_logs_days                      = 1

read_only                             = OFF

#innodb_buffer_pool_size               = 45021M

slave_compressed_protocol             = ON

#query_cache_limit=52428800

#query_cache_size=52428800

max_connections                 = 500

innodb_buffer_pool_size               = 65536M

sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

optimizer_switch = 'split_materialized=off'

#skip-slave-start

#

# * Character sets

# 

# Default is Latin1, if you need UTF-8 set all this (also in client section)

#

#character-set-server  = utf8 

#collation-server      = utf8_general_ci 

#character_set_server   = utf8 

#collation_server       = utf8_general_ci 

pravin_malali, thank you for the report. I see that your innodb_buffer_pool_size is 72GiB despite 64GiB in the configuration, and the total maximum memory usage is 94GiB.

What kind of workload are you running? Any prepared statements or stored procedures? I wonder if this could be related to MDEV-29988, which was a regression introduced in 10.6.11 and supposedly fixed in 10.6.12.

Yes we run a lot of prepared statements and stored procedures. The load is very read heavy.

The anomaly with innodb buffer pool size was weird too. After a restart it is reflecting the current value.

We are running MariaDB 10.6.12 and we still have these issues.

JFYI, in PS/SP was found 4 memory leak, 3 fixed (in review), one in process of fixing...

I have fixed 3 memory leaks (will be in the next release). Feedback is needed if it will hep. Also work of finding memory leaks continue...

I built 10.6.12 with the 3 fixes:

• https://github.com/MariaDB/server/commit/348f4c9f3b9d7b2cb4aeff5f3b5c5d1b7291189a
• https://github.com/MariaDB/server/commit/45d4f6b97b4811b1b7783dcd19526be1dbb196dc
• https://github.com/MariaDB/server/commit/6171119bc145a91d94a89458142e087924e4a9c6

Unfortunately, still the same memory leaks. Looking forward to test with the 4th fix that is coming soon.
Let me know if you need any help in terms of testing.

The MariaDB 10.6.13 came out on 10th of May 2023. Does it address the OOM issues? Or are we still waiting on a fix for this issue in general?

As you can see (comment above) we have fixed some issues, but the bugreport does not point something concrete (no test case and so on) so we are just trying find memory leaks and fix them. If you have something we can repeat you are welcome to share.

Just now we are making tools to catch possible memory leaks in SP/PS and fix all what we have found (so far 3 of them).

We can reproduce memory leaks with the attached query.

It's a typo3 instance which is doing a lot of SELECT queries with many nested subqueries.
Everytime I run this query, the memory usage jumps up (circa 10MB) and is not freed anymore. I can run the query in an infinite loop and see the usage constantly increasing.

No prepared statements nor stored procedures nor virtual columns are used.

Let me know If you need more informations.

query.sql

I will try to reconstruct some database to the query, will see it it will behave the same

Vincent, can you attach 'show create table' for the tables involved in the query ?
If possible, can you try add some 'dummy data' that would show the problem?
You can use the sequence table to insert a lot of data quickly:

insert into other_table (key1,key2,key3) select seq,seq,seq from seq_1_to_1000;

If we can repeat the issue, then we can fix it!

Please also add an EXPLAIN of the query so we can see what the optimizer is doing.

As it's a customer's database, I have to clarify if we can share the table definitions and data. In case I get the permission, do you have a secure way to share that data? Otherwise I will have to anonymize part of the data.

Good Morning,

we do also experiencing a memory leak and just starting to analyze it. Is there any data I can provide to help you?

Version: 10.6.13
Running in: kubernetes 1.24.14
Application: based on spring boot 2.6.15 with spring-data-jpa 2.6.10

We upgraded to 10.6.14. I will provide more information about the memory consumption on Thursday.

Edit: We have still a memory leak with 10.6.14

I just uploaded a full dump and a query through your FTP.

Best way to reproduce it is to run the query simultaneously (~10x) in an endless loop.
Then you should see the virtual memory jumping up pretty fast. RSS is also rising slowly but constantly.

Possible scripts for filling with testdata, but it is needed to clarify the description of the tables and valid range of values for some columns, allowable range and statistic of values for some data:

--source include/have_innodb.inc

--source include/have_sequence.inc

CREATE TABLE `tx_news_domain_model_news`  (

  `uid` int(11) NOT NULL,

  `deleted`  smallint(6) DEFAULT 0,

  `pid`  smallint(6) NOT NULL,

  `sys_language_uid` smallint(6) DEFAULT 0,

  `t3ver_state` smallint(6) DEFAULT 0,

  `t3ver_wsid` smallint(6) DEFAULT 0,

  `t3ver_oid` smallint(6) DEFAULT 0,

  `hidden` smallint(6) DEFAULT 0,

  `starttime` int DEFAULT NULL,

  `endtime` int DEFAULT NULL,

  `fe_group`  varchar(30) DEFAULT NULL,

  `datetime` int DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `sys_category_record_mm`  (

  `uid_foreign` int(11) NOT NULL,

  `uid_local` int(11) NOT NULL,

  `fieldname` varchar(30) NOT NULL,

  `tablenames` varchar(30) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

insert into `tx_news_domain_model_news`(

  `uid`,

  `deleted`,

  `pid`,

  `sys_language_uid`,

  `t3ver_state`,

  `t3ver_wsid`,

  `t3ver_oid`,

  `hidden`,

  `starttime`,

  `endtime`,

  `fe_group`,

  `datetime`

) select

  FLOOR(1 + (RAND() * 300)),

  ELT(FLOOR(1 + (RAND() * 2)), 0, 1),

  FLOOR(1 + (RAND() * 10)),

  ELT(FLOOR(1 + (RAND() * 3)), 0, 1, -1),

  ELT(FLOOR(1 + (RAND() * 3)), 0, 1, -1),

  ELT(FLOOR(1 + (RAND() * 3)), 0, 1, -1),

  ELT(FLOOR(1 + (RAND() * 3)), 0, 1, -1),

  ELT(FLOOR(1 + (RAND() * 2)), 0, 1),

  UNIX_TIMESTAMP(CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 90 * 24 * 60 *60) SECOND),

  0,

  ELT(FLOOR(1 + (RAND() * 5)), '', '1', '0', '-1', NULL),

  UNIX_TIMESTAMP(CURRENT_TIMESTAMP - INTERVAL FLOOR(RAND() * 100 * 24 * 60 *60) SECOND)

from  seq_1_to_1000000;

insert into `sys_category_record_mm`(

  `uid_foreign`,

  `uid_local`,

  `fieldname`,

  `tablenames`

) select

    FLOOR(1 + (RAND() * 300)),

    FLOOR(1 + (RAND() * 10)),

    ELT(FLOOR(1 + (RAND() * 2)), 'categories','fieldname1'),

    ELT(FLOOR(1 + (RAND() * 2)), 'tx_news_domain_model_news','tx_news_domain_model_old')

from  seq_1_to_100000;

drop tables  `tx_news_domain_model_news`, `sys_category_record_mm`;

vrj95 Thank you a lot for the query and the dump, but I can not repeat memaory leack. I will try more but if you could you tell:

1) how the query executed (binary/text protocol)
2) settings of the server (especially sql_mode, optimizer_switch)
3) anything else which can help to repeat

it can speed up the process...

Actually outputs of:

select * from information_schema.processlist;

could be useful to detect which thread/comands lead to memory leack (and if the memory registered at all)

Could you do try to do a graph on
"show global status like "memory_used"
and see if this also increases over time?

If it increases then also doing
select id, info, memory_used, max_memory_used from information_schema.processlist;

And check with process increses max_memory_used the most.

If the above memory_used does not increase, it could be that the problem is memory fragmentation and changing to use jemalloc may help.

I was looking at the graphs attached to the issue.
It shows the memory growing over time, but it also goes down at certain times.
This means that this issue is likely to be one of the following:

• Along running query that frees it's memory at end of the query.
• A memory leak similar to MDEV-29988 where memory associated with a connection or prepared statement continues
  to grow until the connection ends.

Having the output from 'select * from information_schema.processlist' done every 1/2 hour over some period of time would be very helpful in figuring out what is wrong.
Same for "show global status like "memory_used"

Sorry for the long delay. In collaboration with our customer, we managed to setup a replica of their production server so we can test around. I have disabled page caching of the typo3 instance and running a script that constantly makes some requests to the site.

Please have a look at the graph. I can now fully reproduce it.

RES size was about 800MB, Virtual Memory 5GB when I started yesterday.
Today, we're at RES 17.6GB and Virtual Memory 774GB.

I also made a graph from SHOW GLOBAL STATUS LIKE "memory_used" as requested. It does not increase over time.

I also logged the process list and uploaded it through your FTP (MDEV-30889-processlist.log)

Regarding the settings:

sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
in_predicate_conversion_threshold = 4294967295 (required because of the many subqueries)

vrj95, as global "memory_used" value isn't growing, it means the memory that the server can account for isn't leaking. It could still be a memory leak inside InnoDB or not a memory leak at all, but, for example, an extreme case of memory fragmentation that causes memory allocator to use more and more system memory. The latter is more likely, we've seen these cases before.

You're likely using ptmalloc2, default glibc memory allocator. Can you try jemalloc or tcmalloc? A different allocator will have a different memory allocation strategy and often it is enough to fix the fragmentation problem.

If you start MariaDB via mysqld_safe, run it with --malloc-lib=jemalloc or add this to your my.cnf.
If you start MariaDB with systemd, add Environment="LD_PRELOAD=..." to the mariadb.service file.

Sorry, I just realized I never mentioned that I'm using FreeBSD. This may be important for you. I originally posted to MDEV-30848 which is FreeBSD related.

Under FreeBSD, it seems that jemalloc is the default memory allocator.
I will try to build devel/google-perftools which should provide tcmalloc and see if that makes any difference.
As far as I can see, ptmalloc2 is not really available on FreeBSD. There is a port but it is not maintained anymore.

For what it is worth, years ago in InnoDB there was a memory fragmentation issue that was fixed by reducing the number of heap memory allocations when tables are being added to the InnoDB data dictionary cache. I remember that the function dict_add_col_name() was added to address that. That change was bundled with a bigger change MySQL Bug #20877.

My understanding is that memory allocator libraries have it easier if there are fewer memory allocation requests and they are of the same size. Hopefully there are some debugging tools that would report internal memory fragmentation in the allocator, and allows to find the malloc() or realloc() that are most contributing to it.

I was able to switch to the tcmalloc allocator which seems to fix the problem. Both virtual and resident memory remains stable.

When the user moved to use jemalloc, the OOM issues disappeared.
As jemalloc does not report any memory leaks, this is clearly a fragmentation issue.
What needs to be done is to analyze the queries that causes memory fragmentation and find out if there is anything that can be done on MariaDB side to minimize the fragmentation.

Some of the things we can try is to allocate most all memory blocks of the same size, making some special handling of big blobs etc.
However we need a test case that shows the fragmentation to be able to come up with some proven solutions.

We are currently being hit by this issue big time.

• `mysql Ver 15.1 Distrib 10.6.14-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2`
• Ubuntu 20.04 LTS,
• DO, 32GB RAM, 16vCPU (optimized type)
• 2 large dbs (ca. 210GB).

The first graph shows mem usage as reported by the monitoring. With `innodb_buffer_pool_size = 16G` it should not soar so high.

Now I slashed it down to 12G (on a 32GB instance) - and after less than 24hrs it's on 76%.

The second graph - I took VSIZE and RSS from `ps` (every minute). Both basically only go up.

Can we safely jump on the jemalloc train or what are our options? Will upgrading to 10.6.15 change anything?

I am not sure if there is any correlation, but innodb does preload some stuff from disk to speed up "warming up" if I remember correctly? Disabling that feature - or discarding the current "image"?

We take regular backups (mysqldump, single transaction) - obviously during that time it tanks mem, but only after a fresh restart - and that is also expected (tanks below the innodb buffer pool).

When I ask mariadb about the configured size, to be 100% sure no monkey business is involved:

If there's anything I can do to help - let me know. This is our prod machine, so doing risky stuff is not really on the table, but I can spin a copy where I can run experiments.

Thanks in advance for any advice on the subject.

there are many other allocations besides InnoDB buffer pool. check also

show global status like 'memory_used%';

and

select id, memory_used, max_memory_used from information_schema.processlist;

Thanks for your insight. Yes, I am aware of that, but unless there are thousands of connections it should not cause me much of a trouble?

I am puzzled because previously when you allocated innodb buffer pool to be 70% of total RAM (dedicated DB machine) - then it would hold. Now when allocating 37.5% it goes south within 2-3 days.

Same problem with 10.6.16

@Michael Widenius Let me know if there's anything else I can do to help you find the cause. I still believe it may be caused by queries that have a lot of nested subqueries as the one I provided on 06/06/2023. Queries like this one caused the virtual memory to jump up by 400-500MB everytime I execute it.

@Vincent Jancso The issue got completely out of hand here and I decided to follow the initial advice, i.e. `tcmalloc` or `jemalloc`. In my case it was the latter one. It solved the issue - although I am surprised that the fragmentation is going so much out of hand. I have similar workloads that run on the same version and are super stable.

I think that it could be a useful exercise to evaluate and classify all malloc() calls that are executed for some rather short SQL input.

When it comes to the InnoDB storage engine, I think that the situation was improved in the past by MDEV-20950 and MDEV-15199, which replaced some heap memory allocation with stack based allocation.

We had a similar issue in our deployment with a containerized MariaDB 10.11.6. Memory usage was going up with the execution of our daily mysqldump backup and never went down. After some time the DB process was OOM killed. Memory usage curve was very similar to that of Michal. We gave the container up to 20 GB (overkill accroding to mysqltuner) of memory to no avail.

In our case it was tcmalloc which solved the issue. We tried jemalloc too, which changed the course of the curve but not the tendency.

FYI: cat /proc/buddyinfo listed very low numbers on the right side of the table. Memory chunks larger than 512 KiB were almost absent and there were plenty of very small chunks. It looks like a memory fragmentation issue indeed.

We still have this problem and tried to solve it with: disabling Transparent Huge Pages and using jemalloc, didnt helpt.
We are now trying tcmalloc and waiting for the result.
memory_used and the information_schema.processlist only show a fraction of the used memory. (300mb vs 6GB overhead).
We have this on multiple servers with different software, so its not a specific query.
I have a feeling it is caused by inserts and not from selects. If we import a database, wich is actually just create tables and inserts, the overhead grows. For example before the import 10GB and after the import 16GB. Buffer Pool Size is limited to 10GB. <-we checked inside the container it was not mariadb using the memory so this observation was wrong.
We use the 10.6.12 docker image on Debian 11.

We also experience severe memory leaks that require a server reboot every 15 minutes to not run into OOM issues.

Here's the setup:

RAM Limit for MariaDB: 16 GB. Usage: 26.8G (84% of systems memory)

The issue occurs after writing queries like UPDATE or INSERT, especially when TRIGGERs are involved.

Server version: 10.11.6-MariaDB-1:10.11.6+maria~deb11

In the moment, we are porting a lot of projects to PostgreSQL to reduce the number of MariaDB crashes per hour.