SQL queries taking server down

Hi,

on one D6 site I'm experiencing serious performance issues (causing continous server load >8 on a dual core machine); I assume that this is caused by one or a limited set of SQL queries that run multiple times and permanently, when they start to appear (according to mytop, there are currently eight of these queries running; as it seems, each of these queries causes a load of one, multiplying to a total load > 8 which renders the server more or less unusable; the site is hosted on a dedicated server side by side with a bunch of other Drupal sites; mytop allows to distinguish database activity by db user and by database name, so I think I know which site is causing the load).

My knowlede of the Drupal data model and SQL queries is far too limited to give an educated guess what these statements mean, so I'm kindly asking for your help. Two examples:

EXPLAIN SELECT n.nid AS nid, n.vid AS vid, n.title AS title, n.type AS type, COUNT(*) AS cntFROM node nLEFT JOIN term_node tn ON tn.nid = n.nid AND tn.tid IN(2210,2168,2130,1992,1977,1452,2177)WHERE n.type IN ('image') AND n.status = 1 AND tn.tid IS NOT NULL AND n.nid NOT IN (21927)GROUP BY n.nidORDER BY cnt DESC, n.created DESC, n.nid DESCLIMIT 10:

As far as I understand this, in both queries the tid 2210, 2168, 2130, 1992, 1977, 1452, and 2177 are selected; I checked this, these are valid image galleries on the affected site (I can access them by going to ./image/tid/2210 etc.). Anyway, as far as I know I don't have a view which limits to these image galleries.

Also the query is limited to the node type 'image', is published (status = 1), and has a tid (tn.tid IS NOT NULL); n.nid NOT IN (21927) I don't understand (does this mean "NOT node with ID 21927"?). n.nidORDER BY cnt DESC seems to be the sorting (descending by "cnt"?), and limiting to 10 records.

However, this seems to be a view somehow related to the 'image' module.

EXPLAIN SELECT n.nid AS nid, n.vid AS vid, n.title AS title, n.type AS type, COUNT(*) AS cntFROM node nLEFT JOIN term_node tn ON tn.nid = n.nid AND tn.tid IN(2210,2168,2130,1992,1977,1452)WHERE n.type IN ({long-list-of-content-types}) AND n.status = 1 AND tn.tid IS NOT NULL AND n.nid NOT IN (22104)GROUP BY n.nidORDER BY cnt DESC, n.created DESC, n.nid DESCLIMIT 10:

This 2nd query seems similar to the first ine, it also selects the same set of tids, but limits to non-image content types. Again it says something like .nid NOT IN (22104) (NOT node id 22104?) and does this sorting by "cnt". Is there a way to find out which view causes this query?

As soon as I restart MySQL, these queries go away (of course), and the server load decreases to normal values (usually somewhere between 0.8 and 1.7). Since these queries keep coming back every couple of hours, I have to restart MySQL several times a day. So I either can set up a cron job that restarts the database server every hour, disable the 'views' module, or find out where these queries do come from.

But how do I find out which module and/or which view is causing these desastrous SQL queries??

I was suspecting this *ugly* one:

SELECT DISTINCT(node.nid) AS nid, 
node.title AS node_title, 
node.type AS node_type, 
value, 
RAND() AS _random 
FROM node node 
LEFT JOIN votingapi_cache votingapi_cache_node_average ON node.nid = votingapi_cache_node_average.content_id AND (votingapi_cache_node_average.content_type = 'node' AND votingapi_cache_node_average.function = 'average') 
LEFT JOIN term_node term_node_value_0 ON node.vid = term_node_value_0.vid AND term_node_value_0.tid = 2930 
LEFT JOIN term_node term_node_value_1 ON node.vid = term_node_value_1.vid AND term_node_value_1.tid = 2907 
LEFT JOIN term_node term_node_value_2 ON node.vid = term_node_value_2.vid AND term_node_value_2.tid = 2910 
LEFT JOIN term_node term_node_value_3 ON node.vid = term_node_value_3.vid AND term_node_value_3.tid = 2921 
LEFT JOIN term_node term_node_value_4 ON node.vid = term_node_value_4.vid AND term_node_value_4.tid = 2940 
LEFT JOIN term_node term_node_value_5 ON node.vid = term_node_value_5.vid AND term_node_value_5.tid = 2759 
LEFT JOIN term_node term_node_value_6 ON node.vid = term_node_value_6.vid AND term_node_value_6.tid = 2806 
WHERE (node.type in ('image')) AND (node.status <> 0) AND (term_node_value_0.tid = 2930 OR term_node_value_1.tid = 2907 OR term_node_value_2.tid = 2910 OR term_node_value_3.tid = 2921 OR term_node_value_4.tid = 2940 OR term_node_value_5.tid = 2759 OR term_node_value_6.tid = 2806) 
GROUP BY nid 
ORDER BY _random ASC

But obviously it filters for different tids, and also utilizes values from 'votingapi'. So I'm a bit lost here, I can't find a view that resembles the above SQL queries. Is it even save to assume that views is involved, or might this as well be just any other module (meaning: do SQL queries from views have some kind of "fingerprint" to identify their origin)?

What would be the recommended approach to troubleshooting - Systematically mutilate the site and disable all views, then wait for surprises? Or are there some nasty developer tricks for this? ;)

Sidenotice: When digging through my views I discovered that limiting the "items per page" in the view 'image_gallery_terms' would speed up the site noticeably.

Thanks for any help & greetings,
-asb