Join Log in
Score:1
Johnathan Nguyen avatar Ubuntu

MariaDB killed by OOM Killer every couple days

ie flag
Johnathan Nguyen

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.
1773
2 + 7
ram
in flag
matigo
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.
2
Reply
mchid avatar
bo flag
mchid
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.
0
Reply
Johnathan Nguyen avatar
ie flag
Johnathan Nguyen
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.
0
Reply
guiverc avatar
cn flag
guiverc
Does this answer your question? [How do I disable the systemd OOM process killer in Ubuntu 22.04?](https://askubuntu.com/questions/1404888/how-do-i-disable-the-systemd-oom-process-killer-in-ubuntu-22-04)
1
Reply
guiverc avatar
cn flag
guiverc
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...
0
Reply
Johnathan Nguyen avatar
ie flag
Johnathan Nguyen
No, it says "Failed to disable unit: Unit file systemd-oomd.service does not exist."
0
Reply
David avatar
cn flag
David
You only have the bare min memory for the OS. With the database as well it may not be enough.
0
Reply
Score:0
waltinator avatar Ubuntu
it flag
waltinator

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

+ 0
Score:0
danblack avatar Ubuntu
ck flag
danblack

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

+ 0
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.