MariaDB reduce buffer RAM usage per thread

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.

Answer

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

Attribution
Source : Link , Question Author : djaho , Answer Author : Rick James

Leave a Comment