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
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
Hi, Marco,
Please, try the latest 10.5 release, there were few bugs with those symptoms fixed.
On Aug 04, Marco Dickert - evolver group via discuss 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.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Hi Sergei,
a quick follow-up on this topic. We still experience the RAM usage problems. After your suggestion to upgrade MariaDB to the latest 10.5, we switched to the community repository for debian (https://dlm.mariadb.com/repo/mariadb-server/...). This is what we did since:
* upgrade MariaDB to 10.5.21 * upgrade MariaDB to latest 10.6 * switch to jemalloc2 library on 10.6 * upgrade MariaDB to latest 10.11 (system malloc) * switch to jemalloc2 library on 10.11
We ran every setup for several days, to check if there is any improvement on the RAM usage front, but nothing really changed. We noticed that when we use jemalloc2, the gaps between the OOM-kills is a bit bigger, but the general problem persists. Also, we couldn't determine a real "trigger" for the behaviour.
Our application has a very regular usage pattern. There is a peak of activity in the morning (08-10 a.m.), a steady baseline until about 10 p.m., and very low activity at night (see the attached graph 1). The OOM incidents happen both in the morning and in the evening. The second graph shows the RAM usage (RSS) of the mariadbd process over the last three days. Third, I attached a text file showing the timestamps of the OOM kills.
Unfortunately we are not quite sure which is the best way to debug this further. We took a look at our queries, but couldn't determine a problem there. Our frontend application does not use queries with JOINs. Every request triggers only a few SELECTs and UPDATEs. Two tables we write to are relatively big (10 and 12 GB). On average every frontend request triggers about 4-8 queries.
Are there any other metrics we could observe to get a hint to why this happens? Or has anyone another idea on how to get a grip on what is going on?
Am 2023-08-04 19:13:44 schrieb Sergei Golubchik:
Hi, Marco,
Please, try the latest 10.5 release, there were few bugs with those symptoms fixed.
On Aug 04, Marco Dickert - evolver group via discuss 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.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
Have you checked whether:
1) Your theoretical max memory usage exceeds ~90% of RAM?
Start with:
SELECT ((@@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@key_buffer_size + @@query_cache_size + @@max_connections * (@@bulk_insert_buffer_size + @@join_buffer_size + @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@tmp_table_size)) / 1024 / 1024 / 1024) AS max_memory_GB;
2) Are you using /tmp/ for temporary tables and is your /tmp/ mounted as tmpfs?
On Thu, 7 Sep 2023, 14:17 Marco Dickert - evolver group via discuss, < discuss@lists.mariadb.org> wrote:
Hi Sergei,
a quick follow-up on this topic. We still experience the RAM usage problems. After your suggestion to upgrade MariaDB to the latest 10.5, we switched to the community repository for debian (https://dlm.mariadb.com/repo/mariadb-server/...). This is what we did since:
* upgrade MariaDB to 10.5.21 * upgrade MariaDB to latest 10.6 * switch to jemalloc2 library on 10.6 * upgrade MariaDB to latest 10.11 (system malloc) * switch to jemalloc2 library on 10.11
We ran every setup for several days, to check if there is any improvement on the RAM usage front, but nothing really changed. We noticed that when we use jemalloc2, the gaps between the OOM-kills is a bit bigger, but the general problem persists. Also, we couldn't determine a real "trigger" for the behaviour.
Our application has a very regular usage pattern. There is a peak of activity in the morning (08-10 a.m.), a steady baseline until about 10 p.m., and very low activity at night (see the attached graph 1). The OOM incidents happen both in the morning and in the evening. The second graph shows the RAM usage (RSS) of the mariadbd process over the last three days. Third, I attached a text file showing the timestamps of the OOM kills.
Unfortunately we are not quite sure which is the best way to debug this further. We took a look at our queries, but couldn't determine a problem there. Our frontend application does not use queries with JOINs. Every request triggers only a few SELECTs and UPDATEs. Two tables we write to are relatively big (10 and 12 GB). On average every frontend request triggers about 4-8 queries.
Are there any other metrics we could observe to get a hint to why this happens? Or has anyone another idea on how to get a grip on what is going on?
Am 2023-08-04 19:13:44 schrieb Sergei Golubchik:
Hi, Marco,
Please, try the latest 10.5 release, there were few bugs with those symptoms fixed.
On Aug 04, Marco Dickert - evolver group via discuss 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.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
-- Kind regards, Marco Dickert _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
participants (3)
-
Gordan Bobic -
Marco Dickert - evolver group -
Sergei Golubchik