Geekly Articles each Day
📜 ⬆️ ⬇️

Joomla performance on large volumes of content

image

Joomla is very bad at digesting a database with even a few thousand articles in the _content table. With several tens of thousands of queries to the database of standard modules of type mod_articles_popular may hang for seconds.

It's all about the ACL (Access Control List) - access control policy. Checking the legality of user access to the materials takes over 98% of the time the request is executed.
')
In the meantime, there are sites that do not need it. For example, the news site, showing in the left column the module "Most read articles" all in a row. What to do in this case? Disable ACL checking in the helper.php module. Fortunately, this is not difficult - just comment on the line:

$model->setState('filter.access', $access);

Let's look at the result. We turn on debugging in the admin panel, look in the debag information to the request in the database of this module. Here it is without an ACL:

Request to mod_articles_popular DB without ACL
  : 18.84 ms   : 6.38 ms  : 0.012 MB   : 7.288 MB  : 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: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: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 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' AND parent.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 (9,11,12,13,15,21,24,25) AND a.publish_up >= DATE_SUB('2018-04-30 03:27:24', INTERVAL 60 DAY) ORDER BY a.hits DESC LIMIT 5


Here's the ACL:

Request to mod_articles_popular DB with ACL
  : 972.79 ms   : 3.96 ms  : 0.012 MB   : 7.378 MB  : 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: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: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 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' AND parent.published != 1 GROUP BY cat.id ) AS badcats ON badcats.id = c.id WHERE a.access IN (1,1,2,3,6) AND c.access IN (1,1,2,3,6) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND a.catid IN (9,11,12,13,15,21,24,25) AND a.publish_up >= DATE_SUB('2018-04-30 03:36:50', INTERVAL 60 DAY) ORDER BY a.hits DESC LIMIT 5


The difference is just two checks in the WHERE clause:

 a.access IN (1,1,2,3,6) AND c.access IN (1,1,2,3,6)

as well as in the first line query execution time: 19 milliseconds and 973 milliseconds. 98%. On a database with 5,000 articles. On a powerful server hoster. This is the price of the widely advertised Joomla ACL.

But it turns out that the developers of this framework are well aware of this moment, and they have built into its admin panel the ability to disable the ACL check. But they did it so that no one would guess.

The option is located in “General Settings” -> “Materials”, at the bottom of the first “Materials” tab, is called “Show links unauthorized”. It is interpreted by a pop-up hint like: " If set to Yes, then all users, including those who are not authorized, will be able to see links to view the full text of materials, but to view the full text, the system will require to enter a login and password ."

It seems to be irrelevant, but does exactly what is needed - cancels the ACL check for all modules (which use this policy). The $ access variable in $ model-> setState ('filter.access', $ access) is this checkbox (with a backward / exclamation mark). One of the developers of this CMS suggested its existence.

Who needs can revive their Joomla.

Note: As it turned out from the tests and discussions in the comments, such braking on this request with the ACL check is irregular and non-deterministic. A sample without an index with calculated fields in a condition using disk operations with large amounts of data on my tests was sometimes not placed in the DBMS memory, sometimes placed - in this case, the above requests with ACLs were about 50% longer. Perhaps it depends on the server environment.

Source: https://habr.com/ru/post/354678/

More articles:


All Articles