0

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.

enter image description here

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:

  1. wordpress site was enabled and apache with mysql works
  2. 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)
  3. 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)
  4. 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?

  • You cut out the most important part of a top output, ie the top part of it. Please post that as well. – Grumpy Sep 22 '14 at 16:20
  • @Grumpy added new screenshot – Sonique Sep 22 '14 at 16:35
  • 1
    You have too many Apache processes working. You should tune that down significantly. – Michael Hampton Sep 22 '14 at 19:46
1

It looks like you simply ran out of ram as the error suggets cannot allocate memory for the buffer pool.

Your options are:

  • Upgrade the (virtual) hardware so that it has more ram
  • And/Or get more swap
  • Reduce RAM usage by other application, namely apache.

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.

  • System works about 8 month without any troubles and lack of resources. Wordpress website with 0 visitors (I disable network access) stopped mysql - this is problem. – Sonique Sep 22 '14 at 16:52
  • @Sonique Just because you have 0 active visitors right now doesn't mean apache will suddenly release all resources. It only means it'll stop demanding new ones. I suspect your apache has other problematic configuration as well. But first thing you need is to reconfigure your apache and restart apache so that it's at a fresh state rather than the current state which hogs all the resources. It will not let go of them unless you restart right now. – Grumpy Sep 22 '14 at 16:57
  • To change your buffer pool settings, alter the innodb_buffer_pool_size=256M statement in your /etc/my.cnf file. You'll probably want to set it quite a lot lower than the optimal size if you're short on ram (optimal size is "a lot" for a database server). – Some Linux Nerd Sep 22 '14 at 19:22
  • Thanks, I made this, but problem not gone. I did update of post with new info, main problem maybe in CLOSE_WAIT apache child state.(?) – Sonique Sep 25 '14 at 7:42
  • @Sonique If a bot request to some php file causes a lot of close_waits, then it's possible you're under an attack with slowloris method. Or you have a bad program. Or bad setting. Or all of the above. But ultimately, you aren't managing your memory very well. That makes it vulnerable to attacks and prone to instability. It's possible to diagnose an outright bad setting by having you post your apache, php & mysql configs, but others are not something feasible to discuss to a solution over a forum. I'd suggesting hiring a professional. – Grumpy Sep 26 '14 at 4:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.