Apache has got out of control over the past few days and made MySQL crash twice. It all started when I migrated a WordPress website over which also contains a phpBB forum.
I'm not very experienced in server admin so it's been very difficult for me to pinpoint what is causing the issue. When I noticed that MySQL was down, I ran TOP and saw my system-load spike to 98.00. The server runs 10 V-HOSTS all of which receive a healthy amount of traffic so I was obviously seeing many apache-2 processes running.
The high server load continued for 10 mins and then it returned to a normal state. I didn't see a spike of network traffic at this point.
Unfortunately, MySQL error logging was disabled (it's now re-enabled) so no clues there. But I'm pretty sure it is because Apache was consuming all resources, so the MySQL process ID was killed.
My questions are:
Next time this occurs – how can I identify what is causing the system load spike? Could it be a php script that went crazy? Could it be a DDOS attack?
Is there a way of automatically re-starting MySQL when it crashes?
I have now installed htop. Could this be more useful than top?
Here my server stats:
m1.xlarge (8 ECUs, 4 vCPUs, 15 GiB memory, 4 x 420 GiB Storage Capacity)
Ubuntu Server 12.04.3 LTS
Best Answer
MySQL may still not log anything, because what is likely happening is that it is being unceremoniously killed by the system due to system memory pressure from apache's children. There should be a trail of this in /var/log/syslog.
MySQL should try to restart itself in a crash or forced termination, but unless sufficient memory is available, it can't do that... and this second failure is not seen by mysqld_safe as a "crash" but rather as a "refusal to start," so it will not keep trying. The failed restart attempt is often misinterpreted by admins as the "crash," since the nature of the original failure is hidden behind an easily-overlooked message in the MySQL error log:
See InnoDB Crash Post Mortem for a circumstance that I suspect is similar to yours.
The seemingly simple answer to "why" is that between Apache and MySQL, the load you have, and your current configs, you don't have enough memory on the machine, and there's some tipping point related to traffic load that brings this condition out.
Apache serves each concurrent browser request from a child process, so of the number of concurrent connections rises, the number of children will increase. You will first need to limit this value in the apache config so that you can understand what's actually causing the increase in concurrent connections... is it simply a heavy but legitimate traffic spike? Some kind of denial of service? DB queries that delay requests because they run too long? Something needing optimizing?
http://httpd.apache.org/docs/2.2/mod/mpm_common.html#maxclients
Limiting concurrent Apache processes should help prevent this, but to be clear, it is naive to think that this is the full solution, so I don't want to imply that. Once processes are limited to a reasonable or at least safer level, you can proceed with identifying what's really going on. (There are other restraint controls on Apache, but that isn't my area of expertise.)
The "best practice" is of course to run your database on different hardware so that the application can't kill it. While it seems more efficient, on the surface, to "maximize utilization" of one machine by sharing it, this is a false economy. The majority of memory used by MySQL, in a typical workload, is allocated at startup time, and held for as long as MySQL Server is running. The demands on the CPU are likely to share peak times for MySQL and Apache, since they are ultimately serving the same load. You might actually be better off with two m1.large machines instead of the single m1.xlarge, and the cost would be the same since the smaller one is exactly half the price of the larger one... even if you already paid in advance for the additional discount, this change can be accomplished.