Bug 259816 - databases/mysql80-server: 8.0.26 seems to leak memory compared to 8.0.25
Summary: databases/mysql80-server: 8.0.26 seems to leak memory compared to 8.0.25
Status: Closed FIXED
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: Any Any
: --- Affects Only Me
Assignee: Jochen Neumeister
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-11-13 13:27 UTC by Pete French
Modified: 2022-01-07 12:16 UTC (History)
3 users (show)

See Also:
bugzilla: maintainer-feedback? (joneum)


Attachments
the my.cnf file from one of the affected servers (2.16 KB, text/plain)
2021-11-13 13:27 UTC, Pete French
no flags Details
patch for malloc-lib files (791 bytes, patch)
2021-12-20 10:16 UTC, Christos Chatzaras
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Pete French 2021-11-13 13:27:29 UTC
Created attachment 229468 [details]
the my.cnf file from one of the affected servers

Upgraded to 8.0.26 from packages a week ago. Since then the server gradually increases memory usage until it consumes all of swap.

This looks like a classic memory leak, except for one thing - when swap is filled then mysql releases all the extra memory. So it does know there is memory to be released somehow, it just isnt doing it until it runs out of space.

The rate of memory growth is related to the rate of new connections established to the server. Recoding the application to sue less simultaneous connections has mitigated the problem, but it still exists.

As mysql 8 will not run over earlier versions of its files then a downgrade back to 8.0.25 is not feasible as a workaround either.

I have attached my my.conf file in case I am doing anything odd with the config to trigger this. But even if so, this is a problem which has appeared with 8.0.26 as this config worked fine before.

thanks!

-pete.
Comment 1 Jochen Neumeister freebsd_committer 2021-11-28 13:01:14 UTC
Hello,

you are unfortunately the only problem that reports this. I myself do not have this problem with MySQL on FreeBSD either.
Have you asked here directly? https://github.com/mysql
Comment 2 Christos Chatzaras 2021-11-28 14:43:39 UTC
Can you install this https://www.freshports.org/devel/google-perftools and then add to your my.cnf :

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

With tcmalloc my MySQL servers use less RAM.
Comment 3 Pete French 2021-11-29 15:17:02 UTC
(In reply to Christos Chatzaras from comment #2)

Ah, thankyou! I will try that. Does the config you describe work for you though ? I get this:

 mysqld_safe --malloc-lib must be located in one of the directories: /usr/lib /usr/lib64 /usr/lib/i386-linux-gnu /usr/lib/x86_64-linux-gnu

Which agrees with the documentation (and irritates me due to Linux specific stuff creeping into mysql, but there we go). I mean, I can fix it with a symlink, but still annoying.

Hoever, I shall give this a try! Thankyou ;)
Comment 4 Pete French 2021-11-29 15:24:50 UTC
...and it runs! Ok, I shall try this on a production server and let you now in a week or so when I have given it a chance to handle a lot of real load.
Comment 5 Christos Chatzaras 2021-11-29 15:29:43 UTC
Yes it works for MySQL 5.7 but after this patch:

https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=248982

Looks like the patch is needed for 8.0 too.

For temporary fix maybe a symbolic link for one of these directories will work.
Comment 6 Pete French 2021-11-30 17:08:24 UTC
I dont need to run this for a week to know it fixes the issue. After only a day, the mysql using standard malloc has swelled to 25 gig, but the one with tcmalloc has stayed steady at just under 10 gig, which is what I expect from the config.

So, an enormous "thankyou!" from me for this ;)

Still hasn't explained why it happened only after the minor upgrade though, which puzzles me - but a slight change in the way mysql uses memory could trigger this kind of behaviour in the malloc library I guess.

We could mark this as 'closed' I suppose, or at least 'workaround found' if there is such a status ?
Comment 7 Christos Chatzaras 2021-11-30 17:38:13 UTC
I first notice this issue with an old wordpress plugin that cause after few hours MySQL using more than 64GB of RAM and almost all my SWAP (I had to restart MySQL before it crash) and my config was to use only 4GB of RAM.

Also other servers with jemalloc (the default) were using more RAM than my limit with both FreeBSD 12.x and 13.x. When jemalloc is updated I temporarily switch back to jemalloc to see if the issue is resolved, so far no luck.

If I remember correctly there is a bug report about this here: https://github.com/jemalloc/jemalloc/issues
Comment 8 Christos Chatzaras 2021-11-30 17:42:29 UTC
This one:

https://github.com/jemalloc/jemalloc/issues/1152
Comment 9 Christos Chatzaras 2021-12-19 15:30:32 UTC
Can someone add the patch to mysql 8.0?
Comment 10 Jochen Neumeister freebsd_committer 2021-12-19 17:25:37 UTC
(In reply to Christos Chatzaras from comment #9)

i see no patch in this pr - so please upload it
Comment 11 Jochen Neumeister freebsd_committer 2021-12-19 17:26:06 UTC
(In reply to Jochen Neumeister from comment #10)

download, not upload
Comment 12 Pete French 2021-12-19 22:19:43 UTC
(In reply to Jochen Neumeister from comment #11)

Theres no patch for the actual issue, so I assume her is referring to the paths here:

https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=248982

Which then allows you to just switch the malloc library by a setting in my.conf.

The patch in that bug was applied to 5.7, but its also needed for 8.0 or else you need to fiddle with symbolic links to get it to work.
Comment 13 Christos Chatzaras 2021-12-19 22:21:17 UTC
Yes exactly. I will create a patch for 8.0 tomorrow.
Comment 14 Christos Chatzaras 2021-12-20 10:16:27 UTC
Created attachment 230266 [details]
patch for malloc-lib files

Log:

  MySQL 8.0 mysqld_safe allows malloc-lib files only in certain locations.
  google-perftools port puts libtcmalloc_minimal.so inside /usr/local/lib
  To use tcmalloc memory allocator we need mysqld_safe to load libraries from /usr/local/lib

  More information about why to use tcmalloc instead of jemalloc here:

  https://forums.freebsd.org/threads/freebsd-12-x-and-mysql-5-7-and-importing-file-with-lots-of-small-lines-exhaust-ram-and-swap.72733/
Comment 15 Pete French 2021-12-20 19:00:40 UTC
(In reply to Christos Chatzaras from comment #14)

That was fast with the patch! I cant try it until after Christmas, but I assume it works OK. Looks good to me.

Its amazing this isnt in the upstream actually - /usr/local.lib isnt a BSD specific thing after all, am sure SysV systems have it too don't they ? Been a while since I saw one admittedly, but I dont recall everything being dumped into /usr/lib the way Linux likes to.
Comment 16 Christos Chatzaras 2021-12-20 20:24:06 UTC
(In reply to Jochen Neumeister from comment #10)

Can you please commit the patch and close the PR?

-----

(In reply to Pete French from comment #15)

I just build a "test" server with MySQL 8.0 to copy websites there to see if there are any issues with MySQL 8.0 and then to upgrade the "production" systems from 5.7 to 8.0.

So far I notice only issues from connecting to databases using PHP 5.6, as it doesn't include all the new encodings but I patch PHP 5.6 (I run PHP 5.6 in a jail) with this https://github.com/php/php-src/commit/35a28fac51e4b210f8f3f51b754786908a977462 and these websites work fine now.

For everyone doing a MySQL 5.7 to 8.0 upgrade don't forget to run this command before you shutdown mysql-server:

mysql --login-path=local -uroot -e "SET GLOBAL innodb_fast_shutdown=0"

Also I had to add these in my.cnf:

default_authentication_plugin=mysql_native_password
event_scheduler=0

And remove these if exist in your my.cnf:

innodb_file_format
innodb_large_prefix
internal_tmp_disk_storage_engine
query_cache_type
query_cache_size
sql-mode
Comment 17 commit-hook freebsd_committer 2022-01-07 12:14:16 UTC
A commit in branch main references this bug:

URL: https://cgit.FreeBSD.org/ports/commit/?id=a025389152de64d806e403a680dc053ae41ade77

commit a025389152de64d806e403a680dc053ae41ade77
Author:     Jochen Neumeister <joneum@FreeBSD.org>
AuthorDate: 2022-01-07 12:10:00 +0000
Commit:     Jochen Neumeister <joneum@FreeBSD.org>
CommitDate: 2022-01-07 12:13:17 +0000

    databases/mysql80-server: add patch for malloc-lib

    forked from mysql57-server: https://svnweb.freebsd.org/changeset/ports/548590
    Log:
      MySQL 5.7 mysqld_safe allows malloc-lib files only in certain locations.
      google-perftools port puts libtcmalloc_minimal.so inside /usr/local/lib
      To use tcmalloc memory allocator we need mysqld_safe to load libraries from /usr/local/lib

      More information about why to use tcmalloc instead of jemalloc here:

      https://forums.freebsd.org/threads/freebsd-12-x-and-mysql-5-7-and-importing-file-with-lots-of-small-lines-exhaust-ram-and-swap.72733/

    PR:     259816
    Sponsored by:   Netzkommune GmbH

 .../files/patch-scripts_mysqld__safe.txt (new)        | 19 +++++++++++++++++++
 1 file changed, 19 insertions(+)