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):
A script to build a large import file of the sort mysqldump would produce:
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:
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:
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:
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:
I tried some jemalloc tuning but not really sure what I'm looking for so not really winning:
github.com
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):
And look at top:
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.
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:
jemalloc/TUNING.md at dev · jemalloc/jemalloc
Contribute to jemalloc/jemalloc development by creating an account on GitHub.
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.