#StackBounty: #mariadb #memory #mariadb-10.1 Unexpected RAM usage on MySQL

Bounty: 100

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
============================


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.