Home / server / Questions / 1020811
Accepted
artfulrobot
Asked: 2020-06-10 23:14:58 +0800 CST

MariaDB memory use rises beyond configured size

  • 772

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.

graph of memory use

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.

mysql memory-usage debian-buster mariadb

4 Answers

  1. 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!)

    • 5
  2. Best Answer

    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/

    • 4

  3. 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.

    • 1

  4. 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

    • 1