Performance of Joomla on large volumes of content

Performance of Joomla on large volumes of content
 
 
Joomla very poorly digests the database even with several thousand articles in the _content table. With several tens of thousands of requests to the base of standard modules such as mod_articles_popular can hang for a second.
 
 
It's all about the ACL (Access Control List), an access control policy. Verification of the legality of user access to materials takes over 98% of the query execution time.
 
 
In the meantime, there are sites that do not need it. For example, a news site showing the "Most Read Articles" module in the left column for everyone. What to do in this case? Disable ACL checking in the helper.php module. The benefit is not difficult - just comment the line:
 
 
$ model-> setState ('filter.access', $ access);
 
 
Let's look at the result. We include debugging in the admin panel, we look in the debug information request to the database of this module. Here it is without ACL:
 
 
Request to DB mod_articles_popular without ACL [/b]
Request time: ??? ms After the last request: ??? ms Memory of request: ??? MB Memory before query: ??? MB Rows selected: 5
SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias,
CASE WHEN a.modified = '0000-00-???:00:00' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,
CASE WHEN a.publish_up = '0000-00-???:00:00' 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, a.language, LENGTH (a.fulltext) AS readmore,
CASE WHEN badcats.id is not null THEN 0 ELSE a.state END 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> '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, ROUND (v.rating_sum /v.rating_count, 0) AS rating, v.rating_count as rating_count, c.published,
CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_categories AS c
ON c.id = a.catid
LEFT JOIN jos_users AS ua
ON ua.id = a.created_by
LEFT JOIN jos_users AS uam
ON uam.id = a.modified_by
LEFT JOIN jos_categories as parent
ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v
ON a.id = v.content_id
LEFT
OUTER JOIN (SELECT cat.id as id
FROM jos_categories AS cat JOIN jos_categories AS parent
ON cat.lft BETWEEN parent.lft
AND parent.rgt
WHERE parent.extension = 'com_content'
published! = 1
GROUP BY cat.id) AS badcats
ON badcats.id = c.id
WHERE
CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1
AND a.catid IN (?1?1?1?1?2?2?25)
AND a.publish_up> = DATE_SUB ('2018-04-???:27:24', INTERVAL 60 DAY)
ORDER BY a.hits DESC
LIMIT 5

 
 
 
Here's the ACL:
 
 
Request to DB mod_articles_popular c ACL [/b]
Request time: ??? ms After the last request: ??? ms Memory of query: ??? MB Memory before query: ??? MB Rows selected: 5
SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias,
CASE WHEN a.modified = '0000-00-???:00:00' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,
CASE WHEN a.publish_up = '0000-00-???:00:00' 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, a.language, LENGTH (a.fulltext) AS readmore,
CASE WHEN badcats.id is not null THEN 0 ELSE a.state END 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> '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, ROUND (v.rating_sum /v.rating_count, 0) AS rating, v.rating_count as rating_count, c.published,
CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_categories AS c
ON c.id = a.catid
LEFT JOIN jos_users AS ua
ON ua.id = a.created_by
LEFT JOIN jos_users AS uam
ON uam.id = a.modified_by
LEFT JOIN jos_categories as parent
ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v
ON a.id = v.content_id
LEFT
OUTER JOIN (SELECT cat.id as id
FROM jos_categories AS cat JOIN jos_categories AS parent
ON cat.lft BETWEEN parent.lft
AND parent.rgt
WHERE parent.extension = 'com_content'
published! = 1
GROUP BY cat.id) AS badcats
ON badcats.id = c.id
WHERE a.access IN (????6)
AND c.access IN (????6)
AND
CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1
AND a.catid IN (?1?1?1?1?2?2?25)
AND a.publish_up> = DATE_SUB ('2018-04-???:36:50', INTERVAL 60 DAY)
ORDER BY a.hits DESC
LIMIT 5

 
 
 
The difference is only two checks in the WHERE clause:
 
 
a.access IN (????6) AND c.access IN (????6)
 
 
and also in the execution time of the query in the first line: 19 milliseconds and 973 milliseconds. 98%. Based on data from 5000 articles. On a powerful host server. This is the price of the widely advertised Joomla ACL.
 
 
But, it turns out, the developers of this framework are well aware of this point, and built into its administration the ability to turn off the ACL check. But they did it so that no one could guess.
 
 
The option is located in the "General Settings" -> "Materials", at the bottom of the first "Materials" tab, called "Show links unauthorized". It is deciphered with a tooltip as: " " If set to Yes, then all users will be able to see the full text of the materials, including those who have not been authorized, but to view the full text the system will require the login and password ".
 
 
It seems to be irrelevant, but it does exactly what it takes - it cancels ACL checking for all modules (which use this policy). The $ access variable in $ model-> setState ('filter.access', $ access) is just this checkbox (with a backslash /exclamation point). About its existence suggested one of the developers of this CMS.
 
 
Who needs, can revive his Jumlu.
+ 0 -

Add comment