Keyword

DB Problems

  • ADWESO.com Andy Steinhauf
  • ADWESO.com Andy Steinhauf's Avatar Topic Author
  • Offline
  • New Member
More
4 weeks 2 days ago #174184 by ADWESO.com Andy Steinhauf
ADWESO.com Andy Steinhauf created the topic: DB Problems
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.

  • Fotis
  • Fotis's Avatar
  • Offline
  • Administrator
  • JoomlaWorks Support Team
More
4 weeks 2 days ago #174186 by Fotis
Fotis replied the 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.

If you use & love K2, please take a moment to add your review and rate it
at the Joomla Extensions Directory: extensions.joomla.org/extension/k2/


IMPORTANT: Please search the forum before posting a question!

JoomlaWorks Support Team Member

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
1 week 5 days ago #174278 by ADWESO.com Andy Steinhauf
ADWESO.com Andy Steinhauf replied the 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.

  • Fotis
  • Fotis's Avatar
  • Offline
  • Administrator
  • JoomlaWorks Support Team
More
1 week 5 days ago #174279 by Fotis
Fotis replied the topic: DB Problems
Not really.

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

If you use & love K2, please take a moment to add your review and rate it
at the Joomla Extensions Directory: extensions.joomla.org/extension/k2/


IMPORTANT: Please search the forum before posting a question!

JoomlaWorks Support Team Member

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


Powered by Kunena Forum