Last days mysql server on my vps start crashing, sometimes right after restart.
I found that problem in wordpress website. After service mysql start many apache2 processes overload memory and cpu. This is screenshot of top.
I didn't find anything usefull from apache logs, mysql error log show messages about lack of memory:
140922 12:24:43 [Note] Plugin 'FEDERATED' is disabled.
140922 12:24:43 InnoDB: The InnoDB memory heap is disabled
140922 12:24:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140922 12:24:43 InnoDB: Compressed tables use zlib 1.2.8
140922 12:24:43 InnoDB: Using Linux native AIO
140922 12:24:43 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
140922 12:24:43 InnoDB: Completed initialization of buffer pool
140922 12:24:43 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140922 12:24:43 [ERROR] Plugin 'InnoDB' init function returned error.
140922 12:24:43 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140922 12:24:43 [ERROR] Unknown/unsupported storage engine: InnoDB
140922 12:24:43 [ERROR] Aborting
140922 12:24:43 [Note] /usr/sbin/mysqld: Shutdown complete
First I thought maybe problem in db and checked database with mysqlcheck --all-databases but all ok, I tried drop and create new database, no luck.
Also I tried to delete wordpress plugins folder. WordPress is last version, comments disabled, 3 users, little amount of visitors (corporate blog).
Now I don't know where to look and how to diagnose problem, mysql lives no more than 10-20 sec if wordpress config enabled in apache.
UPDATE: I'm deleted WP folder and download new WP without any config. If wp site is enabled, vps starts overloading, apache can't answer to requests. Without WP enabled all works smooth. I don't destroy this droplet yet because I want found the cause of the problem. Maybe this is some exploit in vps?
UPDATE2: after searching in logs and netstat I found that problem appears when:
- wordpress site was enabled and apache with mysql works
- Some request from dummy bot for rpc.php or something like this set apache connection in CLOSE_WAIT, after several request apache had many workers in CLOSE_WAIT condition, as result many workers and luck of memory (yes I should reduce max, but anyway site stopped work when all workers waiting)
- MySQL falling with luck of memory (it's not the problem as suggested in answer it's consequence of another problems, and upgrade VM not solve the issue)
- After mysql falling overload stops. (this led me to think that perhaps the database is corrupted, but after checking db not errors were found)
What reason may enter apache in CLOSE_WAIT state after dummy request for missing file?
Best Answer
It looks like you simply ran out of ram as the error suggets
cannot allocate memory for the buffer pool.Your options are:
Your apache alone is consuming all the memory your system can afford. There is no room left for your mysql. You will want to tweak your apache settings so that it consumes less memory. Biggest impact should come from reducing max children criteria of your apache settings. Using your biggest apache instance as an example, it seems you can only afford 14 children at max before the system will crash from lack of ram which is ~34MB.
If each of your apache is expected to take upto 30MB for example, and you have 128MB for database and another 128MB for the OS, we could do very simplified math. 512 (total) - 128 (db) - 128 (os) will leave 256 for apache. 30MB each means you can only afford 8 apache children. Though there are more, we can at least see 22 children in your top screenshot.
512MB is a small amount to run a site, you will need to do a lot of tweaking in order to optimize the little ram that you have. Also, an engine like InnoDB may not be a good solution for you which has high dependency on RAM. Another engine like myisam may suit your needs better.