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
============================
Best Answer
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 -mimplies 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: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;andSHOW 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