unexpected swap usage for mysqld (but mariadb) process
Hi all! New here, signed up just new, to discuss an interesting mariadb behaviour we are seeing, related to mariadb unexpectedly using swap space. Look at this example: RHEL 8.10, running mariadb-server-utils.x86_64 3:10.5.22-1.module+el8.8.0+20134+a92c7654:
[user@db ~]$ free -g total used free shared buff/cache available Mem: 187 9 11 0 165 175 Swap: 3 3 0 and:
top - 08:52:46 up 39 days, 15:50, 2 users, load average: 1.70, 2.05, 1.99 Tasks: 616 total, 2 running, 614 sleeping, 0 stopped, 0 zombie %Cpu(s): 6.0 us, 0.8 sy, 0.0 ni, 88.2 id, 4.7 wa, 0.1 hi, 0.1 si, 0.0 st MiB Mem : 191529.7 total, 11761.7 free, 9764.9 used, 170003.1 buff/cache MiB Swap: 4096.0 total, 1006.1 free, 3089.9 used. 179429.5 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND *SWAP * 1952811 mysql 20 0 14.4g 5.0g 15172 S 65.7 2.7 9663:14 mysqld* 1.7g * 2934963 root 20 0 5208 2056 1408 S 8.9 0.0 10:39.29 gzip 0 2934962 root 20 0 33612 8268 6960 S 7.9 0.0 2:35.31 mysqldump 0
[user@db ~]$ cat /proc/sys/vm/swappiness 1 We could (we should..) allocate more ram to mariadb, but the point is:
while: there is plenty of RAM available, and there always has been, since boot. And yet, mariadb is using all swap. I guess this has no performance impact, probably the swapped pages are not actually used, but zabbix does not like 100% swap usage. And frankly: neither do I. Why? Can anyone here explain? Swappiness set to 1...is mariadb ignoring swappiness..? Is there something else we can configure..? I read some older posts on ram/swap, and here is some data, requested in that older thread:
[user@db ~]$ sudo ps -o pid,vsz,comm `pgrep mysqld` PID VSZ COMMAND 1952811 15117612 mysqld and
[user@db ~]$ for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{
print ""}' $file; done | sort -k 2 -n -r | grep mysql mysqld 1982672 kB
We are seeing similar behaviour on another machines, running more recent mariadb version: 10.5.22-MariaDB MariaDB Server:
[user@db_another ~]$ free -g total used free shared buff/cache available Mem: 62 40 0 1 21 20 Swap: 4 3 1 Again: enough RAM available, and swap is used 3/4, mostly by MariaDB.
Hope the provided information is enough. Let me know if there is anything else I can provide. Looking forward for any insight or help on the matter. :-) Thanks! MJ
How many CPU sockets? Is NUMA exposed? This could be MySQL/MariaDB "swapping insanity". On Mon, Sep 23, 2024 at 2:44 PM cyusedfzfb via discuss <discuss@lists.mariadb.org> wrote:
Hi all!
New here, signed up just new, to discuss an interesting mariadb behaviour we are seeing, related to mariadb unexpectedly using swap space.
Look at this example:
RHEL 8.10, running mariadb-server-utils.x86_64 3:10.5.22-1.module+el8.8.0+20134+a92c7654:
[user@db ~]$ free -g total used free shared buff/cache available Mem: 187 9 11 0 165 175 Swap: 3 3 0
and:
top - 08:52:46 up 39 days, 15:50, 2 users, load average: 1.70, 2.05, 1.99 Tasks: 616 total, 2 running, 614 sleeping, 0 stopped, 0 zombie %Cpu(s): 6.0 us, 0.8 sy, 0.0 ni, 88.2 id, 4.7 wa, 0.1 hi, 0.1 si, 0.0 st MiB Mem : 191529.7 total, 11761.7 free, 9764.9 used, 170003.1 buff/cache MiB Swap: 4096.0 total, 1006.1 free, 3089.9 used. 179429.5 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND SWAP 1952811 mysql 20 0 14.4g 5.0g 15172 S 65.7 2.7 9663:14 mysqld 1.7g 2934963 root 20 0 5208 2056 1408 S 8.9 0.0 10:39.29 gzip 0 2934962 root 20 0 33612 8268 6960 S 7.9 0.0 2:35.31 mysqldump 0
while:
[user@db ~]$ cat /proc/sys/vm/swappiness 1
We could (we should..) allocate more ram to mariadb, but the point is: there is plenty of RAM available, and there always has been, since boot. And yet, mariadb is using all swap.
I guess this has no performance impact, probably the swapped pages are not actually used, but zabbix does not like 100% swap usage. And frankly: neither do I.
Why? Can anyone here explain? Swappiness set to 1...is mariadb ignoring swappiness..? Is there something else we can configure..?
I read some older posts on ram/swap, and here is some data, requested in that older thread:
[user@db ~]$ sudo ps -o pid,vsz,comm `pgrep mysqld` PID VSZ COMMAND 1952811 15117612 mysqld
and
[user@db ~]$ for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{
print ""}' $file; done | sort -k 2 -n -r | grep mysql mysqld 1982672 kB
We are seeing similar behaviour on another machines, running more recent mariadb version: 10.5.22-MariaDB MariaDB Server:
[user@db_another ~]$ free -g total used free shared buff/cache available Mem: 62 40 0 1 21 20 Swap: 4 3 1
Again: enough RAM available, and swap is used 3/4, mostly by MariaDB.
Hope the provided information is enough. Let me know if there is anything else I can provide. Looking forward for any insight or help on the matter. :-)
Thanks! MJ
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
Wow quick reply. :-) I provided two sample machines in my original post. One of the two is a VM, the other one is physical. The VM "db_another":
[user@db_another ~]$ sudo numastat node0 numa_hit 8701301862 numa_miss 0 numa_foreign 0 interleave_hit 48993 local_node 8701301862 other_node 0 and the physical "db":
[user@db ~]$ sudo numastat node0 node1 numa_hit 8800569447 10675116172 numa_miss 2549915869 2282676844 numa_foreign 2282676844 2549915869 interleave_hit 39195 38959 local_node 8800434335 10675002137 other_node 2550050981 2282790879
I will read up on "swapping insanity". Thanks for the reply! Appreciated! MJ On 9/23/24 13:45, Gordan Bobic wrote:
How many CPU sockets? Is NUMA exposed? This could be MySQL/MariaDB "swapping insanity".
On Mon, Sep 23, 2024 at 2:44 PM cyusedfzfb via discuss <discuss@lists.mariadb.org> wrote:
Hi all!
New here, signed up just new, to discuss an interesting mariadb behaviour we are seeing, related to mariadb unexpectedly using swap space.
Look at this example:
RHEL 8.10, running mariadb-server-utils.x86_64 3:10.5.22-1.module+el8.8.0+20134+a92c7654:
[user@db ~]$ free -g total used free shared buff/cache available Mem: 187 9 11 0 165 175 Swap: 3 3 0
and:
top - 08:52:46 up 39 days, 15:50, 2 users, load average: 1.70, 2.05, 1.99 Tasks: 616 total, 2 running, 614 sleeping, 0 stopped, 0 zombie %Cpu(s): 6.0 us, 0.8 sy, 0.0 ni, 88.2 id, 4.7 wa, 0.1 hi, 0.1 si, 0.0 st MiB Mem : 191529.7 total, 11761.7 free, 9764.9 used, 170003.1 buff/cache MiB Swap: 4096.0 total, 1006.1 free, 3089.9 used. 179429.5 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND SWAP 1952811 mysql 20 0 14.4g 5.0g 15172 S 65.7 2.7 9663:14 mysqld 1.7g 2934963 root 20 0 5208 2056 1408 S 8.9 0.0 10:39.29 gzip 0 2934962 root 20 0 33612 8268 6960 S 7.9 0.0 2:35.31 mysqldump 0
while:
[user@db ~]$ cat /proc/sys/vm/swappiness 1
We could (we should..) allocate more ram to mariadb, but the point is: there is plenty of RAM available, and there always has been, since boot. And yet, mariadb is using all swap.
I guess this has no performance impact, probably the swapped pages are not actually used, but zabbix does not like 100% swap usage. And frankly: neither do I.
Why? Can anyone here explain? Swappiness set to 1...is mariadb ignoring swappiness..? Is there something else we can configure..?
I read some older posts on ram/swap, and here is some data, requested in that older thread:
[user@db ~]$ sudo ps -o pid,vsz,comm `pgrep mysqld` PID VSZ COMMAND 1952811 15117612 mysqld
and
[user@db ~]$ for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{
print ""}' $file; done | sort -k 2 -n -r | grep mysql mysqld 1982672 kB
We are seeing similar behaviour on another machines, running more recent mariadb version: 10.5.22-MariaDB MariaDB Server:
[user@db_another ~]$ free -g total used free shared buff/cache available Mem: 62 40 0 1 21 20 Swap: 4 3 1
Again: enough RAM available, and swap is used 3/4, mostly by MariaDB.
Hope the provided information is enough. Let me know if there is anything else I can provide. Looking forward for any insight or help on the matter. :-)
Thanks! MJ
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
On 9/23/24 13:44, cyusedfzfb via discuss wrote:
Hi all!
New here, signed up just new, to discuss an interesting mariadb behaviour we are seeing, related to mariadb unexpectedly using swap space.
Look at this example:
RHEL 8.10, running mariadb-server-utils.x86_64 3:10.5.22-1.module+el8.8.0+20134+a92c7654:
[user@db ~]$ free -g total used free shared buff/cache available Mem: 187 9 11 0 165 175 Swap: 3 3 0
you still have almost 95% of your total RAM listed as available, so you are not in a "memory is tight, need to swap to make room" situation. What you are rather seeing is that the kernel chose to move allocated, but basically unused (based on last access time) memory pages to swap space storage to make better use of the otherwise unused RAM, usually by using it as part of the file system cache. This behavior is controlled by the kernels vm.swappiness setting, the higher the value the more aggressively will the kernel try to move apparently unused or seldom used memory pages to swap space. There is nothing wrong about this, and even when setting the parameter to the lowest possible value this behavior will not stop completely. There is usually next to no negative performance impact from this either, actually in general quite the contrary due to being able to have a larger file system cache. A classic critical swap situation on the other hand would show only very little memory listed under the "available" column, and would also show high swap-in and swap-out rates in vmstat output. So basically: nothing to see here, move along -- Hartmut Holzgraefe, Principal Support Engineer (EMEA) MariaDB Corporation | http://www.mariadb.com/
Hi
This behavior is controlled by the kernels vm.swappiness setting, the higher the value the more aggressively will the kernel try to move apparently unused or seldom used memory pages to swap space.
As MJ mentioned using RHEL 8 that is not necessarily true. Please have a look at https://access.redhat.com/solutions/6785021 The introduction of cgroups especially v2 has changed that behaviour and there are additional kernel settings available in RHEL 8/9. By the way if you have goferd running on your RHEL systems that might also be a reason for swap being used a bit too much. So if "python" as using a lot of swap (as not all of the swap space on MJ's systems is used by mysql) just restart goferd and enjoy some freshly liberated swap space. Best, Cyril Stoll Senior System Engineer Universität Zürich Cyril Stoll Zentrale Informatik Linux & Identity Services Pfingstweidstrasse 60B 8005 Zürich +41 44 634 29 46 www.zi.uzh.ch cyril.stoll@uzh.ch
Hi, Just to follow-up to everybody: Cyril Stoll was spot-on! Thank you very much! I set vm.force_cgroup_v2_swappiness to 1, and have seen NO MORE unexpected swapping. Really much appreciated..! MJ On 9/23/24 14:42, Cyril Stoll via discuss wrote:
Hi
This behavior is controlled by the kernels vm.swappiness setting, the higher the value the more aggressively will the kernel try to move apparently unused or seldom used memory pages to swap space. As MJ mentioned using RHEL 8 that is not necessarily true. Please have a look athttps://access.redhat.com/solutions/6785021 The introduction of cgroups especially v2 has changed that behaviour and there are additional kernel settings available in RHEL 8/9.
By the way if you have goferd running on your RHEL systems that might also be a reason for swap being used a bit too much. So if "python" as using a lot of swap (as not all of the swap space on MJ's systems is used by mysql) just restart goferd and enjoy some freshly liberated swap space.
Best,
Cyril Stoll Senior System Engineer
Universität Zürich Cyril Stoll Zentrale Informatik Linux & Identity Services Pfingstweidstrasse 60B 8005 Zürich +41 44 634 29 46 www.zi.uzh.ch cyril.stoll@uzh.ch
_______________________________________________ discuss mailing list --discuss@lists.mariadb.org To unsubscribe send an email todiscuss-leave@lists.mariadb.org
On Mon, Sep 23, 2024 at 2:44 PM cyusedfzfb via discuss <discuss@lists.mariadb.org> wrote:
Hi all!
New here, signed up just new, to discuss an interesting mariadb behaviour we are seeing, related to mariadb unexpectedly using swap space.
Look at this example:
RHEL 8.10, running mariadb-server-utils.x86_64 3:10.5.22-1.module+el8.8.0+20134+a92c7654:
Side note: MariaDB Server 10.5.22 was released more than a year ago, in August 2023. Currently, the latest release in that series (10.5.26) is about a year newer.
Again: enough RAM available, and swap is used 3/4, mostly by MariaDB.
If you are using the InnoDB storage engine, the most interesting parameter would be innodb_buffer_pool_size. There are also some other buffer size parameters that may be relevant here. Unfortunately, https://jira.mariadb.org/browse/MDEV-19895 has not been implemented yet. What is sane for one thinkable configuration would be completely insane in another. With best regards, Marko -- Marko Mäkelä, Lead Developer InnoDB MariaDB plc
Hi all, Marko, Gordan, Hartmut, Cyrill, Just to say: Thanks to all of you for the valuable insights and feedback. Appreciated! Having read everyting we understand there is nothing to worry about, and we will need to look into cgroups. Will do. We still feel that with "vm.swappiness=1", this kind of swapping would be nicer NOT to occur. Thanks! MJ On 9/23/24 15:26, Marko Mäkelä wrote:
On Mon, Sep 23, 2024 at 2:44 PM cyusedfzfb via discuss <discuss@lists.mariadb.org> wrote:
Hi all!
New here, signed up just new, to discuss an interesting mariadb behaviour we are seeing, related to mariadb unexpectedly using swap space.
Look at this example:
RHEL 8.10, running mariadb-server-utils.x86_64 3:10.5.22-1.module+el8.8.0+20134+a92c7654: Side note: MariaDB Server 10.5.22 was released more than a year ago, in August 2023. Currently, the latest release in that series (10.5.26) is about a year newer.
Again: enough RAM available, and swap is used 3/4, mostly by MariaDB. If you are using the InnoDB storage engine, the most interesting parameter would be innodb_buffer_pool_size. There are also some other buffer size parameters that may be relevant here. Unfortunately, https://jira.mariadb.org/browse/MDEV-19895 has not been implemented yet. What is sane for one thinkable configuration would be completely insane in another.
With best regards,
Marko
participants (5)
-
Cyril Stoll -
cyusedfzfb -
Gordan Bobic -
Hartmut Holzgraefe -
Marko Mäkelä