2

Recently a server's admin sent a report about a Joomla site that is causing too much resource consumption. The report said :

CAUSE OF HIGH RESOURCE CONSUMPTION:

We have conducted a detailed investigation and it turned out that Joomla executes slow queries towards its database, which eventually hogs the server. The server tries to execute your slow queries while putting other processes in the queue until some memory is freed. While they are waiting however, they stack up and cause further problems. There can be many reasons for slow database queries, but the following 3 are the most common ones:

  1. Large database
  2. Not well written scripts
  3. Large number of internal links that query the database directly.

Here are some of the database queries that are slow and consume a lot of server resources:

  1. Executed 1h 8m 56s ago for 87.512348 sec on Database --> sporthis_tory Date: 2015-08-24 22:24:22 Query_time: 87.512348 Rows_examined: 5593: Rows_sent 247 Lock_time: 0.000197 SELECT a.id, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(':', cc.id, cc.alias) ELSE cc.id END as catslug FROM g06j5_content AS a LEFT JOIN g06j5_categories AS cc ON cc.id = a.catid WHERE a.catid = 105 AND a.state = 1 AND a.access = 1 AND (a.state = 1 OR a.state = -1) AND (publish_up = '0000-00-00 00:00:00' OR publish_up = '2015-08-25 03:22:36') ORDER BY a.created DESC; ------------------------------------------------------------------------------->-----------------------
  2. Executed 57m 7s ago for 67.095903 sec on Database --> sporthis_tory Date: 2015-08-24 22:36:11 Query_time: 67.095903 Rows_examined: 5593: Rows_sent 247 Lock_time: 0.000252 SELECT a.id, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(':', cc.id, cc.alias) ELSE cc.id END as catslug FROM g06j5_content AS a LEFT JOIN g06j5_categories AS cc ON cc.id = a.catid WHERE a.catid = 105 AND a.state = 1 AND a.access = 1 AND (a.state = 1 OR a.state = -1) AND (publish_up = '0000-00-00 00:00:00' OR publish_up = '2015-08-25 03:35:01') ORDER BY a.created DESC; ------------------------------------------------------------------------------->-----------------------
  3. Executed 1h 8m 56s ago for 52.137859 sec on Database --> sporthis_tory Date: 2015-08-24 22:24:22 Query_time: 52.137859 Rows_examined: 5593: Rows_sent 247 Lock_time: 0.000169 SELECT a.id, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(':', cc.id, cc.alias) ELSE cc.id END as catslug FROM g06j5_content AS a LEFT JOIN g06j5_categories AS cc ON cc.id = a.catid WHERE a.catid = 105 AND a.state = 1 AND a.access = 1 AND (a.state = 1 OR a.state = -1) AND (publish_up = '0000-00-00 00:00:00' OR publish_up = '2015-08-25 03:22:52') ORDER BY a.created DESC; ------------------------------------------------------------------------------->-----------------------
  4. Executed 1h 3m 29s ago for 45.861259 sec on Database --> sporthis_tory Date: 2015-08-24 22:29:49 Query_time: 45.861259 Rows_examined: 5593: Rows_sent 247 Lock_time: 0.000650 SELECT a.id, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(':', cc.id, cc.alias) ELSE cc.id END as catslug FROM g06j5_content AS a LEFT JOIN g06j5_categories AS cc ON cc.id = a.catid WHERE a.catid = 105 AND a.state = 1 AND a.access = 1 AND (a.state = 1 OR a.state = -1) AND (publish_up = '0000-00-00 00:00:00' OR publish_up = '2015-08-25 03:28:40') ORDER BY a.created DESC; ------------------------------------------------------------------------------->-----------------------
  5. Executed 1h 20m 37s ago for 40.682781 sec on Database --> sporthis_tory Date: 2015-08-24 22:12:41 Query_time: 40.682781 Rows_examined: 1320: Rows_sent 440 Lock_time: 0.000176 SELECT a.id, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(':', cc.id, cc.alias) ELSE cc.id END as catslug FROM g06j5_content AS a LEFT JOIN g06j5_categories AS cc ON cc.id = a.catid WHERE a.catid = 111 AND a.state = 1 AND a.access = 1 AND (a.state = 1 OR a.state = -1) AND (publish_up = '0000-00-00 00:00:00' OR publish_up = '2015-08-25 03:11:50') ORDER BY a.created DESC;

sporthis_tory 175.1 MB

From the queries above we can conclude that your issue is caused by a not well written scripts.

I would like to know how to start debugging something like this(finding the scripts that executes the query, optimizing the database),or it is just the site that is growing and we have to purchase a bigger plan?

Update:

Thank you for the usefull information. I want to add that site is down due to resource consumption, so I cannot login to access joomla admin panel until I fix this. I have run explain extended with the query and got this result > >

id:1
select_type:SIMPLE
table:a
type: index_merge
possible_keys: idx_access,idx_state,idx_catid
key: idx_catid,idx_state,idx_access
Key_len: 4,1,4
ref: NULL
rows: 1275
filtered: 75.06
Extra: Using intersect(idx_catid,idx_state,idx_access); Using where; Using filesort

id: 1
select_type: SIMPLE
table: cc
type: const
possible_keys: PRIMARY
key: PRIMARY
Key_len: 4
ref:const
rows: 1
filtered: 100.00
Extra:

I have no experience on indexing databases and I want to get this right, I have to create a new index with all three columns (idx_catid,idx_state,idx_access) or are they already indexed? Also on the extra note I am guesing that this query comes from a joomla module, is there any way I can discover wich module executes this strange queries? Note that I am not the creator of the site , I have just been asked to solve this issue.

  • The queries are trying to pull data from the #__content table which stores articles. The core Joomla queries won't be causing this issue, so it will most likely be a 3rd party extension, such as a News Slider or anything else that pulls article data – Lodder Aug 25 '15 at 13:08
2

While there are a lot of actions that you can take to fix this, my main recommendation should hopefully be pretty quick.

First though, how to troubleshoot these issues. Since they give you the slow queries, you should start by seeing why they are slow! The database will tell you this to a degree, so log into your DB (either using the command line or something like PhpMyAdmin). Run one of the slow queries with the word EXPLAIN at the start:

EXPLAIN SELECT a.id, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(':', cc.id, cc.alias) ELSE cc.id END as catslug FROM g06j5_content AS a LEFT JOIN g06j5_categories AS cc ON cc.id = a.catid WHERE a.catid = 105 AND a.state = 1 AND a.access = 1 AND (a.state = 1 OR a.state = -1) AND (publish_up = '0000-00-00 00:00:00' OR publish_up = '2015-08-25 03:22:36') ORDER BY a.created DESC;

This should give you a table of data telling you how your database is going to get this information. Things I would expect to see in this (without having actually run the explain...):

  • The join is on the id for #__categories, so that should be loading the extra table by a key
  • It should also tell you what key (if any) it is using to load records from the #__content table. Joomla by default ships with an index on catid, state, and access, which are all in your WHERE clause and could thus be used

My recommendation for optimization is usually to figure out what index it is trying to use and then add a better index to the table to support this kind of query. (And remember that the trade-off with indexes is always faster read time for slower write time, so if you add a lot of indexes, you should be able to select records faster but add new records slower.)

In looking at everything you select on, you want a specific catid, state, and access, so adding an index that uses all three of these columns in one should let the DB select the correct rows even faster. There will still be some DB processes as it has to sort them by created to order them.

To actually make your decision, I recommend copying the database for your website locally, add different indexes to the g06j5_content table, and keep running that EXPLAIN query until you get the query to run primarily off indexes.


One extra note, you can see in your queries that you do both a check for a.state = 1 and (a.state = 1 OR a.state = -1). This is obviously a useless check, since state can not equal 1 and -1 for the same record, meaning that having (a.state = 1 OR a.state = -1) in this at all is useless.

I don't recommend trying to track down anything like that and remove it, since the DB has a query optimizer that will remove the second check (since it adds no value).

If you are looking to actually modify the query, I would want to figure out why you are selecting two exact publish_up times: (publish_up = '0000-00-00 00:00:00' OR publish_up = '2015-08-25 03:11:50').

There is no index on publish_up times, so it generally means that the DB has to search every record to find if this is true or not. I'm not sure what the underlining logic is in selecting by a specific publish_up time, since that will so rarely be true.

  • 1
    you know that this is the standard Joomla query? I guess its for getting all articles in a category. It took on one of my servers about 0.0018 seconds to return 37 rows. the index uses catid and state. btw. I think publish_up = '...' was changed by SE input filter. – Harald Leithner Aug 25 '15 at 17:03
  • i am really sorry i am new to stack overflow and i edited you post instead of mine. – Agapios Iwsifidis Aug 26 '15 at 15:41
2

You can activate the debug mode in Joomla Settings. This will print a pretty list of all queries at the end of each page including many debug information. This list also tells you how long a query took and what indexes it uses.

It also seam you have 5593 articles in your database that could be a reason why it tooks so long, maybe the default indexes don't help on such a big amount of articles.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.