4

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.
Johnathan Nguyen
  • 41
  • 2
    Welcome to AskUbuntu. There is not enough information here to give you a proper answer. When databases are forcibly shut down by the OS, it's often because of SQL queries that are consuming too much memory. It can be very difficult to track down, but a good first step would be to look at the error log in /var/log/mysql and see if there is a pointer to which SQL query was being run when the OOM process forcibly shut down the database. If the SQL query that is causing the memory issue cannot be found, then it may be necessary to look at how the indexes and swap resources are being used. – matigo Apr 29 '23 at 02:53
  • Welcome. Please don't post screenshots. Please post these as blocks of code. Format the blocks of code by placing a on the line above the block of code and on the line below the block of code (code fencing). Thanks. – mchid Apr 29 '23 at 03:08
  • Sorry, this was my first time posting. I fixed the post and provided more information and removed the screenshots and included code blocks. Let me know if there is any other information that I should include. – Johnathan Nguyen Apr 29 '23 at 03:52
  • 1
    Does this answer your question? How do I disable the systemd OOM process killer in Ubuntu 22.04? – guiverc Apr 29 '23 at 03:54
  • FYI: systemd-oomd was new in 22.04 thus my suggestion of duplicate link; but in most cases the OOMd killed in my experience usually operates because of a system problem that should be explored.. thus consider if the link I tagged actually is best for your system and applications. Systemd-oomd leaves traces in systemd journals showing when/why it killed which you can use for clues & confirmation... – guiverc Apr 29 '23 at 03:56
  • No, it says "Failed to disable unit: Unit file systemd-oomd.service does not exist." – Johnathan Nguyen Apr 29 '23 at 03:58
  • You only have the bare min memory for the OS. With the database as well it may not be enough. – David Apr 29 '23 at 08:03

2 Answers2

1

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

waltinator
  • 36,399
1

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

danblack
  • 111