我的 MariaDB 服务器每隔几天就会被关闭一次。
× 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
首先，我检查了这是否是 InnoDB 的问题，并确保我的缓冲池设置正确，根据文档，因为我的服务器上有 2GB 内存。
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[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.
答案1
您之所以会遇到 OOM killer 问题，是因为您的数据库服务器需要的内存比您拥有的 RAM 多。
解决此问题的最佳方法是设置swap。swap区域是磁盘上的连续区域（文件或整个磁盘分区），用于存储已分配但当前未使用的页面（4KB）。swap将使 OOM 杀手保持安静。
多少swap？1 x RAM 是最低限度，但如果 4 x RAM 对您不起作用，请重新分析您的数据库使用情况。YMMV
阅读man free mkswap swapon fstab
答案2
如果您的整个服务器有 2G 的 RAM，那么将 innodb 缓冲池大小设置为 2147483648 将超出服务器的容量，因为内核和其他 MariaDB 组件也需要 RAM。
建议保持 1G 作为大小，或者根据服务器上运行的其他内容可能更少。
还设置innodb_flush_method=fsync最大限度地利用文件系统缓存（未使用的内存）。