- Posts: 4
COMMUNITY FORUM
High resource using queries
- Baze
-
Topic Author
- Offline
- New Member
Less
More
3 years 8 months ago #178258
by Baze
High resource using queries was created by Baze
Hi Fotis,
i'm running K2 (2.10.3) with 60.000 articles on Joomla 3.9.24, and recently i started getting notifications from the hosting regarding high resource usage queries from K2. I'm on dedicated server (CPU 6 Core E5-2620v2 - 2.00Ghz x2, 32 GB RAM) so the hardware is not a problem. Probably mysql needs proper configuration, or maybe it's something with the mini k2 module i'm using on homepage. Anyway, the notifications i'm getting from the hosting is this:
We noticed high load on yourserver due to MySQL. We found the following queries running at the time of the high resource usage ->
| 2183791 | itwcom_novusr | localhost:56192 | dbname | Query | 3 | Sending data | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183792 | itwcom_novusr | localhost:56196 | dbname | Query | 2 | Creating sort index | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183793 | itwcom_novusr | localhost:56198 | dbname | Query | 1 | Creating sort index | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183795 | itwcom_novusr | localhost:56206 | dbname | Query | 1 | Creating sort index | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183796 | itwcom_novusr | localhost:56208 | dbname | Query | 1 | Sending data | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183797 | itwcom_novusr | localhost:56210 | dbname | Query | 1 | Sending data | SELECT id, alias FROM cepbk_k2_items WHERE published = 1 AND alias = 'qualys-latest-to-be-attacked-t | 0.000 |
| 2183805 | itwcom_novusr | localhost:56226 | dbname | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183812 | itwcom_novusr | localhost:56242 | dbname | Query | 0 | Sending data | SELECT COUNT(*) FROM cepbk_k2_items WHERE catid IN(7) AND published=1 AND trash=0 AND access IN(1,1, | 0.000 |
| 2183814 | itwcom_novusr | localhost:56246 | dbname | Sleep | 0 | |
Due to the high load we restarted MySQL and can see the load average dropped considerably. We recommend raising this with your developers and optimise the queries if needed.
Do you think you can help me out with this? Let me know in pm if you can do it and how much would it cost or it's something i can do it myself?
thanks!
Baze
i'm running K2 (2.10.3) with 60.000 articles on Joomla 3.9.24, and recently i started getting notifications from the hosting regarding high resource usage queries from K2. I'm on dedicated server (CPU 6 Core E5-2620v2 - 2.00Ghz x2, 32 GB RAM) so the hardware is not a problem. Probably mysql needs proper configuration, or maybe it's something with the mini k2 module i'm using on homepage. Anyway, the notifications i'm getting from the hosting is this:
We noticed high load on yourserver due to MySQL. We found the following queries running at the time of the high resource usage ->
| 2183791 | itwcom_novusr | localhost:56192 | dbname | Query | 3 | Sending data | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183792 | itwcom_novusr | localhost:56196 | dbname | Query | 2 | Creating sort index | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183793 | itwcom_novusr | localhost:56198 | dbname | Query | 1 | Creating sort index | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183795 | itwcom_novusr | localhost:56206 | dbname | Query | 1 | Creating sort index | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183796 | itwcom_novusr | localhost:56208 | dbname | Query | 1 | Sending data | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183797 | itwcom_novusr | localhost:56210 | dbname | Query | 1 | Sending data | SELECT id, alias FROM cepbk_k2_items WHERE published = 1 AND alias = 'qualys-latest-to-be-attacked-t | 0.000 |
| 2183805 | itwcom_novusr | localhost:56226 | dbname | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias | 0.000 |
| 2183812 | itwcom_novusr | localhost:56242 | dbname | Query | 0 | Sending data | SELECT COUNT(*) FROM cepbk_k2_items WHERE catid IN(7) AND published=1 AND trash=0 AND access IN(1,1, | 0.000 |
| 2183814 | itwcom_novusr | localhost:56246 | dbname | Sleep | 0 | |
Due to the high load we restarted MySQL and can see the load average dropped considerably. We recommend raising this with your developers and optimise the queries if needed.
Do you think you can help me out with this? Let me know in pm if you can do it and how much would it cost or it's something i can do it myself?
thanks!
Baze
Please Log in or Create an account to join the conversation.
- JoomlaWorks
-
- Offline
- Admin
Less
More
- Posts: 6218
3 years 8 months ago #178270
by JoomlaWorks
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Replied by JoomlaWorks on topic High resource using queries
K2's SQL queries have always been multiple times better compared to com_content's. Especially in the past 3 years, they have been significantly improved to the point that hardware and MySQL's or MariaDB's configuration can be the only bottleneck. If the past couple of weeks, we added a new performance filter for K2 item listings sorted by date specifically (you can install K2 v2.10.4 (dev) on top of v2.10.3 from: getk2.org/downloads/?f=K2_Development_Release.zip).
There are 3 things to keep in mind, in terms of performance...
a) Ensure you use the right configuration combination when fetching content. E.g. on the K2 Content module (and I assume other modules that use its queries) if you can directly select the categories from which you want to fetch items and not *just* the parent categories (with the option to fetch items from children enabled), prefer to do so. It's much better for K2 to know the exact category IDs to fetch content from, rather than traverse the database to find out the category IDs for children categories. Especially if you have dozens of modules in your site's homepage, this can make a huge difference in performance.
b) Ensure caching is enabled in Joomla. There is no need to use 3rd party extensions for caching. Just enable "conservative" caching in Joomla's Global Configuration and also enable the Page Cache system plugin. Additionally, if you can utilize other forms of caching on the server (e.g. Engintron - engintron.com/ - on cPanel servers or Varnish on other servers), Memcached, Redis or APCu as your Joomla cache backend/storage etc., these will also greatly add to your site's performance. In the case of Engintron or Varnish, you can also use this plugin we've made github.com/joomlaworks/url-normalizer to better instruct the caching server on how long to cache a page.
c) Ensure your database is properly optimized for your hardware. The stock MySQL or MariaDB configuration just doesn't cut it for high workloads. Instead, use these configurations as your guide: gist.github.com/fevangelou/0da9941e67a9c9bb2596 (for cPanel servers) & gist.github.com/fevangelou/fb72f36bbe333e059b66 (for any other server) - there are notes on what to adjust. Getting the database config right and troubleshooting your way to improve your database's performance is the trickiest part for sure, but when done right, you won't believe how much CPU/RAM resources your previously wasted because of an un-optimized database server...
On the other hand, if you'd like someone else to handle all that, you can always hire us to optimize your server setup and suggest ways to improve Joomla/K2 performance (setup-wise): www.joomlaworks.net/support/get-help/contact
There are 3 things to keep in mind, in terms of performance...
a) Ensure you use the right configuration combination when fetching content. E.g. on the K2 Content module (and I assume other modules that use its queries) if you can directly select the categories from which you want to fetch items and not *just* the parent categories (with the option to fetch items from children enabled), prefer to do so. It's much better for K2 to know the exact category IDs to fetch content from, rather than traverse the database to find out the category IDs for children categories. Especially if you have dozens of modules in your site's homepage, this can make a huge difference in performance.
b) Ensure caching is enabled in Joomla. There is no need to use 3rd party extensions for caching. Just enable "conservative" caching in Joomla's Global Configuration and also enable the Page Cache system plugin. Additionally, if you can utilize other forms of caching on the server (e.g. Engintron - engintron.com/ - on cPanel servers or Varnish on other servers), Memcached, Redis or APCu as your Joomla cache backend/storage etc., these will also greatly add to your site's performance. In the case of Engintron or Varnish, you can also use this plugin we've made github.com/joomlaworks/url-normalizer to better instruct the caching server on how long to cache a page.
c) Ensure your database is properly optimized for your hardware. The stock MySQL or MariaDB configuration just doesn't cut it for high workloads. Instead, use these configurations as your guide: gist.github.com/fevangelou/0da9941e67a9c9bb2596 (for cPanel servers) & gist.github.com/fevangelou/fb72f36bbe333e059b66 (for any other server) - there are notes on what to adjust. Getting the database config right and troubleshooting your way to improve your database's performance is the trickiest part for sure, but when done right, you won't believe how much CPU/RAM resources your previously wasted because of an un-optimized database server...
On the other hand, if you'd like someone else to handle all that, you can always hire us to optimize your server setup and suggest ways to improve Joomla/K2 performance (setup-wise): www.joomlaworks.net/support/get-help/contact
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Please Log in or Create an account to join the conversation.