大网数据广告活动
服务器运维问答

MariaDB using more memory than expected

· 52CE 我爱测

We're running MariaDB server 10.6.16 from the RPM repository https://mirror.mariadb.org/yum/10.6/rhel9-amd64/ on a AlmaLinux 9.3 system with 48 GiB memory (no swap space) and 32 CPU cores. We're exclusively using InnoDB for the d…

问题原文

原帖链接 · stepping stone AG · CC BY-SA 4.0

We're running MariaDB server 10.6.16 from the RPM repository https://mirror.mariadb.org/yum/10.6/rhel9-amd64/ on a AlmaLinux 9.3 system with 48 GiB memory (no swap space) and 32 CPU cores. We're exclusively using InnoDB for the database tables. The system's filesystems run on NVME SSDs via Ceph.

Memory usage steadily increases more and more, triggering the OOM killer now and then.

The formula from https://serverfault.com/a/1020847 returns an upper memory consumption limit of 7500 MiB. I'm aware that this formula is not exact, but the MariaDB service is currently using about 11 GiB of memory, which is much more than the predicted maximum of 7500 MiB. We observed a memory usage of over 15 GiB in the past, just before the OOM killer hit.

What am I missing here?

/proc/[mariadb-pid]/limits:

Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             191442               191442               processes 
Max open files            32768                32768                files     
Max locked memory         8388608              8388608              bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       191442               191442               signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us  

[mysqld] section of the MariaDB configuration:

basedir = /usr
bind_address = 0.0.0.0
binlog_format = mixed
character-set-server = utf8
collation-server = utf8_unicode_ci
datadir = /var/lib/mysql
expire_logs_days = 7
innodb_buffer_pool_size = 4G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_per_table = on
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_stats_on_metadata = off
key_buffer_size = 16M
lc_messages = en_US
lc_messages_dir = /usr/share/mysql
log-bin = /var/log/mariadb/mysql-bin
log-error = /var/log/mysqld.log
long_query_time = 5
max_allowed_packet = 16M
max_connections = 1024
myisam_sort_buffer_size = 8M
net_buffer_length = 8K
pid-file = /run/mariadb/mariadb.pid
port = 3306
query_cache_size = 4194304
query_cache_type = 1
read_buffer_size = 256K
read_rnd_buffer_size = 512K
require_secure_transport = ON
server-id = 1
skip-external-locking
skip-name-resolve
slow_query_log = ON
slow_query_log_file = /var/log/mariadb/slow-queries.log
socket = /var/lib/mysql/mysql.sock
sort_buffer_size = 2M
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ssl_ca = /etc/ssl/mariadb/chain.pem
ssl_cert = /etc/ssl/mariadb/cert.pem
ssl_key = /etc/ssl/mariadb/privkey.pem
table_definition_cache = 8000
table_open_cache = 16000
thread_cache_size = 8
thread_stack = 256K
tmp_table_size = 8M
tmpdir = /tmp
user = mysql

SHOW VARIABLES: https://pastebin.com/raw/NcT5jxiR

SHOW GLOBAL STATUS: https://pastebin.com/raw/B8MFr2WX

SHOW ENGINE INNODB STATUS:

=====================================
2024-02-07 09:51:38 0x7fcf4d171640 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 508984 srv_idle
srv_master_thread log flush and writes: 508978
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 191793820
Purge done for trx's n:o < 191793816 undo n:o < 0 state: running
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7fd05f21e880), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fd05f21dd80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fd05f21bc80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fd05f21b180), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fd05f21a680), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fd05f21d280), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fd05f21c780), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION (0x7fd05f219b80), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
31442319 OS file reads, 3626734 OS file writes, 3262529 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 5.99 writes/s, 5.99 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 65657865607
Log flushed up to   65657865557
Pages flushed up to 65594938073
Last checkpoint at  65594938073
0 pending log flushes, 0 pending chkp writes
3185884 log i/o's done, 6.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4328521728
Dictionary memory allocated 61678248
Buffer pool size   259584
Free buffers       1
Database pages     259583
Old database pages 95802
Modified db pages  5013
Percent of dirty pages(LRU & free pages): 1.931
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 7999092, not young 2607506757
0.00 youngs/s, 0.00 non-youngs/s
Pages read 31427077, created 59965, written 435199
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: 259583, unzip_LRU len: 0
I/O sum[290]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 564360, updated 1722533, deleted 374105, read 92464595804
0.00 inserts/s, 3.00 updates/s, 0.00 deletes/s, 211216.78 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
============================

SELECT COUNT(*) FROM information_schema.tables:

+----------+
| COUNT(*) |
+----------+
|    14520 |
+----------+
1 row in set (0.125 sec)

SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables LIMIT 100000:

+----------+------------------+-------------------+----------------+
| COUNT(*) | sum(data_length) | sum(index_length) | sum(data_free) |
+----------+------------------+-------------------+----------------+
|      228 |           106496 |            106496 |              0 |
+----------+------------------+-------------------+----------------+
1 row in set (0.618 sec)

已采纳解答

原帖链接 · Wilson Hauck · CC BY-SA 4.0

3/1/2024 stepping stone AG
This is likely contributing to your problem.

https://stackoverflow.com/questions/78071133/mariadb-not-releasing-ram-after-jobs-finish

Global Variable SELECT @@version_malloc_library; needs to be jemalloc

The best to you, always.

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2024-02-07)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。