MySQL consuming all the ram and crashing

Hi. I've an ispconfig server running on ubuntu 12.04. The sever is a virtual xen host with 4 cores and 8Gb of ram. I'm running ~300 websites there, mostly wordpress. All vhosts are served by php-fpm.
The machine runs fine, load is around 1.5 or even less, memory around 40/50% at most.
Sadly sometimes mysql get mad and start consuming all the available ram, pumping up load until kernel's oom kills the process and so the box returns usable.
I was using customised mysql parameters based in mysqltuner suggestions. Now I removed them and it's a stock ubuntu config, still this situation happens from time to time. Sometimes twice a day, sometime once a week.
I'm a little puzzled because I don't know how to investigate this. I haven't found anything into system or mysql logs.
Should I enable mysql full query log? Is there anything else I could check?
I have zabbix monitoring the server but as soon as the load spikes it stops logging.

Any advice is welcome.
Thanks

 

are you using mysql 5.6? if so, check the value of performance_schema in my.cnf. Try to set it to: performance_schema = 0 and see if the problem persists

 

sadly not, I'm still on 5.5.44-0ubuntu0.12.04.1

 

Are you 100% sure it's mysql that's causing your memory spike? it couldn't be apache for sure? If it could be apache, could you post your version?

 

Not 100% sure, because I never managed to be "in" when one of these happened. For sure MySQL is the one taking the most ram.
Apache 2.2.22-1ubuntu1.10
php5 5.3.10-1ubuntu3.19
all sites are configured with fpm in ondemand mode.

 

yeah, i asume that mysql itself is the problem then. Make sure that you log mysql it's memory usage. if you don't have a tool to do this, you could use this simple script:

Code:

while true
do
  date >> ps.log
  ps aux | grep mysqld >> ps.log
  sleep 60
done

 

thanks but adding this won't help prevent the problem.
I was considering limiting the max number of connections per user, but I don't see this option in ISPConfig :/

 

I meant this http://dba.stackexchange.com/a/35238/57892

 

oh I missed it! thank you very much!

 

I added `max_user_connections` but sadly it doesn't seem to be enogh, the server just suffered another crash. From what I could catch from other_vhosts.log I got tens of visits from the Google favicon bot, which seems legit. But these kind visits were very much, and from different IPs. Having so many visits (just to the homepage, tough) to so many hosts opened more than 80 connections to MySQL, and the server went out of memory again.

I was now considering setting a maximum limit of allowed connections, in order to avoid resource exaustion and consequent mysql crash, but this will still cause some sites to return mysql connection errors...
Is there a better approach? thanks

 

You should check the OS logs /var/log/messages (i think) to verify exactly which process OOM killed due to high memory usage. I don't often see mysqld killed, it's usually httpd. But it's easy to verify exactly which process is being killed.

btw, I signed up for an account just to post that, lol

 

thanks for your effort, but from the syslog it's really mysqld being oom-killed.