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) 
Max_used_connections=205withUptime=512543. Pretty sure you can tune downmax_connectionsfrom 1024.