问题原文
原帖链接 · djaho · CC BY-SA 4.0
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.
已采纳解答
原帖链接 · djaho · CC BY-SA 4.0
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.
摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。
