Keyword

Database Query Overloading CPU

  • Red Evolution
  • Red Evolution's Avatar Topic Author
  • Offline
  • Senior Member
More
1 month 22 hours ago #172965 by Red Evolution
Red Evolution created the topic: Database Query Overloading CPU
Hi Fotis,

We've been running the dev version of K2 on afloat.ie and it had been gradually getting slower and slower. Our server guy noticed that the CPU was getting absolutely hammered by the site and it used to be really quick. When he did some digging into it he found a few issues and said this:

"For example, this query:
SELECT tag.*
FROM vfi6j_k2_tags AS tag
JOIN vfi6j_k2_tags_xref AS xref ON tag.id = xref.tagID
WHERE tag.published = 1
AND xref.itemID = 41910
ORDER BY xref.id ASC
Was looking at over 20,000 rows to return a SINGLE row, with no indexes or anything! It looks like the developers did not profile their sql queries.]
I have added two indexes, and now that query loads and process 1 row and is a magnitude faster
And this was just one of the several queries I saw tonight."

Is this something you could look at implementing into your next version to help streamline it all a bit more.

Thanks,
Dave

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

  • Fotis
  • Fotis's Avatar
  • Offline
  • Administrator
  • JoomlaWorks Support Team
More
1 month 4 hours ago #172967 by Fotis
Fotis replied the topic: Database Query Overloading CPU
Indexes are in place for new installations for sure, you can check the installation SQL file (e.g. github.com/getk2/k2/blob/master/administrator/components/com_k2/install.mysql.sql#L147 ).

If you gradually upgraded K2 over the years though, some of these improvements would not have been reflected on your DB tables. E.g. your K2 items table may still be using MyISAM if you switched your DB to use InnoDB for the storage engine.

You can easily go through the installation SQL file and use it to verify which indexes are enabled and which should be added. Additionally, I highly recommend you switch to InnoDB instead of MyISAM if you haven't done so already.

Lastly, this is a good starting point for optimizing your database (MySQL or MariaDB) provided it's on InnoDB:
gist.github.com/fevangelou/fb72f36bbe333e059b66 (generic for Ubuntu, CentOS etc.)
gist.github.com/fevangelou/0da9941e67a9c9bb2596 (for cPanel based servers)

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.

  • Fotis
  • Fotis's Avatar
  • Offline
  • Administrator
  • JoomlaWorks Support Team
More
1 month 4 hours ago #172968 by Fotis
Fotis replied the topic: Database Query Overloading CPU
Since you seem to be on Ubuntu (on Linode) and judging by the long TTFB, I would recommend a server performance check (I'd be happy to assist - just contact by email). Being on a VPS does not always guarantee proper resource usage as well, e.g. another VPS on the same host server as yours may be killing IO or CPU and thus cause issues and delays to neighbouring VPSs.

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.

  • Red Evolution
  • Red Evolution's Avatar Topic Author
  • Offline
  • Senior Member
More
4 weeks 2 days ago #172977 by Red Evolution
Red Evolution replied the topic: Database Query Overloading CPU
Hi Fotis,

I'll pass that feedback onto our server guy and let him pick through it. I've got no doubts about the quality of the server though as Phil Taylor is the guy who looks after it all, so he definitely knows what he is doing.

Dave

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

Moderators: william white

Powered by Kunena Forum