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.
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
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.
(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 ;)
...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.
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.
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 ?
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
This one: https://github.com/jemalloc/jemalloc/issues/1152
Can someone add the patch to mysql 8.0?
(In reply to Christos Chatzaras from comment #9) i see no patch in this pr - so please upload it
(In reply to Jochen Neumeister from comment #10) download, not upload
(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.
Yes exactly. I will create a patch for 8.0 tomorrow.
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/
(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.
(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
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(+)