Joomla Core Query Performance Issue

[Umut KIRGÖZ]

I have been developing a Joomla site with version 2.5.11.Site will be under very high traffic.

My problem is about MySQL query performance. Database includes about 60000 rows in content table, and the query seen below (core com_content articles model query) execution time is about 6 seconds.Very slow.

Query is completely core query, as all we know,it's not good to hack core code, because of known reasons. Explain result of this query screen shot can be found as attachment.

What can I change my MySQL config to succeed to better execution times for this query. (I donot want to change the query)

MySQL Server Config can be seen below:

skip-external-locking
skip-name-resolve
key_buffer_size = 128M
max_connections=1024
max_allowed_packet = 16M
net_buffer_length = 8K
table_open_cache = 512
table_cache = 2048
join_buffer_size = 128M
read_buffer_size = 128M
sort_buffer_size = 128M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=128M
thread_concurrency=16
query_cache_size = 128M
query_cache_type=1
thread_cache_size = 1300
query_cache_limit=128M
max_heap_table_size = 1024M
tmp_table_size = 1024M

SELECT a.id,
   a.title,
   a.alias,
   a.title_alias,
   a.introtext,
   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,
   uam.name as modified_by_name,
   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,
   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,
   contact.id as contactid,
   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 #__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 #__users AS uam ON uam.id = a.modified_by
 LEFT JOIN 
 (SELECT contact.user_id, MAX(contact.id) AS id, contact.language FROM
  #__contact_details AS contact WHERE contact.published = 1 GROUP BY
   contact.user_id, contact.language) AS contact ON contact.user_id =
    a.created_by
 LEFT JOIN #__categories as parent ON parent.id = c.parent_id
 LEFT JOIN #__content_rating AS v ON a.id = v.content_id
 LEFT OUTER JOIN 
 (SELECT cat.id as id FROM #__categories AS cat JOIN #__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.featured = 0 AND
  a.id NOT IN (8921, 33722, 33728, 33729, 34187, 35047, 36784, 36236, 33724,
   19522) AND
  a.catid IN (8, 39, 40, 38, 72, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
   20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 36, 37, 41) AND
  (a.publish_up = '0000-00-00 00:00:00' OR
  a.publish_up <= '2013-06-12 06:44:44') AND
  (a.publish_down = '0000-00-00 00:00:00' OR
  a.publish_down >= '2013-06-12 06:44:44')
    GROUP BY a.id,
     a.title,
     a.alias,
     a.title_alias,
     a.introtext,
     a.checked_out,
     a.checked_out_time,
     a.catid,
     a.created,
     a.created_by,
     a.created_by_alias,
     a.created,
     a.modified,
     a.modified_by,
     uam.name,
     a.publish_up,
     a.attribs,
     a.metadata,
     a.metakey,
     a.metadesc,
     a.access,
     a.hits,
     a.xreference,
     a.featured,
     a.fulltext,
     a.state,
     a.publish_down,
     badcats.id,
     c.title,
     c.path,
     c.access,
     c.alias,
     uam.id,
     ua.name,
     ua.email,
     contact.id,
     parent.title,
     parent.id,
     parent.path,
     parent.alias,
     v.rating_sum,
     v.rating_count,
     c.published,
     c.lft,
     a.ordering,
     parent.lft,
     fp.ordering,
     c.id,
     a.images,
     a.urls
 ORDER BY publish_up DESC
 LIMIT 4,  4

[Hannes Papenberg]

Hello Umut,
you have to edit the articles model and improve the query a bit. That
query is really awfull.
First of all, by having allways the current timestamp to check against
for the publish-up-time, you are disabling the mysql caching. Refactor
that to only check against every full hour or something. Then remove the
hit counter for articles, too. That means that the table is not updated
every time someone looks at an article, invalidating the cache. Then you
should remove the join to the contact table if you don't need that
feature. Last but not least, by checking the ordering of the articles,
you want to remove a bunch of the fields that is ordered by. If you
order by the categories table, even though you don't need it, MySQL has
to copy both tables into a temporary table and do the sorting there and
then give you the result from that table.

Fixing these things up for a customer improved the performance from 12
seconds on a site with more than 150k articles to 0.1 seconds for the query.

Hannes

> --
> You received this message because you are subscribed to the Google
> Groups "Joomla! CMS Development" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to joomla-dev-cm...@googlegroups.com.
> To post to this group, send an email to joomla-...@googlegroups.com.
> Visit this group at
> http://groups.google.com/group/joomla-dev-cms?hl=en-GB.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Michael Babker]

Without yet another major refactor of the database layer, something we need to explore is a way to optimize the queries in the components for the various database drivers without making performance suffer across the board.  Something that was brought up with this question on SO was the reason for so many columns in the GROUP BY clause, with the only reason being for our multi-database support.  I don't know enough about benchmarking queries or anything like that to judge, but I'm fairly certain we've added some performance issues to MySQL databases by adding support for SQL Server and PostgreSQL to the CMS.  That in itself is a good thing, but what impact has it made?

On Thu, Jun 13, 2013 at 6:59 AM, Hannes Papenberg <hack...@googlemail.com> wrote:

Hello Umut,
you have to edit the articles model and improve the query a bit. That
query is really awfull.
First of all, by having allways the current timestamp to check against
for the publish-up-time, you are disabling the mysql caching. Refactor
that to only check against every full hour or something. Then remove the
hit counter for articles, too. That means that the table is not updated
every time someone looks at an article, invalidating the cache. Then you
should remove the join to the contact table if you don't need that
feature. Last but not least, by checking the ordering of the articles,
you want to remove a bunch of the fields that is ordered by. If you
order by the categories table, even though you don't need it, MySQL has
to copy both tables into a temporary table and do the sorting there and
then give you the result from that table.

Fixing these things up for a customer improved the performance from 12
seconds on a site with more than 150k articles to 0.1 seconds for the query.

Hannes

[Mark Dexter]

It has never been clear to me why we need to use a GROUP BY in that
query in the first place. In principal it would seem to me that we
could re-work the query to not use grouping. But of course the devil
is in the details on these things, and I have not taken the time to
test all of the edge cases where it might be needed.

If we do need the GROUP BY, then normal SQL syntax requires all
columns in the select statement and not inside an aggregate expression
be in the GROUP BY. If you think about it logically, if you have a
GROUP BY one column and a different column appears "naked" in the
SELECT statement, it is not at all clear what that means. MySQL
handles this (I would guess by assuming some type of aggregate
expression) but it is not handled by other db's and makes no logical
sense.

Mark

[Umut KIRGÖZ]

Hi;

First of all, sorry for very late reply, but I am very very busy nowadays with many Joomla sites.

Firstly I want to give some information about myself; I have been developing Joomla sites since Mambo times, those old developers know what I mean.

We decided as a company to migrate all content based sites to Joomla, a few years ago.
Joomla sites with high volume of data and sites that are really under heavy load.I mean ~2M page views per day.

There are a few performance issues we discovered during development process, we solved some by using system plugin override method of core classes.But this is not always possible.

I want to report and share those issues with you and our workarounds to discuss on.

One of those issues

Environment :
Joomla 2.5.x (Maybe 3.x, not tested yet)
Database includes 5 categories, some categories include ~60K articles.

When adding new article to a category which includes ~60K articles, it takes more than 30 seconds to save.

When I dig the code,I found the reason in /libraries/joomla/database/table.php

JTable::reorder($where = '') method takes too much time, it's triggered in prepareTable method of article model.
If a new article added, that method changes the ordering of all articles in that category.Means 60K queries, huh.
Maybe it can be right in business side, but not right in this coding style in my opinion.

For now, I comment out that code, because I donot use article ordering anywhere, but this isnot a good solution.

What you recommend?

Regards...

Umut.

[Amy Stephen]

Umut -

The next release of 2.5 and 3.x has a huge performance fix for the articles model. If you are able to test the 2.5 branch in github or if you want to test by replacing the components/com_content/articles/model/articles.php file with the new file (only for testing), you should be able to determine if it works for you.

[Thierry bela nanga]

Hello,

I had similar issue long time ago http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_item_id=26291

Some of the solutions I found was:

- Removal of JOIN when they are not required. As an example if a user select a category, we know that category id, we can just replace that with WHERE article.catid = catid

--

You received this message because you are subscribed to the Google Groups "Joomla! CMS Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to joomla-dev-cm...@googlegroups.com.
To post to this group, send an email to joomla-...@googlegroups.com.

Visit this group at http://groups.google.com/group/joomla-dev-cms.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

http://tbela99.blogspot.com/