MariaDB memory use rises beyond configured size

Question

I noticed a steady increase in system memory use %age (as reported by sar) which I've tracked to MariaDB. It's does not seem to be related to db load: this increase is notmatched by an increasing number of queries.

I have the following size configuration:

key_buffer_size         =  400000000                                                                                  
innodb_buffer_pool_size = 1210612736

Total there is 1.5GB, yet it's now topped out at 2.3GB on RSS memory.

Why is it getting so greedy? Can I get it to stick to its limits better? Or perhaps it is sticking to the limits on InnoDB but is using memory for something else, if so, is there a way to understand that so that I can adjust the configuration to keep the total memory use within a certain level? Or perhaps it's like Linux kernel and just uses all free memory for caching while it's free, but happily relinquishes it as needed?

I'm on MariaDB 10.3.22 on Debian 10 (Buster). The workload is a webserver (so I need to keep some memory for other processes: nginx+php). Happy to post more config if required.

Run [mysqltuner.pl](http://mysqltuner.pl) and it will explain the memory usage in detail (among many other useful things). — Michael Hampton, Jun 10 '20 at 19:45

score 5 · Answer 1 · answered Jun 10 '20 at 10:37

With credit for the forumla due to Gordon's answer and the link therein, the following command will tell you how much your MySQL/MariaDB database needs:

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`;

(I am the OP and this gives 2.9GB as my total configured usage - time to lower some figures!)

score 4 · Accepted Answer · answered Jun 10 '20 at 08:46

4

The memory settings you listed are shared. There are also various buffers that are allocated per thread. Here is a link to a MySQL memory calculator:

https://www.mysqlcalculator.com/

answered Jun 10 '20 at 08:46

Gordan Bobić

936
4
10

iBug · Answer 3 · 2020-06-11T05:35:39.367

1

If you want to limit the overall memory usage of MariaDB, assuming you installed it with apt / apt-get, you should be able to accomplish this with systemd.resource-control(5).

Call with root:

systemctl edit mariadb.service

Enter the following configuration in the editor that shows up, tweak if you want:

[Service]
MemoryMax=1024M
MemorySwapMax=128M

Save and quit. It takes effect immediately and is backed by cgroup memory controller.

edited Jun 11 '20 at 05:35

answered Jun 10 '20 at 17:05

iBug

1,048
2
9
21

Does this assume that you are using cgroups? – Rick James Jun 10 '20 at 21:20
What happens if the service hits the limit? Kill? Swap? – Rick James Jun 10 '20 at 21:20
@RickJames It's not *you* using cgroups, but *systemd* who handles it for you. It may go to swap depending on settings, but the cgroup behavior is further memory allocation request fails (`mmap(2)` returns error). – iBug Jun 11 '20 at 05:35

score 1 · Answer 4 · answered Jun 10 '20 at 21:29

Let's pick this apart:

( @@GLOBAL.key_buffer_size              40M unless using MyISAM tables 
 + @@GLOBAL.query_cache_size            0 -- it is mostly useless, esp on production
 + @@GLOBAL.innodb_buffer_pool_size     1G if you have only 3G of ram

               No more than 1% of RAM for these:
 + @@GLOBAL.tmp_table_size              -- belongs in the group below
 + @@GLOBAL.innodb_log_buffer_size 
     @@GLOBAL.sort_buffer_size
   + @@GLOBAL.read_buffer_size 
   + @@GLOBAL.read_rnd_buffer_size 
   + @@GLOBAL.join_buffer_size 
   + @@GLOBAL.thread_stack 
   + @@GLOBAL.binlog_cache_size)

 + @@GLOBAL.max_connections * (         100 is probably plenty

There is no "perfect" formula. The mysqlcalculator site has many flaws. As does any other site claiming to have a formula.

If you are using MyISAM (or a mixture of MyISAM and InnoDB), then see http://mysql.rjweb.org/doc.php/memory

Thanks for your opinions! I use a different formula for allocating `key_buffer_size` and `innodb_buffer_pool_size` based on the ratio of innodb index:data. And I set `max_connections` to slightly above the `max_children` in my php fpm setting since every worker will require at least one connection. — artfulrobot, Jun 11 '20 at 09:31

MariaDB memory use rises beyond configured size

4 Answers4