Unexpected RAM usage on MySQL
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:
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 80% 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
============================