Solved FreeBSD 12.x and MySQL 5.7 and importing file with lots of small lines exhaust RAM and swap

Thought I'd try out the new 12.1 beta and RC releases but have got side-tracked down a MySQL 5.7 rabbit hole.

When I try and import a 17Gb database dump file from mysqldump my machines run out of RAM then they swap, then they run out of that, then the OOM killer kicks in with sad results (usually have to physically reboot the machine).

From what I've read there are a LOT of better ways to do what I'm doing and so I'll look at those for this specific table giving me a problem.

My concern is that what I'm seeing here might bite me somewhere else - so if there's a class of issue I'd like to know how to address the class of issue not just this *specific* issue. So I know I can use InnoDB, change how I layout the table, change how I export and import the data data etc. to fix this specific issue - but that won't help me if there's some memory issue with 12.x and 5.7.

I've reproduced this on a number of machines - plumping to demonstrate it on an Intel NUC with 8 Gb of RAM because it shows the symptoms sooner than a Dell T330 with 48Gb of RAM.

There's lots on the internet about it - MySQL 5.7 and jemalloc - but the Linux solution seems to be "use a different memory allocator".

For example: https://bugs.mysql.com/bug.php?id=83047

So on FreeBSD 11.3 and MySQL 5.6 and MySQL 5.7 - no issues. FreeBSD 11.3 uses jemalloc 4.2.1-0-g3de035335255d553bdb344c32ffdb603816195d8

On FreeBSD 12.0 (release, patched) - MySQL 5.6 works fine (as long as I set max_allowed_packet to 64Mb).

MySQL 5.7 shows the problem behaviour. FreeBSD 12.0 uses jemalloc 5.1.0-0-g61efbda7098de6fe64c362d309824864308c36d4

I /think/ I'm seeing this: https://github.com/jemalloc/jemalloc/issues/1152

I'm used to building stuff from ports, so I've built from mysql57-server, default options (except turning off PERF SCHEMA in case that was causing the RAM issues). The 5.7 port installs a "sensible" default my.cnf and I haven't changed that. If I build the same port on FreeBSD 11.3 - it works.

I've boiled it down to this set of steps to reproduce:

A simple table (could probably even be simpler. I think it's more to do with the amount of data coming in):
Code:
CREATE TABLE crash (
    crash_id    INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    crash_text    TEXT,
    crash_15    VARCHAR(15)
) ENGINE=MyISAM;

A script to build a large import file of the sort mysqldump would produce:
Code:
<?php
# php build_crasher.php > bc.sql
# Try 10 million rows
define('ROWS',10000000);
# 20K columns per INSERT (needs to fit within 64M max_allowed_packet)
define('COLS',20000);
$j=0;
for ($i=0;$i<ROWS;$i++) {
    if ($j==0) {
        echo "INSERT INTO crash (crash_id,crash_text,crash_15) VALUES";
    }

    echo "($i,'This is $i','VC $i')";

    $j++;
    if ($j<COLS) {
        echo ",";
    } else {
        echo ";\n";
        $j=0;
    }
}
?>

Key thing is that you wants lots and lots of (values),(values),(values) in each INSERT statement.

Open one console window with top running in it.

Open another console window with the MySQL client. (I use \W to show warnings, don't think the issue here but I'll go and try without it!) Type the following commands:
Code:
\W
CREATE DATABASE crash;
USE crash;
use-the-CREATE-TABLE-above
SOURCE bc.sql

And watch in top as the 20K-per-INSERT lines are pulled in. The below screenshot is a few 10s of seconds into the run:
Code:
last pid: 70399;  load averages:  0.90,  0.74,  0.57                                                                                                            up 0+03:45:56  15:19:21
28 processes:  2 running, 26 sleeping
CPU:  7.1% user,  0.0% nice,  4.1% system,  0.2% interrupt, 88.7% idle
Mem: 4893M Active, 3012K Inact, 1608M Laundry, 1091M Wired, 774M Buf, 162M Free
Swap: 3656M Total, 601M Used, 3055M Free, 16% Inuse, 3544K In, 138M Out

  PID USERNAME    THR PRI NICE   SIZE    RES STATE    C   TIME    WCPU COMMAND
70394 mysql        39  20    0  9000M  6516M select   5   3:04  83.44% mysqld
70399 webarts       1  23    0    23M  7556K CPU3     3   0:06   5.76% mysql

mysqld at this stage has SIZE ~9G, RES ~6G (this is on an 8GB RAM machine) so swapping is up to 16%

Wait a little bit longer:
Code:
last pid: 70403;  load averages:  0.61,  0.69,  0.56                                                                                                            up 0+03:46:35  15:20:00
27 processes:  1 running, 26 sleeping
CPU:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt, 99.9% idle
Mem: 2154M Active, 94M Inact, 4085M Laundry, 1083M Wired, 768M Buf, 337M Free
Swap: 3656M Total, 1909M Used, 1748M Free, 52% Inuse, 196K In

  PID USERNAME    THR PRI NICE   SIZE    RES STATE    C   TIME    WCPU COMMAND
70394 mysql        39  20    0    10G  6345M select   5   3:16   0.02% mysqld

mysqld now ~10G/~6GB and swap at 52%. I usually kill the mysql process at this point to stop the OOM killing my services.

When I let it run to death I will have this sort of message:
Code:
Sep 29 15:07:21 remote kernel: swap_pager_getswapspace(20): failed
Sep 29 15:07:21 remote kernel: swap_pager_getswapspace(18): failed
Sep 29 15:07:21 remote kernel: swap_pager_getswapspace(32): failed
Sep 29 15:07:21 remote syslogd: last message repeated 1 times
Sep 29 15:07:26 remote kernel: pid 5913 (mysqld), uid 88, was killed: out of swap space
Sep 29 15:07:27 remote kernel: pid 888 (ntpd), uid 123, was killed: out of swap space
Sep 29 15:07:27 remote kernel: swap_pager_getswapspace(32): failed

I tried some jemalloc tuning but not really sure what I'm looking for so not really winning:


I've managed to get it to finish once on the machine with 48GB but MySQL does not free the memory (or the OS doesn't reclaim it).

So if I start another memory-hungry process (e.g. I asked vim to open the MySQL binary log files, and as I went through those - RAM exhausted, swap started being used, machine very unhappy.)

If I re-start mysqld then memory usage goes back as expected (so this in on the NUC):

Code:
# /usr/local/etc/rc.d/mysql-server restart
Stopping mysql.
Waiting for PIDS: 70394.
Starting mysql.

And look at top:
Code:
last pid: 71771;  load averages:  0.16,  0.14,  0.16                                                                                                                                 up 0+04:17:09  15:50:34
27 processes:  1 running, 26 sleeping
CPU:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt,  100% idle
Mem: 296M Active, 643M Inact, 132K Laundry, 1026M Wired, 761M Buf, 5848M Free
Swap: 3656M Total, 18M Used, 3638M Free

  PID USERNAME    THR PRI NICE   SIZE    RES STATE    C   TIME    WCPU COMMAND
71707 mysql        38  52    0  1730M   321M select   4   0:03   0.02% mysqld

At this point wondering if anyone seeing the same or can reproduce it or any advice on tuning/debugging? If you want to try reproducing it, please do not do so on an important machine!

Like I said - I can work around the actual issue by changing to InnoDB, changing the table layout, tweaking how I export & import the data, etc., etc. but I'm more interested in WHY this combination of FreeBSD 12.x and MySQL can cause this behaviour - in case the same issue causes me grief elsewhere or with another program.

I'll try with the MySQL 5.7 package as well - see if that makes any difference.

Thanks,
Richard.
 
More info:
Code:
> uname -a
FreeBSD remote.xyzxyz.co.nz 12.0-RELEASE-p10 FreeBSD 12.0-RELEASE-p10 GENERIC  amd64
> mount
/dev/ada0p2 on / (ufs, local, journaled soft-updates)
devfs on /dev (devfs, local, multilabel)
> pkg info | grep mysql
mysql57-client-5.7.27          Multithreaded SQL database (client)
mysql57-server-5.7.27          Multithreaded SQL database (server)

So not ZFS/ARC (that seems to be a common discussion point when talking about RAM usage).

Changing to InnoDB makes it work fine.

So changing:
Code:
) ENGINE=MyISAM;

to
Code:
) ENGINE=InnoDB;

Seems to address the underlying issue (but does not address the concern I might encounter the same issue on something else in 12.x).

Something about MyISAM, MySQL 5.7, importing 10Ks-of-rows-at-once from a 10s-of-GBs mysqldump, and FreeBSD 12.x (but it also seems to have affected people on Linux, so maybe jemalloc).
 
Thanks to a cluestick on another thread, I can mark this one as solved.

I had read about using tcmalloc as an option, but everything I read was in terms of Linux so I didn't twig I could do this on FreeBSD if required.

tcmalloc seems to be part of this port: google-perftools

So I installed that. MySQL 5.7 seems to insist on the malloc-lib files being in certain locations, so I had to copy /usr/local/lib/libtcmalloc_minimal.so to /usr/lib/

I added this to my.cnf:
Code:
[mysqld_safe]
malloc-lib=/usr/lib/libtcmalloc_minimal.so

Restart MySQL and my troublesome file will import.

Probably still best I move things to InnoDB and for my real case I can improve a table's layout - but at least I've got the above option as well.

I wasn't sure how to know MySQL was using tcmalloc, but this worked:
Code:
# procstat -v files 94426
procstat: elf_begin: Invalid argument
procstat: procstat_open()
  PID              START                END PRT  RES PRES REF SHD FLAG  TP PATH
94426           0x200000           0xd5c000 r-- 2908 6736   3   1 CNS-- vn /usr/local/libexec/mysqld
...
94426        0x801ae0000        0x801af4000 r--   20   45   3   1 CN--- vn /usr/lib/libtcmalloc_minimal.so
94426        0x801af4000        0x801b0a000 r-x   22   45   3   1 CN--- vn /usr/lib/libtcmalloc_minimal.so
94426        0x801b0a000        0x801b0b000 rw-    1    0   2   0 C---- vn /usr/lib/libtcmalloc_minimal.so
94426        0x801b0b000        0x801b0c000 r--    1    0   2   0 C---- vn /usr/lib/libtcmalloc_minimal.so
...
If anything wrong or poor practice in the above, please let me know. I'm not going to use this in production unless I have to - will move to InnoDB and re-arrange the troublesome table that I have and that will probably fix things well enough for my use case.
 
I notice this problem is my servers and in one of them the problem was huge (MySQL was taking all RAM+SWAP). I switch to tcmalloc and the memory usage is stable.
 
MySQL 5.7 seems to insist on the malloc-lib files being in certain locations, so I had to copy /usr/local/lib/libtcmalloc_minimal.so to /usr/lib/

In source code the file ./scripts/mysqld_safe.sh has this check:

Code:
set_malloc_lib() {
  # This list is kept intentionally simple.
  malloc_dirs="/usr/lib /usr/lib64 /usr/lib/i386-linux-gnu /usr/lib/x86_64-linux-gnu"
  malloc_lib="$1"

  # Allow --malloc-lib='' to override other settings
  [ -z  "$malloc_lib" ] && return

  case "$malloc_lib" in
    /*)
      if [ ! -r "$malloc_lib" ]; then
        log_error "--malloc-lib can not be read and will not be used"
        exit 1
      fi

      # Restrict to a the list in $malloc_dirs above
      case "`dirname "$malloc_lib"`" in
        /usr/lib) ;;
        /usr/lib64) ;;
        /usr/lib/i386-linux-gnu) ;;
        /usr/lib/x86_64-linux-gnu) ;;
        *)
          log_error "--malloc-lib must be located in one of the directories: $malloc_dirs"
          exit 1
          ;;
      esac
      ;;
    *)
      log_error "--malloc-lib must be an absolute path ignoring value '$malloc_lib'"
      exit 1
      ;;
  esac

  add_mysqld_ld_preload "$malloc_lib"
}

I will submit a bug report.
 
Hi guys I have the same problem.

Mysql 5.6 ran on server fine for years.

Updated to 5.7 and within a few days it just grows and grows the ram usage until no memory left to allocate. The usage is way above configured pool and query cache.

I got very hopeful when I seen the fix regarding
/usr/lib/libtcmalloc_minimal.so



But that file doesnt exist on my system. Was it removed after 12.0?

There is also several reports on the mysql bugs system reported by linux users, every single one the mysql dev's stated they couldnt repeat the behaviour.

--edit--

I misread the post, installed the port now, symlinked the library, and will let everyone here know if it fixes my problem later.

Also for those who think zfs ARC is inpolite on memory usage, my mysqld grew to 39 gig memory usage (2 gig pool configured), it pushed ARC down to a gig, so ARC politely shrunk, and grabbed 18 gig from swap as well.
 
Hope it works for you. I'm having issues with MySQL 5.6 to do with memory issues, and I'm trying to understand how VM works. But it's a big topic!

It's been around and discussed for a long time e.g. (not directly related to FreeBSD and not 12.x which has changed from 11.x):

https://redmine.ixsystems.com/issues/35515

(Those links also to do with ZFS etc which I'm not using.)

The behaviour I see is lots of memory ends up Inactive (which is meant to be a good thing - it's paged-into-RAM stuff that FreeBSD thinks you might want again soon, so let's keep it in RAM instead of re-loading from disk). There's a bit of Free memory, and there's a threshold (vm.v_free_min - the value will depend on your RAM) below which that Free memory should not fall. If there is any memory pressure from MySQL - in my case either a big import or export of a "big" table (5Gb and 6Gb seem to be my two problem tables) then FreeBSD tries to use the Free memory, hits the threshold, decides it doesn't have time to sort out Inactive memory and so starts to give MySQL swap (or it's swapping Inactive memory to swap). Active memory climbs to deal with the immediate request from MySQL. For MySQL 5.6 I can ease the swapping by giving MySQL a lot less RAM that I thought was OK (6GB on a 32GB RAM machine seems to work for my current workload.)

tcmalloc seemed to help with my import into 5.7, but doesn't help with the 5.6 export.

Got another machine with 64GB so trying to see what happens on that - if I eventually have 60G Inactive, would it still want to swap to preserve the Inactive memory?

There are tunables to play with, but want to understand a bit more what I'm fiddling with.

Lots to learn!
 
Well the problem of course with it pushing my ARC down (and it also pushed inactive down) is read performance tanked as there was barely anything cached. If I wanted mysql to use all my ram then I would configure it as such, but if it does work, I wonder if its worth us asking the port maintainer to make the library with the other malloc library a dependency option for the port.

Now whats odd for me is two things.

1 - The problem was not immediate on upgrading to 5.7, it took around a week to start happening, it did initially behave.
2 - I have also seen it mentioned its new with FreeBSD 12, but the problem had already started before I upgraded from 11. So the combo of 11 and mysql 5.7 it was happening as well.

We are in the process of migrating all myisam tables to innodb, as I suspect that may help.

As for inactive vs swap, FreeBSD seems to behave better than other OS, but I have seen it prefer swap over inactive at times still, so it is hard to answer that question. But in my case inactive was shrunk to almost 0 when it started swapping.
 
Ok I have an update,I feel it has not solved the leak directly, but it is helping, it is making the growth stop at a point when free memory runs out, but then it is more polite that it doesn't eat into inactive and ZFS ARC. It stops at about 3 gig usage (even if I set the pool to 300MB), but that's much better than consuming 10's of gigs of memory.

I feel this bug has been left unaddressed in the software because there is such a huge focus on enterprise, where the standard practice there is to have the service have an entire server to itself.
 
What seems to have worked for you? tcmalloc?

I think there are certain workloads in MySQL that push any operating system's virtual memory the wrong way, so think there's maybe not so much a bug as such as "sub-optimal behaviour" for certain workloads.

From postings on the internet it seems to affect Linux as well, and the MySQL team's response seems to be "your virtual memory system/memory allocator is wrong" and to use different memory allocators or (like you say) use dedicated machines (with plenty of physical RAM).

For me, some big table operations seem to push FreeBSD to use swap.

Wonder if something like xtra-backup would work better, so that's on my TODO list to look at.
 
Yep the tcmalloc from that google-perftools port.

Most tables have also been moved to innodb, but I had noticed improvement even before the migration was completed.

I have even noticed there is a few gig free as well, so it just seems to stop around 3.2 gig which is nice.

Also to clarify by bug, I mean in Mysql, not FreeBSD, the fact its affecting linux as well, and I don't see this behaviour in apache, dovecot, exim, and any other services running.


Code:
Mem: 1577M Active, 7276M Inact, 415M Laundry, 19G Wired, 148M Buf, 3335M Free
ARC: 10G Total, 7204M MFU, 1097M MRU, 141M Anon, 113M Header, 1534M Other
     6061M Compressed, 8575M Uncompressed, 1.41:1 Ratio
Swap: 64G Total, 64G Free

  PID USERNAME    THR PRI NICE   SIZE    RES STATE    C   TIME     CPU COMMAND
34786 mysql       102  20    0  3762M  3226M select   1 487:48 102.58% mysqld
 
For my issue with importing into MySQL 5.7 on FreeBSD 12.1 moving tables to InnoDB also made a positive change, so think that's one improvement that helps.

I think the MySQL developers are focused on InnoDB these days (rather than MyISAM) so it would make some sense that InnoDB might work better.

Some reports say tcmalloc is slightly slower than jemalloc. But benchmarks are benchmarks, so really depends on your workload etc.
 
Been chipping away at this in the background, and have come up with a little script to try and force FreeBSD to free up some inactive RAM for mysqld to use during mysqldump instead of swapping.

I've done this in PHP because I've got that on the servers, but I made a C version as well.
Code:
<?php
ini_set('memory_limit',-1);
$mem=str_repeat('*',8*1024*1024*1024);
sleep(10);
?>
So the idea is FreeBSD thinks what is in Inactive memory might be needed again, so when the long-running mysqld process asks for gobs of RAM for mysqldump, FreeBSD hands it out of swap.

The short-running process above will make FreeBSD decide to hand out the 8G I ask for out of Free/Inactive, and when the script ends (after the 10 second sleep), FreeBSD will hand that back to Free space, so I should end up with 8G in Free memory.

Then my mysqldump process will run, demand memory, and FreeBSD will say "oh, look, I've got that right here in Free memory, you can have it!". mysqldump will run, mysqld will gobble up the free memory, FreeBSD will stick it back in inactive (and it will - hopefully! - leave my swap alone.)

Don't try this on production or if you do, try it with asking for less RAM first (change the 8 to a 2 or whatever). This is PURELY for the situation that I've found on two servers now - 20+GB of Inactive RAM, less than 1GB Free RAM, and when I run mysqldump, FreeBSD hands out swap space to mysqld. When swap hits 100% FreeBSD kills a process which I don't want to happen.

EDIT: at various points in time I've thought it was FreeBSD 11.x versus FreeBSD 12.x, the jemalloc implementation on those (so tcmalloc seemed sometimes to help), MySQL 5.6 versus 5.7, MyISAM versus InnoDB, but for what I'm seeing seems to be more FreeBSD's reluctance to hand over that Inactive RAM when mysqldump is running. Hence my current attempt to see if a short-lived memory-grabbing process can do the trick for me! I suspect there also may be some vm tunables that would help.
 
I see now, we appear to have what is two different problems but potentially the same.

So your problem is mysql asks for a lot of ram, you have no issue with this request and dont consider it a mysql problem, however FreeBSD for some reason allocates from swap when there is enough inactive memory to allocate. So your script makes FreeBSD use inactive instead of swap, it is interesting you managed to pull that off.

My problem, again mysql asking for lots of memory but I treated it as a mysqld side problem, where mysql is not letting go of memory instead of in a forever and ever loop in asking for more memory. In my case from what I observed it does take from inactive first, and there after the ZFS ARC, and finally when both of these have been pillaged it utilises swap.

My mysqld is still creeping up, way way slower, but after my last post with no restarts it is now using over 4 gig of memory, there is still some myisam usage going on (I was lied to), so that may still be a factor, but it is at least now bearable, if I have to restart mysqld I can probably get away with doing it every 1-2 months now instead of before twice a day. Current swap usage is 0, 2 gig of completely free ram, 13gig inactive and ZFS just below its max ARC.
 
All I am certain about is that I'm confused. Things that seemed to make a difference (FreeBSD version, MySQL version, InnoDB/MySQL, jemalloc/tcmalloc) don't always make a difference.

It seems (what I'm working with) to be a problem with importing or exporting tables with tens of millions of rows. On one machine (MySQL 5.6 running on 12.1) the swap seems to have stabilised after I converted a few tables from MyISAM to InnoDB. On another machine (also MySQL 5.6, but on FreeBSD 11.4) swap is creeping up 2% per day - and I've converted all tables from MyISAM to InnoDB. This latter machine is where I'm trying the Inactive-to-Free RAM conversion before mysqldump runs. But I missed one mysqldump instance last night, and only set it to try for 4GB, so swap crept up over night. Tonight I'll try and get all instances of mysqldump, and try for 8GB conversion first. This is still experimental - I certainly don't have the answers!

The design and implementation of the FreeBSD operating system (2nd ed) has stuff like this (p. 292) "FreeBSD uses a least actively used algorithim to preserve pages that have a history of usage so that they will be favoured over the once-used pages brought in by a period of high memory demand". Page 295 "The default is to scan every active page about once every 10 minutes".

So I think that's what I'm seeing. FreeBSD wants to look after the mysqld inactive pages because it believes they are more useful in RAM than the burst of memory required for import/export, so either it allocates some of the new requests in swap, or it is swapping out some of the inactive pages of mysqld. But in fact I don't want it using that much swap (because I can't free it without re-starting mysqld) and I'd rather it gave the export the Inactive RAM for the 5 minutes or so the export takes.

Going to have a play on Linux to see what happens with these databases.
 
No, I'm not using ZFS. I've tried O_DIRECT and that's in use on the 11.4 system.

I'll see if my script (in the first post) still shows the same behaviour - that's probably easier to use to demonstrate the apparent issue.
 
Thank you.
This fix my issue with restoring a small sql dump and running out of memory (and swap)

mysqld_safe(1)

Code:
Note
           As of MySQL 5.7.13, MySQL distributions no longer include a
           tcmalloc    library.

install devel/google-perftools

Edit /usr/local/etc/mysql/my.cnf and add:

Code:
[mysqld_safe]
malloc-lib=/usr/local/lib/libtcmalloc_minimal.so
 
Hi

I tried this way and some time it was working, but now it doesn't work and mysqld can't start. Here what I see in his err log:

Code:
    2021-01-12T21:54:35.338027Z 0 [Note] InnoDB: Setting file '/var/db/mysql/ibtmp1' size to 128 M
    B. Physically writing the file full; Please wait ...
    21:54:35 UTC - mysqld got signal 10 ;
    This could be because you hit a bug. It is also possible that this binary
    or one of the libraries it was linked against is corrupt, improperly built,
    or misconfigured. This error can also be caused by malfunctioning hardware.
    Attempting to collect some information that could help diagnose the problem.
    As this is a crash and something is definitely wrong, the information
    collection process might fail.
    
    key_buffer_size=268435456
    read_buffer_size=131072
    max_used_connections=0
    max_threads=151
    thread_count=0
    connection_count=0
    It is possible that mysqld could use up to
    key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 322098 K  bytes of memory
    Hope that's ok; if not, decrease some variables in the equation.
    
    Thread pointer: 0x0
    Attempting backtrace. You can use the following information to find out
    where mysqld died. If you see no messages after this, something went
    terribly wrong...
    stack_bottom = 0 thread_stack 0x40000
    2021-01-12T21:54:35.338497Z 0 [Note] InnoDB: Progress in MB:
    0x1452188 <my_print_stacktrace+0x38> at /usr/local/libexec/mysqld
    0x13ad15e <handle_fatal_signal+0x2be> at /usr/local/libexec/mysqld
    0x801f28b70 <_pthread_sigmask+0x530> at /lib/libthr.so.3
    The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
    information that should help you find out what is causing the crash.
    2021-01-12T21:54:35.6NZ mysqld_safe mysqld from pid file /var/db/mysql/vpyx.bamus.cz.pid e

As soon as I remove malloc-lib from my.cnf it works fine again. Does anybody know how to fix it with malloc-lib?

Thanks.
 
Back
Top