MySQL memory usage keeps growing. 30GB growth in 3 weeks We have a server with 64GB memory.

After restarting MySQL, in 3 weeks memory is almost full.

MySQL was installed using Debian Percona packages.

Version: Percona Server Release 17 5.7.19-17-log

In daytime, average selects per second are 2500.
Average writes per second are around 100.

Here is the mysqld config.
I'm not a DBA but I have access to server and MySQL root.

Are there any obvious problems? Thanks.

[mysqld]
skip-slave-start
log_error = /var/log/mysql/error.log
server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 1
max_binlog_size         = 1G
binlog_do_db            = gsa

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
explicit_defaults_for_timestamp
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES
symbolic-links=0

# CACHES AND LIMITS #
tmp-table-size                 = 512M
max-heap-table-size            = 512M
query-cache-type               = 1
query-cache-size               = 200M
query-cache-limit              = 10M
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 102400
table-definition-cache         = 16384
table-open-cache               = 400

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 20G
innodb-buffer-pool-instances   = 8


innodb_write_io_threads = 8
innodb_read_io_threads = 8
Well, I did not spot a likely cause of the 30GB, but there are some issues to address:

Observations:


Version: 5.7.19-17-log
64 GB of RAM
Uptime = 23d 17:12:41
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.


The More Important Issues:

Normally I would suggest increasing innodb_buffer_pool_size, but since the goal here is to figure out why so much RAM is being used; I will put that off.

Tables are being opened at a large rate.  Perhaps because of lots of short connections?  Perhaps queries with lots of tables?  The OS apparently has a limit of 1024 files per process.  This is unreasonably small for MySQL; try to change the OS limit (to, say, 4096) so that open_file_limit can go up, and then table_open_cache can go up -- to, say, 1000.

'open_files_limit', '1024' -- this disagrees with the unreasonable setting (102400) in my.cnf.  Probably the OS overrode it.

innodb_log_file_size is unreasonably low at only 32M; raise it to 200M.  (See the 5.7 manual for specifics on how to do that.)

Do you have essentially no "transactions"?  That is, are you happy with autocommit=ON?

Some of the details below indicate inefficient queries -- find them so we can discuss them and hopefully make them run more efficiently.

Are you using SSD drives?  (Some of the numbers hint that you are.)

Hundreds of SHOW commands are being run per minute.  (Production systems rarely need to run any SHOW commands.)  Are you using some 3rd party software that can't remember what the schema is?

Details and other observations:

( Innodb_buffer_pool_pages_flushed ) = 220,833,222 / 2049161 = 107 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 20480M / 65536M = 31.2% -- % of RAM used for InnoDB buffer_pool

( Opened_tables ) = 84,342,523 / 2049161 = 41 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 3,568,448 / 2049161 = 1.7 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles &amp; kern.maxfilesperproc) or something else (OS dependent)

( Table_open_cache_overflows ) = 84,342,104 / 2049161 = 41 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 84,342,523 / 2049161 = 41 /sec
-- May need to increase table_open_cache

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((42793206 + 220833222) ) / 2049161 = 128 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 234,594,526,208 / (2049161 / 3600) / 2 / 32M = 6.14 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 2,049,161 / 60 * 32M / 234594526208 = 4.88 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 94,976,602 / 102716752 = 0.925 -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( Innodb_row_lock_waits ) = 398,208 / 2049161 = 0.19 /sec -- How often there is a delay in getting a row lock.
-- May be caused by complex queries that could be optimized.

( Innodb_dblwr_writes ) = 16,335,687 / 2049161 = 8 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( expand_fast_index_creation ) = OFF -- ALTER and OPTIMIZE may be greatly sped up by using ON.
-- Probably better to be ON.

( innodb_thread_concurrency ) = 0 -- 0 = Let InnoDB decide the best for concurrency_tickets.
-- Set to 0 or 64. This may cut back on CPU.

( innodb_print_all_deadlocks ) = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( bulk_insert_buffer_size / _ram ) = 8M / 65536M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.

( Created_tmp_disk_tables ) = 2,577,361 / 2049161 = 1.3 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( tmp_table_size ) = 512M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (88969136 + 202120925 + 856731 + 0) / 7 = 4.17e+7 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 19,298,109 / 2049161 = 9.4 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (88969136 + 856731 + 0 + 0 + 202120925 + 0) / 2049161 = 142 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( expire_logs_days ) = 1 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin = OFF)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".

( slave_pending_jobs_size_max / max_allowed_packet ) = 16M / 4M = 4 -- For parallel slave threads
-- slave_pending_jobs_size_max must not be less than max_allowed_packet

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( back_log / max_connections ) = 92 / 214 = 43.0%

( Connections ) = 170,473,414 / 2049161 = 83 /sec -- Connections
-- Increase wait_timeout; use pooling?

Abnormally large (not necessarily "bad"):

Binlog_cache_use = 130 /sec
Bytes_sent = 15636572 /sec
Com_release_savepoint = 0.042 /HR
Com_rollback_to_savepoint = 0.39 /sec
Com_savepoint = 0.042 /HR
Com_show_create_table = 0.39 /sec
Com_show_fields = 0.83 /sec
Com_show_table_status = 0.39 /sec
Com_show_triggers = 0.43 /sec
Com_update = 99 /sec
Created_tmp_files = 0.26 /sec
Handler_commit = 1423 /sec
Handler_savepoint = 0.084 /HR
Handler_savepoint_rollback = 0.79 /sec
Innodb_buffer_pool_pages_made_not_young = 4570 /sec
Innodb_buffer_pool_read_ahead = 9.24e+7
Innodb_master_thread_active_loops = 2.02e+6
Innodb_rows_deleted = 46 /sec
Open_table_definitions = 16,384
Performance_schema_digest_lost = 4.1e+7
Qcache_hits = 543 /sec       -- This seems strange since the QC is OFF
Qcache_inserts = 198 /sec
Sort_rows = 10139 /sec
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 53
Ssl_session_cache_size = 0.0MB
eq_range_index_dive_limit = 200
innodb_purge_threads = 4
table_definition_cache = 16,384


Note

The buffer_pool will grow until it gets to 20GB.  So, I would expect MySQL to use some amount more than that, but not 30GB.  Did it keep growing after 30?