Unexpected RAM usage on MySQL

Question

We have a server which hosts about 10 customers in a multi-tenant web application, each customer has its own database which is cloned from a template, and each database is a little less than 3000 tables.

The server currently has 8 GB of RAM with the following configuration for MySQL (actually MariaDB 10.1) database:

# http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/
default_storage_engine = InnoDB
innodb_flush_log_at_trx_commit = 1
sync_binlog = 0
innodb_flush_method = O_DIRECT
#innodb_thread_concurrency = 8
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown = 0
innodb_buffer_pool_load_at_startup = 0
innodb_buffer_pool_dump_pct = 100
innodb_adaptive_hash_index_partitions = 8
innodb_checksum_algorithm = crc32
innodb_log_checksum_algorithm = crc32
#table_open_cache_instances = 16
innodb_read_io_threads = 8
innodb_write_io_threads = 4
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10

# PER CONNECTION BUFFER
max_connections = 20
sort_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
join_buffer_size = 4M
thread_stack = 512K
binlog_cache_size = 32K
myisam_sort_buffer_size = 1M
net_buffer_length = 16K

# GLOBAL BUFFER
key_buffer_size = 128K
query_cache_type = 0
query_cache_size = 0
tmp_table_size = 256M
max_heap_table_size = 256M
aria_pagecache_buffer_size = 128M
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_log_files_in_group = 2 # (innodb_log_file_size * innodb_log_files_in_group ~= innodb_buffer_pool_size / 4)
innodb_log_buffer_size = 64M
table_open_cache = 30000 # = max_connections * table_number / 2
table_definition_cache = 15400 # = 400 + table_open_cache / 2
open_files_limit = 60000 # = table_open_cache * 2
thread_cache_size = 8
thread_handling = one-thread-per-connection
max_sp_recursion_depth = 16

If I understand correctly, the amount of RAM which MySQL should use with this configuration, should be about

GLOBAL_BUFFERS + MAX_CONNECTIONS * PER_CONNECTION_BUFFERS

which in this case should be about

2.3 GB + 20 * 26.5 MB = 2.8 GB

however, from "top", I am currently seeing a much higher memory usage:

top - 15:21:14 up 3 days, 16 min,  1 user,  load average: 0.00, 0.03, 0.26
Tasks: 103 total,   1 running, 102 sleeping,   0 stopped,   0 zombie
%Cpu(s):  0.0 us,  0.0 sy,  0.0 ni, 99.7 id,  0.3 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem :  8003556 total,   253772 free,  7393084 used,   356700 buff/cache
KiB Swap: 14679344 total,  4428224 free, 10251120 used.   334716 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
29341 mysql     20   0   17.1g   6.2g   1760 S   0.0 80.8 233:11.06 mysqld

and also

[root@asp3 ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:           7815        7219         246          13         349         326
Swap:         14335       10010        4324

How can I determine why is it using so much memory, where/to what it is allocated, and how to reduce it? In general, I would like the database server to not use more than 4GB of RAM.

EDIT:

We have upped the server RAM from 8 to 16 GB, and with the same exact mysql configuration shown above, I am now seeing this:

top - 12:34:29 up  9:33,  2 users,  load average: 0.18, 0.72, 0.75
Tasks: 114 total,   2 running, 112 sleeping,   0 stopped,   0 zombie
%Cpu(s):  7.0 us,  2.9 sy,  0.0 ni, 49.1 id, 40.3 wa,  0.0 hi,  0.0 si,  0.7 st
KiB Mem : 16261092 total,   151284 free, 15816876 used,   292932 buff/cache
KiB Swap: 14679344 total,  4011528 free, 10667816 used.   102740 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
21542 mysql     20   0   28.9g  14.5g   1980 S   0.0 93.5   2:20.33 mysqld

and

[root@asp3 ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:          15879       15366         202          22         311         167
Swap:         14335       10462        3872

So even doubling the server more RAM, and without changing the configuration, mysql is using double the RAM it was using before.

EDIT 2:

This is the current InnoDB Engine Status:

=====================================
2019-03-04 14:12:14 7f8c146ec700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 779 srv_active, 0 srv_shutdown, 15348 srv_idle
srv_master_thread log flush and writes: 16127
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2521
OS WAIT ARRAY INFO: signal count 2450
Mutex spin waits 4614, rounds 44977, OS waits 901
RW-shared spins 6032, rounds 149583, OS waits 1515
RW-excl spins 9, rounds 498, OS waits 9
Spin rounds per wait: 9.75 mutex, 24.80 RW-shared, 55.33 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 350226263
Purge done for trx's n:o < 350226131 undo n:o < 0 state: running but idle
History list length 91
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 662, OS thread handle 0x7f8c146ec700, query id 3912641 localhost root init
show engine innodb status
---TRANSACTION 350226262, not started
MySQL thread id 661, OS thread handle 0x7f8cac180700, query id 3912640 localhost root 
---TRANSACTION 350196739, not started
MySQL thread id 1, OS thread handle 0x7f8cac202700, query id 0 Waiting for requests
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (write thread)
I/O thread 11 state: waiting for i/o request (write thread)
I/O thread 12 state: waiting for i/o request (write thread)
I/O thread 13 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
132809 OS file reads, 6754 OS file writes, 4910 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1770, seg size 1772, 218 merges
merged operations:
 insert 576, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
1.48 hash searches/s, 0.04 non-hash searches/s
---
LOG
---
Log sequence number 53848288186
Log flushed up to   53848288186
Pages flushed up to 53848288186
Last checkpoint at  53848288186
Max checkpoint age    434154333
Checkpoint age target 420587011
Modified age          0
Checkpoint age        0
0 pending log writes, 0 pending chkp writes
1988 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2249195520; in additional pool allocated 0
Total memory allocated by read views 272
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 50366080    (35406656 + 14959424)
    Page hash           277432 (buffer pool 0 only)
    Dictionary cache    412787997   (8851664 + 403936333)
    File system         31712800    (812272 + 30900528)
    Lock system         5314496     (5313416 + 1080)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 403936333
Buffer pool size        131064
Buffer pool size, bytes 2147352576
Free buffers            56472
Database pages          73681
Old database pages      27307
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 73143, created 538, written 4456
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 73681, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            7021
Database pages          9247
Old database pages      3427
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9175, created 72, written 1238
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9247, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            7246
Database pages          9022
Old database pages      3348
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8955, created 67, written 179
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9022, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            6995
Database pages          9271
Old database pages      3433
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9199, created 72, written 352
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9271, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            6977
Database pages          9290
Old database pages      3440
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9227, created 63, written 531
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9290, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            7027
Database pages          9244
Old database pages      3424
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9182, created 62, written 960
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9244, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            7126
Database pages          9145
Old database pages      3388
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9085, created 60, written 358
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9145, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            6813
Database pages          9457
Old database pages      3506
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9376, created 81, written 599
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9457, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size        16383
Buffer pool size, bytes 268419072
Free buffers            7267
Database pages          9005
Old database pages      3341
Modified db pages       0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 2, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8944, created 61, written 239
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9005, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread process no. 15053, id 140239600166656, state: sleeping
Number of rows inserted 1904, updated 170, deleted 50, read 1767868
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 82.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Answer 1

There is no good formula for the 'max' memory MySQL will use. Some settings are not only per-connection, but per-subquery-per-connection.

free -m implies that there is a lot of RAM in use. But the settings for MySQL do not account for it all. Do you have other applications running? How much space are they consuming?

Baring the puzzling value of free -m, I would suggest these changes:

innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 5
key_buffer_size = 20M  -- assuming you are not using MyISAM
max_connections = 100
tmp_table_size = 64M
max_heap_table_size = 64M
table_open_cache = 2000
table_definition_cache = 1500

But, the real fix may be to re-think the need for 3000 tables.

After you have been running for at least a day (with or without those changes), provide SHOW GLOBAL STATUS; and SHOW VARIABLES;. We can get further details from them.

Some recent changes

----- 2019-02-11 MariaDB 10.2.22 & 2019-01-29 MariaDB 10.4.2 -- -- -----

Fix a memory leak in ALTER TABLE

----- 2019-02-11 MariaDB 10.2.22 & 2019-01-29 MariaDB 10.4.2 -- -- -----

MDEV-15114 : Fix memory leaks

----- 2019-02-06 MariaDB 10.1.38 -- -- -----

Silence LeakSanitizer by default in mariabackup, so that phanthom "leaks" would not hide more interesting information, like invalid memory accesses.

----- 2019-01-02 MariaDB 10.2.21 -- -- -----

Silence LeakSanitizer by default in mariabackup, so that phanthom "leaks" would not hide more interesting information, like invalid memory accesses. Merge Revision #33caaba5c8 2018-12-28 17:40:38 +0200 - Merge 10.1 into 10.2

----- 2018-11-01 MariaDB 10.0.37 & 2018-09-07 MariaDB 10.1.36 & 2018-08-07 MariaDB 10.1.35 & 2018-08-01 MariaDB 10.0.36 -- -- -----

MDEV-16757 Memory leak after adding manually min/max statistical data for blob column

Answer 2

How can I determine why is it using so much memory, where/to what it is allocated, and how to reduce it?

Unfortunately, as far as I know, there isn't a single go-to feature to give an overview of memory usage, so you'll have to look at individual system and status variables, or use a third-party tool that collates them for you.

Since all the tables are InnoDB, at least you don't have to worry about other storage engines. You can do SHOW ENGINE INNODB STATUS\G to get an overview of InnoDB memory usage by going to the 'BUFFER POOL AND MEMORY' section of the output. Perhaps particularly interesting here would be the amount of memory allocated to the dictionary.

As for third-party tools you could try an open-source software like Percona Monitoring and Managmeent (PMM) which among other things gives an overview of memory usage over time, separated into the different pools and buffers used.

There is a possibility that what you are experiencing is some kind of memory leak, especially because despite investigating, it remains a mystery what buffer is using all the extra memory beyond what you have allocated, which is mostly for the InnoDB buffer pool. Normally, in an InnoDB-only MariaDB instance with 2G allocated for the InnoDB buffer pool, you'd expect that to account for most of the instance's used memory.

In general, I would like the database server to not use more than 4GB of RAM.

There is no single setting within MariaDB to control total memory usage. However, if this is running on Linux, there are features in the OS that allows you to limit how much memory a process or user can allocate for themselves. You may want to look into Linux Control Groups, cgroups. See the official cgroups documentation by Redhat.