3

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.

3
  • How many concurrent connections do you expect for this database? 35 seems way too many for the compute and memory resources of a 1 GB host. Or I suppose about 30 after subtracting the non-connection threads. Jun 9, 2020 at 14:47
  • Yes, you could REMOVE 4 critical GLOBAL VARIABLES from your my.cnf - they are - join_buffer_size sort_buffer_size read_buffer_size read_rnd_buffer_size and max_allowed_packet - to allow defaults work for you until you are more familiar with this type of system. A 10M size on read_buffer_size or read_rnd_buffer_size will KILL performance and have an unnecessarily large RAM requirement. Jun 10, 2020 at 0:46
  • The max used number of connections was 30, so I set this limit to 35. Although I would like to have it higher in case the service needs more than just 35 db connections.
    – djaho
    Jun 10, 2020 at 12:17

3 Answers 3

5

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.

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

3
  • innodb_buffer_pool_size is actually set to 100M, but it could probably be lower because I have only about 20M of data in InnoDB tables. I don't believe that max_connections should be set to 20. I had a higher connection usage in the past on this database (30) so 20 is way too low in my opinion. However,I still have no idea what affects the memory usage per thread value.
    – djaho
    Jun 10, 2020 at 13:31
  • An addition tomy previous comment. The total buffer size is in my case mostly innodb_buffer_pool_size and aria_pagecache_buffer_size. In case someone is wondering what might be increasing their value there.
    – djaho
    Jun 10, 2020 at 13:56
  • The buffer_pool and pagecache are not "per connection". The items I list are "per query" -- but are unused in most queries, and may have multiple uses in a single query.
    – Rick James
    Jun 10, 2020 at 17:51
-1

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.

3
  • The line - [--] Total buffers: 292.0M global + 18.8M per thread (35 max threads) - is influenced by many Global Variables besides the 5 I have suggested removing from this user's configuration. The per connection variables are all contributing to this instance's 18.8M size. Jun 10, 2020 at 1:01
  • You say "per connection". The question was "per thread". Thread has as much relation to connection as lemon is to pretzel. Thread is an OS concept, but database connection or session is not . Thread has a stack, and no other "buffers" , And I know this is constantly being mislabeled , thus I give the pedantic answer Jun 10, 2020 at 5:46
  • @djaho and Vladislav Maybe you can convince MySQLTuner developers to change 'per thread' to 'per connection' to more accurately represent the object of their calculation. No wonder there is confusion. But then again, this is Open Source. Jun 10, 2020 at 12:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .