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

[richardtoohey2]

Seems to be working here for me (a backup machine so not doing much, but I can certainly start-up MySQL 5.7.33 without errors):

% pkg info | grep perf
google-perftools-2.8.1         Fast, multi-threaded malloc() and nifty performance analysis tools
...
% cat /usr/local/etc/mysql/my.cnf | grep malloc
# tcmalloc to see if it helps on FreeBSD 12.x (instead of newer jemalloc)
#malloc-lib=/usr/lib/libtcmalloc_minimal.so
malloc-lib=/usr/lib/libtcmalloc_minimal.so

If you manually copied the .so file (/usr/local/lib/libtcmalloc_minimal.so to /usr/lib/), did you copy the newer version across (i.e. after the upgrade to 2.8.1)?

 

[richardtoohey2]

FreeBSD CURRENT uses jemalloc 5.2.1. Does someone test if this version fixes this issue?

Looks interesting, so on my list to experiment.

https://github.com/jemalloc/jemalloc/blob/dev/ChangeLog - this bit is from the 5.20 changes:

This release includes a few notable improvements, which are summarized below:
1) improved fast-path performance from the optimizations by @djwatson; 2)
reduced virtual memory fragmentation and metadata usage; and 3) bug fixes on
setting the number of background threads. In addition, peak / spike memory
usage is improved with certain allocation patterns.

So I'll have a play with BETA1 (when I can!) - there's also some early benchmarks that make 13.0 look like it has a general performance boost so that should help with the overall import speed.

 

[richardtoohey2]

Initial test with 13.0 BETA2 looks very promising.

At the end of my import:

Mem: 5646M Active, 7832M Inact, 26M Laundry, 1933M Wired, 1527M Buf, 338M Free
Swap: 3962M Total, 3962M Free

So this is without tcmalloc.

Unfortunately I'm not completely comparing apples with apples - I didn't keep my old test data set, so this is a bigger file (but from the same database), on a different machine. MySQL 5.7 won't build with clang 11 (which is what FreeBSD 13.0 uses) so it wanted to pull in llvm9. I changed that to llvm10 to match FreeBSD 12.2 - just to see if it would even work. Also my original test was MySQL 5.7.32 and the current version is 5.7.33 (don't think they changed anything, but another difference.)

But under FreeBSD 12.2 my test import - if using jemalloc - would gobble up all Free RAM and march straight into swap until the OOM kicked in. That didn't happen at all with FreeBSD 13.0. Basic install + MySQL 5.7.

On FreeBSD 13.0 Free RAM got down to about 337M and then it cycled around 331 - 341M during the rest of the import. It seemed reluctant to use any of the Inactive memory, but after several minutes that changed, and Active memory went up and Inactive went down.

No performance change from 12.2 for me in this test - FreeBSD 13.0 took 4 minutes longer but then had about another 4Gb of data to import, and this machine has a bit less RAM than the other one I tested on before.

This is just ONE test, so take with a pinch of salt. But looks promising.

 

[CyberCr33p]

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.

I'm updating this thread with the latest details. On FreeBSD 13.2 with MySQL 8.0, the issue of increased memory usage persists when utilizing jemalloc. However, when switching to tcmalloc, the memory consumption stabilizes. I will wait for FreeBSD to use jemalloc 5.3.0 (currently uses 5.2.1) to redo the test.

 

[CyberCr33p]

Also I found this: https://blog.herecura.eu/blog/2020-05-12-mysql-memory-usage-in-real-life/