Keyword

DB Problems

  • ADWESO.com Andy Steinhauf
  • ADWESO.com Andy Steinhauf's Avatar Topic Author
  • Offline
  • New Member
More
4 years 3 months ago #174184 by ADWESO.com Andy Steinhauf
DB Problems was created by ADWESO.com Andy Steinhauf
Hello,

since a OS update of our webhoster we have problems with k2 (Version 2.10.2) / Joomla 3.9.14 / MySql 5.7.28 / PHP 7.3.xx:

The query

SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias, c.params AS categoryparams FROM j52nk_k2_items AS i RIGHT JOIN j52nk_k2_categories AS c ON c.id = i.catid WHERE i.published = 1 AND i.access IN(1,5) AND i.trash = 0 AND c.published = 1 AND c.access IN(1,5) AND c.trash = 0 AND (i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-12-18 07:00:49') AND (i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-12-18 07:00:49') AND c.id IN(6,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54) ORDER BY i.created DESC LIMIT 0, 20

from com_k2/models/itemlist.php

causes server overload because processing time is over 1000 seconds. The database table k2_items contains more than 15000 datasets. For now we have activated the joomla builtin caches, but they cause other tiny problems, so this can be only a temporarily solution.

Do you have any hints, how to fix this ?


Best regards


Andy

Please Log in or Create an account to join the conversation.

More
4 years 3 months ago #174186 by JoomlaWorks
Replied by JoomlaWorks on topic DB Problems
a) We manage sites with more than half a million K2 items and no perf. issues even on moderate hardware.
b) If your site chokes on that with just 15k items, make sure InnoDB is enabled for your K2 tables and request your webhost to perform some basic optimization on MySQL, e.g. like: gist.github.com/fevangelou/0da9941e67a9c9bb2596 - stock my.cnf configuration won't cut it.

Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)

Please Log in or Create an account to join the conversation.

  • ADWESO.com Andy Steinhauf
  • ADWESO.com Andy Steinhauf's Avatar Topic Author
  • Offline
  • New Member
More
4 years 2 months ago #174278 by ADWESO.com Andy Steinhauf
Replied by ADWESO.com Andy Steinhauf on topic DB Problems
Thanks a lot for your response. InnoDB is activated, but the problem still remains. Perhaps it is a bug in the used mysql server.

Do you have an alternate query, which can be used in this case ?


best regards


Andy

Please Log in or Create an account to join the conversation.

More
4 years 2 months ago #174279 by JoomlaWorks
Replied by JoomlaWorks on topic DB Problems
Not really.

You should look into using an optimized configuration for MySQL. It'll make a huge difference.

Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)

Please Log in or Create an account to join the conversation.


Powered by Kunena Forum