MySQL hogging memory

Question

I have an Ubuntu 18.04 server running mysql 5.7 and a handful of webapps. Mysql is using significant memory and forcing swap usage. We've had a few fatal crashes from high memory usage. I'm working on decreasing the memory reqs of the other programs running on this server but I'm curious if mysql is obviously mis-configured in some way.

I ran mysqltuner.pl, and took some of the recommendations therein.

8 cores, 20GB RAM, 8GB swap

/etc/mysql/mysql.conf.d/mysqld.cnf

# The MySQL database server configuration file.

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
# * Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address = 0.0.0.0

# * Fine Tuning
key_buffer_size = 16M
max_allowed_packet = 256M
thread_stack = 192K
thread_cache_size = 8
myisam_recover_options = BACKUP

# * Query Cache Configuration
query_cache_size = 0

# * Logging and Replication
log_error = /var/log/mysql/error.log

expire_logs_days = 10
max_binlog_size = 100M

# * InnoDB
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=8

Answer 1

The recommendations of your mysql tuner are making the assumption that this is a dedicated mysql server, and that that the server isn't also handling webapps.

You have innodb_buffer_pool_size=10G which means that MYSQL will always try and use 10GB of memory if it can, in order to maximize its performance.

If you need more memory for other programs on the server to use, you should reduce the innodb_buffer_pool_size, and set it to a lower amount of memory. However keep in mind that this could severely harm the performance of your database.

It's worth mentioning that in production environments, it's generally considered best practice to have the database and the web app programs running on separate servers.