High server load, but can't identify code position

[richey]

Hello,

since a couple of days (I had to set up a new webserver, so I can't exactly put my finger on a particular change since when it started) I detected a very high server load on the mysqld and php_cgi process on my server.

MySQL's Slow-log indicates this query in *every* line, so this one seems to be a main cause of the problem:

SELECT COUNT(p.post_id) as count
FROM phpbb_posts p
WHERE p.post_time > ########
AND p.post_visibility = 1
AND p.poster_id != ##;

however I couldn't even spot the 'and p.post_visibility = 1' string anywhere in the phpbb code! Can anyone point me to the right direction please?

thanks,
r.

[Pond Life]

however I couldn't even spot the 'and p.post_visibility = 1' string anywhere in the phpbb code!

viewtopic.php

Code: Select all

						AND p.post_visibility = " . ITEM_APPROVED . '

includes\constants.php

Code: Select all

define('ITEM_APPROVED', 1); // => has been approved, and has not been soft deleted

[edit]I forgot to say that viewtopic.php is just one of the files it appears in, I just gave that as an example.[/edit]

[richey]

I have identified the reason, thanks for your response!
It was caused by an old phpbb MOD, "display number of new posts on index".

Code: Select all

	if ($auth->acl_get('m_approve'))
	{
		$m_approve_fid_ary = array(-1);
		$m_approve_fid_sql = '';
	}
	else
	{
		$m_approve_fid_ary = array();
		$m_approve_fid_sql = ' AND p.post_visibility = ' . ITEM_APPROVED; ;
	}
	if ($user->data['user_id'] != ANONYMOUS){
		$sql = 'SELECT COUNT(p.post_id) as count 
				FROM ' . POSTS_TABLE . ' p
				WHERE p.post_time > ' . $user->data['user_lastvisit'] . "
				$m_approve_fid_sql
				" . ((sizeof($ex_fid_ary)) ? ' AND ' . $db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '') .
				' AND p.poster_id != ' . $user->data['user_id'];
		$result = $db->sql_query($sql);
		$post_count = $db->sql_fetchfield('count', false, $result);
		$db->sql_freeresult($result);
	}

I like the feature of users seeing how many new posts were made since their last login, so does anyone have an idea on how I could optimize this SQL statement in order to be more resource effective?

thanks,
r.