- Posts: 92
COMMUNITY FORUM
Database Query Overloading CPU
- Red Evolution
-
Topic Author
- Offline
- Premium Member
Less
More
5 years 3 months ago #172965
by Red Evolution
Database Query Overloading CPU was created by Red Evolution
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
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.
- JoomlaWorks
-
- Offline
- Admin
Less
More
- Posts: 6218
5 years 3 months ago #172967
by JoomlaWorks
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Replied by JoomlaWorks on 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 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)
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Please Log in or Create an account to join the conversation.
- JoomlaWorks
-
- Offline
- Admin
Less
More
- Posts: 6218
5 years 3 months ago #172968
by JoomlaWorks
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Replied by JoomlaWorks on 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.
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Please Log in or Create an account to join the conversation.
- Red Evolution
-
- Offline
- Premium Member
Less
More
- Posts: 92
5 years 3 months ago #172977
by Red Evolution
Replied by Red Evolution on 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
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.