Note: This post is slightly oriented for programmers. We just wanted to let you know before reading it in case you found it a bit too technical for your taste!
Another note: The solution presented in this post requires a core modification since it is addressing a bug in Joomla’s core. Proceed with caution and at your own risk, while keeping in mind that core modifications may be wiped out with a Joomla update.
A client of ours with a high traffic Joomla website (the website is a magazine), called us yesterday and told us that her website was experiencing some serious load issues. So we checked the server hosting the website and we noticed that the load was constantly in the double digits. This was odd because this issue happened all of a sudden, and they didn’t experience any spike in traffic. In fact, the traffic was relatively low this time of year for them (which is the case for many online businesses).
The first thing that we did was checking the slow query log, and it was full of the following queries:
# Time: 151228 16:39:29
# User@Host: db_user @ localhost []
# Query_time: 1.030854 Lock_time: 0.000249 Rows_sent: 11676 Rows_examined: 58413
SET timestamp=1449873569;
SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.language, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias, CASE WHEN a.modified = 0 THEN a.created ELSE a.modified END as modified, a.modified_by, CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, LENGTH(a.fulltext) AS readmore,a.state AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,c.published, c.published AS parents_published
FROM #__content AS a
LEFT JOIN #__content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN #__categories AS c ON c.id = a.catid
LEFT JOIN #__users AS ua ON ua.id = a.created_by
LEFT JOIN #__categories as parent ON parent.id = c.parent_id
WHERE a.access IN (1,1) AND c.access IN (1,1) AND a.state = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2015-12-11 22:39:28') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2015-12-11 22:39:28')
ORDER BY CASE WHEN a.publish_up = 0 THEN a.created ELSE a.publish_up END DESC, a.created;
Even though the query above was only taking a second, it was a problem, because there were many identical queries being executed every second.
Looking closer at the following line…
# Query_time: 1.030854 Lock_time: 0.000249 Rows_sent: 11676 Rows_examined: 58413
…we noticed that the rows being sent by the database to the (Joomla) application were 11,676 rows! Really? Why and where would Joomla need 11,676 rows from the #__content table in one shot? We thought it might be a very badly written module, and so we just emptied the index.php file located under the templates/[joomla-template] folder, and then we checked the load, which dropped a bit, but remained steadily in the double digits. This meant that the issue wasn’t caused by a module.
We then disabled all the published plugins, but, as we expected, it wasn’t the problem. We even switched to a basic template, but with no avail: the load was still very high!
So it wasn’t a module, it wasn’t a plugin, and it wasn’t the template causing the problem. What was it?
We then started debugging the problem, and we discovered that all of a sudden, the website started experiencing an abnormally high number of 404s. When we focused more on this issue, we realized that the high server load was caused by those 404 pages, and it didn’t take us long to know that the root of the issue was a bug in Joomla’s core.
You see, for a mysterious reason, when a user hits a 404 page, Joomla tries to load all the active articles from the database, and, when the database is very large, this can cause serious load issues on the server.
But is there a reason why Joomla does that?
No there isn’t. In fact, Joomla thinks that it’s not doing that. Let us explain by examining some code in the function getItems which is located in the category.php file (which is in turn located under the components/com_content/models folder).
if ($limit >= 0)
{
$this->_articles = $model->getItems();
if ($this->_articles === false)
{
$this->setError($model->getError());
}
}
else
{
$this->_articles = array();
}
As you can see, Joomla checks if $limit is greater or equal to zero, and if it is, it gets the articles from the database, if $limit is less than zero, then Joomla will not get anything form the database. In case you’re wondering, $limit tells Joomla to limit the number of articles retrieved from the database to its value, for example, if $limit is 20, then Joomla only asks MySQL to send 20 articles from the database (instead of 11k rows).
So, for the untrained eye, the above condition is solid: if $limit is less than zero, then do not get anything from the database. But what if $limit is NULL?
Well, if a value is NULL, then PHP will cast it (casting, in programming terms, means transforming a variable from one type to another) to a zero, and then Joomla will get all the active articles from the database (because technically, there is no limit imposed on the number of articles to be retrieved).
If you haven’t guessed it already, 404 pages on the Joomla website had a $limit of NULL, and thus each 404 page was technically loading 11k articles from the database into the memory – both creating a load on the database server and quickly exhausting the available memory.
In short, the condition if ($limit >= 0) is wrong – because if $limit is zero or equivalent to zero, then we will have a serious problem.
So, how did we fix the problem?
We fixed the problem by replacing, in the aforementioned category.php the following line:
$limit = $this->getState('list.limit');
with this one:
$limit = $this->getState('list.limit');
if (!isset($limit) || empty($limit))
return array();
This ensured that we just return an empty array (as we should) whenever we have a $limit that is not even set, is NULL, or is set to zero.
But why did our client have this sudden spike in 404s?
Our initial guess at that was a change in the linking structure, and, after asking the client, we were proven correct in our assumption: the client made a massive change in the linking structure without making the necessary .htaccess redirection from the old links to the new links.
Now, if you, our dear, dear reader, are having slowdown issues after changing the linking structure on your Joomla website, then try the above solution and add the appropriate redirects in your .htaccess file. If you need help doing that, then please let us know. We are always available, our work is super professional, and we don’t charge much!