Ubuntu 22.04上MariaDB每隔几天被OOM Killer杀死的解决求助

各位好，我的MariaDB服务器每隔几天就会被强制终止，每次查看服务状态都显示是OOM Killer导致的，具体状态信息如下：

× 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)

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

一开始我怀疑是InnoDB的问题，因为我的服务器只有2GB内存，我按照官方文档确认了缓冲池的设置是正确的，当时的关闭日志显示是正常 shutdown：

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

后来我才发现是系统的OOM Killer在杀死MariaDB进程，systemd的相关日志如下：

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'.

现在我想请教各位，该怎么解决这个问题？我的服务器运行的是Ubuntu 22.04 LTS。

另外，今天查看/var/log/mysql/error.log时，看到了这些完整的日志内容，可能对排查问题有帮助：

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.

备注：内容来源于stack exchange，提问作者Johnathan Nguyen