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

richardtoohey2

Active Member

Reaction score: 96
Messages: 196

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.
 
OP
R

richardtoohey2

Active Member

Reaction score: 96
Messages: 196

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).
 
OP
R

richardtoohey2

Active Member

Reaction score: 96
Messages: 196

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.
 

CyberCr33p

Well-Known Member

Reaction score: 22
Messages: 285

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.
 

CyberCr33p

Well-Known Member

Reaction score: 22
Messages: 285

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.
 

CyberCr33p

Well-Known Member

Reaction score: 22
Messages: 285

Maybe my old post was related:

 
Top