I'd appreciate assistance in helping me understand a few errors I see after mysqld crashes.
The MySQL log shows the following
180113 3:31:13 InnoDB: The InnoDB memory heap is disabled
180113 3:31:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180113 3:31:13 InnoDB: Compressed tables use zlib 1.2.7
180113 3:31:13 InnoDB: Using Linux native AIO
180113 3:31:13 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137756672 bytes) failed; errno 12
180113 3:31:13 InnoDB: Completed initialization of buffer pool
180113 3:31:13 InnoDB: Fatal error: cannot allocate memory for the buffer pool
180113 3:31:13 [ERROR] Plugin 'InnoDB' init function returned error.
180113 3:31:13 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
180113 3:31:13 [ERROR] mysqld: Out of memory (Needed 128917504 bytes)
180113 3:31:13 [Note] Plugin 'FEEDBACK' is disabled.
180113 3:31:13 [ERROR] Unknown/unsupported storage engine: InnoDB
180113 3:31:13 [ERROR] Aborting
MySQL was setup with default my.cnf settings which allocates 128M to innoDB buffer pool. I've recently changed this to a larger size, but it's too early to tell if the new setting will have any affect. (I've increased the buffer pool size to 512M going against many suggestions to reduce it's size. My thinking is that the 128M buffer pool is inadequate to handle the demands of indexes etc, and that is why mysql is crashing in the first place. The reason mysql reports out of memory could just be the default error event for memory related issues.)
My my.cnf file currently looks like this:
innodb_file_per_table = 1
thread_concurrency = 8
query_cache_size = 16M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 4M
read_buffer_size = 1M
sort_buffer_size = 1M
table_open_cache = 256
max_allowed_packet = 1M
key_buffer_size = 256M
innodb_buffer_pool_size = 512M
My problem initially was in the the fact that my server is not reaching close to the memory limit before the crash as you can see this from this virtualmin system stats graph. Could anyone explain why mysql would be crashing and reporting out of memory but memory usage is nowhere near the servers limit? I'm assuming if apache were experiencing excessive load it would show in memory usage and CPU usage, but there is no CPU spike when the server crashes. As mentioned above, mysql might be throwing a default memory exception error when the buffer pool is too small to handle demand.
A side query in handling this issue is to allocate a swap file. In line with a response in this article, how do I allocate a system swap to mysql? A swap was suggested as another option to avoid mysqld crashing. I have checked in Webmin--> Disk and Network Filesystems and there are already 4 RAM/Swap Disk (tmpfs). Why would they not be assisting with the mysql memory issue? Do I need to do set up some specific configuration for a swap to help in my scenario?
I referenced the following articles in researching this problem:
https://www.digitalocean.com/community/questions/mysql-server-keeps-stop... https://stackoverflow.com/questions/12114746/mysqld-service-stops-once-a... https://askubuntu.com/questions/457923/why-did-installation-of-mysql-5-6... https://dba.stackexchange.com/questions/10496/mysql-said-innodb-initiali... https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
A previous issue I have had relating to mysql and reported on Virtualmin: