High server load, but can't identify code position

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Get Involved
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
richey
Registered User
Posts: 617
Joined: Mon Feb 18, 2002 4:26 pm
Location: now@Cyberspace
Contact:

High server load, but can't identify code position

Post by 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
Registered User
Posts: 388
Joined: Sat Jan 20, 2007 1:55 am

Re: High server load, but can't identify code position

Post by Pond Life »

richey wrote: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]
Never argue with idiots, they will drag you down to their level and beat you with experience.
richey
Registered User
Posts: 617
Joined: Mon Feb 18, 2002 4:26 pm
Location: now@Cyberspace
Contact:

Re: High server load, but can't identify code position

Post by 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.
.
Locked

Return to “[3.1.x] Support Forum”