MyBB Community Forums

Hello everybody,

I have a problem with my MyBB forum. The problem is that it uses a lot of MySQL or something like that.. I know it because the MySQL processes use a lot of CPU (40%+) everytime. In the bottom of the forum it is printed this:

[attachment=6811]

And in a MyBB forum the PHP usage is more used that the MySQL one (or at least in the others I had have).
Note: It is quite big the forum, but that it isn't the problem of the capacity of the host (it is a big VPS).

Information:

MyBB: 1.2.9
PHP Version: 5.2.3
MySQL: 5.0.27

I have this plugins activated:
Advanced Stats on Index/Portal (v2.5)
Board Message (v1.0.1)
Show Events on Index (v1.5.3)
Forum Warning System (v1.1.2)
Groups legend (v2.0)
PJIRC Chat (v1.1)
PM On Registration (v1.1.0)
Simple Calendar on Portal (v1.1)
Portal Redirect (v1.0.3)
Posts Required To View Forums (v1.0)
Shoutbox (v2.0)
Thread Descriptions (v1.2)
viewe post (v0.8.0)
Welcome Message (v1.1)

In my WMH/CPanel the MySQL process viewer print this:

[attachment=6812]


Sorry for my english.. :/

Any idea? If not, I will have problems with the host company :/.

It's most likely that a plugin is causing this, but I don't know exactly which one. You could disable them one by one and see if the MySQL usage gets lowered.

Go to your debug page when this is happening and look at the slow queries

If this is your own server; I suggest modifying the my.cnf file and entering the following:

[mysqld]
long_query_time = 10
log-slow-queries = /var/log/longquery

Then when you're done, create the file /var/log/longquery by doing:

touch /var/log/longquery

Then CHMOD it to 775 so MySQL user can read/write it.

chmod 775 /var/log/longquery

From time to time; I'd suggest looking at that file; it will log all the queries taking more than 10 seconds. If you think that number is too low; change the long_query_time to how many seconds you think is appropriate.

Also; you can also run this cronjob I have made which checks if there is any new long queries and send it to you via email.

#!/bin/bash

LOGSIZE=`cat /var/log/longquery | wc -l`
LOG=`cat /var/log/longquery`

if [ "$LOGSIZE" != "1" ]
then
 echo "$LOG"
 echo '' > /var/log/longquery
fi

Simply save that somewhere and then:

crontab -e

Place the following in it:

*/5 * * * * /scripts/longquerycheck

It will run each 5 minutes and check if there is any long queries; if there are not; it won't report anything; however; I'd suggest making the cronjob output the results to an email you actually read (Usually people don't bother reading root email).

You can recieve emails sent to root by logging into WHM > "Server Contacts" > "Change System Mail Preferences". -- Make sure root mail is forwarded to something you read; then you'll get the results from the cronjob.

destroyer Wrote:It's most likely that a plugin is causing this, but I don't know exactly which one. You could disable them one by one and see if the MySQL usage gets lowered.

I will look for the causant of this high MySQL usage..

Tikitiki Wrote:Go to your debug page when this is happening and look at the slow queries

This is an image of my debug screen.. there are some 'plugin hooks' that are logn queries, or not? (I don't know about MySQL basicaly..):

nvez Wrote:If this is your own server; I suggest modifying the my.cnf file and entering the following:

[mysqld]
long_query_time = 10
log-slow-queries = /var/log/longquery

Then when you're done, create the file /var/log/longquery by doing:

touch /var/log/longquery

Then CHMOD it to 775 so MySQL user can read/write it.

chmod 775 /var/log/longquery

From time to time; I'd suggest looking at that file; it will log all the queries taking more than 10 seconds. If you think that number is too low; change the long_query_time to how many seconds you think is appropriate.

Also; you can also run this cronjob I have made which checks if there is any new long queries and send it to you via email.

#!/bin/bash

LOGSIZE=`cat /var/log/longquery | wc -l`
LOG=`cat /var/log/longquery`

if [ "$LOGSIZE" != "1" ]
then
 echo "$LOG"
 echo '' > /var/log/longquery
fi

Simply save that somewhere and then:

crontab -e

Place the following in it:

*/5 * * * * /scripts/longquerycheck

It will run each 5 minutes and check if there is any long queries; if there are not; it won't report anything; however; I'd suggest making the cronjob output the results to an email you actually read (Usually people don't bother reading root email).

You can recieve emails sent to root by logging into WHM > "Server Contacts" > "Change System Mail Preferences". -- Make sure root mail is forwarded to something you read; then you'll get the results from the cronjob.

Thanks for your tip, I will try it.. you seem to be a hosting company owner/employer or something like that .


I will post results later. Thanks to all who posted here for help me!

Greetings, TiNy.

Ok, when I desactivate the Forum Warning System plugin, forum uses too less MySQL queries.. (FWS plugin queries are quite long), MySQL queries go from 33 (with activated plugin) to 11 or 12 (desactivated).

But the MySQL CPU usage it is keept quite high... :/

I will do what nvez say and I don't know waht more.

Thanks, TiNy.

OK, for starters - the Forum Warning System modification has a small flaw - it can end up causing a massive number of queries to run on your forum if you have a lot of users with a warning level of some sort.

If you open up inc/plugins/fws.php and find (line 877 through to 888):

	$fws_query = $db->query("SELECT u.uid, u.fws_warnings FROM ".TABLE_PREFIX."users u WHERE u.fws_warnings>=100");
	while($fws_b_user = $db->fetch_array($fws_query))
	{
		$query = $db->query("SELECT uid FROM ".TABLE_PREFIX."banned WHERE uid='".$fws_b_user['uid']."'");
		if($db->num_rows($query) <= 0)
		{
			$no_more_ban = array(
				"fws_warnings"	=> $mybb->settings['fws_revert_points'],
			);
			$db->update_query(TABLE_PREFIX."users", $no_more_ban, "uid='".$fws_b_user['uid']."'");
		}
	}

Replace it with this modified version:

	$fws_query = $db->query("SELECT u.uid, u.fws_warnings FROM ".TABLE_PREFIX."users u, ".TABLE_PREFIX."banned b WHERE b.uid=u.uid AND u.fws_warnings>=100");
	while($fws_b_user = $db->fetch_array($fws_query))
	{
		$no_more_ban = array(
			"fws_warnings"	=> $mybb->settings['fws_revert_points'],
		);
		$db->update_query(TABLE_PREFIX."users", $no_more_ban, "uid='".$fws_b_user['uid']."'");
	}

That should get rid of half of the queries you're currently executing per page.

You can also use the attached inc/plugins/fws.php file.

As for your MySQL usage, are there any other scripts running on the server? MySQL is a global server process so any usage is across scripts being executed off the entire server.

I think you've misunderstood your footer statistics. It says that it took 0.7 seconds total, and 77% of those 0.7 seconds were processing SQL queries. Even the 3.6 MiB RAM usage is low enough.

Chris Boulton Wrote:OK, for starters - the Forum Warning System modification has a small flaw - it can end up causing a massive number of queries to run on your forum if you have a lot of users with a warning level of some sort.

If you open up inc/plugins/fws.php and find (line 877 through to 888):

	$fws_query = $db->query("SELECT u.uid, u.fws_warnings FROM ".TABLE_PREFIX."users u WHERE u.fws_warnings>=100");
	while($fws_b_user = $db->fetch_array($fws_query))
	{
		$query = $db->query("SELECT uid FROM ".TABLE_PREFIX."banned WHERE uid='".$fws_b_user['uid']."'");
		if($db->num_rows($query) <= 0)
		{
			$no_more_ban = array(
				"fws_warnings"	=> $mybb->settings['fws_revert_points'],
			);
			$db->update_query(TABLE_PREFIX."users", $no_more_ban, "uid='".$fws_b_user['uid']."'");
		}
	}

Replace it with this modified version:

	$fws_query = $db->query("SELECT u.uid, u.fws_warnings FROM ".TABLE_PREFIX."users u, ".TABLE_PREFIX."banned b WHERE b.uid=u.uid AND u.fws_warnings>=100");
	while($fws_b_user = $db->fetch_array($fws_query))
	{
		$no_more_ban = array(
			"fws_warnings"	=> $mybb->settings['fws_revert_points'],
		);
		$db->update_query(TABLE_PREFIX."users", $no_more_ban, "uid='".$fws_b_user['uid']."'");
	}

That should get rid of half of the queries you're currently executing per page.

You can also use the attached inc/plugins/fws.php file.

As for your MySQL usage, are there any other scripts running on the server? MySQL is a global server process so any usage is across scripts being executed off the entire server.

Hello Chris,

I will use you fws fix, thanks a lot .

I don't have nothing more in the VPS, only the MyBB forum..

laie_techie Wrote:I think you've misunderstood your footer statistics. It says that it took 0.7 seconds total, and 77% of those 0.7 seconds were processing SQL queries. Even the 3.6 MiB RAM usage is low enough.

Oh, I didn't understood, now yes..

But if the 3.6 MiB of RAM are low, why CPU usage is too high? :/

TiNyHispano Wrote:

laie_techie Wrote:I think you've misunderstood your footer statistics. It says that it took 0.7 seconds total, and 77% of those 0.7 seconds were processing SQL queries. Even the 3.6 MiB RAM usage is low enough.

Oh, I didn't understood, now yes..

But if the 3.6 MiB of RAM are low, why CPU usage is too high? :/

Server load 3 is for all processes running on the server, and not just this one thread of Apache (or IIS or whatever). I don't think many hosts complain as long as it stays below 5.