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 <[email protected]> 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-of-memory-issues > > Regards, > Marco > _______________________________________________ > discuss mailing list -- [email protected] > To unsubscribe send an email to [email protected] _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
