Hi folks,
we experience a RAM issue with MariaDB (version 10.5.19-MariaDB-0+deb11u2-log)
on a standard Debian bullseye system. The problem is that over time MariaDB uses
more and more RAM, until the kernel's oom-killer terminates it.
The server is a VMware guest with 8G RAM, 2G swap and 4 cores. The most
important parameter, innodb_buffer_pool_size, is set to 2G. I know this is quite
low for this system, but I want to have proof that MariaDB consumes defininitely
more RAM than allowed.
According to the calculation in this comment [1], the RAM usage shouldn't exceed
4.2G. Here is the result, including the raw configuration values:
```
MariaDB [(none)]> SELECT ROUND(
-> ( @@GLOBAL.key_buffer_size
-> + @@GLOBAL.query_cache_size
-> + @@GLOBAL.tmp_table_size
-> + @@GLOBAL.innodb_buffer_pool_size
-> + @@GLOBAL.innodb_log_buffer_size
-> + @@GLOBAL.max_connections * (
-> @@GLOBAL.sort_buffer_size
-> + @@GLOBAL.read_buffer_size
-> + @@GLOBAL.read_rnd_buffer_size
-> + @@GLOBAL.join_buffer_size
-> + @@GLOBAL.thread_stack
-> + @@GLOBAL.binlog_cache_size)
-> ) / 1024 / 1024, 1) `total MB`;
+----------+
| total MB |
+----------+
| 4220.0 |
+----------+
1 row in set (0,001 sec)
MariaDB [(none)]> SELECT @@GLOBAL.key_buffer_size, @@GLOBAL.query_cache_size
, @@GLOBAL.tmp_table_size, @@GLOBAL.innodb_buffer_pool_size
, @@GLOBAL.innodb_log_buffer_size, @@GLOBAL.max_connections
, @@GLOBAL.sort_buffer_size, @@GLOBAL.read_buffer_size
, @@GLOBAL.read_rnd_buffer_size, @@GLOBAL.join_buffer_size
, @@GLOBAL.thread_stack, @@GLOBAL.binlog_cache_size \G
1 row in set (0,000 sec)
*************************** 1. row ***************************
@@GLOBAL.key_buffer_size: 10485760
@@GLOBAL.query_cache_size: 536870912
@@GLOBAL.tmp_table_size: 16777216
@@GLOBAL.innodb_buffer_pool_size: 2147483648
@@GLOBAL.innodb_log_buffer_size: 134217728
@@GLOBAL.max_connections: 512
@@GLOBAL.sort_buffer_size: 2097152
@@GLOBAL.read_buffer_size: 131072
@@GLOBAL.read_rnd_buffer_size: 262144
@@GLOBAL.join_buffer_size: 262144
@@GLOBAL.thread_stack: 299008
@@GLOBAL.binlog_cache_size: 32768
```
However, MariaDB uses up to 5.4G so far. And it keeps increasing. Last time the
oom-killer was invoked, MariaDB used 10G total, and 7.4G rss (given in 4k
pages):
```
[Mi Jul 26 16:01:06 2023] Tasks state (memory values in pages):
[Mi Jul 26 16:01:06 2023] [ pid ] uid tgid total_vm rss
pgtables_bytes swapents oom_score_adj name
...
[Mi Jul 26 16:01:06 2023] [3462912] 10000 3462912 2753196 1895256 20762624
472509 0 mariadbd
...
[Mi Jul 26 16:01:06 2023]
oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/mariadb.service,task=mariadbd,pid=3462912,uid=10000
[Mi Jul 26 16:01:06 2023] Out of memory: Killed process 3462912(mariadbd)
total-vm:11012784kB, anon-rss:7581024kB, file-rss:0kB,shmem-rss:0kB, UID:10000
pgtables:20276kB oom_score_adj:0
```
The MariaDB knowledge base [2] says that the problem probably correlates with
one of the following options:
```
+----------------------------+----------------------+----------------+
| variable_name | default_value | variable_value |
+----------------------------+----------------------+----------------+
| INNODB_IO_CAPACITY_MAX | 18446744073709551615 | 2000 |
| QUERY_CACHE_LIMIT | 1048576 | 67108864 |
| MAX_CONNECTIONS | 151 | 512 |
| TABLE_OPEN_CACHE_INSTANCES | 8 | 7 |
| LONG_QUERY_TIME | 10.000000 | 5.000000 |
| KEY_BUFFER_SIZE | 134217728 | 10485760 |
| QUERY_CACHE_SIZE | 1048576 | 536870912 |
| INNODB_LOCK_WAIT_TIMEOUT | 50 | 80 |
| INNODB_LOG_BUFFER_SIZE | 16777216 | 134217728 |
| HOST_CACHE_SIZE | 128 | 654 |
| INNODB_FT_MIN_TOKEN_SIZE | 3 | 2 |
| WAIT_TIMEOUT | 28800 | 600 |
| INNODB_FAST_SHUTDOWN | 1 | 0 |
| INNODB_LOG_FILE_SIZE | 100663296 | 1073741824 |
| THREAD_POOL_SIZE | 8 | 4 |
| INNODB_BUFFER_POOL_SIZE | 134217728 | 2147483648 |
| MAX_ALLOWED_PACKET | 16777216 | 268435456 |
| BACK_LOG | 150 | 254 |
+----------------------------+----------------------+----------------+
```
I can't see what setting could probably cause the issue. Some values are Debian
defaults. Most of them we set intentionally. Can anyone give a hint on where to
look for? Do you need further information?
Thanks for any help.
[1] https://serverfault.com/a/1020847
[2]
https://mariadb.com/kb/en/mariadb-memory-allocation/#how-to-troubleshoot-out-of-memory-issues
Regards,
Marco
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
