MariaDB killed by OOM Killer every couple days

Question

my MariaDB server is being killed every couple days.

× mariadb.service - MariaDB 10.6.12 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: failed (Result: oom-kill) since Wed 2023-04-19 08:27:06 UTC; 15h ago
Docs: man:mariadbd (8)
https://mariadb.com/kb/en/library/systemd/
Process: 1178 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=killed, signal=KILL) Main PID: 1178 (code=killed, signal-KILL)
Status: "Taking your SQL requests now..."
CPU: 1h 29.637s

First, I checked if it was an issue with InnoDB and I made sure my buffer pool was set correctly, according to the documentation since I have 2GB of memory on my server.

2023-04-20 2:19:52 0 [Note] /usr/sbin/mariadbd (initiated by: unknown): Normal shutdown 2023-04-20 2:19:52 0 [Note] InnoDB: FTS optimize thread exiting.
2023-04-20 2:19:52 0 [Note] InnoDB: Starting shutdown...
2023-04-20 2:19:52 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool 2023-04-20 2:19:52 0 [Note] InnoDB: Restricted to 2028 pages due to innodb_buf_pool_dump_pct=25 2023-04-20 2:19:52 0 [Note] InnoDB: Buffer pool(s) dump completed at 230420 2:19:52 2023-04-20 2:19:52 0 [Note]
InnoDB: Removed temporary tablespace data file: "./ibtmpl" 2023-04-20 2:19:52 0 [Note] InnoDB: Shutdown completed; log sequence number 83083437260; transaction id 17055849 2023-04-20 2:19:52 0 [Note] /usr/sbin/mariadbd: Shutdown complete

Then, I realized that it was the OOM Killer that is killing the MariaDB process.

systemd[1]: mariadb.service: A process of this unit has been killed by the OOM killer. systemd[1]: mariadb.service: Main process exited, code=killed, status=9/KILL
systemd[1]: mariadb.service: Failed with result 'oom-kill'.

How should I go about fixing this? I am running Ubuntu 22.04 LTS on my server.

When looking at /var/log/mysql/error.log, I see the following for today.

2023-04-27  2:47:14 0 [Note] Starting MariaDB 10.6.12-MariaDB-0ubuntu0.22.04.1 source revision  as process 1784903
2023-04-27  2:47:14 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-04-27  2:47:14 0 [Note] InnoDB: Number of pools: 1
2023-04-27  2:47:14 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-04-27  2:47:14 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2023-04-27  2:47:14 0 [Note] InnoDB: Completed initialization of buffer pool
2023-04-27  2:47:14 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=84733028486,84733489023
2023-04-27  2:47:14 0 [Note] InnoDB: Starting a batch to recover 6771 pages from redo log.
2023-04-27  2:47:15 0 [Note] InnoDB: Starting final batch to recover 2462 pages from redo log.
2023-04-27  2:47:15 0 [Note] InnoDB: 128 rollback segments are active.
2023-04-27  2:47:15 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2023-04-27  2:47:15 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-04-27  2:47:15 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-04-27  2:47:15 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-04-27  2:47:15 0 [Note] InnoDB: 10.6.12 started; log sequence number 84784396439; transaction id 17923460
2023-04-27  2:47:15 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-04-27  2:47:15 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-04-27  2:47:15 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
2023-04-27  2:47:15 0 [Note] Server socket created on IP: '127.0.0.1'.
2023-04-27  2:47:15 0 [Note] /usr/sbin/mariadbd: ready for connections.
Version: '10.6.12-MariaDB-0ubuntu0.22.04.1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 22.04
2023-04-27  2:47:15 0 [Note] InnoDB: Buffer pool(s) load completed at 230427  2:47:15
2023-04-27 11:50:57 0 [Note] /usr/sbin/mariadbd (initiated by: unknown): Normal shutdown
2023-04-27 11:50:57 0 [Note] InnoDB: FTS optimize thread exiting.
2023-04-27 11:50:57 0 [Note] InnoDB: Starting shutdown...
2023-04-27 11:50:57 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2023-04-27 11:50:57 0 [Note] InnoDB: Restricted to 2028 pages due to innodb_buf_pool_dump_pct=25
2023-04-27 11:50:57 0 [Note] InnoDB: Buffer pool(s) dump completed at 230427 11:50:57
2023-04-27 11:50:57 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2023-04-27 11:50:57 0 [Note] InnoDB: Shutdown completed; log sequence number 84880339314; transaction id 17971478
2023-04-27 11:50:57 0 [Note] /usr/sbin/mariadbd: Shutdown complete
2023-04-27 11:50:57 0 [Note] Starting MariaDB 10.6.12-MariaDB-0ubuntu0.22.04.1 source revision  as process 1886019
2023-04-27 11:50:57 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2023-04-27 11:50:57 0 [Note] InnoDB: Number of pools: 1
2023-04-27 11:50:57 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2023-04-27 11:50:57 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728 
2023-04-27 11:50:57 0 [Note] InnoDB: Completed initialization of buffer pool
2023-04-27 11:50:57 0 [Note] InnoDB: 128 rollback segments are active.
2023-04-27 11:50:58 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2023-04-27 11:50:58 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2023-04-27 11:50:58 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2023-04-27 11:50:58 0 [Note] InnoDB: 10.6.12 started; log sequence number 84880339314; transaction id 17971479
2023-04-27 11:50:58 0 [Note] Plugin 'FEEDBACK' is disabled.
2023-04-27 11:50:58 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2023-04-27 11:50:58 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
2023-04-27 11:50:58 0 [Note] Server socket created on IP: '127.0.0.1'.
2023-04-27 11:50:58 0 [Note] /usr/sbin/mariadbd: ready for connections.
Version: '10.6.12-MariaDB-0ubuntu0.22.04.1'  socket: '/run/mysqld/mysqld.sock'  port: 3306  Ubuntu 22.04
2023-04-27 11:50:58 0 [Note] InnoDB: Buffer pool(s) load completed at 230427 11:50:58
2023-04-27 12:14:23 44 [Note] InnoDB: Requested to resize buffer pool. (new size: 2147483648 bytes)
2023-04-27 12:14:23 0 [Note] InnoDB: Resizing buffer pool from 134217728 to 2147483648 (unit=134217728).
2023-04-27 12:14:23 0 [Note] InnoDB: Disabling adaptive hash index.
2023-04-27 12:14:23 0 [Note] InnoDB: Withdrawing blocks to be shrunken.
2023-04-27 12:14:23 0 [Note] InnoDB: Latching whole of buffer pool.
2023-04-27 12:14:23 0 [Note] InnoDB: buffer pool resizing with chunks 1 to 16.
2023-04-27 12:14:23 0 [Note] InnoDB: 15 chunks (121680 blocks) were added.
2023-04-27 12:14:23 0 [Note] InnoDB: Resizing also other hash tables.
2023-04-27 12:14:23 0 [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
2023-04-27 12:14:23 0 [Note] InnoDB: Completed to resize buffer pool from 134217728 to 2147483648.
2023-04-27 12:14:23 0 [Note] InnoDB: Completed resizing buffer pool at 230427 12:14:23.

score 3 · Answer 1 · answered Jun 05 '23 at 01:06

If your entire server has 2G of ram, then setting the innodb buffer pool size to 2147483648 will exceed the capacity of the server as the kernel and other MariaDB components need RAM too.

Recommend keeping 1G as the size, and maybe less depending on what else is running on the server.

Also set innodb_flush_method=fsync to maximise the use of filesystem cache (unused memory).

score 1 · Answer 2 · answered Apr 30 '23 at 01:18

You're getting the OOM killer because your database server wants more memory than you have RAM.

The best way to work around this problem is to set up swap. A swap area is a contiguous area on disk (either a file or a whole disk partition) used to store allocated but not currently in-use pages (4KB). swap will keep the OOM killer quiet.
How much swap? 1 x RAM is a minimum, but if 4 x RAM doesn't do it for you, re-ananlze your database use. YMMV
Read man free mkswap swapon fstab

sachin · Answer 3 · 2024-09-01T02:06:43.287

0

Setting innodb_flush_method=fsync as suggested by @danblack showed immediate improvement on my server with 2 GB RAM/ 1.50 GB swap.

edited Sep 01 '24 at 02:06

answered Sep 01 '24 at 01:52

sachin

1

MariaDB killed by OOM Killer every couple days

3 Answers3