VPS server on Centos 7 x64 with 8bg, cpanel/whm

Thanks Erik for reporting the bug.

The time"59:49" on the 10.6 measurement is ambiguous. If you've got further 10.6 measurements to confirm the trend that would be useful.

10.5+ has performance schema, which I'm not terribly well versed, in however setting the following start to provide instrumentation:

performance_schema=on

performance-schema-instrument='memory/%=ON'

After that the following tables/views will be populated:

MariaDB [sys]> show tables from sys like 'mem%';

+-----------------------------------+

| Tables_in_sys (mem%)              |

+-----------------------------------+

| memory_by_host_by_current_bytes   |

| memory_by_thread_by_current_bytes |

| memory_by_user_by_current_bytes   |

| memory_global_by_current_bytes    |

| memory_global_total               |

+-----------------------------------+

5 rows in set (0.001 sec)

MariaDB [sys]> show tables from performance_schema like 'mem%';

+-----------------------------------------+

| Tables_in_performance_schema (mem%)     |

+-----------------------------------------+

| memory_summary_by_account_by_event_name |

| memory_summary_by_host_by_event_name    |

| memory_summary_by_thread_by_event_name  |

| memory_summary_by_user_by_event_name    |

| memory_summary_global_by_event_name     |

+-----------------------------------------+

5 rows in set (0.001 sec)

SELECT * FROM performance_schema.memory_summary_global_by_event_name might start to show a leak by area.

Much appreciate your assistance in narrowing down this problem.

Yes, the time is pretty nebulous. Its growing by about 320MB daily it appears. My initial guess is that this may well be related to MDEV-29404

Those prepared statements above are by far the bulk of what happens on this server.

I'll work on getting more data.

I've added some files. I restarted the server at 16:19 and took the second snapshots at 8:45.

ps -aux output @ 11.28.2022-8:41:AM is
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
mysql 4822 1.3 6.8 2785864 548716 ? Ssl Nov27 13:19 /usr/sbin/mariadbd

It cannot be MDEV-29404 because a comment there says "Verified that this is not a new regression" and yours is not present in 10.3.36.

Can you try to close all connections for a moment and see if the memory would stop growing after they reconnect? That would mean the memory is not truly lost, but is freed on disconnect.

Does your processlist have unusually many connections in the "Starting cleanup" state?

It might be MDEV-29988 (see also duplicates, listed there).

As a workaround you can try to wrap your parameters (question marks) in CONVERT and see it that helps, like in

SELECT webfolder, webpassword

             FROM aercon0_aerweb.webaxess

             WHERE webusername = CONVERT(? USING _charset_of_webusername)

Can you try to close all connections for a moment and see if the memory would stop growing after they reconnect? That would mean the memory is not truly lost, but is freed on disconnect.

I am unable to stop all connections easily, but the one I did reconnect did appear to release about 300MB of memory.

Does your processlist have unusually many connections in the "Starting cleanup" state?

I have a number of items in sleep, but none in "Starting cleanup"

This is a production server in active use, I am somewhat limited in my testing options. Also note that I am using 10.6.11 for these present tests.

Yes, MDEV-29988 was in 10.6.11 too. But let's first check the preconditions:

• you use prepared statements
• you prepare once and then execute many thousands of times before deallocating the statement
• your statements contains a string comparison with strings in different (but compatible) character sets (e.g. utf8 and latin1).

if all that matches your use case you could be affected by MDEV-29988. If you do, then

• every execution of such a prepared statements will allocate more memory
• all memory will be freed when a statements is deallocated (explicitly or when the client disconnects)
• time to deallocate a prepared statement is proportional to the number of times it was executed

and workarounds could be

• deallocate prepared statements more often
• rewrite statements to avoid comparing strings in different character sets

or wait for the next release that will have it fixed

Are there any update and fix for this memory issue in 10.6.11?
We upgraded our production database servers from 10.3 to 10.6.10 and now we got crashes of mariadbd every day because it eats all memory (OOM).
Then we upgraded from 10.6.10 to 10.6.11 but it behaves the same wrong way. MariaDB is configured to use about 9GB of total memory but when we run project import (many queries from PHP application with PDO and prepared statements from Nette library) mariadbd uses over 40GB of memory + swap. Then system kills it because there is no more memory to use.
The same application runs fine on an older instances with 10.3 on VMs with about 8 - 10GB of total RAM.

Lachman, yes, it's a duplicate of MDEV-29988, and MDEV-29988 is marked a blocker so it will be in the next release

memory leak on 10.5.18 (debian 11) here

it seems to match quite well the description by Sergei Golubchik above

also, information_schema.PROCESSLIST.memory_used points a finger on emqx 4.1.3, which is indeed executing always the same prepared statement with string comparisons each time an MQTT PUBLISH is received, which is thousands of times.

what doesn't match is that if I kill the emqx mysql connections no memory is released to the OS (instead, /proc/pid/VmSize keeps growing at the same rate).

is it supposed to be released to the OS or to a mariadb memory pool?

also, in https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1027337 it seems that the bug was introduced between 10.5.15 and 10.5.18.

can you confirm?

we would like to downgrade to the latest non-affected debian package while we wait for 10.5.19

thanks

MAtteo

Introduced in 10.5.18 (there's a "Caused by" link in MDEV-29988).

Other possible workarounds, besides downgrading, could be

• do not keep the client connection constantly open, reconnect periodically (not very often)
• avoid the need for an implicit charset conversion — change collation_connection to match table's collation or add an explicit CAST

10.5.19 is planned to be released (see https://jira.mariadb.org) in two weeks

I tried forcing disconnections using mysql kill (the application reconnects gracefully), but apparently did not free the memory (at least from the OS perspective), and did not stop the mariadb memory occupation growth (so it seems that also mariadb didn't recover the memory).

our workload includes at least 20/s queries to stored procedures with string comparisons and different charsets, which triggered MDEV-29988

if we downgrade to 10.1.15, could we be exposed to the bug whose fix introduced MDEV-29988 (https://jira.mariadb.org/browse/MDEV-19605)?

thanks

MAtteo

I'm using 10.11.4 (Debian Bookworm package) and PowerDNS is causing this leak pretty repeatably

echo 'kill user dns' | mysql helps temporarily

I don't think it should be closed, it is neither related to any tickets as "duplicate" nor fixed

xani, if you're using 10.11.4, it cannot be this leak, it must be something else.

Does MariaDB internal memory monitoring show the leak? MEMORY_USED status variable? MEMORY_USED column in the INFORMATION_SCHEMA.PROCESSLIST, performance schema?