High memory usage on digital ocean droplet by mysql 8

Question

I have a laravel application which I've installed on a 1GB standard droplet running ubuntu 20.4, nginx, MySQL 8 and php 7.4

The application isn't even live yet and I notice it's already using over 60% memory - 68%

Below is a snap shot of the current high memory running processes. Is this level of memory usage normal for a laravel application which is not even live i.e. limited load?

top - 15:47:51 up 1 day, 23:53,  1 user,  load average: 0.00, 0.01, 0.00
Tasks: 100 total,   3 running,  97 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.3 us,  0.3 sy,  0.0 ni, 99.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :    981.3 total,     89.5 free,    612.3 used,    279.5 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.    200.7 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  815 mysql     20   0 1306168 408208   2892 S   0.3  40.6  18:20.97 mysqld
 2259 www-data  20   0  244024  38424  25504 S   0.0   3.8   0:05.27 php-fpm7.4
  388 root      19  -1  106512  38196  37008 S   0.0   3.8   0:06.24 systemd-journal
 2265 www-data  20   0  244032  37988  25108 S   0.0   3.8   0:05.14 php-fpm7.4
 2257 www-data  20   0  244004  37780  24856 S   0.0   3.8   0:05.22 php-fpm7.4
  988 root      20   0  125160  30236   4640 S   0.3   3.0   2:03.41 php
  741 root      20   0  649232  22700   6672 S   0.0   2.3   0:17.61 snapd
  544 root      rt   0  280180  17976   8184 S   0.0   1.8   0:11.26 multipathd
  743 root      20   0   31348  16972   2468 S   0.0   1.7   0:31.63 supervisord
  738 root      20   0  238392  13208   7244 S   0.0   1.3   0:09.45 php-fpm7.4
  726 do-agent  20   0  559692  11852   5560 S   0.0   1.2   0:20.79 do-agent
  825 root      20   0  108036  11804   4160 S   0.0   1.2   0:00.10 unattended-upgr
  736 root      20   0   29220  11364   3708 S   0.0   1.1   0:00.11 networkd-dispat
    1 root      20   0  168752  10208   5772 S   0.0   1.0   0:12.03 systemd
15532 sammy     20   0   18556   8488   6948 S   0.0   0.8   0:00.08 systemd
  778 www-data  20   0   71004   8276   3552 S   0.0   0.8   0:02.71 nginx
15522 root      20   0   13760   8256   6816 S   0.0   0.8   0:00.01 sshd
  623 systemd+  20   0   23912   6756   2752 S   0.0   0.7   0:00.60 systemd-resolve
15634 osama     20   0   13892   5912   4452 S   0.0   0.6   0:00.06 sshd
  747 root      20   0   16860   5564   4552 S   0.0   0.6   0:02.54 systemd-logind
15635 sammy     20   0   10032   5104   3412 S   0.0   0.5   0:00.04 bash
15537 sammy     20   0  169820   4572      0 S   0.0   0.5   0:00.00 (sd-pam)
  763 root      20   0   12160   4504   3572 S   0.0   0.4   0:01.33 sshd
  621 systemd+  20   0   18544   4292   3276 S   0.0   0.4   0:01.11 systemd-network
  659 root      20   0   18880   3808   2528 S   0.0   0.4   0:01.92 systemd-udevd

My concern is once the application goes live and the load increases, more database connection it going to run out of memory. I know I can resize the droplet and increase the memory or set up some swap space but is this amount of memory usage normal for an unused laravel application?

How can I optimize the high memory usage processes such as mysql, niginx, php. Looks like mysql8 is the main culprit hogging all the memory. Below are the mysql8 settings:

#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user            = mysql
# pid-file      = /var/run/mysqld/mysqld.pid
# socket        = /var/run/mysqld/mysqld.sock
# port          = 3306
# datadir       = /var/lib/mysql


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system- variables.html#sysvar_tmpdir
# tmpdir                = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size         = 16M
# max_allowed_packet    = 64M
# thread_stack          = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log                = 1
# slow_query_log_file   = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id             = 1
# log_bin                       = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db          = include_database_name
# binlog_ignore_db      = include_database_name

Any tips and advice much appreciate as this is the first time I'm using a vps.

*** UPDATE ***

Further to advice from tim, switching off mysql performance schema has made a huge difference I now get the following:

top - 10:33:26 up 5 days, 18:39,  1 user,  load average: 0.01, 0.03, 0.00
Tasks: 114 total,   1 running, 107 sleeping,   6 stopped,   0 zombie
%Cpu(s):  0.7 us,  0.3 sy,  0.0 ni, 98.7 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :    981.3 total,    253.8 free,    360.9 used,    366.7 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.    449.2 avail Mem

Run [mysqltuner.pl](http://mysqltuner.pl/) and post its output (after reading it yourself). — Michael Hampton, Jul 02 '20 at 20:33
Short answer 1: have Digital Ocean Manage your databases. Short answer 2: DO NOT HOST your database in the same Server as everything else. https://www.digitalocean.com/products/managed-databases-mysql/ — suchislife, Jul 02 '20 at 21:31

Tim · Answer 1 · 2020-07-02T22:04:03.333

1

An operating system will use all the RAM for cache - cache can be freed almost instantly and helps performance. So RAM available generally isn't important, it's freeable memory that's important.

From the info above:

612.3 used
279.5 buffer / cache
89.5 free

You actually have 297 + 89 = 386MB available for use.

I have MySQL, PHP, Nginx, and a few utilities running on an AWS server with 512MB RAM and 512MB of swap, so it's possible. I made a six part tutorial which is probably a bit out of date now but could have some useful tips.

From memory the key things to do are:

Disable the MySQL performance schema as this hugely reduces RAM usage, and set up parameters that suit your system (tutorial for MySQL 5.6)
Limit the number of PHP threads, and limit the RAM they use to only what they need. PHP uses huge amounts of RAM.
Add a bit of swap, just in case your run out of memory. The OS will push anything it doesn't really need all the time out to swap even if it has enough RAM.

edited Jul 02 '20 at 22:04

answered Jul 02 '20 at 18:18

Tim

30,383
6
47
77

Hi, I can't seem to find any cnf file which contains `performance_schema` but when I run `SHOW VARIABLES LIKE 'perf%';` I can see its on. Do I just need to add the setting to one of the cnf file and what exactly is it used for? – adam78 Jul 02 '20 at 18:41
how do I limit the number of Php thread and ram they use - is this done in php.ini file? – adam78 Jul 02 '20 at 18:45
You're going to have to do some research to work out how to do this on your OS / PHP / MySQL versions. I can give you the big picture but haven't done it recently. There will be plenty of docs online for MySQL. For PHP you just define a pool and set its parameters like threads and memory. For example I have a second PHP pool with different settings for my test environment where 0 threads run by default but are created on demand. – Tim Jul 02 '20 at 20:27
switching off the mysql performancee schema has made a huge difference - see my updated results. Thanks. – adam78 Jul 06 '20 at 10:37
Looks like it saved you 300MB - great :) If you're still having problems sort top by memory usage "hit M" and see what's taking memory. Also, adding some swap can be good and not cause problems, there's some things that are rarely used that can be pushed out and the OS manages what goes into swap. Add 1GB of swap if you like, it will give you a bit of a buffer in case something unexpected happens. – Tim Jul 06 '20 at 17:48

score 1 · Answer 2 · answered Jul 02 '20 at 21:39

You may start with

https://www.techolac.com/linux/how-to-optimize-apache-mysql-performance-for-1gb-ram-vps-centos-rhel/

Good advice for a low memory instance..and tuning mysql down.

Be advised, that you will loose performance, so understand where your bottle necks are.

If you have massively complex queries, the optimise your database.

You might even very simple queries, and you can use an entirely different database server instead , such as a NOSQL database.

So not only think about tuning your server, but look at code running on it, and perhaps optimise your code too.

High memory usage on digital ocean droplet by mysql 8

2 Answers2