Forge and Mysql memory usage

Without knowing the details of any differences in the data/indexes etc - it's hard to say. Maybe you've got the performance schema stuff running on the new one? I think it defaulted to 'on' recently (ish) so might be different from a previous install?

I've tried to disable performance schema but nothing changes. Once mysqld service is restarted, memory is around 15%, but some minutes later it rises to 50%. Since it's a clean forge installation, I was wondering if someone else had the same problem.

Again - without knowing what's in your DB it's hard to say. I seem to remember having to set 'table_definition_cache = 400' in my.cnf once to lower memory usage on a small box - it might help. You can check the current value in the mysql shell by running show variables like '%cache%';

Turn on debug and see what queries are running and how fast. Maybe something is running like a scheduled query.

You can "tail" the log for it.

I turned on query logging, but didn't find anything strange. Ohffs what do you mean "without knowing what's in your db"? My db is completely empty except for system tables, and table_definition_cache was already set to 400. The only solution I can think of right now is to go back to mysql version 5.5. Anyway, thank you both for your help.

I have to wait and see on mine (Forge on DO - mysql 5.7.10), but I checked a second ago with:

performance_schema=off

and the mysql user was at 26.3% of a 2GB droplet. I checked the docs and notice they used ON or OFF so I switched the value to OFF and restarted mysql. It's running at 4% right now, but that's with zero load. I don't know if the directive has become case sensitive. It certainly wasn't in 5.6.

Amost forgot to report back. When idle, it's at 22.4%. So, more research is needed.

Unfortunately I had to go back to mysql 5.5 and memory consuption is now on normal level. Not the ideal solution but had no other way...

This gave me the impetus to move to MariaDB 10.1. 20.3% at idle with performances hematite on and holding at 7.3% with it off. I dug as deeply as my competence would allow but couldn't figure out of on mysql 5.7.11. The path from mysql is a little gnarly so don't jump in willy nilly. I'll write it up when I can find time.

EDIT: I had time https://tenerant.com/blog/convert-mysql-to-mariadb/

Having similar issues even with performance_schema=off.

What are your default cache options for the server?

Are you referring to the query cache?

This is a new forge install (ubuntu 14.04, mysql 5.7.11) with no changes other than toggling performance_schema. Forge doesn't seem to alter any default settings for mysql when it comes to caching, innodb, or anything for that matter outside of setting up ports/sockets/user. I believe it's just pulling down the stock setup from the official mysql provided apt repo.

Doing some more digging, I found this page http://mysql.rjweb.org/doc.php/memory that references 5.7 toward the very bottom.

5.7 stores a lot more information in RAM, leading to the footprint being perhaps half a GB than 5.6. See Memory increase in 5.7

Reading the referenced Oracle blog article didn't really make it clear to me as to why there is such higher footprint in 5.7 though. It really just explains what the memory summary tables are, and that they are an addition to the performance schema.

If I am to assume that the memory summary tables are indeed the cause of the memory increase and they are part of the performance schema. It would stand to reason that disabling performance schema would still reduce the memory footprint to levels similar to 5.6. That doesn't seem to be the case though.

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
16956 mysql     20   0 1638.5m 565.6m   0.4m S  0.0 56.9   3:47.95 mysqld

Again, that's with no changes to an empty default forge install except disabling performance_schema.

Probably just going to switch over to mariadb on my smaller boxes now that it's an option.

I wonder if this was part of the reason Red Hat ditched mysql in favour of MariaDB. I'd always assumed it was just a dislike of Oracle, but there might be technical reasons after all ;-)

Now that Forge offers MariaDb installation out of the box, I'm wondering it solves the problem on small droplets. Has anyone tried it?

Hi, I'm currently experiencing the same excessive memory usage on MySQL. I noticed even more memory usage after updating to 5.7.11 this morning. I had to add a swap file to keep the server from falling over.

+1 few days ago my server running with forge act like this too. MySql ate my memory almost 80%.

Taylor suggest me try convert it to mariadb.

Anyone tried to use MariaDB? Would love to read about your results in memory usage!

@stefr Yes now i running with mariadb.

So far the memory usage is more stable than mysql.

@sstarlight can you tell me the difference? Can you see usage percentage compared to MySQL?

Just found this tweet: https://twitter.com/laravelphp/status/700345154935197696

So, I've heard of people having servers run out of memory, and I believe there is an issue going on (not necessarily Forge's fault), but we should cover how to see how much ram is truly being used.

Free

I've just spun up a Forge server and ran free -m (the command free -h actually is a little more human readable).

root@mysqltest2:~# free -h
             total       used       free     shared    buffers     cached
Mem:          994M       844M       149M        24M        20M       509M
-/+ buffers/cache:       313M       680M
Swap:         1.0G         0B       1.0G

On first glance, you'll notice that it says there's only 149M free. Technically, that's true - but the majority of it is being used by cache. Read more about that at http://www.linuxatemyram.com/.

The true amount free is 680M, which is shown on the line with -/+ buffers/cache:.

Top

The "top" also shows you this half truth, and doesn't have an easy way to see around it.

Htop

The htop utility is better at this. Install it via sudo apt-get install -y htop. Then try it out:

Screenshot of htop: http://drop.userscape.com/lfZ2/1EAwWJSa

Htop shows the memory usage but provides a nice blue line on the memory usage we care about, along with a number that makes sense to our concerns on the right.

If you add New Relic server monitoring (Free!), you'll see it agrees with the RAM usage.

MySQL 5.7.11

MySQL is still taking up about 16% of the server's memory, which might indeed be higher than previous versions.

Turning performance schema off and restarting mysql immediately took this down to about 4%. (Although in any case, this amount does slowly increase and increased to ~10% by the time I was done writing this).

; File /etc/mysql/my.cnf
[mysqld]
; NOTE THAT WE ADD THIS  UNDER THE MYSQLD SECTION
performance_schema = OFF

Then restart:

sudo service mysql restart

Here's what New Relic shows: http://drop.userscape.com/12Vzt/50r4VAXm

We see RAM usage drop a bit. MySQL is still the larger user of ram (sort of) at 158M. This isn't too bad. (Nginx and PHP-FPM aren't doing anything at the moment on this new server).

Is there a problem?

I'm not really sure. If you run out of memory quicker than you used to, yes. However what I'm seeing here doesn't really show me that MySQL 5.7 is using much more RAM in a problematic way, just that it might be starting to use more RAM in general in comparison to previous versions of MySQL.

I did a bit of digging and found a few references from oracle mysql folks saying after 5.5 (for instance) they started changing the defaults from assuming a small constrained server to a somewhat larger one - so seems mysql is just moving to a more memory intensive state. I switched to mariadb a couple of years ago for various reasons so I am happy to ignore it ;-)

I got the same problems. I've updated some of my VPS to newly php7 and mysql5.7 forge installation, and now all have memory issues. Also found a lot of stackexchange complains about this topic like this one: http://dba.stackexchange.com/questions/129411/mysql-5-7-ubuntu-14-04-eating-up-my-ram .

So what is the ideal fix for this, without downgrading the version of mysql? Can I update the mysql conf so that will fix this issue or should I go with mariadb ?

@AlexRo mariadb is now the default on forge so you might as well go with the flow :-)

I've got the same issue 5.7.11 I just tried disabling the performance_schema. I've also upped the ram on the server temporarily while I get this worked out.

Partially tempted to switch to Maria, but no time to do so at the moment.

• Jack

@jack it's worth the time. I went from 71% memory usage to 28% by switching to MariaDB!

The newest version of MySQL recommends a minimum of 1gb memory. We all know a minimum is barely enough with specs. If your running a web server and database (as most of us do for first deployment) I recommend the $20 do box with 2gb memory. Huge, I mean huge, difference in response time.

On a side note: seems MySQL usage is on a decline since their latest update on new instances. I to looked into the other alternatives as forcing some of the defaults and now having to constantly tweak settings is a pain in the butt. If it ain't broke, let's break it!

On a 1GB AWS I'm seeing MySQL creep up to 70-80% of the whole server's RAM, causing deploys to fail due to lack of overall memory.

When I run ps aux --sort -rss to show memory usage from highest to lowest the top line is something like this: USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND mysql 5929 0.0 70.8 1760336 719548 ? Sl 18:22 0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql

.. and the server doesn't use any MySQL databases!

I've worked around by stopping the MySQL service manually, and creating an hourly cron job saying 'service mysql stop'.

Has anyone managed to get around this in an elegant way? The 1GB server tier on Forge becomes pretty useless using a standard (MySQL-on) install...

@OK200Paul

I had the same thing on a 1GB DO droplet. I created a new droplet using MariaDB instead of MySQL. I went from 71% memory usage to 28%!

@OK200Paul If you want to stick with MySQL 5.7 then you have to increase your RAM memory size to a minimum of 2GB. Read this about the memory use of 5.7 version ( http://dev.mysql.com/doc/refman/5.7/en/memory-use.html )

There are other options also. You can downgrade to MySQL 5.6 or you can install MariaDB (this comes with fresh forge install by default).