My Mysql unexpected shutdown and wouldn’t restart until I shutdown the whole server
When I inspected the error log it gave me this set of errors
2020-05-13 11:12:40 0 [ERROR] mysqld: Out of memory (Needed 128663552 bytes)
2020-05-13 11:12:40 0 [ERROR] mysqld: Out of memory (Needed 96485376 bytes)
2020-05-13 11:12:40 0 [Warning] InnoDB: innodb_open_files 300 should not be greater than the open_files_limit 155
2020-05-13 11:12:40 0 [Note] InnoDB: Using Linux native AIO
2020-05-13 11:12:40 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-05-13 11:12:40 0 [Note] InnoDB: Uses event mutexes
2020-05-13 11:12:40 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-05-13 11:12:40 0 [Note] InnoDB: Number of pools: 1
2020-05-13 11:12:40 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-05-13 11:12:40 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)
2020-05-13 11:12:40 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-05-13 11:12:40 0 [ERROR] InnoDB: mmap(134217728 bytes) failed; errno 12
2020-05-13 11:12:40 0 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2020-05-13 11:12:40 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-05-13 11:12:40 0 [Note] InnoDB: Starting shutdown…
Basically I’m out of memory. and also that “innodb_open_files 300 should not be greater than the open_files_limit 155”
What shall I do to fix these problems from happening in the future?
Increase your memory size?
Well, this solution sounds too easy. I’m trying to squeeze the most out of the server before upgrading memory.
What about the third line that says “[Warning] InnoDB: innodb_open_files 300 should not be greater than the open_files_limit 155”
Do I need more memory too?
HestiaCP is not a 1 thing solves all the sys admin problems it makes most of the admin task easier. But you still need to modify / monitor the performance and with the help of Google Allot of issues can be solved.
@bgbs you should be more informative at least
your sql engine tries to allocate 128MB for innodb and obviously fails. how small is that box you are trying to get this working on? maybe avoid using clamav and spamassassin, as these two are ressource heavy but not essential, which makes them the best candidates to go without.
It’s a Digital Ocean 1GB Droplet.
I don’t have clamav or spamssassin installed on the server, and I have dovecot, exim4, bind9 disabled. So, I’m not sure what is eating up all the memory. When I run the command free -h it tells me that:
- 985M total
- 227M used
- 89M free
- 132M shared
- 669M buff/cache
- 461M available
Basicly you only have 89M free, we can not know here what exactly consumes how much of it. Honestly I dont think we can help you out here, otherwise we would have to check your server directly, but this would be paid support.
it should run fine on a 1GB vm anyway. that 89M free now is only after buffers/cache, so there’s actually plenty of room, as the systems frees up the memory used for those buffers/cache, if needed elsewhere.
as @ScIT already mentioned, we can’t tell why your memory obviously runs full (real use, not just cache) at some point so that mysql dies because it can’t allocate any more memory. it hints towards something else creating a memory leak or allocating everything available and leaving nothing for the rest of the services.
what kind of page or app are you running? have you set a high memory limit in php? maybe there is something forcing php to eat up a lot of memory? could be during cleanup or backup processes from greedy apps like magento or cloud-stuff etc.
essentially check everything and try to set low memory limits (as pointed out esp. php comes to mind) to keep enough wiggle room for all services.
Of course @falzo is right with the cached ram part, but the problem is still the same: “something” consumes ram, please follow @falzo ideas to track down the problem and let us know if you got it fixed.
Thank you so much guys, I will investigate this further.
Generally speaking, I hove some test boxes with apache+nginx+mysql+exim+dovecot and are running fine. MySQL never complained
I’m also using some guidance from MySQL Tuning Primer script.
Using MySQL with innoDB based DBs on less than 2 GB RAM is fatal… you just don’t have any room for the buffers and they’d overrun. Also check and see if you have atleast 2GB SWAP space and check swappiness values, MySQL is really pesky about SWAP. But Nutshell, use atleast a 2GB RAM VPS with anything MySQL intensive app.