IIRC binlog_cache_size is not per session, but tmp_table_size is more or less per session, and tmp_table_size is usually quite large compared to other things. On Fri, Aug 4, 2023 at 4:27 PM Marco Dickert - evolver group via discuss <discuss@lists.mariadb.org> wrote:
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...
Regards, Marco _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org