Top Posters on Index

All new MODs released in our MOD Database will be announced in here. All support for released MODs needs to take place in the Customisations Database.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTICE: This forum is only for the announcement of new releases and/or updates of MODs. Any MOD support should be obtained through the Customisations Database in the support area designated for each MOD.

A direct link to support for each MOD is in the first post of the respective topic.
Locked
User avatar
lefty74
Registered User
Posts: 3649
Joined: Wed Sep 14, 2005 8:26 pm
Location: NL
Contact:

Re: Top Posters on Index

Post by lefty74 » Sat Nov 15, 2008 8:23 pm

well, it does not to me and i have not experienced it either yet but i guess if you have a very busy board it may be slowing it down a little.

i will try to look into it this weekend if i can find the time.
lefty74
zoocrew.eu - help us save our wildlife
My MODS | Due to lack of time I unfortunately am unable to work on my mods anymore, anyone who has ideas to improve them and would like to take any of them over, please PM me.

ScrapbookSupplies
Registered User
Posts: 361
Joined: Tue Jan 16, 2007 7:59 pm

Re: Top Posters on Index

Post by ScrapbookSupplies » Sat Nov 15, 2008 10:38 pm

NP - I haven't noticed it either, but our forum isn't too big either. Was just wondering from the previous post. If it appears to be an issue then I can disable it, but for now I am keeping it on there. I like it! :mrgreen:

updown
Registered User
Posts: 542
Joined: Sat Jan 05, 2008 6:53 am

Re: Top Posters on Index

Post by updown » Sat Nov 15, 2008 11:45 pm

I have many mods installed that all effect the index loading-time. From lefty's Announcement-Centre or Birthday-Ahead-List, to much more querying-extensive Mods like TheUniqueTiger's "Latest Posts" (tuned by myself), nickvergessen's "Who-was-here-MOD" and HoL's "Activity Stats MOD", just to name a few.

In DEBUG_EXTRA's Explain-Mode, the page-load of my index (php-generating and sql) is ~0.330 seconds (which I think is quite fast for a heavy modded board with over 90 MODs) with 32 queries + 9 queries returning data from cache, and many of them influence the index-load directly or indirectly (e.g. function-calls). :arrow: The querying of the 24h-top-posters alone needs ~0.143s, that's 43% :!: (these are average values!)

The main problem is, that in some rare cases, when either there's some traffic or my hoster's db sucks a little bit, this single query thrashes the mysql in such a way, that this query itself extends to 10-13 seconds :!: querying-time!

And the board is not very big at all: ~300 active users at all, ~80 users visits each day, ~5 users online the same in average, ~150 posts a day, with a total of 50.000 posts in 4500 topics.

A caching of the output is totally necessary to lower such issues! A caching for just 5 minutes would do an immense advantage, since this query starts every time the index is loaded, by every user, every second!

Like I've written some posts above, Highway Of Life cached the whole function-output in his "Activity Stats MOD" instead of the single sql-queries. Since I am not a coder, I can "just" understand and analyze how codes work, but I do a little clumsy while trying to program it on my own. So please lefty, could you take a look in it? I really love this MOD, but this little query without any caching is getting more and more annoying.

User avatar
lefty74
Registered User
Posts: 3649
Joined: Wed Sep 14, 2005 8:26 pm
Location: NL
Contact:

Re: Top Posters on Index

Post by lefty74 » Sun Nov 16, 2008 1:15 am

had a quick look at includes/cache.php and hols' mod which is basically the same principle if i am not mistaken.

try this then: have not tested it much tho

replace the includes/functions_topposter.php with below
it is set to 5 minutes at the moment

Code: Select all

<?php
/**
*
* @package phpBB3
* @functions_topposter.php
* @copyright (c) 2008 lefty74
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/

/**
* @ignore
*/
if (!defined('IN_PHPBB'))
{
	exit;
}

/**
* Obtain Top Posters information
**/
function get_top_posters()
{
	global $cache, $config, $db, $user, $auth;
	global $template;
		
	$spammer_user_ids = array(); 
	$spammer_user_ids = ($config['top_posters_excl_ids']) ? explode(",", $config['top_posters_excl_ids']) : array();
	
	$acl_am = array();
	   
	if ($config['top_posters_excl_adm'])
	{
	   $acl_am[] = 'a_';
	}
	if ($config['top_posters_excl_mod'])
	{
		$acl_am[] = 'm_';
	}

	$exclude_ids_ary = array();
	$top_posters_list = $top_posters_hours_list = '';
     

	if (sizeof($acl_am))
	{
		$user_ary = $auth->acl_get_list(false, $acl_am, false);

		foreach ($user_ary as $forum_id => $forum_ary)
		{
			foreach ($forum_ary as $auth_option => $id_ary)
			{
				$exclude_ids_ary = array_merge($exclude_ids_ary, $id_ary);
			}
		}
	}
	$exclude_ids_ary = array_merge($exclude_ids_ary, $spammer_user_ids);
	$exclude_ids_ary = array_unique($exclude_ids_ary);

	$exclude_ids_ary_hours = ($config['top_posters_excl_hours']) ? $exclude_ids_ary : $spammer_user_ids;
	
	$excluded_ids = (sizeof($exclude_ids_ary)) ? 'AND ' . $db->sql_in_set('user_id', $exclude_ids_ary, true) : '';
	$excluded_ids_hours = (sizeof($exclude_ids_ary_hours)) ? 'AND ' . $db->sql_in_set('user_id', $exclude_ids_ary_hours, true) : '';
	

	$top_posters = $config['amount_top_posters'];

	// count top x posters
	$sql = "SELECT username, user_id, user_type, user_colour, user_posts
        	FROM " . USERS_TABLE . "
        	WHERE user_id <> " . (int) ANONYMOUS . "
				AND user_type <> " . (int) USER_IGNORE . "
					AND user_posts > 0
						" . $excluded_ids . "
        	ORDER BY user_posts DESC";
	$result = $db->sql_query_limit($sql, $top_posters, 0, 0);
	//delete the above line and uncomment below line if you want to cache the query for an hour
	//$result = $db->sql_query_limit($sql, $top_posters, 0, 3600);

	while ($row = $db->sql_fetchrow($result))
	{
		$top_posters_list  .= (($top_posters_list  != '') ? ', ' : '') . get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']) . '&nbsp;('. $row['user_posts'] .')';
	}
	$db->sql_freeresult($result);


	// count x top posters in the last x hours
	if ( $config['top_posters_hours'] )
	{
		$top_posters_hours_list = obtain_top_posters_hour($excluded_ids_hours);
	}
	
	$top_posters_hours = ( $config['top_posters_hours'] == 1 ) ? $user->lang['TOP_POSTERS_HOUR'] : sprintf($user->lang['TOP_POSTERS_HOURS'],$config['top_posters_hours']);
// Assign index specific vars
$template->assign_vars(array(
	'TOP_POSTERS_LIST'			=> $top_posters_list,
	'TOP_POSTERS_HOURS'			=> $top_posters_hours,
	'TOP_POSTERS_HOURS_LIST'	=> $top_posters_hours_list));
}

function obtain_top_posters_hour($excluded_ids_hours=false)
{
	global $db, $config,$cache;
	
	if (($top_posters_hours_list = $cache->get('_topposterhour')) === false)
	{
		$xhours = ( $config['top_posters_hours'] * 3600 );
		
		$time = time() - $xhours;
		$sql = "SELECT u.user_id, u.username, u.user_type, u.user_colour, COUNT(p.post_id) as total_posts
	        	FROM " . USERS_TABLE . " u, " . POSTS_TABLE . " p 
				WHERE p.post_time > " . (int) $time . "
					AND u.user_id = p.poster_id
						AND u.user_id <> " . (int) ANONYMOUS . "
							AND u.user_type <> " . (int) USER_IGNORE . "
								" . $excluded_ids_hours . "
				GROUP BY u.user_id 
				ORDER BY total_posts DESC";
			$result = $db->sql_query_limit($sql, $config['amount_top_posters'], 0, 0);

		$top_posters_hour_cache = '';
		while ($row = $db->sql_fetchrow($result))
		{
			$top_posters_hours_list  .= (($top_posters_hours_list  != '') ? ', ' : '') . '<em>' . get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']) . '&nbsp;('. $row['total_posts'] .')</em>';
		}
		$db->sql_freeresult($result);

		$cache->put('_topposterhour', $top_posters_hours_list, 300);
	}

	return $top_posters_hours_list;
}

?>
lefty74
zoocrew.eu - help us save our wildlife
My MODS | Due to lack of time I unfortunately am unable to work on my mods anymore, anyone who has ideas to improve them and would like to take any of them over, please PM me.

updown
Registered User
Posts: 542
Joined: Sat Jan 05, 2008 6:53 am

Re: Top Posters on Index

Post by updown » Sun Nov 16, 2008 3:38 am

YES!! You did it - that's it :idea:


Thank you very much!!!

Wedgy
Registered User
Posts: 44
Joined: Thu Aug 28, 2008 5:49 pm

Re: Top Posters on Index

Post by Wedgy » Sun Nov 16, 2008 9:03 am

Thats great lefty! Many thanks for your time and effort :D

I guess to change from 5 minutes to 1 hour you change the 300 to 3600?

Code: Select all

      $cache->put('_topposterhour', $top_posters_hours_list, 3600);

User avatar
lefty74
Registered User
Posts: 3649
Joined: Wed Sep 14, 2005 8:26 pm
Location: NL
Contact:

Re: Top Posters on Index

Post by lefty74 » Sun Nov 16, 2008 9:29 am

updown wrote:that's it :idea:
Great :)
Wedgy wrote:I guess to change from 5 minutes to 1 hour you change the 300 to 3600?
yes, that's correct.
lefty74
zoocrew.eu - help us save our wildlife
My MODS | Due to lack of time I unfortunately am unable to work on my mods anymore, anyone who has ideas to improve them and would like to take any of them over, please PM me.

Wedgy
Registered User
Posts: 44
Joined: Thu Aug 28, 2008 5:49 pm

Re: Top Posters on Index

Post by Wedgy » Sun Nov 16, 2008 10:25 am

Having just installed it, unfortunately I think its still only caching the last 24 hours posters, not the overall posters :(

User avatar
lefty74
Registered User
Posts: 3649
Joined: Wed Sep 14, 2005 8:26 pm
Location: NL
Contact:

Re: Top Posters on Index

Post by lefty74 » Sun Nov 16, 2008 10:40 am

correct, i thought that's where the issue was.

edit: you should be able to cache the first query by editing the following in includes/functions_topposter.php:
$result = $db->sql_query_limit($sql, $top_posters, 0, 0);
//delete the above line and uncomment below line if you want to cache the query for an hour
//$result = $db->sql_query_limit($sql, $top_posters, 0, 3600);
lefty74
zoocrew.eu - help us save our wildlife
My MODS | Due to lack of time I unfortunately am unable to work on my mods anymore, anyone who has ideas to improve them and would like to take any of them over, please PM me.

Wedgy
Registered User
Posts: 44
Joined: Thu Aug 28, 2008 5:49 pm

Re: Top Posters on Index

Post by Wedgy » Sun Nov 16, 2008 11:08 am

Doh! Sorry, forgot I had turned off caching for that while it didnt cache both ... my bad :D

User avatar
potku
Registered User
Posts: 850
Joined: Wed Dec 22, 2004 10:26 pm
Location: Tampere, Finland
Contact:

Re: Top Posters on Index

Post by potku » Tue Nov 18, 2008 9:43 pm

I am sure it depends, but this MOD slowed our website to a halt, pretty much. Our CPU use went to 100 % immediately and pretty much stayed there as long as we run this. I have out of curiosity tried to run this MOD on quieter times, but it's always been the same. We are on a virtual server (too lazy to translate, but I am sure y'all get the idea):

Code: Select all

- 20 GB levytilaa RAID-varmennetulla levyjärjestelmällä 
- 768 MB jakamatonta keskusmuistia (384 MB RAM + 384 MB swap) 
- 100 GB/kk liikennöintisuositus 
- Palvelin kytketty nopeaan kahdennettuun 100 Mbit/sek verkkoyhteyteen
But we still have this MOD installed, just disabled. Whenever lefty finds time to work on this one again, we'll start using it on the spot! :)
Potku.net

"Before I speak, I have something important to say."

- Groucho Marx

User avatar
lefty74
Registered User
Posts: 3649
Joined: Wed Sep 14, 2005 8:26 pm
Location: NL
Contact:

Re: Top Posters on Index

Post by lefty74 » Tue Nov 18, 2008 9:58 pm

have you tried the caching bit i posted earlier?
http://www.phpbb.com/community/viewtopi ... 5#p7733515
lefty74
zoocrew.eu - help us save our wildlife
My MODS | Due to lack of time I unfortunately am unable to work on my mods anymore, anyone who has ideas to improve them and would like to take any of them over, please PM me.

User avatar
potku
Registered User
Posts: 850
Joined: Wed Dec 22, 2004 10:26 pm
Location: Tampere, Finland
Contact:

Re: Top Posters on Index

Post by potku » Wed Nov 19, 2008 9:16 am

I did, but didn't notice any change - but it seems to work a lot better now!! Thanks! :)
Potku.net

"Before I speak, I have something important to say."

- Groucho Marx

User avatar
potku
Registered User
Posts: 850
Joined: Wed Dec 22, 2004 10:26 pm
Location: Tampere, Finland
Contact:

Re: Top Posters on Index

Post by potku » Wed Nov 19, 2008 9:37 am

I might have spoken too soon. Hmph. Well, we'll wait for a better solution :)
Potku.net

"Before I speak, I have something important to say."

- Groucho Marx

User avatar
lefty74
Registered User
Posts: 3649
Joined: Wed Sep 14, 2005 8:26 pm
Location: NL
Contact:

Re: Top Posters on Index

Post by lefty74 » Wed Nov 19, 2008 11:20 am

i cant think of a better solution then caching the data to be honest.

how big is ur board?
lefty74
zoocrew.eu - help us save our wildlife
My MODS | Due to lack of time I unfortunately am unable to work on my mods anymore, anyone who has ideas to improve them and would like to take any of them over, please PM me.

Locked

Return to “[3.0.x] MOD Database Releases”