Tuesday, March 06, 2007

OpenSEF RC5 SP2 Perfomance Improvement

Hi, all!

Recently I’ve rewritten a pretty large site on Joomla (it has about 200 online users all the time, as Joomla backend stats shows, >17000 content items etc.) and noticed that the page generation time started to grow ftom 0.101 secs to 0.5 after the first day, 0.7 - 1 next day…). Not very good, right? First of all I went to the MySQL console at the time of high loads and… voila - there is a `opensef_sef` MySQL query present almost constantly. Guys, can anybody explain me why do they fetch into the memory almost all records in sef.php [Line #173]:


if ($sefSite->id == null) $sefSite->id = -1;
$query = “SELECT * FROM #__opensef_sef WHERE published = ‘1′” .
“\nAND valid = ‘1′” .
“\nAND (direction IS NULL OR direction = ‘’ OR direction = ‘o’)” .
“\nAND (site_id IS NULL ” .
($sefSite->id ? ‘ OR site_id = ‘ . $sefSite->id : ‘’) . ‘)’ .
“\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC”;
$database->setQuery( $query );
if ($database->loadResult()) {
$opensef_aliases = $database->loadObjectList();
}

A quick fix is to replace the query with this one:
if ($sefSite->id == null) $sefSite->id = -1;
$query = “SELECT * FROM #__opensef_sef WHERE published = ‘1′” .
“\nAND valid = ‘1′” .
“\nAND (direction IS NULL OR direction = ‘’ OR direction = ‘o’)” .
“\nAND (site_id IS NULL ” .
($sefSite->id ? ‘ OR site_id = ‘ . $sefSite->id : ‘’) . ‘)’ .
“\nAND (external=’{$_SERVER[’REQUEST_URI’]}’” .
“\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC”;
$database->setQuery( $query );
if ($database->loadResult()) {
$opensef_aliases = $database->loadObjectList();
}


After that, find the lookupOutgoing function  in administrator/components/com_sef/sef.class.php and replace it with this one:

function lookupOutgoing( $site_id, $url ) { // static method
global $opensef_aliases, $database;

$returnaliases = array();
/*
if ($site_id == null) $site_id = -1;
if (count($opensef_aliases) > 0) {
foreach($opensef_aliases as $aliases){
if ( $aliases->internal == $url ) {
$returnaliases[] = $aliases;
}
}
}
*/
if (!count($returnaliases))
{
$query = "SELECT * FROM #__opensef_sef WHERE published = '1'" .
"\nAND valid = '1'" .
"\nAND (direction IS NULL OR direction = '' OR direction = 'o')" .
"\nAND internal='$url'".
"\nORDER BY site_id DESC, published DESC, valid DESC, LENGTH(direction) DESC limit 1";

$database->setQuery( $query );
if ($database->loadResult())
{
$aliases = $database->loadObjectList();
foreach($aliases as $k=>$val)
{
$returnaliases[] = $val;
}
}
}

if (is_array( $returnaliases ) && count( $returnaliases )) {
$returnaliases = xclCastObjectList( $returnaliases, 'JosOpenSEFAlias' );
return $returnaliases[0];
}
$null = null;
return $null;
}

Hurray! It works as a charm. Page generation time is now 0.07 seconds.

Hope this will help somebody else.

Thanks.

Labels: , , , ,

7 Comments:

Blogger Motin said...

Hey thanks bigtime for the OpenSEF performance tweak.

You shortened our site's downtime today since it went down due to maximum memory limit was reached (try to fetch 11k row objects into memory without reaching a 32mb memory limit..).

As a developer for the NuSEF (OpenSEF renamed) component I'll make sure it makes the next version if I can't find any side effects.

Do you have any more tweaks for OpenSEF that you would like to share?

12:56 AM  
Blogger Alexander Alfimov said...

Hi!

You're very welcome.

You may wish to check my site for other Joomla! related tweaks (including OpenSEF).

Thanks.

PS. Glad to see NuSEF developer here :)

1:39 AM  
Blogger Motin said...

That was a quick reply :)

NuSEF developers is a rare thing nowadays since Marko has declared he has no time for OpenSEF anymore.

I am a web developer like you that has found and fixed bugs in OpenSEF (as well as implemented some new features) and am taking the time to incorporate them into the main trunk.

Just to let you know, I ended up using this code for lookupOutgoing, as it supports multisites configuration and saves fetched aliases in memory:

/**
* Check outgoing URL
*
* @param string $site_id
* @param string $url
* @return result
*/
function lookupOutgoing( $site_id, $url ) { // static method
global $opensef_aliases, $database;

$returnaliases = array();

if ($site_id == null) $site_id = -1;
if (count($opensef_aliases) > 0) {
foreach($opensef_aliases as $aliases){
if ( $aliases->internal == $url ) {
$returnaliases[] = $aliases;
}
}
}

/* NuSEF v1.0.1 Performance Fix: By Alexander Alfimov (http://alfim.blogspot.com/) Big thanks! Multiple sites support + memory cache of fetched aliases added by Motin */
if (!count($returnaliases))
{
$query = "SELECT * FROM #__opensef_sef WHERE published = '1'" .
"\nAND valid = '1'" .
"\nAND (direction IS NULL OR direction = '' OR direction = 'o')" .
"\nAND internal='$url'".
"\nAND published=1".
"\nAND (site_id='$site_id' OR site_id = -1)".
"\nORDER BY site_id DESC, link_prio, LENGTH(direction) DESC limit 1";

$database->setQuery( $query );
if ($database->loadResult())
{
$aliases = $database->loadObjectList();
foreach($aliases as $k=>$val)
{
$opensef_aliases[] = $val; // Store fetched alias record in memory
$returnaliases[] = $val;
}
}

//var_dump(count($opensef_aliases), $url, $query, $returnaliases, $site_id);

}

if (is_array( $returnaliases ) && count( $returnaliases )) {
$returnaliases = xclCastObjectList( $returnaliases, 'JosOpenSEFAlias' );
return $returnaliases[0];
}
$null = null;
return $null;
}


PS I cannot properly intent it through this comment system, but it'll be in the development trunk soon at http://joomlacode.org/gf/project/nusef/

Cheers

4:12 AM  
Blogger Alexander Alfimov said...

My reply was not faster than yours :).

Thank you very much for keeping my name with the fix.
Could you please change alfim.blogspot.com to custompcsoftware.com? :)

Thank you!

4:34 AM  
Anonymous Website Hosting said...

Thanks for the nice post.

11:39 PM  
Anonymous Magento Themes said...

Hello,

This blog is very good and also its readers are very good e.g "Motin".

6:04 PM  
Blogger Unknown said...


Virtuemart Extensions

Virtuemart Templates

Joomla Furniture Template

Joomla Flower Template

One Page CheckOut for Virtuemart

Magento Jewelry Theme

Magento Printing Website Theme

Magento Facebook Login

Responsive Magento Themes

Magento Quick View Extension

Magento Color Swatch

Magento Themes

Magento Extensions




1:12 AM  

Post a Comment

<< Home