Performance Joomla MySQL

Question

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

My problem is MySQL performance. The database includes about 60,000 rows in the content table, and the query indicated below (query execution time of the core model com_content) is about 6 seconds. Very slow.

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 

For example, when I changed

  (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') 

with

 (a.publish_up >= DATE_SUB(NOW(), INTERVAL 2 MONTH)) 

Request execution time about ~ 0.5 seconds

Request Explanation:

What can I do without cracking the kernel code, I never want to crack the main code ...

MySQL server configuration:

 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 

Answer 1

The best way I've found to improve this very slow query is to remove archive links (which destroy the whole purpose of archive status, which is to remove these items from the list results). Just take the articles that are published (or published + unpublished), so eiher state = 1 or state IN (0,1). You can look, possibly using http://extensions.joomla.org/extensions/style-a-design/templating/15611

Switching to InnoDB may also help, but the most recent version of MySQL is the best way to get improvements.

About “what you can do now” I would really like to see a transfer request to add date limits to this request. We recently added data math to JDatabaseQuery, so I'm relatively easy to use and would be a really good improvement. There are also some other unresolved issues that have a big impact on performance, and if you check and comment on them, they will go to the code base for the next version. For example: https://github.com/joomla/joomla-cms/pull/1274

Answer 2

Do you really need to do GROUP BY on all of these columns? Is simple GROUP BY a.id enough?

If this has no effect, try increasing the values ​​of the tmp_table_size and max_heap_table_size variables. This is probably why you are using Use Temporary in the Advanced column. This message is that MySQL has finished physical memory by sorting and must first write data to disk, which is slower.

Find out what these values ​​mean:
SELECT @@tmp_table_size, @@max_heap_table_size;

And then set them to something higher until "Using Temporary" disappears. (I hope you have enough memory to work):
SET tmp_table_size = ###, @@max_heap_table_size = ###;

More information on this page: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

If the above does not work, you can try throwing each of your derived queries into a temporary table with indexes on each of the columns that you join, and join each new temp table. But this can slow down, especially if you need to do this during each request. Otherwise, if the data is not updated, then this may work.

PS I just understood your comment: "What can I do without hacking the main code." We hope the session variables work.

Answer 3

Since you have to crack the kernel to make changes to the request, it is best to add a new model / view to com_content. Adding will not be overwritten by kernel updates.

Answer 4

I have the same big problem as when I opened the thread. Time for me to first connect to the site "Get www.HDsports.at" - more than 1.5 seconds. I think the reason is because I have transmission over a network with 4 gigabytes per hour.

My host server sends my mysqlslow.log, and I see a lot of requests that had a knife:

 # Time: 150408 19:35:15 # User@Host : dbo376405720[dbo376405720] @ localhost [] # Query_time: 2.358831 Lock_time: 0.000273 Rows_sent: 5293 Rows_examined: 174411 SET timestamp=1428514515; 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 ngie_content AS a LEFT JOIN ngie_content_frontpage AS fp ON fp.content_id = a.id LEFT JOIN ngie_categories AS c ON c.id = a.catid LEFT JOIN ngie_users AS ua ON ua.id = a.created_by LEFT JOIN ngie_users AS uam ON uam.id = a.modified_by LEFT JOIN ngie_categories as parent ON parent.id = c.parent_id LEFT JOIN ngie_content_rating AS v ON a.id = v.content_id LEFT OUTER JOIN (SELECT cat.id as id FROM ngie_categories AS cat JOIN ngie_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,5) AND c.access IN (1,1,5) AND CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1 AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2015-04-08 17:35:12') AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2015-04-08 17:35:12') ORDER BY c.lft, CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END DESC , a.created; 

Here you can download the full file: http://forum.joomla.org/download/file.php?id=111296

Hope someone can help me.

Best wishes