MyBB Community Forums

I have a fairly good VPS - quad core, 2gb RAM, 50gb disk, all really fast but...

since I've switched to Debian and Lighttpd, we keep getting PHP and MySQL randomly hogging CPU and the whole machine grinding to a halt in a flood of processes (mostly php-cgi) and dying due to maxed out CPU.

We suspect it might be members searching through our 350mb database and/or configuration issues related to this. These queries seem to take between 10 and 45 seconds in the MySQL slow queries log, although the actual time for the search results to show is much quicker.

What should I do?

Thanks guys,

Tidal Wave

if it is really search related, consider sphinx search and mysql 5.5 with innodb tables

Here is my MySQL version

mysql Ver 14.14 Distrib 5.5.30, for debian-linux-gnu (x86_64) using readline 6.1

I don't think I'm using innodb tables am I? I'm just using default MyBB... HOWEVER, our database was originally converted from phpBB (years ago now). Does that matter?

I'll look into Sphinx search. This is a tricky one ._.

---

Also, here is my my.cnf:

http://pastebin.com/q2unrNZF

and some of my slow queries log:

http://pastebin.com/AsFJseyb


Some are really long I know... sometimes it might be due to a maxed out CPU though I dunno o.O

Are all the table indexes setup properly?

Anything else running on the same server?
How fast is your disk?

I really don't know how to examine or setup table indexes ._.

I've always just let MyBB do its thing with the DB and not interfered.

It's a VPS this is running on, and I find it suspicious that most of the CPU being used falls in the top "st" section and not "us", suggesting it may potentially be getting allocated to another VPS or something. I'm asking my host about that one!

---

Also, here is what it looks like when it happens:

http://snag.gy/VzRm8.jpg

---

Oh, and disk speed I'm also not sure about so I'll ask my web host

---

This might be of help - some info about some tables in my forum database (the results of SHOW CREATE TABLE and SHOW TABLE STATUS):

http://pastebin.com/49WQuZLP

Ah it's not stolen time ._.

Look...

http://i.imgur.com/gASMyQM.jpg (my site on low CPU)

http://i.imgur.com/RmT9JcW.jpg (my site when it all locks up)

can you try MyTOP when the box slows down? This version supposedly works for MySQL 5.x