Excessive consumption RAM of mysqld daemon in Ubuntu 16.04

[VaryHeavy]

[Status]

Suspected cause is triaged upstream in MySQL; awaiting fix.

[Workaround]

Proposed in comment 10; please report success or failure.

[Original Description]

In the last days using Kubuntu 16.04 I've noticed that after a while of use, RAM consumption of mysqld daemon rises above 800 MB, even surpassing Firefox, as you can see in the attached images.

Initially, when system is just booted, RAM consumption of mysqld daemon it remains approximately 135-140 MB, but I have seen how after a while, in a matter of a few minutes, without touching anything, consumption rises progressively to around 830 MB.
In the second image you can see that mysqld daemon is related to Akonadi, which at the same time has a lot of threads running. It is possible that the problem is in managing Akonadi processes, but it is clear that consumption of mysqld daemon is not normal, and that there must be a problem, either MySQL or Akonadi, causing this situation.

I tried replacing MySQL with MariaDB, but will not let me uninstall MySQL components without also uninstall a lot of applications as dependencies (like Akonadi, Amarok, Kontact, KAdressBook ...). What I can do, even as an interim solution?

Here are the attached screenshots:
https://www.sendspace.com/file/712oyk
https://www.sendspace.com/file/qcqeem

[Clint Byrum (clint-fewbar)]

Ubuntu 16.04 does not have MySQL 5.5, it has 5.7. The most likely explanation for the memory usage going up is that default buffer sizes have been increased significantly for mysqld over the last few releases. You may need to edit config files to reduce the buffers.

[Robie Basak (racb)]

akonadi package configures its own mysqld, so any configuration changes for akonadi users need to be made in akonadi packaging and not mysql packaging.

akonadi packagers/Kubuntu team: I'm happy to help with this, let me know. It probably makes more sense for someone familiar with Kubuntu and/or akonadi to figure out what is needed on that side, though?

[Christophe (touil1976)]

Same problem for me, not with akonadi, simply by installing mysql. mysqld uses approximately 130Mb at startup and goes up to 806 Mb adter 10 minutes, without any server process running.

Any workaround yet ?

[Christophe (touil1976)]

I must add that I use Ubuntu 16.04 too. (not Kubuntu)

[Norvald H. Ryeng (nryeng)]

It might be this bug: http://bugs.mysql.com/bug.php?id=80772

Can try the workaround mentioned in the bug report?

[Philip Muškovac (yofel)]

Regarding switching to MariaDB, that is supported, but you need to replace mysql with mariadb in one go:

# apt-get install mariadb-client-core-10.0 mariadb-server-core-10.0

should give you:

The following packages will be REMOVED:
  mysql-client-core-5.7 mysql-server-core-5.7
The following NEW packages will be installed:
  libreadline5 mariadb-client-core-10.0 mariadb-common mariadb-server-core-10.0

[Robie Basak (racb)]

On Mon, May 02, 2016 at 12:52:44PM -0000, Philip Muškovac wrote:
> Regarding switching to MariaDB, that is supported, but you need to
> replace mysql with mariadb in one go:
>
> # apt-get install mariadb-client-core-10.0 mariadb-server-core-10.0

Note that migrating your data back to MySQL is generally not possible, so back
up your data first. MySQL/MariaDB packaging cannot currently do it anyway. I'm
not sure about akonadi.

[Philip Muškovac (yofel)]

> I'm not sure about akonadi.

It can't.

I meant we support it packaging wise as some people prefer to use another MySQL backend. But once you do that you're on your own.

[Robie Basak (racb)]

If this is the upstream MySQL bug that Norvald mentioned, then the fix would be in mysql-5.7, so adding a task for that. We can mark one of the tasks as Invalid when we figure out which is right.

[Robie Basak (racb)]

So the workaround suggested upstream is to add:

innodb-read-io-threads=1
innodb-write-io-threads=1

to

~/.local/share/akonadi/mysql.cnf

Can someone please confirm if this is accurate and if it works? We'll need to wait for a fix from upstream, but in the meantime hopefully the workaround will work for those affected.

[Lemmiwinks (lemmiwinks)]

The workaround in comment #10 seems to work for me. Memory usage is now at around 200 MB.

Conf file is:
~/.local/share/akonadi/mysql.conf -> add under [mysqld]

[Robie Basak (racb)]

Thank you for confirming, this is useful. We'll wait for a fix from MySQL upstream, then.

[Lemmiwinks (lemmiwinks)]

Unfortunately it actually does not seem to solve the problem. Memory usage is already up to more than 500 MB. Sorry for not testing long enough obviously.

[Lemmiwinks (lemmiwinks)]

Is there any progress on this bug? I'm burning my SSD drive because of memory swapping...

[Lemmiwinks (lemmiwinks)]

Mysql version 5.7.12-0ubuntu1.1 from ubuntu proposed repo does not fix this bug.

[Alexandr (xellgf2013)]

The problem is observed on the mysql-5.7

[Carlos (carlos-camara)]

Workaround on comment #10 seemed to work for me, but after some time memory consumption started to grow up to 957MB

[Stefan (spbkslx)]

Seems to be fixed for me with mysql 5.7.13 from yakkety.
RAM consumtion dropped from ~900 to ~200 MB.

[Colan Schwartz (colan)]

This is happening on Ubuntu Server as well; updated title.

Looks like this is now fixed upstream. Can we get the fix in the stable package? Thanks!

[Colan Schwartz (colan)]

This should be fixed now with the recent security releases in the stable channel. We should be at 5.7.15 which is fixed upstream. Can someone confirm?

[Colan Schwartz (colan)]

Confirming that my memory footprint for MySQL dropped significantly after installing the latest software updates.