Something in MySQL is causing progressively high load which consumes almost all memory

I have this server that has only one site with one database on it. And yet, I cannot tweak it to make the load go back down to normal.

If I restart mysql service, memory and load go down to normal, but a few days later, memory and load are progressively increasing.

Server specs

• Centos 7

• total memory 7.8G

• swap 1G

htop output

MySQL version

mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper

my.cnf

`# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

#[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0

#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

#max_allowed_packet=1024M

##################

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The serv

table_open_cache=1501,open_files_limit=3003 (as suggested by Vasiliki) should be fine.

Note: When open_files_limit is more than ulimit -n, the former is lowered during startup. So, do SHOW VARIABLES LIKE 'open_files_limit'; to see what the setting really is. (Ditto for table_open_cache.)

table_open_cache is a "cache". As such, too-large a value will waste memory; too-small a value will slow certain things down. The value is the number of tables, not the number of bytes. 2000 is the median value used by over a hundred servers I polled. (10th percentile: 400; 90th percentile: 10000. Your 39390 is about 95th percentile--a red flag.) Set it to 1501 and forget it unless you are doing something really abnormal.

ulimit -n is a per-process file limit imposed by Linux (and its cousins). 1024 is the default in certain Operating System versions, and is a reasonable limit for casual programs. However, it is too low for MySQL, as has been discovered by many users doing non-trivial database work. It is, however, mostly a limit to keep you from going hog-wild in opening files. Setting it to 10000 is probably reasonable; that is helpful for MySQL, and not 'harmful'.

open_files_limit is initialized to ulimit -n. (I do not understand how you have 1024 and 19107. But note "[Warning] Changed limits: table_open_cache: 2419 (requested 39390)") But, again, this is merely a limit to keep the program from getting out-of-hand.

table_open_cache is the critical one in all this. However, you may be stopped from setting it because of the other two settings.

But...

You asked about "high load" -- Almost always that comes from inefficient queries. Such can usually be fixed by improving indexing (think "composite") and/or reformulating queries. Let's have a look at a slow query and discuss it.

You showed a large number of processes. Please provide SHOW VARIABLES LIKE 'max_connections'; It may be a default of 151, which is OK. (Note: this default has changed a few times based on version.) Consider lowering it to 50. Also, let's see SHOW GLOBAL STATUS LIKE 'Max_used_connections'; If this is more than, say, 10, then that could be a symptom of other issues.

You mention that memory keeps growing. Well, this is 'normal', but only to a point. The biggest user of RAM should be the buffer_pool; innodb_buffer_pool_size = 2G says that that part will grow until it occupies 2GB, then stop growing. The excessively large table cache (controlled by table_open_cache) is another memory user. (No, I don't know how many bytes in each entry.) Each "connection" consumes some memory. And connections are "cached", so my comments about max_connections indicate another memory consumer and way to control it. (Again, I don't know the per-connection memory usage.)

The htop output does not back your "high load". You have 6 cores? And they average about 3-4%? On the other hand, Load average is more than 3? (I trust the CPU percentage more than the LA.) Again; let's look at a slow query; you "can't tune your way out of a performance problem".

StackExchange Database Administrators Q#256651, answer score: 3