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)
