MySQL (MariaDB) crashes frequently
I hope it will be helpful to you.
try
performance_schema = off
in [mysqld]
section of your configuration.
https://mariadb.com/resources/blog/starting-mysql-on-low-memory-virtual-machines/
Related videos on Youtube
Matt Hamann
Updated on September 18, 2022Comments
-
Matt Hamann over 1 year
I recently migrated an old server running MySQL to a new VPS running MariaDB 5.5. I don't have too much running on the server (just a few PHP sites) and free memory seems to be OK, but the DB keeps crashing--sometimes every few days, other times within a few hours.
I receive the following errors in the logs:
131231 1:43:04 [ERROR] mysqld: Out of memory (Needed 128917504 bytes) 131231 1:43:04 [ERROR] mysqld: Out of memory (Needed 96681984 bytes) 131231 1:43:04 [ERROR] mysqld: Out of memory (Needed 72499200 bytes) 131231 1:43:04 [ERROR] mysqld: Out of memory (Needed 54362112 bytes) 131231 1:43:04 InnoDB: The InnoDB memory heap is disabled 131231 1:43:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins 131231 1:43:04 InnoDB: Compressed tables use zlib 1.2.3.4 131231 1:43:04 InnoDB: Using Linux native AIO 131231 1:43:04 InnoDB: Initializing buffer pool, size = 128.0M InnoDB: mmap(137756672 bytes) failed; errno 12 131231 1:43:04 InnoDB: Completed initialization of buffer pool 131231 1:43:04 InnoDB: Fatal error: cannot allocate memory for the buffer pool 131231 1:43:04 [ERROR] Plugin 'InnoDB' init function returned error. 131231 1:43:04 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 131231 1:43:04 [Note] Plugin 'FEEDBACK' is disabled. 131231 1:43:04 [ERROR] Unknown/unsupported storage engine: InnoDB 131231 1:43:04 [ERROR] Aborting 131231 1:43:04 [Note] /usr/sbin/mysqld: Shutdown complete
I've played with the my.cnf settings for InnoDB Heap, which does not seem to help. Here's the relevant portion:
innodb_buffer_pool_size = 128M innodb_log_buffer_size = 8M innodb_file_per_table = 1 innodb_open_files = 400 innodb_io_capacity = 400 innodb_flush_method = O_DIRECT
I seem to have "plenty" of free RAM, and I have some swap available as well:
root@phoenix:~# free -m total used free shared buffers cached Mem: 994 923 71 0 19 417 -/+ buffers/cache: 486 508 Swap: 1023 131 892
How can I solve / troubleshoot this issue? I've scoured the interwebz for clues, but nothing has helped.
-
Zoredache over 10 yearsWhat does the memory look like right before it crashes? The
[ERROR] mysqld: Out of memory
sure looks like a strong hint. Did you check your system logs to see if the kernel (OOM) decided to kill mysql? -
Ashwin over 10 yearsYou should log the file handles used and the memory used both before and after the crash (maybe even the dump of
top
command every interval) to know what could cause the crash. Any other services run in your servers like mysql (mariadb)? -
Matt Hamann over 10 yearsThere's a really great answer over on DBA that seems to be helpful: dba.stackexchange.com/questions/1927/…
-
Admin over 9 yearsTry performance_schema = off ?
-
-
Matt Hamann about 9 yearsGiving this a try. Will report back soon after I give the service a few days to stay up and running consistently.
-
Matt Hamann almost 9 yearsSeems to have helped quite a bit. Not sure 100% solved, but it's been running pretty well since I adjusted this. Thanks.