[#25770] - [4.0] Many articles cause backend listing to stall
- Closed
- 4 Oct 2019
- Medium
- Build: staging
- # 25770
Steps to reproduce the issue
I am making test on joomla site with about 160.000 articles adn about 50000 tags. I open the backend for listing the articles to select one or some of them for manipulation.
Expected result
I am eexpeting to see the list of the articles.
Actual result
The list is taking to long to appear and sometimes it reponse nothing.
System information (as much as possible)
Joomla 4, mariadb 10.4, 16 cpu, 32 GB ram ( 24 on pool buffer size of the mariadb), nginx 1.10.
Additional comments
The problem is how the Joomla\CMS\MVC\Model\BaseDatabaseModel: _getListCount() works.
If it finds ,group or merge or querySet or having clause in the query then, as it is written on the own method comments:
// Otherwise fall back to inefficient way of counting all results.
The above for 160.000 articles is exhausting.
I have override the method
Joomla\CMS\MVC\Model\ListModel::getTotal() which calls _getListCount().
The override made at the:
Joomla\Component\Content\Administrator\Model class. I added the method:
public function getTotal()
{
// Get a storage key.
$store = $this->getStoreId('getTotal');
// Try to load the data from internal storage.
if (isset($this->cache[$store]))
{
return $this->cache[$store];
}
try
{
$query = clone $this->_getListQuery();
$query->clear('select')->clear('order')->clear('limit')->clear('offset')->clear('group') -> select('COUNT(*)');
$this->getDbo()->setQuery($query);
$this->cache[$store] = (int) $this->getDbo()->loadResult();
}
catch (\RuntimeException $e)
{
$this->setError($e->getMessage());
return false;
}
return $this->cache[$store];
}
which solved this problem for me.
Perhaps you could imported it on the joomla code.
I can not attach the database backup because of its size. I have below a link to wetransfer were you can downloaded it:
https://we.tl/t-43tKJWNkZ7
The above link will be valid unti 17 August, 2019
| Labels |
Added:
?
|
||
| Labels |
Added:
J4 Issue
|
||
| Status | New | ⇒ | Information Required |
Proposed solution returns incorrect results. We do need the slow query if we have a group clause. Maybe associations could be retrieved separately like in frontend. This would remove the group clause.
All columns of all tables
joomla-cms/administrator/components/com_content/Model/ArticlesModel.php
Lines 269 to 300 in c4237c3
Omg! No, for this we should not make an index!
Quy
- | Title |
|
||||||
I'm interested in knowing what size datasets are being used for 4.0 development. I have two News based sites that have article counts over 30K each, with one being drastically higher and it would be great to know.
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/25770.
We don't use large dataset in development for obvious reasons. My guess is that 99% of all Joomla sites have a article count less than 1000, so having a really big site needs more work and a bigger server and so on. I will not say we should not look into such issues and we should work to make in better and more perfomant but, hoenstly, this isn't a priority in development.
I'm sorry but I don't agree with your philosophy at all and I hope this is not how the entire team feels.
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/25770.
Sorry if that came across the wrong way. Of course performance for large sites is important for us, but at this point in time we are working more on making everything feature complete than to tweak performance. Performance (especially for large sites) will be a high priority as soon as we are feature complete.
Besides the focus of the developers, we also have an issue with our tooling. Large datasets for testing are sparse and while some have SQL files of large sites and I know that someone had a script to generate large sets of dummy data, but at least as far as I know, there is no official way to generate dummy testing data for a large site. No rocket science there, just again something that we have to get around to creating.
My personal guess would be, that we will work on performance improvements around the PBF in October. Maybe you want to help out then?
For explanation of the previous comment by hackwar: "PBF" means "Pizza, bugs and Fun", see https://community.joomla.org/events/pizza-bugs-fun/3992-pizza-bugs-fun-2019-worldwide.html
bad performance = bad design, most of the times
or this is what i've learned in my experience
I'm interested in knowing what size datasets are being used for 4.0 development. I have two News based sites that have article counts over 30K each, with one being drastically higher and it would be great to know.
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/25770.
My databases holds about 160.000 articles with about 50.000 tags
I have updated my post and included a link to the database backup.
Proposed solution returns incorrect results. We do need the slow query if we have a group clause. Maybe associations could be retrieved separately like in frontend. This would remove the group clause.
I made very few test indeed but it did worked. If you provide me a scenario with wrong results I would like to give it a try.
I think that the articles listing is very crucial to have a good performance. After all is the cenrtal point for the cms content. I think it should not be left with generic function for caclulating total rows but make an override with better performance.
I am preparing an copy of the db with dummy data, but it is about 4G. I don't know if I can uploaded it here.
I have updated my post. I have included an wetransert link to recent database backup with dummy data.
No we can have a database with a large amount of articles.
If you provide me a scenario with wrong results I would like to give it a try.
No special scenario. Just look at the number of results returned. It's going to be higher than it should be. You can add this to the article list view layout to see exact number of results:
<?php echo $this->pagination->getResultsCounter(); ?>
I was unable to import your database due to issues with included 3rd party tables. But maybe you can test this and see if it makes any difference 4.0-dev...SharkyKZ:j4/query/articlesMultilang
@ChristoforosKor @GCLW Please test PR #26465.
| Status | Information Required | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2019-10-04 13:52:41 |
| Closed_By | ⇒ | joomla-cms-bot |
| Closed_By | joomla-cms-bot | ⇒ | alikon |
Set to "closed" on behalf of @alikon by The JTracker Application at issues.joomla.org/joomla-cms/25770
@wilsonge can you please comment?