Hello, I have a multiple server conf with ubuntu 20.04 and mysql Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 I try to tune my VPS memory. You can see on below munin graph that I have enough memory but committed memory is to high and leads to swap... I try ro reduce allocated memory. As far as I understand this is VIRT value in top. # top says Code: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 674 mysql 20 0 2766712 122684 20340 S 0.0 1.5 3:31.25 mysqld So 2.7 gb virtual for 122mb used ! I try to tune a bit but #mysql -e "show variables; show status" says Code: | key_buffer_size | 128.000 MB | | query_cache_size | 16.000 MB | | innodb_buffer_pool_size | 128.000 MB | | innodb_additional_mem_pool_size | 0.000 MB | | innodb_log_buffer_size | 16.000 MB | +------------------------------------------+--------------------+ | BASE MEMORY | 288.000 MB | +------------------------------------------+--------------------+ | sort_buffer_size | 2.000 MB | | read_buffer_size | 0.125 MB | | read_rnd_buffer_size | 0.250 MB | | join_buffer_size | 0.250 MB | | thread_stack | 0.285 MB | | binlog_cache_size | 0.031 MB | | tmp_table_size | 16.000 MB | +------------------------------------------+--------------------+ | MEMORY PER CONNECTION | 18.941 MB | +------------------------------------------+--------------------+ | Max_used_connections | 26 | | max_connections | 151 | +------------------------------------------+--------------------+ | TOTAL (MIN) | 780.477 MB | | TOTAL (MAX) | 288.000 MB | +------------------------------------------+--------------------+ How can I have 2.7gb VIRT... ? May be something to find here Code: MariaDB [(none)]> SHOW VARIABLES LIKE 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | OFF | +--------------------+-------+ 1 row in set (0.001 sec) Do you advise to use ? Code: [mysqld] performance_schema=ON Any help, please
This committed memory leads to swap ! This is a nightmare with apache... If VIRT is unrelevant, how can I identify from where committed memory is requested?
You had that graph in #4 but did not show it at the beginning of thread? Use the various top tools to see which process is using memory. If it is mariadb, I think it has tools to see memory usage, you already seem to use those. Have you checked with free -h how memory usage looks like there? It is bad if swap is used, suitable swap use makes system faster.
unfortunately, I don't have beginning of the thread because I discover it quite late. Mariadb is using almost no memory except VIRT which is very high. On a test server, I try performance_schema=ON, but I see almost no difference on committed memory and other memory parameters... free -h Code: total used free shared buff/cache available Mem: 7.8Gi 5.1Gi 2.1Gi 44Mi 565Mi 2.4Gi Swap: 2.0Gi 325Mi 1.7Gi I reboot and wait. Here munin graphs At each backup (everyday) I have a swap ... I thought it was because of committed memory (blue line above) What do you think ?
At least at the time df was run memory situation is good. I am not surpised some big job like backup uses all RAM, maybe for cache or buffers. Have you read this: https://www.linuxatemyram.com/
Thanks a lot. On my VPS, allocated memory (around 10G) is much higher than possible memory (8G on my VPS) I would like to decrease this allocated memory but I have no idea how to do that. Even no idea how to identify to which process, memory is allocated ?
Hello Thanks to munin, I see this morning a decrease of used memory, number of thread. How can i know what happened with munin ?