Solved FreeBSD running out of memory - MySQL

Sparkee

Sparkee

My 12.2-RELEASE-p3 server is running out of memory then crashing, most of the memory is used by mysql57-server-5.7.32
The server is using ZFS, any ideas what should I check/change?
Code: 
% sysctl hw.physmem
hw.physmem: 137297674240
% sysctl hw.ncpu
hw.ncpu: 72
/var/log/messages says things like
Code: 
Jan  8 07:20:56 db-server kernel: swap_pager_getswapspace(31): failed
Jan  8 07:20:56 db-server kernel: swap_pager_getswapspace(24): failed
Jan  8 07:20:56 db-server kernel: swap_pager_getswapspace(21): failed
Jan  8 07:20:56 db-server kernel: swap_pager_getswapspace(19): failed
Jan  8 07:20:56 db-server kernel: swap_pager_getswapspace(3): failed
Jan  8 07:20:56 db-server kernel: swap_pager_getswapspace(20): failed
 
If you are not winning with ZFS changes, you could consider tcmalloc or upgrading to FreeBSD 13: https://forums.freebsd.org/threads/...exhaust-ram-and-swap.72733/page-2#post-494213

Also worth finding out what is happening at 7.20 on your server - is it "just" normal MySQL usage or some particular task (e.g. mysqldump or an import) that causes issues.

But come back to all this after you've done the ZFS changes - that often seems to be the solution.
 
Thank you guys, I'm applying these now and I'm going to update the forum once I have some good feedback.
 
Ok, so far
Code: 
% sudo zfs-stats -A -E

------------------------------------------------------------------------
ZFS Subsystem Report                Tue Jan 11 17:44:42 2022
------------------------------------------------------------------------

ARC Summary: (HEALTHY)
    Memory Throttle Count:            0

ARC Misc:
    Deleted:                0
    Mutex Misses:                0
    Evict Skips:                0

ARC Size:                25.00%    15.98    GiB
    Target Size: (Adaptive)        100.00%    63.93    GiB
    Min Size (Hard Limit):        16.67%    10.66    GiB
    Max Size (High Water):        6:1    63.93    GiB
    Decompressed Data Size:            44.68    GiB
    Compression Factor:            2.80

ARC Size Breakdown:
    Recently Used Cache Size:    50.00%    31.97    GiB
    Frequently Used Cache Size:    50.00%    31.97    GiB

ARC Hash Breakdown:
    Elements Max:                371.98    k
    Elements Current:        99.89%    371.56    k
    Collisions:                610.69    k
    Chain Max:                3
    Chains:                    4.07    k

------------------------------------------------------------------------

ARC Efficiency:                    139.89    m
    Cache Hit Ratio:        99.49%    139.17    m
    Cache Miss Ratio:        0.51%    717.09    k
    Actual Hit Ratio:        99.45%    139.12    m

    Data Demand Efficiency:        68.06%    980.68    k
    Data Prefetch Efficiency:    0.00%    356.73    k

    CACHE HITS BY CACHE LIST:
      Anonymously Used:        0.04%    50.96    k
      Most Recently Used:        2.78%    3.87    m
      Most Frequently Used:        97.18%    135.25    m
      Most Recently Used Ghost:    0.00%    0
      Most Frequently Used Ghost:    0.00%    0

    CACHE HITS BY DATA TYPE:
      Demand Data:            0.48%    667.43    k
      Prefetch Data:        0.00%    9
      Demand Metadata:        99.46%    138.42    m
      Prefetch Metadata:        0.06%    78.46    k

    CACHE MISSES BY DATA TYPE:
      Demand Data:            43.68%    313.25    k
      Prefetch Data:        49.75%    356.72    k
      Demand Metadata:        4.82%    34.57    k
      Prefetch Metadata:        1.75%    12.55    k

------------------------------------------------------------------------

Code: 
  PID USER      PRI  NI  VIRT   RES S CPU% MEM%   TIME+  Command
47604 mysql      20   0 34.8G 34.0G S 104. 26.0  2h30:40 /usr/local/libexec/mysqld
 
I forgot to say, I added these to /boot/loader.conf, then rebooted
Code: 
vfs.zfs.arc_max="68648837120"
vfs.zfs.arc_meta_limit="45765891412"
vfs.zfs.arc_min="11441472852"
 
grahamperrin oh, vfs.zfs.arc_free_target interesting, thank you!
You can set: vfs.zfs.arc_free_target to cause the ARC to proactively free space memory if your free ram drops below that threshold. Note: it is measured in PAGES not bytes, so 1 = 4kb, so 256,000 = 1GB
Click to expand...
 
Unfortunately it has happened again and the server has crashed with the same message.
I'm going try lowering ARC reservations and playing with vfs.zfs.arc_free_target.
What I don't understand, instead of crashing why isn't the OS killing processes using up the memory?
 

Attachments

  • memory usage.png
    memory usage.png
    352.1 KB · Views: 192
Sparkee said:
Unfortunately it has happened again and the server has crashed with the same message.
I'm going try lowering ARC reservations and playing with vfs.zfs.arc_free_target.
What I don't understand, instead of crashing why isn't the OS killing processes using up the memory?
Click to expand...
Crashing is probably safer because killing processes randomly could lead to data loss. The OS probably is programmed to hard crash and this would prevent writing to the disks and corrupting something.
 
I found https://forums.freebsd.org/threads/...ts-of-small-lines-exhaust-ram-and-swap.72733/, it looks someone has very similar issues. For now we installed https://www.freshports.org/devel/google-perftools/, updated my.cnf with:
[mysqld_safe]
malloc-lib=/opt/lib/libtcmalloc_minimal.so
Then re-started the ~750GB SQL import

I was also trying to add limits in /etc/login.conf, both for default:\ as for mysql:\, specifically for memoryuse, then ran cap_mkdb /etc/login.conf but this didn't seem to work. Even restarted the server and still got different values when I ran limits

Code: 
default:\
.
.
	:memoryuse=32G:\
.

mysql:\
	:memoryuse=32G:\
	:vmemoryuse=82G:


root@mysql:~ # echo limits | su -m mysql
Resource limits (current):
cputime infinity secs
filesize infinity kB
datasize 33554432 kB
stacksize 524288 kB
coredumpsize infinity kB
memoryuse 67108864 kB
memorylocked infinity kB
maxprocesses 89999
openfiles 3770991
sbsize infinity bytes
vmemoryuse infinity kB
pseudo-terminals infinity
swapuse infinity kB
kqueues infinity
umtxp infinity

mysql server runs as mysql user
 
Thanks covacat!
Code: 
% sudo ps auxw -o class | more
USER       PID   %CPU %MEM      VSZ      RSS TT  STAT STARTED        TIME COMMAND          CLASS
root        11 7188.3  0.0        0     1152  -  RNL  20:54   83434:30.18 [idle]           -
mysql    91038   12.2 26.5 35699412 35469920  -  S    21:27     518:42.30 /usr/local/libex default
above ps is telling me that mysql is in default class, I have defined memoryuse for that one too, I'm not sure why isn't applied
 
Adding mysql_login_class=mysql to rc.conf then restarting mysql server didn't help, however it has helped using chpass mysql (I'm doing this on a test server)
Code: 
# ps aux -o class | grep mysql
mysql      45289   0.0 33.9 2056020 340732  -  I    18:01   0:00.53 /usr/local/libex mysql

I have the following values in /etc/login.conf
Code: 
mysql:\
        :memoryuse=500M:\
        :memorylocked=700M:

It seems these are not applied
Code: 
# echo limits | su -m mysql
Resource limits (current):
  cputime              infinity secs
  filesize             infinity kB
  datasize             33554432 kB
  stacksize              524288 kB
  coredumpsize         infinity kB
  memoryuse            infinity kB
  memorylocked         infinity kB
  maxprocesses             5734
  openfiles               28287
  sbsize               infinity bytes
  vmemoryuse           infinity kB
  pseudo-terminals     infinity
  swapuse              infinity kB
  kqueues              infinity
  umtxp                infinity

I did run cap_mkdb /etc/login.conf and also rebooted the server
 
Ok, I think I used the wrong command do determine a user's limit, this seems to be the way:
Code: 
# limits -U mysql
Resource limits for class mysql:
  cputime              infinity secs
  filesize             infinity kB
  datasize             infinity kB
  stacksize            infinity kB
  coredumpsize         infinity kB
  memoryuse              512000 kB
  memorylocked           716800 kB
  maxprocesses         infinity
  openfiles            infinity
  sbsize               infinity bytes
  vmemoryuse           infinity kB
  pseudo-terminals     infinity
  swapuse              infinity kB
  kqueues              infinity
  umtxp                infinity

And this is an easy way to check class:
Code: 
# pw showuser mysql
mysql:*:88:88:mysql:0:0:MySQL Daemon:/var/db/mysql:/usr/sbin/nologin
 
Sparkee said:
I found https://forums.freebsd.org/threads/...ts-of-small-lines-exhaust-ram-and-swap.72733/, it looks someone has very similar issues.
Click to expand...
Cough I did refer you to that exact link.

I also found running a "grab gigs of RAM" script before an export seemed to force FreeBSD to give MySQL more space (real RAM) to stop it swapping.

FreeBSD 13 has a newer version of jemalloc and that seems to have fixed the problem for me. I never took the tcmalloc change live on production.

Definitely a pathological case where MySQL strains the OS's memory allocator - I couldn't quite track it down - millions or tens of millions of "small" rows being imported, also on exporting.

I don't yet use ZFS so that wasn't a factor in my case.

Hope it's all working for you now.
 
I still don't see a my.cnf file, because that's more than likely your problem.
Also, what's the memory configuration? Swap? Swap partitions or files? Etc. etc. etc.
 
richardtoohey2 you did sent the link, thank you!
We'd like to upgrade to FreeBSD 13 however we can't do it for a few more months.
The mysql import generally runs for ~10 days, so far it's running for 4 days.

mark_j nobody asked for my.cnf so far, let me add it now. The server has 128GB of RAM (see original post too), 8GB swap partition.
Let me know what do you mean by "etc" and I'm happy to add it, thank you!

Code: 
[mysqld]
tmpdir          = /var/tmp
skip-external-locking
plugin-load=auth_pam=auth_pam.so
key_buffer_size         = 64M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 8
default-storage-engine  = InnoDB
query_cache_limit       = 1M
query_cache_size        = 128M
table_open_cache        = 768
innodb_buffer_pool_size        = 30G
innodb_flush_method            = O_DIRECT
innodb_log_file_size           = 500M
innodb_log_buffer_size         = 128M
innodb_file_per_table          = true
slow_query_log          = on
innodb_buffer_pool_instances    = 2
max_connect_errors      = 100
max_connections         = 2000
server-id               = 8916
log_warnings            = 2
log-error               = /var/db/mysql/mysqld.log
log_bin                 = /var/db/mysql/mysql-bin.log
relay_log               = hostname-relay-bin
expire_logs_days        = 1
max_binlog_size         = 500M
binlog-format           = mixed
binlog_cache_size       = 128M
log-slave-updates       = 0
replicate-wild-ignore-table = mysql%.%
relay_log_purge         = 1
max_relay_log_size      = 100M
relay_log_space_limit   = 20G
read_only                               = ON
[mysqld_safe]
malloc-lib=/usr/local/lib/libtcmalloc_minimal.so
 
mark_j said:
I still don't see a my.cnf file, because that's more than likely your problem.
Also, what's the memory configuration? Swap? Swap partitions or files? Etc. etc. etc.
Click to expand...
OP's config runs on ZFS. ZFS doesn't use swap, but IIRC, in a default ZFS setup, /tmp (zroot/tmp) is a separate dataset, as is /var/tmp.
 
astyle said:
… IIRC, in a default ZFS setup, /tmp (zroot/tmp) is a separate dataset, as is /var/tmp.
Click to expand...

Correct, as far as I can tell.

I chose to change the canmount property of my august/tmp, using tmpfs instead:

Code: 
% zfs get canmount august/tmp august/var/tmp
NAME            PROPERTY  VALUE     SOURCE
august/tmp      canmount  off       received
august/var/tmp  canmount  on        default
% grep tmpfs /etc/fstab
# tmpfs         /compat/linux/dev/shm   tmpfs      rw,mode=1777               0     0
tmpfs           /tmp                    tmpfs      rw,mode=01777              0     0
tmpfs           /compat/ubuntu/dev/shm  tmpfs      rw,late,size=1g,mode=1777  0     0
%
 
You must log in or register to reply here.
Back
Top