0

My database server's CPU has been pegged for the last two hours and the site is down as a result. Looking at the process list, I see dozens of queries that all begin "SELECT DISTINCT node.nid AS nid," and from my reading, it appears that this should not be a common occurrence because these queries are very expensive and this could be the source of my problems.

Is it safe to say there is a view or module making a query incorrectly and if so, how can I can track that query down?

9
  • This is a simple query its unlikely that its killing your performance. Install the devel module and make it display the query log by time. drupal.org/project/devel Apr 22, 2014 at 15:13
  • Yeah, I've installed that and looked at the queries before, but how do I trace those queries back to specific modules or views?
    – clg
    Apr 22, 2014 at 15:23
  • Some quick searching suggests to me that no such query exists in core at least (though some similar looking ones in the taxonomy and tracker modules). I would suggest updating the title to better reflect what you are asking, which is how to track down the source of a specific query.
    – Letharion
    Apr 22, 2014 at 15:34
  • 1
    @clg: Devel module shows you what does that query - the second row in the query log, titled "where", gives you the module. Apr 22, 2014 at 15:37
  • @AlexeiRayu So it does! My costliest queries are all coming from "execute," which points to api.drupal.org/api/function/execute/6 but that page does not exist. Is that typical for the Drupal API? Sorry for all the basic questions, I am very new to Drupal and helping someone who's been having problems for a long time on their own.
    – clg
    Apr 22, 2014 at 16:54

1 Answer 1

0

from what I see, you may be running views, and complex views, that perform complex queries. execute() is a database abstraction layer function. I am deducing that you are using views from the image you sent, which lists queries that look like generated by the views module.

Luckily, if my supposition is correct, then your problem can be lifted. When editing the view, you can enable caching. Unless you have a constant flow of new view items, you can make caching time 10, 30, or even 60 minutes. Or more. Making these expensive queries cached will lift the overhead. Also, if you are using panels, you can cache panel panes as well, or whole pages for anonymous users, keeping in mind, that caching on the larger level (page vs a single view) is more preferable.

1
  • This sounds perfect. I just checked the views and Cache is not enabled for any of those that I looked at. I found an option in Tools that tags each query in such a way that I can trace them back to specific views. I REALLY appreciate your help here and have a better idea of how to proceed.
    – clg
    Apr 22, 2014 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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