Home / server / Questions / 1020632
Accepted
djaho
Asked: 2020-06-09 23:07:39 +0800 CST

MariaDB reduce buffer RAM usage per thread

  • 772

I was looking for an answer to this question, but couldn't find it anywhere.

I want to reduce the memory usage per each database connection. Here is what mysql tuner currently says about memory usage for one of my databases:

[--] Physical Memory     : 985.2M
[--] Max MySQL memory    : 950.4M
[--] Other process memory: 0B
[--] Total buffers: 292.0M global + 18.8M per thread (35 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 310.8M (31.55% of installed RAM)
[!!] Maximum possible memory usage: 950.4M (96.47% of installed RAM)

You can see that the memory usage per thread is quite high 18.8M. And here is an output from one of my other servers:

[--] Total buffers: 400.0M global + 2.8M per thread (250 max threads)

You can see that the memory usage here is a lot lower (only 2.8M), therefore I can have many more connections to the database.

I tried to find out which variable controls the memory usage per thread, but couldn't find anything. I thought that it is related to sort_buffer_size or read_buffer_size, but when I change one of these variables, the usage per thread doesn't seem to fall down.

It appears to me that it is not something that I can modify at all and is probably calculated based on actual usage of memory per thread?

Is there anything I can do to reduce the buffer memory usage per thread?

Edit (2020-06-10): The difference between these two databases is that the first one is version MariaDB 10.4.12, while the second one is MariaDB 10.1.38. If I compare variables such as sort_buffer_size, read_buffer_size, key_buffer_size and others, mentioned in comments, all I see is that they are set to the same values.

memory buffer mariadb

3 Answers

  1. Best Answer

    Okay, so after some more investigation I finally figured out why were these two numbers so different.

    The thing is that I was using an old version of mysqltuner.pl on on the server where the max memory usage per thread was just 2.8 MB. And 6 months ago, the mysqltuner.pl started also using the max_allowed_packet variable value in the usage per thread calculation, which adds additional 16 MB and gets to 18.8 MB. Here is the change on github: https://github.com/major/MySQLTuner-perl/commit/c5765f02133259b40d9932eb7d35ba5c1665bad9 After running the updated version of mysqltuner.pl, the RAM usage is shown like that:

    [--] Total buffers: 400.0M global + 18.8M per thread (250 max threads)

    These are the variables which are used to calculate the buffer usage per thread (for relatively new MariaDB versions):

    read_buffer_size,
read_rnd_buffer_size,
sort_buffer_size,
thread_stack,
max_allowed_packet,
join_buffer_size

    Only @Wilson Hauck mentioned the max_allowed_packet variable ;)

    And here is the query to check those values if anyone needs it:

    SHOW GLOBAL VARIABLES WHERE variable_name IN('read_buffer_size', 'read_rnd_buffer_size', 'sort_buffer_size', 'thread_stack', 'max_allowed_packet', 'join_buffer_size');

    So after all this research, I figured out that I have way too high number of allowed db connections on the second server (250). If used in total, my DB could use 5GB of RAM, while I only have 2GB on this system.

    Actual answer to the initial question: So if I would want to lower the memory usage per thread or per connection (as a few of you mentioned), I would probably have to lower the max_allowed_packet value, but I am not sure if this is a good idea.

    Thanks to everybody for the help.

    • 4

  2. That is a bogus number. It is based on a lot of pessimistic assumptions. Even so, in some really extreme situations, it is an underestimate. (There is no good expression for that metric.)

    You seem to have 1GB of RAM. This is a very small amount, but it is viable.

    (Note: When you run out of RAM, swapping occurs. But, since MariaDB is optimized with the assumption of no swapping, swapping leads to really bad performance.)

    Do you have any other apps running in that 1GB? If so, things are even tighter.

    First, check innodb_buffer_pool_size. 200M may be too high. max_connections should be at most 20. These should be under 10M: tmp_table_size, max_heap_table_size, sort_buffer_size, innodb_log_buffer_size, read_buffer_size, read_rnd_buffer_size .

    If you would like further analysis, see http://mysql.rjweb.org/doc.php/mysql_analysis#tuning

    • 2

  3. The pedantic answer to your question is : thread_stack_size controls memory per thread.

    Any other variable has nothing to do with threads, although max_connections caps the number of threads, but even thens of thousands of connections you can still be using one thread (thread_handling = pool-of-threads, thread_pool_max_threads=1), although I do not recommend that.

    • -1