我们有一个服务器,它在一个多租户web应用程序中托管了大约10个客户,每个客户都有自己的数据库,该数据库是从一个模板中克隆出来的,每个数据库的表略少于3000个。
服务器目前有8GB内存,MySQL (实际上是MariaDB 10.1)数据库的配置如下:
# 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如果我正确理解,MySQL应该在这个配置中使用的内存数量应该是大约。
GLOBAL_BUFFERS + MAX_CONNECTIONS * PER_CONNECTION_BUFFERS在这种情况下应该是关于
2.3 GB + 20 * 26.5 MB = 2.8 GB然而,从"top“来看,我目前看到的内存使用量要高得多:
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而且还
[root@asp3 ~]# free -m
total used free shared buff/cache available
Mem: 7815 7219 246 13 349 326
Swap: 14335 10010 4324我如何确定它为什么要使用这么多内存,在哪里/分配什么内存,以及如何减少它?一般来说,我希望数据库服务器不使用超过4GB的RAM。
编辑:
我们已经将服务器RAM从8 GB提高到16 GB,使用上面所示的完全相同的mysql配置,我现在看到的是:
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和
[root@asp3 ~]# free -m
total used free shared buff/cache available
Mem: 15879 15366 202 22 311 167
Swap: 14335 10462 3872因此,即使服务器的RAM增加了一倍,在不改变配置的情况下,mysql使用的RAM也是以前的两倍。
编辑2:
这是当前的InnoDB引擎状态:
=====================================
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
============================发布于 2019-03-04 19:19:13
没有好的公式‘最大’内存MySQL将使用。有些设置不仅是每个连接,而是每个子查询-每个连接.
free -m意味着有很多内存在使用。但是,MySQL的设置并不能解释这一切。您还有其他应用程序在运行吗?它们消耗了多少空间?
考虑到free -m的令人费解的价值,我建议进行以下更改:
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但是,真正的解决办法可能是重新考虑对3000张桌子的需求。
在运行了至少一天(不管是否有这些更改)之后,提供SHOW GLOBAL STATUS;和SHOW VARIABLES;。我们可以从他们那里得到更多的细节。
-- 2019-02-11 MariaDB 10.2.22 & 2019-01-29 MariaDB 10.4.2
修复ALTER中的内存泄漏
-- 2019-02-11 MariaDB 10.2.22 & 2019-01-29 MariaDB 10.4.2
MDEV 15114:修复内存泄漏
- 2019-02-06 MariaDB 10.1.38
默认情况下,在mariabackup中保持LeakSanitizer的沉默,这样phanthom“泄漏”就不会隐藏更有趣的信息,比如无效的内存访问。
- 2019-01-02 MariaDB 10.2.21
默认情况下,在mariabackup中保持LeakSanitizer的沉默,这样phanthom“泄漏”就不会隐藏更有趣的信息,比如无效的内存访问。合并修订#33caaba5c8 2018-12-28 17:40:38 +0200 -合并10.1为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
手动添加blob列的最小/最大统计数据后的MDEV 16757内存泄漏
发布于 2019-03-04 13:10:26
我如何确定它为什么要使用这么多内存,在哪里/分配什么内存,以及如何减少它?
不幸的是,据我所知,没有一个功能可以概述内存的使用情况,所以您必须查看单个系统和状态变量,或者使用第三方工具为您整理它们。
因为所有的表都是InnoDB,至少您不必担心其他存储引擎。您可以通过SHOW ENGINE INNODB STATUS\G访问输出的“缓冲池和内存”部分来了解InnoDB内存的使用情况。也许这里特别有趣的是分配给字典的内存量。
至于第三方工具,您可以尝试像Percona监测和管理这样的开放源代码软件,其中包括概述内存使用情况,并将其分为不同的池和缓冲器。
您所经历的可能是某种类型的内存泄漏,特别是因为尽管进行了调查,但缓冲区使用的额外内存(主要是用于InnoDB缓冲池)之外的所有额外内存仍然是一个谜。通常,在仅为InnoDB分配2G用于InnoDB缓冲池的InnoDB实例中,您会期望该实例的大部分已使用内存都是由该实例分配的。
一般来说,我希望数据库服务器不使用超过4GB的RAM。
MariaDB中没有控制总内存使用的单一设置。但是,如果这是在Linux上运行的,那么操作系统中的一些特性允许您限制一个进程或用户可以为自己分配多少内存。您可能需要查看,cgroup。请看红帽的官方cgroup文档。
https://dba.stackexchange.com/questions/230961
复制相似问题