Performance: Long running SQL-queries slowing down MySQL [#723686]

Hi,

I'm using 'taxonomy_vtn' on several sites, and I like very much what the module is offering; also, the module is giving me very little trouble usually.

However, for weeks it is driving me nuts on one site where it appears to inject long-running SQL statements into MySQL which don't go away and are slowing down the server significantly (from an average load < 1 the load increases to 5 and more on a dual core CPU). After a day or so the server stops responding in a reasonable time and I have to restart MySQL. After a few hours these statements start to re-appear, and the story repeats again and again. When 'taonomy_ctn' is enabled, I have to restart MySQL at least 3-4 times a day to keep the server load below 3.

Example for one of these long-running SQL statements (ripped with the 'mytop' utility):

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(1667,1451)WHERE n.type IN ('artikel','blog','etappe','nachricht','poi','produkt','region','reisebeschreibung','ressource','rezension','seite') AND n.status = 1 AND tn.tid IS NOT NULL AND n.nid NOT IN (4838)GROUP BY n.nidORDER BY cnt DESC, n.created DESC, n.nid DESCLIMIT 10:

I first suspected 'views' to cause these queries, but could eliminate this (simply by disabling everything depending on 'views'). When disabling the three 'taxonomy_vtn' sub-modules, these queries do not show up over a period of several days, so I'm pretty sure that 'taxonomy_vtn' is the root cause. Also I could verify that these long-running queries were causing the high server load (simply by killing the threads after disabling 'taxonomy_vtn').

Since the affected site is definitely not extremely large (~32000 nodes, ~3000 terms) and I'm running 'taxonomy_vtn' on the same server on a larger site with a similar configuration of 'taxonomy_vtn', but without encountering these problems, I think reproducing the problems might be hard. Thus I'm posting this mostly as a reminder that there might be something wrong in the module's SQL statements.

Anyway, I'd love to hear ideas or suggestions what I could try to use the module again, if there are any.

Thanks & greetings, -asb

Comments

Comment #1

AlexisWilke commented 23 February 2010 at 23:34

Hi asb,

I have a similar problem on a site, although that one had 285,000 nodes.

In that case, what I have done was turn off any counting of nodes. The performance boost was incredible (from over 1 minute to generate the index, to just 1 or 2 seconds.) That's a little hard to do your selection to make sure you don't hit the node counting and I wanted to add a red WARNING on all the options that will generate that counting. You probably have that in your module.

Imagine that this order is executed once per term, you have 32,000 nodes x 3,000 terms = 96,000,000 reads for the very complex COUNT(*). Even with the help of a few indexes... that's a lot. (In my case, I had 285,000 nodes x 1,500 terms = 427,500,000 reads...)

Outside of that, the SQL you present here includes changes from other modules, I think. http://drupal.org/project/tac_lite and other modules can inject SQL code to your orders. It looks to me that your order was just like that and thus is probably 3 to 4 times more complex than usual. If you want to see the original orders, they are in that file:

taxonomy_vtn/taxonomy_vtn_overwrites.inc.php

I hope to soon find some time to add the info about that in the settings screen.

Thank you.
Alexis